重建undo表空间

最新推荐文章于 2021-05-24 21:26:25 发布

scliu

最新推荐文章于 2021-05-24 21:26:25 发布

阅读量155

点赞数

本文链接：https://blog.csdn.net/scliu/article/details/82322294

版权

1.确认文件

SQL> select file_name,bytes/1024/1024 from dba_data_files   2  where tablespace_name like 'UNDOTBS1';  FILE_NAME -------------------------------------------------------------------------------- BYTES/1024/1024 --------------- +ORADG/danaly/datafile/undotbs1.265.600173875           27810

2.检查UNDO Segment状态

SQL> select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks   2  from v$rollstat order by rssize;         USN      XACTS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024    SHRINKS ---------- ---------- --------------------- ---------------------- ----------          0          0            .000358582             .000358582          0          2          0            .071517944             .071517944          0          3          0             .13722229              .13722229          0          9          0            .236984253             .236984253          0         10          0            .625144958             .625144958          0          5          1            1.22946167             1.22946167          0          8          0            1.27175903             1.27175903          0          4          1            1.27895355             1.27895355          0          7          0            1.56770325             1.56770325          0          1          0            2.02474976             2.02474976          0          6          0             2.9671936              2.9671936          0  11 rows selected.

3.创建新的UNDO表空间

SQL> create undo tablespace undotbs2 datafile '/opt/oracle/oradata/oralnx/undotbs02.dbf' size 300m AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED;

Tablespace created.

4.切换UNDO表空间为新的UNDO表空间
SQL> alter system set undo_tablespace=undotbs2 scope=both;

System altered.

5.等待原UNDO表空间所有UNDO SEGMENT OFFLINE

SQL> select usn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks   2 from v$rollstat order by rssize;          USN      XACTS STATUS          RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024    SHRINKS ---------- ---------- --------------- --------------------- ---------------------- ----------         14          0 ONLINE                     .000114441             .000114441          0         19          0 ONLINE                     .000114441             .000114441          0         11          0 ONLINE                     .000114441             .000114441          0         12          0 ONLINE                     .000114441             .000114441          0         13          0 ONLINE                     .000114441             .000114441          0         20          0 ONLINE                     .000114441             .000114441          0         15          1 ONLINE                     .000114441             .000114441          0         16          0 ONLINE                     .000114441             .000114441          0         17          0 ONLINE                     .000114441             .000114441          0         18          0 ONLINE                     .000114441             .000114441          0          0          0 ONLINE                     .000358582             .000358582          0         USN      XACTS STATUS          RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024    SHRINKS ---------- ---------- --------------- --------------------- ---------------------- ----------          6          0 PENDING OFFLINE             2.9671936              2.9671936          0  12 rows selected.

select * from v$rollname

6.删除原UNDO表空间
SQL> drop tablespace undotbs1 including contents and datafiles;