If this were a non-SYSTEM tablespace that contained tables and indexes, you could simply move the tablespace's contents to a new tablespace. However, most of the Data Dictionary objects cannot be moved to a new tablespace. And there is no way to tell Oracle to reorganize the contents of a tablespace in such a manner as to move the tablespace's free space to the end of the tablespace, thus allowing you to resize your datafile to a smaller size.
The best option here is to perform a complete reorganization of your database. You can use the export and import utilities to perform this reorganization. The following steps can be used:
- Take a complete backup of your database just in case.
- Take a full export of your database:
exp userid=system file=my_db.dmp full=y
- Using the DBCA, delete your database.
- Using the DBCA, re-create the database.
- Import the full contents of the database:
imp userid=system file=my_db.dmp full=y
In this reorganization, you will incur downtime and you will be wiping out your database and re-creating it. But that is the best way of reclaiming space in your SYSTEM tablespace.
Dig Deeper on Oracle database backup and recovery
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.