The RAC environment handles the problem of excessive undo tablespace.

In the RAC environment, the undo tablespace is too large:

1. SQL> conn/AsSysdba
2. Connected.
3. SQL>SelectInstance_number, instance_nameFromGv $ instance;
5. INSTANCE_NUMBER INSTANCE_NAME
6. -------------------------------
7. 1 unipsms1
8. 2 unipsms2
10. SQL>SelectInstance_number, instance_nameFromV $ instance;
12. INSTANCE_NUMBER INSTANCE_NAME
13. -------------------------------
14. 2 unipsms2
16. SQL> show parameter undo_tablespace
18. NAMETYPE VALUE
19. -----------------------------------------------------------------------------
20. Undo_tablespace string UNDOTBS2
21. SQL>SelectTs #,Name FromV $ tablespaceWhere Name='Dotbs2';
23. TS #NAME
24. ----------------------------------------
25. 4 UNDOTBS2
26. SQL>SelectTs #,Name, BYTES/1024/1024/1024, CREATE_BYTES/1024/1024/1024FromV $ DATAFILEWhereTs # = 4;
28. TS #NAMEBYTES/1024/1024/1024 CREATE_BYTES/1024/1024/1024
29. ---------------------------------------------------------------------------------------------------------------------
30. 4 + DG01R10/unipsms/datafile/undotbs2.267.746473789 7887.5791 4
31. SQL>CreateBigfile undo tablespace UNDOTBS3 datafile'+ Dg01r10' Size500g autoextendOn Next1g maxsize unlimited;
33. The tablespace has been created.
34. SQL>AlterSystemSetUndo_tablespace = UNDOTBS3 scope = both sid ='Unipsms2';
36. The system has been changed.
37. SQL> show parameter undo_tablespace;
39. NAMETYPE VALUE
40. -----------------------------------------------------------------------------
41. Undo_tablespace string UNDOTBS3
42. SQL>SelectSEGMENT_NAME, TABLESPACE_NAME, STATUSFromDba_rollback_segsWhereTABLESPACE_NAME ='Dotbs2' AndStatus ='Online';
44. No RowsSelected
45. SQL>SelectTs #,Name FromV $ tablespaceWhere Name='Dotbs2';
47. TS #NAME
48. ----------------------------------------------------------------------
49. 4 UNDOTBS2
51. SQL>SelectTs #,Name, BYTES/1024/1024/1024, CREATE_BYTES/1024/1024/1024FromV $ DATAFILEWhereTs # = 4;
53. TS #NAMEBYTES/1024/1024/1024 CREATE_BYTES/1024/1024/1024
54. ---------------------------------------------------------------------------------------------------------------------
55. 4 + DG01R10/unipsms/datafile/undotbs2.267.746473789 7887.5791 4
57. SQL>DropTablespace UNDOTBS2 including contentsAndDatafiles;
59. Tablespace dropped.
61. SQL>SelectTs #,Name FromV $ tablespaceWhere Name='Dotbs2';
63. No RowsSelected
65. SQL>SelectTs #,Name, BYTES/1024/1024/1024, CREATE_BYTES/1024/1024/1024FromV $ DATAFILEWhereTs # = 4;
67. No RowsSelected