CODEWITHSUNDEEP
oracle-databaseoracle11g
samar
samar

Reputation: 5201

shrink a database in oracle 11g

I am not a database administrator by any means so I might be wrong in stating some of the things here.

In SQL Server, when we add a large amount of data in the database and then when we delete it, the size of the data files (.mdf file) or database (or whatever it is called) does not get reduced to the original size. We need to shrink it.

Do the same fundamentals work in Oracle? If yes then how should I go about shrinking an Oracle 11g database?

Upvotes: 2

Views: 24992

Answers (2)

Mahmoud Samir
Mahmoud Samir

Reputation: 1

I have a similar case for a table size 28 Gb, when I deleted data from it, I saw no change occurred.

after I created another table from this table and dropped the first one, the size shrunk by 10 Gb to be 18Gb.

CREATE TABLE NEW_TBL AS SELECT * FROM OLD_TBL

Upvotes: 0

xelco52
xelco52

Reputation: 5347

Full explanation from the Oracle Documentation: Reclaiming Wasted Space. For the short version:

"You can shrink space in a table, index-organized table, index, partition, subpartition, materialized view, or materialized view log. You do this using ALTER TABLE, ALTER INDEX, ALTER MATERIALIZED VIEW, or ALTER MATERIALIZED VIEW LOG statement with the SHRINK SPACE clause."

So, after running the Oracle Segment Advisor to recommend areas to shrink, something like the following will shrink space in a table named mytable. 

SQL> alter table mytable enable row movement;
Table altered

SQL> alter table mytable shrink space;
Table altered

Upvotes: 7

Related Questions