Downsizing Database

[netbar]

I have inherited a historical database which is in excess of 150gb it has partitioned tables and indexes, however it is severly oversized and is using about 30GB. What is the best plan of action or options I have in order to downsize this database.

Thanks in advance

[SANJAY_G]

Resize datafiles if possible.
If resizing is not possible then Export/Import.
Consider using LMT.

Sanjay

[netbar]

Resizing datafiles can be dangerous if there is any existing row chaining happening.

Perhaps export/import but that could provce slow and laborious.

Any other ideas guys?

[SANJAY_G]

I don't think resizing datafile will impact row chaining. How did you conclude that it will affect row chaining?
If there is any row chaining, fix it first.

Sanjay

[netbar]

You are making the assumption that the space being used is at the beginning of the file. I must check DBA_EXTENTS to see where the extents in the file are located. Looking at BLOCK_ID and BLOCKS to see the starting block and the total number of blocks in the extent respectively.

Resizing can be problematic..

[tycho]

Hi Netbar,

If you are on 8i you can move tables (or table partitions) to new locally managed tablespaces and rebuild your indexes in new locally managed tablespaces. Drop your old tablespace when all contents are transfered.

This will reorg your database.

Tycho

[netbar]

Hi Tycho,
That sounds like a good solution. Is it something you've done yourself

[netbar]

Also,

Are you able to move partitioned tables and indexes

[tycho]

Netbar,

Yes I have moved tables and partitions and rebuild partitioned indexes.

Point off caution could be that if you move a table (or table partition) the index(es) will be getting the status unusable.

A strategy can be:
- move the small tables first using a small initial extent size
- rebuild the unusable indexes with a smal initial

- move the medium size tables/partitions
- rebuild the unusable indexes

and so on.

Don't start with rebuilding all your indexes and later move the tables because you have to rebuild them again.

HTH,

Tycho