shrink a database in oracle 11g

2
votes

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?

2
oracleoracle11g
there is a COMPRESS EXTENTS = Y option in EXP - not actually sure how that moved forward to the data pumps. - Randy
@Randy, I did not understand what you are trying to say. Can you please elaborate a little more. - samar
Compress extents is an export/import option that does not impact the size of the underlying data files. The compress extents option specifies that, upon import, all the table data will be placed in the initial extent. - xelco52

2 Answers

7
votes

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
0
votes

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