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

The explanation is in one of the many emails I sent. I apologize. I was unclear yesterday. We have 2 schemas. One active that defaults to the current tablespace. One inactive. When we import the new tablespace. We default the inactive schema to the new tablespace.

We then 'point' the synonyms to the inactive schema. That becomes active. The old schema becomes inactive. We then drop the old tablespace.

Ive explained the TS$ update several times. I guess noone has a better alternative that fills our requirements without forcing us to purchase a third party tool.
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Saturday, May 10, 2003 9:51 AM

>
> That would mean that your Table names are different. The TableName in one
> Tablespace is "TAB1", the TableName in the other is "TAB".
>
> {you really can't do a "create synonym X for tablespace.tab", it is
> "create synonym X for schema.tab"}
>
> If the TableNames are different and you can create synonyms referencing
the
> tables,
> why do you need to change Tablespace names in TS$?
>
> hemant
>
> At 11:27 AM 09-05-03 -0800, you wrote:
> >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).
>
> 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: Ryan
  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).