RE: RE: Renaming tablespace by updating SYS.TS$

We have two identical tablespaces in terms of table structure.

Tablespace Current
Tablespace New

Tablespace new was just transported to the database. Same table structure as Current, but different data.

The queries access the tables through a synonym.

so current.tab1 is access with a
select col
from current;

so its using the synonym.

We then recreate the synonyms to 'point' to the new tablespace.

create or replace synonym col on NEWTABLESPACE.TAB

So the user still does

select col
from tab

but is 'pointed' to a new tablespace. This way if there are any queries that are running, its a clean switch over. User doesnt even notice it.

Please state what you are unclear on. Not sure how else to explain it.

>
> From: "Khedr, Waleed" <Waleed.Khedr_at_FMR.COM>
> Date: 2003/05/09 Fri PM 02:52:56 EDT
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Subject: RE: RE: Renaming tablespace by updating SYS.TS$
>
> Still do not understand what you mean by these synonyms!
>
> -----Original Message-----
> Sent: Friday, May 09, 2003 2:32 PM
> To: Multiple recipients of list ORACLE-L
>
>
> My bad on the explanation.
>
> We have synonyms of the form
>
> create public synonym X on tablespace.x
>
> So when we have two tablespaces in the database we recreate the synonyms as
> such:
>
> create public synonym X on new_tablespace.x
>
> then drop the old tablespace. We have noticed that in doubt queries are not
> adversely affected. Thus zero downtime.
> >
> > From: Hemant K Chitale <hkchital_at_singnet.com.sg>
> > Date: 2003/05/09 Fri PM 01:31:55 EDT
> > To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> > Subject: Re: RE: Renaming tablespace by updating SYS.TS$
> >
> >
> > I'm not quite sure I understand how you have "synonyms" for the Tablespace
>
> > names.
> >
> > Hemant
> > At 08:16 AM 09-05-03 -0800, you wrote:
> > >Now I understand the process better. Im hoping someone has a better
> process.
> > >
> > >We have a staging database onsite. We need to transport tablespaces every
>
> > >day. Sometimes in the middle of the day to a remote production database.
> > >Therefore any downtime during transport is out of the question.
> > >
> > >1. So here is what we do. In our staging database. We rename the
> > >tablespace that will be transported to something of the form
> > >
> > ><name>_data, we then transport it and rename it back to its original
> name.
> > >
> > >2. On the production server we have two copies of the this tablespace.
> > >
> > ><name>_oldDate
> > ><name>_currentDate
> > >
> > >3. We then import the new tablespace. When import is complete we have the
>
> > >synonyms that pointed to the old tablespace point to the new tablespace.
> > >We therefore have no downtime whatsoever. Downtime is only a few minutes,
>
> > >but we cannot have any downtime at all.
> > >
> > >We have tested this with in doubt queries(again no DML is performed) and
> > >found that if we switch the synonyms to the new tablespace during a
> query,
> > >Oracle is smart enough to not skip a beat and complete the query
> properly.
> > >
> > >anyone have a better solution? Renaming TS$ is risky. We pretty much
> > >follow a standard Datawarehouse publication process.
> > >
> > >--
> > >Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > >--
> > >Author: <rgaffuri_at_cox.net
> > > INET: rgaffuri_at_cox.net
> > >
> > >Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > >San Diego, California -- Mailing list and web hosting services
> > >---------------------------------------------------------------------
> > >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).
> >
> > Hemant K Chitale
> > My personal web site is : http://hkchital.tripod.com
> >
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Hemant K Chitale
> > INET: hkchital_at_singnet.com.sg
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting services
> > ---------------------------------------------------------------------
> > 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.net
> --
> Author: <rgaffuri_at_cox.net
> INET: rgaffuri_at_cox.net
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> 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.net
> --
> Author: Khedr, Waleed
> INET: Waleed.Khedr_at_FMR.COM
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> 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.net
-- 
Author: <rgaffuri_at_cox.net
  INET: rgaffuri_at_cox.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).