Since its birth, Oracle's AUM (AutoUndoManagement) often appears to only expand, not shrink (shrink) (usually we can set enough UNDO tablespace size, however
Since its birth, Oracle's AUM (Auto Undo Management) has often experienced scaling-only and shrink (we can usually set enough UNDO tablespace size, however
Since its birth, Oracle's AUM (Auto Undo Management) has often experienced scaling-only, not shrinking (shrink) (usually we can set enough UNDO tablespace size, and then cancel the auto-scaling attribute). In the actual environment, when the undo tablespace is broken up, alarm messages are continuously sent.
1. Confirm the file
SQL> select file_name, bytes/1024/1024 from dba_data_files 2 where tablespace_name like 'undotbs1 '; FILE_NAME--------------------------------------------------------------------------------BYTES/1024/1024 --------------- + ORADG/danaly/datafile/undotbs1.265.60017387527810
2. Check the UNDO Segment status
SQL> select usn, xacts, rssize/1024/1024/1024, hwmsize/1024/1024/1024, shrinks 2 from v $ rollstat order by rssize; usnxacts rssize/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS ------------ ------------------------- -------------------- ---------- donerows selected.
3. Create a New UNDO tablespace
SQL> create undo tablespace undotbs2; Tablespace created.
4. Switch the UNDO tablespace to the new UNDO tablespace.
SQL> alter system set undo_tablespace = undotbs2 scope = both; System altered.
Used hereSpfileYou must note that you have previously recorded such a case:Oracle diagnosis case-Spfile case 1
5. Wait for all undo segment offline of the original UNDO tablespace
SQL> select usn, xacts, status, rssize/1024/1024/1024, hwmsize/1024/1024/1024, shrinks 2 from v $ rollstat order by rssize; usnxacts statusrssize/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS ---------- ----------------- please wait ---------- 140 then there will be too many requests when there are too many STATUSRSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS ---------- --------------- --------------------- ---------------------- ---------- 60 PENDING OFFLINE2.96719362.9671936012 rows selected.
Let's look at it again:
11:32:11 SQL>/USNXACTS STATUSRSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS ---------- --------------- ---------------------- 151 million rows have been written too many rows have been inserted into rows selected. elapsed: 00:00:00. 00
6. Delete the original UNDO tablespace.
11:34:00 SQL> drop tablespace undotbs1 including contents; Tablespace dropped. Elapsed: 00:00:03. 13
Perform the following operations on your own;
-- Procedure
1. Create an undo tablespace
Create undo tablespace undotbs2 datafile '/dev/vgdata01/rlv_ora_log11 ';
Alter tablespace undotbs2 add datafile '/dev/vgdata01/rlv_ora_log12 ';
Alter tablespace undotbs2 add datafile '/dev/vgdata01/rlv_ora_log13 ';
2. Switch the undo tablespace
Alter system set undo_tablespace = undotbs2 scope = both;
3. Delete the original tablespace
Drop tablespace undotbs1 including contents;
4. Original tablespace and data files:
/Dev/vgdata01/rlv_ora_rbs01 UNDOTBS1
,