Tablespace_回收UNDO表空间

左手的ㄟ右手 2022-06-15 05:07 225阅读 0赞

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.