Re: Fixing a DB

From: Ruth Gramolini <rgramolini_at_tax.state.vt.us>
Date: Wed, 21 Nov 2001 06:41:27 -0800
Message-ID: <F001.003CA89F.20011121061019@fatcity.com>

I will try to answer these. See answers in line: RBG ----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> 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: ken.janusz_at_sufsys.com
>
> 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: ListGuru_at_fatcity.com (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: rgramolini_at_tax.state.vt.us

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: ListGuru_at_fatcity.com (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).

Received on Wed Nov 21 2001 - 08:41:27 CST