Tablespace_回收UNDO表空间_undo tablespace expired 回收-CSDN博客

本文链接：https://blog.csdn.net/java3344520/article/details/7267834

本文详细介绍了如何确认当前UNDO表空间、检查其使用情况、创建新的UNDO表空间、切换表空间以及管理旧表空间的过程。通过调整参数和执行特定SQL命令，可以有效优化数据库性能和资源管理。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

select file_name,bytes/1024/1024 from dba_data_files where tablespace_name like 'UNDOTBS1';

一：确认当期UNDO表空间

SQL> select file_name,bytes/1024/1024 from dba_data_files where tablespace_name like 'UNDO%';

FILE_NAME BYTES/1024/1024
-------------------------------------------------------------------------------------
/usr/app/db-server/ora_base/oradata/gis/undotbs01.dbf 18125

二：检查UNDO表空间当期使用情况

SQL> select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks from v$rollstat order by rssize;

USN XACTS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS
---------- ---------- --------------------- ---------------------- ----------
0 0 0.00035858154296875 0.00035858154296875 0
23 0 0.0450363159179688 0.0450363159179688 0

三：创建新的表空间

SQL> create undo tablespace undotbs2 datafile '/sdb/opt/oracle-file/oradata/undotbs02.dbf' SIZE 1024M autoextend off;

Tablespace created.

四：切换到新的表空间

SQL> show parameter spfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /usr/app/db-server/ora_home/db
s/spfilegis.ora
SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> alter system set undo_tablespace=undotbs2 scope=both;

System altered.

SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2

四：删除老的表空间

SQL> drop tablespace undotbs1 including contents and datafiles;

五：创建新的表空间

SQL> create undo tablespace undotbs1 datafile '/sdb/opt/oracle-file/oradata/undotbs01.dbf' SIZE 1024M autoextend on next 50M;

Tablespace created.

六：切换到新的表空间

SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2

SQL> alter system set undo_tablespace=undotbs1 scope=both;

System altered.

SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1

七：删除老的表空间

SQL> drop tablespace undotbs2 including contents and datafiles;

Tablespace dropped.