Q
Problem solve Get help with specific problems with your technologies, process and projects.

Encountering error trying to resize datafile

The size of our system datafile is around 5 GB. In this datafile there were around 200 users. I have already transferred all these users to another tablespace, but when I pass the "alter database datafile resize" command, it displays an error message that I cannot resize the datafile.

The size of our system datafile is around 5 GB. In this datafile there were around 200 users. I have already transferred all these users to another tablespace, but when I pass the "alter database datafile resize" command, it displays an error message that I cannot resize the datafile; the data is beyond the size. Please tell me how to reduce the system datafile.

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:

  1. Take a complete backup of your database just in case.
  2. Take a full export of your database:
       exp userid=system file=my_db.dmp full=y
    
  3. Using the DBCA, delete your database.
  4. Using the DBCA, re-create the database.
  5. 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.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close