RE: Fixing a DB
Looks good. Does "fol" mean following? Thanks, Ken -Original Message- Sent: Wednesday, November 21, 2001 8:50 AM To: Multiple recipients of list ORACLE-L Subject: Re: Fixing a DB 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 . rebuild online 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: 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).
RE: Fixing a DB
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 . rebuild online 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).
Re: Fixing a DB
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 . rebuild online 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).
Re: Fixing a DB
Ken, I don't think you will need steps 2 thru 5. After step 1, just write sql that will spool out "alter index rebuild tablespace " & run the resulting sql. Will that work ? HTH Srini Chavali Oracle DBA Cummins Inc Ken Janusz <[EMAIL PROTECTED]>@fatcity.com on 11/21/2001 08:25:34 AM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
RE: Fixing a DB
Why not just create the tablespace and move the indexes? alter index rebuild with new storage parameters. > -Original Message- > From: Ken Janusz [SMTP:[EMAIL PROTECTED]] > Sent: Wednesday, November 21, 2001 8:26 AM > To: Multiple recipients of list ORACLE-L > Subject: Fixing a DB > > 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Koniszewski, Mike 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).
Re: Fixing a DB
I will try to answer these. See answers in line: RBG - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, November 21, 2001 8:25 AM > 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. OK > > 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. This will be needed to recreate this database in the future. > > 3. Then run the DDL SQL script to create the new DB. Not necessary to fix this problem now. Just create the new tablespace of the correct size as determined in 1 above. E.g. create tablespace IDX default storage (initial 20K next 20K minextents 1 maxextents unlimited pctincrease 0) datafile '/oraidx/idx01.dbf' size 20M reuse, '/oraidx/idx02.dbf' size 20M reuse; etc.. > > 4. Then do and Export of the data from the old DB and Import it into the new > DB. Not necessary to fix this problem now. > > 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? Just rebuild the indexes into the new tablespace. Run dynamic sql to create the rebuild statements. E.g select 'alter index owner.||index_name||' rebuild tablespace IDX;' from dba_tables where owner='OWNER'; Spool the outcome and run the spooled file. > > Is there anything I have missed? I'm sure there is. I mite have missed something...if you need further explanation just let me know Ruth > > 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini 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).
