Shrink the Oracle tablespace physical file

In Oracle, there are often situations in which a table space is too large due to misoperation. The Delete method does not clear the high watermark, after using truncate, although the high watermark has been cleared, but the expanded table space does not shrink, it should be reduced by the following method:

1. Find the data file ID for the table space, taking the users table space as an example:

Select file_id from dba_data_files where tablespace_name = ' USERS '

2. Allow table space to shrink (dissolve table space):

Alter TABLESPACE users COALESCE;

3. Query the table space for the size of the table, to determine which table to operate:

SELECT * from dba_segments where tablespace_name= ' USERS ' and segment_type= ' TABLE '

4. Allow the table to move rows (some tables cannot be truncate, only delete)

ALTER TABLE test row movement;

5. High watermark Recovery for the table:

ALTER TABLE test shrink space;

6. Finally, the data file ID of the first step is compressed (resize), the size must be greater than the size used:

ALTER DATABASE datafile 4 resize 2000M;

After the above operation, the enlarged table space can be shrunk back to normal size, the pro-test is available!

Shrink the Oracle tablespace physical file