> We do have some curstomers that only have (bad design) a single
> tablespace (SYSTEM). Is there any possibility to defragment it ?
I presume by de-fragmention you mean to remove rollback segments,
temporary segments and data and indexes into their own respective
tablespaces.If so I would do the following steps, (having backed up my
original database first - just in case!)
- Create the 4 necessary tablespaces;
- issue the command;
alter user <username> default tablespace <data_tbsp_name>
temporary tablespace <temp_tbsp_name>;
- create new rollback segments in the rollback segment tablespace
take offline and drop existing rollback segments (you may have to
wait for current transactions to complete before doing
this)
change the rollback_segments entry in the init.ora
- exp userid=sys/password file=nodata.dmp full=y rows=n
- edit the nodata.dmp file to substitute TABLESPACE "SYSTEM" with
TABLESPACE "<data_tbsp_name>"
- exp userid=sys/password file=alldata.dmp full=y rows=y
- drop all users from your existing database (including system)
- imp userid=sys/oa file=nodata.dmp indexes=n full=y
- imp userid=sys/oa file=alldata.dmp indexes=n full=y ignore=y
- imp userid=sys/oa file=alldata.dmp indexfile=cr_inds.sql full=y
- edit cr_inds.sql to substitute TABLESPACE "SYSTEM" with TABLESPACE
"<index_tbsp_name>"
- run cr_inds.sql against the database
Hopefully you should now have all your original data separated out into
the appropriate tablespaces.
Good Luck,
Ian
Received on Fri Nov 07 1997 - 00:00:00 CST
