• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1052
  • Last Modified:

Reduce Oracle Database size

Dear Experts ,

I have oracle 9i database and 10g database in the company which is used for testing purpose. The Database Size is 42 GB and the company decided to remove 2 year old data and  take the export and store it on the DVD and use this server for different purpose.
I have deleted almost 90 % of the data from almost 100 tables but the size of the database is not reduced. The Database size is still the same.
I know that oracle 9i does not reclaim the free space. As far as i know for oracle 10g we can use shrink space or shrink space compact

So i need to reduce the size to as less as possible. So what is the best way to do that ?

Please let me know to reduce the size of the database for both 9i and 10g database ?


1 Solution
you should shrink the database files this link may halp you.

Aaron ShiloChief Database ArchitectCommented:

in 9i : use the ALTER table XXX move;
this will rebuild the table and use only the space it needs.
you alsow must rebuild all indexes after this action.

in 10g: use shrink space compact

after you done that if you want to shrink the physical size of the datafile use :
alter database datafile resize XXmb;
oraclescsaAuthor Commented:
Dear Ashilo,

In Oracle 9i i have the same as suggested and it is successful.

In oracle 10g do i need to rebuild the index after running the shrink space compact command.
Is there any impact if this command is used .
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now