Re: How to shrink a tablespace?

mgraz_at_netcom.com (Michael Graz) wrote:
>Greetings -
>
>I have inherited an Oracle database that is taking up far too much disk
>space. I would like to shrink the tablespaces a bit. Any thoughts on
>the easiest/safest way to accomplish this?
>
>I'm guessing that I'll have to export a tablespace at a time, recreate
>it using less space, and then import the data. Is there a safe way to
>export everything in a single tablespace and then restore it?
>
>Thanks to any suggestions - Mike :)
>
>----------------
>Michael Graz
>mgraz_at_netcom.com
>----------------
>
>

Mike,
This can be a very though one. I hope your database has tables in one tablespace, indexes in another, and so on. The only thing that gets exported when you're not exporting the complete database are tables. I can imagine two ways to do it. The first one is by executing a nasty trick:
Make a complete export of the database and make sure to record this fact in the export tables. The export command has a switch for this. Then make sure to make any silly change to every table in that tablespace (you can find out which tables are in the tablespace by executing, select owner, segment_name from sys.dba_segments where segment_type = 'TABLE' and tablespace_name = '<Your tablespace'). The next action is to make an incremental export of the same database. Only the tables in your tablespace will be exported, because those are the only tables changed. Next, drop the tablespace, recreate it and pre-create all objects in your tablespace, by executing the appropiate create scripts (I hope you have them, if not you can extract them from your export by importing it with 'show=yes log=<any file name>). Finally import the data.

Second solution replaces the incremental export, by creating a parameter file for the export with sqlplus commands. The select you have to use is similar to the one on dba_segments above. Recreating the tablespace and importing the data is done in the same way.

One warning: the compress extents switch in the export command only causes export to summarize the currently used space. If you're waisting space, compress extents won't help you. You have to gather tablestatistics by executing analyze table commands and query the user_tables view to see how many empty blocks there are.

Third solution: wait until Oracle 7.2 is available (soon). According to an Oracle magazine, it will allow you to shrink tablespaces, probably in the same fashion as it is shrinking rollback segments now.

Hope this helps

Sybrand Bakker
Senior IS Analyst
Bentley Systems Europe

Any opinions expressed here are my own Received on Thu Aug 03 1995 - 00:00:00 CEST