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

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