Compressing/Compacting Tablespace and Data Files

5 pts.
Tags:
Oracle
Oracle 10g
Can some one give me some advice on how to best compress or compact a Tablesapce so that I can recover disk space? Many thanks, Sam

Software/Hardware used:
Oracle 10G
ASKED: March 2, 2012  10:06 PM
UPDATED: March 5, 2012  9:59 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Assuming that you are specifically asking how to shrink a tablespace and its related datafile(s) due to over-allocation/under-utilization, then you can run the command “alter database datafile ‘/directory-name/filename.dbf resize ???M;” where “directory-name” is the directory name/filesystem path that the datafile resides in, “filename.dbf” is the name of the datafile, and “???M” or “???G” is the size that you want to resize the datafile to. Note that you cannot shrink a datafile below the highwater mark of active blocks and will receive an error telling you that you cannot shrink the file, and if this happens you will need to export all of the tables out of the tablespace, truncate the tables, shrink the datafile(s), and re-import.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following