Re: What's the "standard" step using exp/imp to eliminate fragmentation ?

First you decide to which tablespace is eliminated fragmentation. Maybe the tablespaces are data and dbidx. If my thought is right, follow the next steps.

1. Check the table and index's owner.
2. Do the user full export exp user1/passwd1 grants=y buffer=81920 full=y indexes=no log=exp.log file=user1.dmp exp user2/passwd2 ..... exp user3/passwd3 ....
3. export index script exp user1/passwd1 rows=no indexfile=user1idx exp user2/passwd2 rows=no indexfile=user2idx
   ....
   user1idx.sql , user2idx.sql,... script file is produced.
4. Drop the tablespaces which the tables and indexs exist in. drop tablespace data including contents; drop tablespace dbidx including contents;
5. Recreate the tablespaces
6. Import data imp user1/passwd1 buffer=81920 file=user1.dmp imp user2/passwd2 buffer=81920 file=user2.dmp
   .....
   
7. Create the indexes using script file produced on step 3 sqlplus user1/passwd_at_user1idx.sql
   ......
   

It is possible that there are some syntax error, but the steps may helps you.

Gool luck.

S.R.LEE Received on Fri Apr 02 1999 - 20:24:05 CST