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

Just a word of caution to the Jr DBA (and anyone else). Before you do any of this, get a cold backup (just in case you screw up somewhere).

rr

Sang Ryung Lee wrote:

> 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 Tue Apr 06 1999 - 17:55:09 CDT