oracle undo表空间

undo表空间用于存放undo数据，当执行DML操作（insert、update、delete）时，oracle会将这些操作的旧数据写入到undo段。

undo数据的作用

1.回退事务

当执行DML操作修改数据后，旧数据被存放在undo段中。只要数据为提交、回滚段未写满或者回滚段为超时的情况下，旧数据都能被回滚回来。

2.读一致性

通过DML操作后的数据没有提交之前，其他用户读取的数据都是回滚段里面的旧数据。

使用undo参数

1.undo_management

该初始化参数用于指定undo数据的管理方式。如果要使用自动管理模式，必须设置为auto，如果使用手工管理模式必须设置该参数为manual，使用自动管理模式时，oracle会使用undo表空间管理，使用手工管理模式时，oracle会使用回滚段管理undo数据。需要注意,使用自动管理模式时,如果没有配置初始化参数UNDO_TABLESPACE,oracle会自动选择第一个可用的UNDO表空间存放UNDO数据,如果没有可用的UNDO表空间,oracle会使用SYSTEM回滚段存放UNDO记录,并在ALTER文件中记载警告。

2,UNDO_TABLESPACE

该初始化参数用于指定例程所要使用的UNDO表空间,使用自动UNDO管理模式时,通过配置该参数可以指定例程所要使用的UNDO表空间.

在RAC(Real Application Cluster)结构中,因为一个UNDO表空间不能由多个例程同时使用,所有必须为每个例程配置一个独立的UNDO表空间.

3,UNDO_RETENTION

该初始化参数用于控制UNDO数据的最大保留时间,其默认值为900秒,从9i开始,通过配置该初始化参数,可以指定undo数据的保留时间,从而确定倒叙查询特征(Flashback Query)可以查看到的最早时间点.

手工管理回滚段的规划：

SQL> show parameter  undo;

NAME                                 TYPE        VALUE

———————————— ———– ——————————

undo_management                string      AUTO

undo_retention                       integer    900

undo_tablespace                    string      UNDOTBS1

SQL> show parameter  transactions;

NAME                                 TYPE        VALUE

———————————— ———– ——————————

transactions                         integer     187             ———-系统准备支持的事务连接数量。

transactions_per_rollback_segment    integer     5       ————–每个回滚段支持的事务连接数量。回滚段数=187/5

SQL> show  parameter rollback;

NAME                                 TYPE        VALUE

———————————— ———– ———-

fast_start_parallel_rollback         string      LOW

rollback_segments                      string ———————-设置回滚段的数量

transactions_per_rollback_segment    integer     5

错误原因：

SQL语句执行时间太长，或者UNDO表空间过小，或者事务量过大，或者过于频繁的提交，导致执行SQL过程中进行一致性读时，SQL执行后修改的 前镜像（即UNDO数据）在UNDO表空间中已经被覆盖，不能构造一致性读块（CR blocks）。  这种情况最多。

解决办法：

第1种情况解决的办法：

（1）增加UNDO表空间大小

（2）增加undo_retention 时间，默认只有15分钟

alter  system set undo_retention=14400 ；

undo_retention这个值可以根据情况调大一些。

（3）优化出错的SQL，减少查询的时间，首选方法

（4）避免频繁的提交

还有一种情况，可能是oracle BUG，官网说法ORA-01555BUG比较多，

其他参考：

https://www.cnblogs.com/Richardzhu/archive/2013/03/25/2981610.html

查看表空间使用情况
SELECT a.tablespace_name,
ROUND (a.total_size) “total_size(MB)”,
ROUND (a.total_size) – ROUND (b.free_size, 3) “used_size(MB)”,
ROUND (b.free_size, 3) “free_size(MB)”,
ROUND (b.free_size / total_size * 100, 2) || ‘%’ free_rate
FROM ( SELECT tablespace_name, SUM (bytes) / 1024 / 1024 total_size
FROM dba_data_files
GROUP BY tablespace_name) a,
( SELECT tablespace_name, SUM (bytes) / 1024 / 1024 free_size
FROM dba_free_space
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+);

TABLESPACE_NAME                total_size(MB) used_size(MB) free_size(MB) FREE_RATE
—————————— ————– ————- ————- —————————————–
SYSAUX                                    900       835.687        64.313 7.15%
UNDOTBS1                                24576        53.875     24522.125 99.78%
USERS                                       5         1.312         3.688 73.75%
SYSTEM                                   4170      4160.687         9.313 .22%
USER_DATA                                 150       105.062        44.938 29.96%

计算所需undo表空间的大小:

1.计算业务高峰期每秒产生undo数据块的个数
SQL> select max(undoblks / ((end_time – begin_time)*24*3600)) from v$undostat;

MAX(UNDOBLKS/((END_TIME-BEGIN_
——————————
11.305

2.得到undo数据块在undo表空间中可以保留的最长时间
SQL> show parameter undo_retention;

NAME                                 TYPE        VALUE
———————————— ———– ——————————
undo_retention                       integer     86400

3.得到数据块大小
SQL> show parameter db_blo

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_block_buffers                     integer     0
db_block_checking                    string      FALSE
db_block_checksum                    string      TYPICAL
db_block_size                        integer     8192

4.将以上三者的数据相乘就是所需undo表空间的大小数
SQL> select (11.305*86400*8192)/1024/1024/1024 undoTablespace_GB from dual;

UNDOTABLESPACE_GB
—————–
7.4520263671875

发现undo表空间不够的时候，赶紧增加undo表空间的大小，执行语句如下：
alter tablespace undotbs1 add datafile ‘/u01/database/instance_name/undotbs02.dbf’ size 100M autoextend on next 128M maxsize 24G;
alter tablespace undotbs1 add datafile ‘/u01/database/instance_name/undotbs03.dbf’ size 100M autoextend on next 128M maxsize 24G;
alter tablespace undotbs1 add datafile ‘/u01/database/instance_name/undotbs04.dbf’ size 100M autoextend on next 128M maxsize 24G;

–查看会话数
select count(*) from v$session;

–查看进程数
select count(*) from v$process;

–查看数据库的并发连接数
select * from v$session where status=’ACTIVE’;

–查看当前数据库建立的会话
SELECT SID,SERIAL#,USERNAME,PROGRAM,MACHINE,STATUS FROM V$SESSION;

–查看数据库允许的最大连接数
SELECT value FROM V$PARAMETER WHERE NAME=’processes’

–查看数据库允许的最大会话数
SELECT value FROM V$PARAMETER WHERE NAME=’sessions’
–查看后台正在运行着的sql语句
select a.program,b.spid,c.sql_text from v$session a,v$process b,v$sqlarea c where a.paddr=b.addr and a.sql_hashvalue=c.hash_value and a.username is not null;

查询所有数据库的连接数
select schemaname,count(*)from v$session group by schemaname;

查询终端用户使用数据库的连接情况。
select osuser,schemaname,count(*) fromv $session group by schemaname,osuser;

查看当前不为空的连接
select * from v$session where username is not null

查看不同用户的连接数
select username,count(username) from v$session where username is not null group by username

转自：https://blog.csdn.net/qiuzhi__ke/article/details/78937078