| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: RE: Renaming tablespace by updating SYS.TS$
But, in "NEWTABLESPACE.TAB" NEWTABLESPACE is schema name.
Why do you refer to it as a tablespace?
Igor Neyman, OCP DBA
ineyman_at_perceptron.com
-----Original Message-----
rgaffuri_at_cox.net
Sent: Friday, May 09, 2003 2:28 PM
To: Multiple recipients of list ORACLE-L
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
> >
>---------------------------------------------------------------------> > >(or the name of mailing list you want to be removed from). You may
> > >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
-- 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: Igor Neyman INET: ineyman_at_perceptron.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).Received on Fri May 09 2003 - 14:58:47 CDT
 |
 |