博客

调整undo表空间

1、查看回滚段状态

看回滚段的数据量，如果不为NULL，则undotbs1表空间不能被删除

SQL> show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

--检查有无活动的事务
SQL> select count(*) from v$transaction;

  COUNT(*)
----------
         0

Executed in 0.049 seconds

--检查活动事务的进程ID
SQL> select p.SPID from v$session s,v$process p where s.PADDR=p.ADDR and s.SID in (select s.sid From v$transaction t,v$session s where t.addr=s.taddr);

SPID
------------------------

Executed in 0.035 seconds

SQL> select sum(bytes) from dba_undo_extents where tablespace_name='UNDOTBS1' and status='ACTIVE';

SUM(BYTES)
----------

Executed in 0.067 seconds

以上都没有数据，说明当前undo没有事务执行。可以重建！

2、创建新的UNDO表空间

创建新的回滚段

create undo tablespace UNDOTBS datafile 'C:\APP\ORADATA\UNDOTBS.dbf' size 8g autoextend on next 4m maxsize 16 g;

--设置新回滚段应用到当前
SQL> alter system set undo_tablespace='UNDOTBS';

System altered


Executed in 0.079 seconds

--原回滚段下线
SQL> alter tablespace undotbs1 offline;

Tablespace altered


Executed in 0.452 seconds

--删除原回滚段的表空间和数据
SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped


Executed in 0.147 seconds

--检查当前回滚段状态
SQL> show parameter undo_tablespace;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      UNDOTBS

回滚段调整完毕！！！