This is an old revision of the document!

Reduce_tablespace_size_using_Import/Export_method

more ideas here

Situation:

Due to much activity in the tablespace, lots of duplicate imports etc, tablespace has grown much bigger than the data in it.

Goal:

To bring down the tablespace ( datafile ) size to a reasonable level.

Steps:

Export the schema objects stored in the tablesace (datafile)
Drop those schema objects
Purge the recyclebin
Coalesce the tablespace
Resize the datafile
Import the objects

Check space in tablespaces

0@@

Here we see that the tablespace is 156Gb but most of it is free space (156 - 125 = 31Gb used).

Prepare a script to clean out user objects (after export)

1@@

Export everything in the tablespace (only 1 schema in this case)

2@@

Run the clean out script

3@@

Delete any rubbish lying around

4@@

Gather together the empty space

5@@

Reduce the datafile size

6@@

Import the tablespace contents

7@@

Recompile any invalid objects

8@@

Check our new space situation

9@@

Here we see we have reduced the tablespace size down from 156Gb to about 30Gb and almost all the free space has gone - only 1.5Gb free space - but autoextend is on so it can grow.

10@@

And finally we see the datafile has been reduced to 30Gb on the filesystem<br /> <br /> Job done.

dbawiki

Table of Contents