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

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).