OraFAQ Forum: Server Administration » Database Reorganisation with "alter table move"

Home » RDBMS Server » Server Administration » Database Reorganisation with "alter table move"

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Database Reorganisation with "alter table move" [message #206787]

Fri, 01 December 2006 08:03

ThomasG
Messages: 3212
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.

Report message to a moderator

Re: Database Reorganisation with "alter table move" [message #206807 is a reply to message #206787]

Fri, 01 December 2006 10:46

Mahesh Rajendran
Messages: 10707
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~

Report message to a moderator

Re: Database Reorganisation with "alter table move" [message #207636 is a reply to message #206787]

Wed, 06 December 2006 06:17

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;

)

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 05:49:27 CDT 2024