Clean #Oracle schema used space after an archival

When archiving a big database it is very important to free up space after that.

After running the archival scripts that basically deleted old data from the production database, some extra steps were necessary to actually clean up the used space in the database.

To first check how much space we still use after deleting the data we can execute with the mySchema user:

SELECT SUM(bytes)/1024/1024/1024 as GB FROM user_segments;

This will return all the space in GB used by the current schema.

STEP 1: Clean up the deprecated indices

After removing a lot of information from the database obviously the indices will no longer refer to existing data and will also refer to data already deleted. We need to rebuild all the indices.

The following query will generate a set of alter statements that should be run to rebuild all the indices from “mySchema”

 select 'alter index '||owner||'.'||index_name ||' rebuild online nologging;'
        from dba_indexes
            where owner=upper('mySchema');

After the rebuild statements are executed run again:

SELECT SUM(bytes)/1024/1024/1024 as GB FROM user_segments;

You will notice that less space is used.

But wait this is still not enough 🙂

STEP 2: Clean up the Recycle Bin

When deleting data sometimes some segments are just moved to some special $BIN… segments that are part of the recycle bin.

To check the recycle bin just execute:

show recyclebin;

You will notice a lot of $BIN segments still there

Then to actually free up space purge the Recycle Bin.

purge recyclebin;

This will free up space from the schema for good.

In the end check again the space used by the schema

SELECT SUM(bytes)/1024/1024/1024 as GB FROM user_segments;

For a more detailed information for each individual segment execute:

SELECT SUM(bytes)/1024/1024/1024 as GB, segment_name
  FROM user_segments group by segment_name order by GB desc;