Oracle's restored tablespace UNDO is full of disk space. The specific steps to solve this problem are oracleundo.

Oracle's restored tablespace UNDO is full of disk space. The specific steps to solve this problem are oracleundo.
Zookeeper

There are two main causes:
1. There is a large volume of transactions that allow Oracle Undo to automatically expand, resulting in excessive disk space occupation;
2. There are large transactions that have not been contracted or are not committed by the system;
Note: this problem is normal in ORACLE system management. Pay attention to disk space monitoring during routine maintenance.

UNDO tablespace Introduction
The UNDO tablespace is used to store UNDO data. When you perform DML operations (INSERT, UPDATE, and DELETE), oracle writes the old data of these operations to the UNDO segment before oracle9i, rollback Segment is used to manage UNDO data. from oracle9i, you can use not only rollback segments but also UNDO tablespaces to manage UNDO data. the planning and management of rollback segments are complex. All oracle databases have completely discarded 10 Gb of rollback segments. the UNDO tablespace is used to manage UNDO data.

1. view the system disk status
AIX:/> df-g (Linux: df-h)
Filesystem GB blocks Free % Used Iused % Iused Mounted on
/Dev/undolv 30.00 0.00 100% 9 1%/u01/app/u01/app/oracle/undo

2. view the table space share of the Oracle database
Select a. tablespace_name,
Round (a. maxbytes/1024/1024), 2) "sum MB ",
Round (a. bytes/1024/1024), 2) "datafile MB ",
Round (a. bytes-B. bytes)/1024/1024), 2) "used MB ",
Round (a. maxbytes-a.bytes + B. bytes)/1024/1024), 2) "free MB ",
Round (a. bytes-B. bytes)/a. maxbytes) * 100, 2) "percent_used"
From (select tablespace_name, sum (bytes) bytes, sum (maxbytes) maxbytes
From dba_data_files where maxbytes! = 0
Group by tablespace_name),
(Select tablespace_name, sum (bytes) bytes, max (bytes) largest
From dba_free_space
Group by tablespace_name) B
Where a. tablespace_name = B. tablespace_name
Order by (a. bytes-B. bytes)/a. maxbytes) desc
 
Tablespace_name SumDatafile (MB) Datafile Used Free Precent_used
1 UNDOTBS1 32767.98 30000 29968 2799.98

Or run the following script to check the space occupied by the tablespace in the database:
Select tablespace_name, sum (bytes)/1024/1024/1024 GB
From dba_data_files group by tablespace_name
Union all
Select tablespace_name, sum (bytes)/1024/1024/1024 GB
From dba_temp_files group by tablespace_name order by GB;

3. Find the path and size of the UNDO tablespace.
SQL> select file_name, bytes/1024/1024 from dba_data_files
Where tablespace_name like 'undotbs1'
 
/U01/app/oracle/undo/undotbs01.dbf 30000

4. Check the UNDO Segment status
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
1 0 0 0.000358582 0.000358582 0
2 14 0 0.796791077 0.796791077 735
3 13 0 0.800453186 0.800453186 894
4 12 0 0.805213928 0.805213928 728
5 15 0 1.186126709 1.186126709 922
6 1 0 1.723365784 1.963180542 946
7 3 0 1.732704163 1.977462769 1051
8 5 0 1.978370667 2.228370667 654
9 2 0 2.032501221 2.034454346 707
10 4 0 2.065216064 2.318145752 875
11 11 0 2.100006104 2.100006104 1269
12 8 0 2.630340576 2.700653076 897
13 6 0 2.740814209 2.740814209 1030
14 9 0 2.745697021 2.772064209 1037
15 7 0 2.833526611 2.833526611 1033
16 10 0 3.088363647 3.310592651 989

This restores 16 rollback objects in the tablespace.

5. Create a new temporary UNDO tablespace
You can temporarily create and restore tablespaces in other disk spaces.
SQL>
Create undo tablespace undotbs2
Datafile '/u01/app/oracle/pub/undotbs02.dbf'
Size 10 M autoextend on;

Tablespace created.

6. Switch the UNDO tablespace to the new UNDO tablespace.

SQL> alter system set undo_tablespace = undotbs2 scope = both;
System altered.

7. Verify the restored tablespace of the current database
SQL> show parameter undo

NAME TYPE VALUE
-------------------------------------------------------------
Undo_management string AUTO
Undo_retention integer 900
Undo_tablespace string UNDOTBS2

8. Wait for all undo segment offline of the original UNDO tablespace

Select t. segment_name, t. tablespace_name, t. segment_id, t. status from dba_rollback_segs t;
SEGMENT_NAME TABLESPACE_NAME SEGMENT_ID STATUS
1 SYSTEM 0 ONLINE
2 _ SYSSMU1 $ UNDOTBS1 1 OFFLINE
3 _ SYSSMU2 $ UNDOTBS1 2 OFFLINE
48 _ SYSSMU47 $ UNDOTBS1 47 OFFLINE
49 _ SYSSMU48 $ UNDOTBS1 48 OFFLINE
50 _ SYSSMU49 $ UNDOTBS1 49 OFFLINE
51 _ SYSSMU50 $ UNDOTBS1 50 OFFLINE
52 _ SYSSMU51 $ UNDOTBS1 51 OFFLINE
53 _ SYSSMU52 $ UNDOTBS1 52 OFFLINE
54 _ SYSSMU53 $ UNDOTBS1 53 OFFLINE
55 _ SYSSMU54 $ UNDOTBS1 54 OFFLINE
56 _ SYSSMU55 $ UNDOTBS1 55 OFFLINE
57 _ SYSSMU56 $ UNDOTBS1 56 OFFLINE
58 _ SYSSMU57 $ UNDOTBS1 57 OFFLINE
59 _ SYSSMU58 $ UNDOTBS1 58 OFFLINE
60 _ SYSSMU59 $ UNDOTBS1 59 OFFLINE
61 _ SYSSMU60 $ UNDOTBS1 60 OFFLINE
62 _ SYSSMU61 $ UNDOTBS1 61 OFFLINE
63 _ SYSSMU62 $ UNDOTBS2 62 ONLINE
64 _ SYSSMU63 $ UNDOTBS2 63 ONLINE
65 _ SYSSMU64 $ UNDOTBS2 64 ONLINE
66 _ SYSSMU65 $ UNDOTBS2 65 ONLINE
67 _ SYSSMU66 $ UNDOTBS2 66 ONLINE
68 _ SYSSMU67 $ UNDOTBS2 67 ONLINE
69 _ SYSSMU68 $ UNDOTBS2 68 ONLINE

The rollback segments corresponding to the UNDOTBS1 tablespace are OFFLINE.

9. Delete the original UNDO tablespace

SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

10. You can view the system disk space again:
AIX:/> df-g (Linux: df-h)

To standardize the tablespace and path of the database, the restored tablespace name undotbs1 and path cannot be changed,
You can switch back the installation steps.
1. Create a New UNDO tablespace
You can temporarily create and restore tablespaces in other disk spaces.
SQL>
Create undo tablespace undotbs1
Datafile '/u01/app/oracle/undo/undotbs01.dbf'
Size 10 M autoextend on maxsize 15G;

It is 10 MB at the beginning, and automatic scaling is set. The maximum size is 15 GB.

Tablespace created.

2. Switch the UNDO tablespace to the new UNDO tablespace.

SQL> alter system set undo_tablespace = undotbs1 scope = both;
System altered.

3. Verify the restored tablespace of the current database.
SQL> show parameter undo

NAME TYPE VALUE
-------------------------------------------------------------
Undo_management string AUTO
Undo_retention integer 900
Undo_tablespace string UNDOTBS1

4. Wait for all undo segment offline of the original UNDO tablespace

Select t. segment_name, t. tablespace_name, t. segment_id, t. status from dba_rollback_segs t;
SEGMENT_NAME TABLESPACE_NAME SEGMENT_ID STATUS

 

The rollback segments corresponding to the UNDOTBS2 tablespace are OFFLINE.

5. Delete the UNDO2 tablespace

SQL> drop tablespace undotbs2 including contents and datafiles;

Tablespace dropped.

6. You can view the system disk space again:
AIX:/> df-g (Linux: df-h)

 

Undo_retention: specifies the time (in seconds) for undo to be saved after a transaction is committed. The default value is 900 seconds in ORACLE10g.

GUARANTEE: ensure that the time specified by the undo_retention parameter is valid. This is a new 10g function.

SQL> ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

SQL> ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;

Without the guarantee, ORACLE cannot store undo information for 900 seconds. If the undo tablespace is insufficient, ORACLE will ignore the undo_retention setting and overwrite the previous undo, this may cause ORA-01555 errors. If the undo tablespace space is sufficient, the undo tablespace will be stored for a long period of time until the undo tablespace reaches maxsize, And the undo information will not be overwritten at this time, ORACLE will overwrite the oldest undo information.

ORACLE recommends that you set maxsize for the datafile In the undo tablespace instead of auto-scaling. If ORACLE has the auto-scaling capability, the old undo will not be overwritten, in the future, the undo tablespace will become larger and larger until the disk space is exhausted.

With the guarantee of guarantee, ORACLE will ensure that the undo information can be saved to the value set by undo_retention before being overwritten. If many things are executed at the same time, the undo tablespace will be exhausted, so that thing will fail, will report a ORA-30036 error, so use guarantee must be careful, if you have to use guarantee, then try to set the undo tablespace a little larger.

At the beginning of Oracle10g, if you set UNDO_RETENTION to 0, Oracle Enables automatic adjustment to meet the needs of the longest running query. Of course, if the space is insufficient, Oracle will meet the maximum allowed long-time queries without manual adjustments.

The oracle undo tablespace is deleted, and the database cannot be started. How can this problem be recovered?

First, the undo tablespace is full of normal, and oracle will naturally reuse or expand it. Generally, you don't need to worry about it.
Now, you need to set the undo tablespace to manual, start the database, and create a new undo tablespace. Set the new one to the default one.
Assume that your library is in the mounted state.
1. Create a PFILE (update if it already exists)
SQL> create pfile from spfile;
2. Shut down the database
SQL> shutdown immediate
3. Find a name under your $ ORACLE_HOME/dbs directory
Init <database>. ora file, change one line of undo_management = AUTO
Undo_management = MANUAL
If not, enter a line at the end of the file.
4. Connect to the database as sysdba
SQL> connect "/as sysdba"
Start the database with the modified File
SQL> startup pfile = <full path and name of the file just now>
This step is the most critical. If it succeeds, it will be okay later.
5 drop the original tablespace
SQL> drop tablespace <name of the original undo tablespace> including contents;
6. Create a New undo tablespace.
SQL> create UNDO tablespace undotbs2 datafile '/u01/app/oracle/oradata/orcl/undotbs02.dbf' size 100 M autoextend on;
7. Shut down the database,
SQL> shutdown immediate
Set UNDO_MANAGEMENT = AUTO in the Start init file
And UNDO_TABLESPACE = UNDOTBS2
8. Perform Step 4 again.
9. Update spfile
SQL> create spfile from pfile;
10 shut down the database and restart normally
SQL> shutdown immediate
SQL> startup
11 go online and teach you to delete the undo location and scold it. Take it for granted. There is no reason to do this
12 ask your engineers to study oracle Training

Steps 5th in the preceding steps may cause problems. I'm not sure... However, even if Step 1 fails, the problem should not be serious.

How to delete the space occupied by oracle undo tablespace

After the system has been in use for many years, with frequent data operations, the oracle undo tablespace keeps increasing, occupying disk space. To clear the space, follow these steps to complete the replacement operation. 1. Create a New undo TABLESPACE undotbs2CREATE undo tablespace UNDOTBS2 DATAFILE '/u01/app/oracle/oradata/mydb/comment 'size 512 m reuseautoextend on next 64 m maxsize unlimited retention noguarantee blocksize 8 k flashback on; replace the red part with the actual oracle database Path 2. Switch the system tablespace alter system set undo_tablespace = UNDOTBS2 scope = both; switch the current oracle undo tablespace to undotbs2. 3. Restart the database and log on to the database through the command line, [root $ smserver] sqlplus/nolog [root $ smserver] conn/as sysdba [root $ smserver] shutdown immediate; [root $ smserver] startup4: Delete the original undo content drop tablespace undotbs1 including contents and datafiles; 5. Repeat the third operation and restart the database. 6. manually delete the database file corresponding to the original undotbs.