RAC環境處理undo表空間過大的問題

RAC環境處理undo表空間過大的問題：

1.SQL> conn /as sysdba 
2.Connected. 
3.SQL> select instance_number,instance_name from gv$instance;  4. 
5.INSTANCE_NUMBER INSTANCE_NAME 
6.--------------- ----------------   7.              1 unipsms1 
8.              2 unipsms2 
9. 
10.SQL> select instance_number,instance_name from v$instance;  11. 
12.INSTANCE_NUMBER INSTANCE_NAME 
13.--------------- ----------------   14.              2 unipsms2 
15. 
16.SQL> show parameter undo_tablespace 
17. 
18.NAME                                 TYPE        VALUE  19.------------------------------------ ----------- ------------------------------   20.undo_tablespace                      string      UNDOTBS2 
21.SQL> select ts#,name from v$tablespace where name = 'UNDOTBS2';  22. 
23.       TS# NAME  24.---------- ------------------------------   25.         4 UNDOTBS2 
26.SQL> select ts#,name,BYTES/1024/1024/1024,CREATE_BYTES/1024/1024/1024 from V$DATAFILE where ts# = 4;  27. 
28.       TS# NAME                                                         BYTES/1024/1024/1024 CREATE_BYTES/1024/1024/1024  29.---------- ------------------------------------------------------------ -------------------- ---------------------------   30.         4 +DG01R10/unipsms/datafile/undotbs2.267.746473789                        7887.5791                           4 
31.SQL> create bigfile undo tablespace UNDOTBS3 datafile '+DG01R10' size 500g autoextend on next 1g maxsize unlimited;  32.   
33.表空間已創建。 
34.SQL> alter system set undo_tablespace=UNDOTBS3 scope=both sid='unipsms2';  35.   
36.系統已更改。 
37.SQL> show parameter undo_tablespace; 
38. 
39.NAME                                 TYPE        VALUE  40.------------------------------------ ----------- ------------------------------   41.undo_tablespace                      string      UNDOTBS3 
42.SQL> select SEGMENT_NAME,TABLESPACE_NAME,STATUS from dba_rollback_segs where TABLESPACE_NAME = 'UNDOTBS2' and status = 'ONLINE';  43. 
44.no rows selected  45.SQL> select ts#,name from v$tablespace where name = 'UNDOTBS2';  46. 
47.       TS# NAME  48.---------- ------------------------------------------------------------   49.         4 UNDOTBS2 
50. 
51.SQL> select ts#,name,BYTES/1024/1024/1024,CREATE_BYTES/1024/1024/1024 from V$DATAFILE where ts# = 4;  52. 
53.       TS# NAME                                                         BYTES/1024/1024/1024 CREATE_BYTES/1024/1024/1024  54.---------- ------------------------------------------------------------ -------------------- ---------------------------   55.         4 +DG01R10/unipsms/datafile/undotbs2.267.746473789                        7887.5791                           4 
56. 
57.SQL> drop tablespace UNDOTBS2 including contents and datafiles;  58. 
59.Tablespace dropped. 
60. 
61.SQL> select ts#,name from v$tablespace where name = 'UNDOTBS2';  62. 
63.no rows selected  64. 
65.SQL> select ts#,name,BYTES/1024/1024/1024,CREATE_BYTES/1024/1024/1024 from V$DATAFILE where ts# = 4;  66. 
67.no rows selected 
 

服務器租用/服務器托管中國五強！虛擬主機域名注冊頂級提供商！15年品質保障！--億恩科技[ENKJ.COM]