收缩UNDO表空间，解决ora-01628

1.首先查看undo情况

SQL> SELECT * FROM dba_data_files where tablespace_name LIKE '%UNDO%';
 
FILE_NAME                                                                           FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS    RELATIVE_FNO AUTOEXTENSIBLE   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_STATUS
-------------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ------------ -------------- ---------- ---------- ------------ ---------- ----------- -------------
+DG01DATA/......                                            3 UNDOTBS1                       2232418304    1362560 AVAILABLE         1024 YES            7036874412 4294967293          320 2232313446     1362496 ONLINE

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

2.然后创建新的undo

SQL> create bigfile undo tablespace UNDOTBS2 datafile '+DG01DATA' size 20g autoextend on next 100M maxsize unlimited;

已创建

3.查看状态

SQL> SELECT tablespace_name FROM dba_data_files where tablespace_name LIKE '%UNDO%';
 
TABLESPACE_NAME
------------------------------
UNDOTBS1
UNDOTBS2
 
SQL> 

4.切换表空间

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> 

5.查看是否可以删除了，如果查询为空，就表示事务已经处理完成，可以删除

SQL> select extent_id, status from dba_undo_extents where tablespace_name='UNDOTBS1' and status <> 'EXPIRED';
 
 EXTENT_ID STATUS
---------- ---------
         1 UNEXPIRED
         0 UNEXPIRED
         0 UNEXPIRED

6.或者执行下面命令，看是否offline

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

7.删除原undo表空间

SQL>drop tablespace undotbs1 including contents;