| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Oracle 8i database reorg
Have you considered converting one tablespace at a time?
Consider this option: Create a dummy tablespace big enough to hold everything from one tablespace.
Issue "alter table {table name} move tablespace dummy_tbs;"
Move all of the objects out from your target tablespace. Drop and recreate
the tablespace.
Reissue the "alter table...move" commands again - moving the tables back to
the original tablespace. Don't forget to issue "alter index {index name}
rebuild;" commands for all the tables you moved as they will be "unusable".
I find this a bit less messy than export import - it all stays within the database. It probably would take more time to complete, but your database could stay up during the process.
Just another option.
Thanks
Tom Mercadante
-----Original Message-----
From: Stephen.Lee_at_DTAG.Com [mailto:Stephen.Lee_at_DTAG.Com]
Sent: Thursday, May 13, 2004 12:15 PM
To: oracle-l_at_freelists.org
Subject: RE: Oracle 8i database reorg
> -----Original Message-----
> I have a 30 GB Oracle 8.1.7 database that I would like to
> reorg and at the same time modify to use locally managed
> tablespaces.
I favor tablespaces with uniform extent sizes, so one must create new tablespaces, then use exp/imp to copy the user data. The only snag I've encountered doing it this way is that the initial and next extent sizes defined in the export file will probably not match your nice and tidy uniform extent sizing. You will still get uniform extent sizes, but the sizes might not be what you had in mind. The tables and indexes will be created at least as big as the initial extent size in the create statement, but uniform extents might make them bigger. For example, if you defined 10M uniform extents, and you have a bunch of little tables, then each table (and index) will get at least 10M no matter what you say.
For my test and dev environments, where we try to pack things tighter than production, I have a C programs (could also be done with PURL ... but not with sed (I'm pretty sure) ... but if the exp file isn't too big, it's possible to do it with emacs ... but not vi) that reads the exp file and changes every occurrence of INITIAL and NEXT to either 256K or 2M depending what environment I'm populating.
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Thu May 13 2004 - 13:15:15 CDT
 |
 |