Undone space, once allocated, is available for reuse but is not released to the operating system. The best way to shrink the undo tablespace is to switch to a new undo tablespace and delete the old undo tablespace.
< li>Errors you may get: ORA-03297: File contains used data beyond requested RESIZE value.
If the undo retention is set high, you must allocate more space for the undo to retain information for the time limit set in the undo retention. Only stale transactions based on undo retention are cleaned up and deleted.
Delete unused UNDO tablespace
First, get a list of all the data files for your UNDO tablespace. select file_name from dba_data_files where tablespace_name = ‘UNDOTBS’; Next, delete these data files one by one using alter tablespace drop datafile command as shown below. Now delete the UNDO tablespace.
Question: What can I do to keep my UNDO tablespace from growing? Answer: The size of the UNDO tablespace is determined by the parameter undo_retention. f is nondefault and set to a large value to allow for a large flash recovery area. Undoing the tablespace will keep the old data up to that time.
The basic syntax for this command is: change database name resize data file ‘filename’; where name is the name of the database, file_name is the name of the file, and size is the new size to create this file. We can see this resizing both in the dba_data_files table and from the server.
Oracle Segment Shrink Space option will consolidate the fragmented free space below the upper bound and compress the data. The high water mark is pushed back and the reclaimed space is freed. After the shrink operation, the data in the segment is contained in a smaller number of blocks.
For lobs, the database tries to respect the minimum undo retention period specified by undo_retention. However, if disk space is running low, unexpired lob undoes information may be overwritten. You can determine the current retention period by querying the tuned_undoretention column of the v$undostat view.
During database recovery, undo records are used to undo any uncommitted changes applied to the data files from the redo log. Undo records provide read consistency by preserving the data’s before image for users accessing the data while another user is modifying it.
Switching undo tablespaces
You can switch from one undo tablespace to another. Because the UNDO_TABLESPACE initialization parameter is a dynamic parameter, the ALTER SYSTEM SET statement can be used to assign a new undo tablespace.
The UNDO tablespace should not be configured with autoextend as this will affect the normal operation of the UNDO mechanism. If UNDO errors occur regularly, the UNDO tablespace is very likely too small and should be expanded manually in known steps until the errors disappear.
Manually resizing a data file
For a big file tablespace, you can use the ALTER TABLESPACE statement to resize a data file. You must not add a data file to a bigfile tablespace. You can reclaim unused database space by manually reducing the size of data files.
If we delete the rows from the table using the DELETE command, the extents associated with the table are not freed and the table still contains them, whereas when using TRUNCATE with the DROP STORAGE clause (which is the default ), the space is released back to Tablespace.