Home » RDBMS Server » Server Administration » Database Reorganisation with "alter table move"
Database Reorganisation with "alter table move" [message #206787] Fri, 01 December 2006 08:03 Go to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Hi,

I need to reorganize a database ( 9.2.0.6 ) and don't have much space to do it. Wink

I have thought of using

alter table aaa move;
alter index aaa_idx01 rebuild;
alter index aaa_idx02 rebuild;
......
alter table zzz move;
alter index zzz_idx01 rebuild;


after deleting the not longer needed data.

And then shrink the datafiles with "alter database datafile xxx resize ..." if possible.

I have tried that with a few of the bigger tables, and the free space inside the tablespace is indeed increasing.

What I haven't figured out yet is will the data end up at the *beginning* of the datafiles during the move, so that I will acually be able to shrink them after the move?

Has anyone done this or something similar, or are there other options aside of an export/import ( which would require me to get some extra storage space set up at the location the DB is running, and I don't like that. Wink )

Thanks for your time.
Re: Database Reorganisation with "alter table move" [message #206807 is a reply to message #206787] Fri, 01 December 2006 10:46 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>or something similar
Using deallocate unsed could help to some extent.
Though moving the table/export & import/or table-reorg will be the best method.
The shrink option in 10g is way cool Smile.
Regards~
Re: Database Reorganisation with "alter table move" [message #207636 is a reply to message #206787] Wed, 06 December 2006 06:17 Go to previous message
bwetkstr
Messages: 114
Registered: August 2005
Senior Member
ThomasG wrote on Fri, 01 December 2006 15:03
Hi,

I need to reorganize a database ( 9.2.0.6 ) and don't have much space to do it. Wink

I have thought of using

alter table aaa move;
alter index aaa_idx01 rebuild;
alter index aaa_idx02 rebuild;
......
alter table zzz move;
alter index zzz_idx01 rebuild;


after deleting the not longer needed data.

And then shrink the datafiles with "alter database datafile xxx resize ..." if possible.

I have tried that with a few of the bigger tables, and the free space inside the tablespace is indeed increasing.

What I haven't figured out yet is will the data end up at the *beginning* of the datafiles during the move, so that I will acually be able to shrink them after the move?

Has anyone done this or something similar, or are there other options aside of an export/import ( which would require me to get some extra storage space set up at the location the DB is running, and I don't like that. Wink )

Thanks for your time.



The way I did it , was recreating the tablespace when all objects were moved, and then moved them back.

[Updated on: Wed, 06 December 2006 06:17]

Report message to a moderator

Previous Topic: TRUNCATE COMMAND
Next Topic: exact copy
Goto Forum:
  


Current Time: Sun Dec 04 00:21:32 CST 2016

Total time taken to generate the page: 0.24208 seconds