database reorg doubt

[gnagesh]

Hi

I am going reorg one of my production database on test server first using exp/imp utility. I would like to do full exp.

Now my doubt is whether to use compress=Y or not. Please advice.


Thanks in advance.

[pravin]

U can use the compress option.

U can also take a full cold backup and restore it into a new db.

or
U can take a full export backup with option full=y
Create the tablespaces with the same name in the new db.
Create the users with appropriate privelege.
run the import command by giving fromuser , touser option.

Good luck

If u need anything let us know

[gnagesh]

Originally posted by pravin
U can use the compress option.

U can also take a full cold backup and restore it into a new db.

or
U can take a full export backup with option full=y
Create the tablespaces with the same name in the new db.
Create the users with appropriate privelege.
run the import command by giving fromuser , touser option.

Good luck

If u need anything let us know

Thanks Pravin,

The main reason, I am going for this reorg is to clear fragmentation. Most of my tablespaces are above 50% fragmented.

If I take cold backup and restore it, it won't help me in de-fragment my tablespaces. And If I take export with option compress=Y, my objects storage parameters will be changed. Right?

So, What I am planning to do is, I created schema scripts using TOAD. I will drop and re-create the tablespaces, and then create the tables by using TOAD generated schema sequel script. And then import without indexes, and create indexes, using TOAD script again.

I am experimenting this on my test server first. If every thing smooth, I will implement the same on production server soon.

I can not use LMT because of my Oracle version(8.1.5).

Any comments from experts please..


Thanks

[pando]

what do you mean by fragmantation

also if you think you cannot use LMT there is still this storage parameter

MINIMUM EXTENT

which ensures your extent are multiple of size specified

CREATE TABLESPACE tabspace_3
DATAFILE 'tabspace_file5.dbf' SIZE 2M
MINIMUM EXTENT 64K
DEFAULT STORAGE (INITIAL 128K NEXT 128K);

extents will be multiple of 64K hence reusing free space efficiently

[abhaysk]

If Fragmentation is the major concern, then instead of full DB EXP/IMP,

* Tables -> Move Table to new TS ( LMT ).

* Indexes -> Rebuild or Recreate in new TS ( LMT ).

* TS -> Coalasce or drop old one as all objects are moved to new TS.

Abhay.

[gnagesh]

Originally posted by pando
what do you mean by fragmantation

also if you think you cannot use LMT there is still this storage parameter

MINIMUM EXTENT

which ensures your extent are multiple of size specified

CREATE TABLESPACE tabspace_3
DATAFILE 'tabspace_file5.dbf' SIZE 2M
MINIMUM EXTENT 64K
DEFAULT STORAGE (INITIAL 128K NEXT 128K);

extents will be multiple of 64K hence reusing free space efficiently

Thanks pando. I have not thought of this. Good Idea. But what would be the ideal starting size for MININUM EXTENT.

Thanks once again.

[TomazZ]

To reorg production database, IMHO, best is to do tablespace by tablespace as abhaysk said:
create new tablespace
for all tables loop
move table to new tablespace
rebuild all indexes of this table
end loop
drop old tablespace

[pando]

you should create multiple size tablespaces for different sizing strategy. For example create 5 tablespaces with minimum extent 128k, 1m, 4m, 32m, 128m then just move your objects to each corresponding tablespace according to their size and growing pattern

[gnagesh]

Originally posted by pando
you should create multiple size tablespaces for different sizing strategy. For example create 5 tablespaces with minimum extent 128k, 1m, 4m, 32m, 128m then just move your objects to each corresponding tablespace according to their size and growing pattern

Thanks for your help.