The only problem with this technique is that you won't reclaim the space in the first tablespace that used to be used by the indexes (since the high water marke will be set). If this isn't an issue (either lots of storage space or the data is expected to grow enough to use this space) then I agree this is the better option.
Jay Miller -----Original Message----- Sent: Wednesday, November 21, 2001 9:50 AM To: Multiple recipients of list ORACLE-L Sorry forgot the command in my earlier email To recap, here are the steps 1. create the new tablespace 2. For all indexes that need to be moved, run the fol command alter index <index_owner>.<index_name> rebuild online tablespace <new_tablespace>; >From: Ken Janusz <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: Fixing a DB >Date: Wed, 21 Nov 2001 05:25:34 -0800 > >We have a DB that is currently in more-or-less development mode for a state >government client. The DB has been created (11,300 lines of SQL*Plus code) >with the tables and indexes stored in the permanent tablespace >(SOS2_TBLSPC). There is no separate tablespace for indexes. The DB >currently holds test data. I think the process to correct this would be: > >1. Determine the size, etc. of the INDEX tablespace, write the script and >then run it to create the tablespace. > >2. Go through the DDL SQL script for OKSOS (state gov. office name >abbreviation) and change the tablespace name SOS2_TBLSPC to the new INDEX >tablespace name. > >3. Then run the DDL SQL script to create the new DB. > >4. Then do and Export of the data from the old DB and Import it into the >new >DB. > >5. Then I think I have to regenerate your indexes. I'm not clear on this >point. Will the Export/Import process may take care of this? > >Is there anything I have missed? I'm sure there is. > >Thanks, > >Ken Janusz, CPIM >Database Conversion Lead >Sufficient System, Inc. >Minneapolis, MN > > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Ken Janusz > INET: [EMAIL PROTECTED] > >Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California -- Public Internet access / Mailing Lists >-------------------------------------------------------------------- >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may >also send the HELP command for other information (like subscribing). _________________________________________________________________ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sunny Verghese INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
