| 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$
No, it' still not clear.
The best way to explain this would be to provide documentation, and use the *actual* DatabaseNames/ServerNames, TablespaceNames, SchemaNames and TableNames -- then we [or, at least, I] might understand your implementation.
e.g.
"we load the data into a staging table, then ... move it to another
tablespace"
followed by the line "we change the name in our staging server" has me
confused.
"change the name" of WHAT -- the Table, the Schema.Table, the Tablespace ?
Do you "change" or do you "rename" or do you "move".
Is this what you are doing ? [The "I" here is you, not me]. On the staging server STG:
1. I create table SCOTT.EMP which is in the DATA1LOAD tablespace. 2. I load data and scrub the data. 3. I then run an "ALTER TABLE SCOTT.EMP MOVE TABLESPACE DATA2FINAL"
On the production server LIVE:
4. An existing table SCOTT.EMP exists.
I want to retain this table while I am transporting the tablsepace from
LIVE. Therefore,
when I last transported the tablespace, I had renamed the Tablespace by
updating
TS$ to DATA2HERE. Thus, SCOTT.EMP resides in DATA2HERE.
5. I now transport the tablespace DATA2FINAL in to LIVE.
6. .... wait. .... I have to rename the SCOTT.EMP table to something else
anyway.
Unless it came in as SCOTT2.EMP
ok. so I change step 3 above to rebuild the table in the SCOTT2 schema,
somehow,
using COPY or EXPORT/IMPORT while in the STG server.
7. Now, DATA2FINAL comes in with SCOTT2.EMP 8. I run DROP PUBLIC SYNONYM EMP ; CREATE PUBLIC SYNONYM EMP FOR SCOTT2.EMP 9. I now run DROP TABLESPACE DATA2HERE INCLUDING CONTENTS10. I then update TS$ to change DATA2FINAL to DATA2HERE
now I wonder, do I need to have different TablespaceNames AND different
SchemaNames ?
Do I HAVE to update TS$ ?
[options : think about using ALTER TABLE ... MOVE. even in LIVE]
I [me, Hemant, here] am sure that the rest of the list here can add /
improve / correct the steps
outlined here
Hemant
At 07:14 PM 09-05-03 -0800, you wrote:
>We are renaming them in our staging database.
>
>Let me explain further. We have:
>
>1. Staging Database.. where we load the data to a staging table, then we
>scrub it and move it to another tablespace in its 'final' verseion before
>transport
>2. Production server on a remote site.
>
>We change the name in our staging server. The users dont have access to it.
>Its just for loading and scrubbing. If we transport tablespace 'A' every day
>then we have 50 tablespace A's. So we need new names. Now lets say we do use
>2 tablespaces in our staging server? Then we need to copies of our tables in
>their final veresion. These are upwards of 40-50 GBs. These would need to be
>copied EVERY day. This is alot of additional I/O on top of:
>
>1. Loading data
>2. Inserts, updates, and deletes to scrub the data
>3. Bandwidth to transport the tablespace to the remote site.
>4. Once at the remote site we actually import them to multiple databases for
>different applications.
>
>This has to be done serially and takes time. We cant take on the additional
>I/O of maintaining 2 identical tablespaces in our staging server.
>
>Was it clear this time?
>----- Original Message -----
>To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
>Sent: Friday, May 09, 2003 7:26 PM
>
>
> > If you have 2 schema's, and you use synonyms to point the app
> > to the current schema, *why* are you renaming the tablespace?
> >
> > All you have to do is stop doing that. Just use 2 tablespace names
> > and alternate between them. Renaming them is not buying you
> > anything.
> >
> > Jared
> >
> >
> >
> >
> >
> >
> > <rgaffuri_at_cox.net>
> > Sent by: root_at_fatcity.com
> > 05/09/2003 02:12 PM
> > Please respond to ORACLE-L
> >
> >
> > To: Multiple recipients of list ORACLE-L
><ORACLE-L_at_fatcity.com>
> > cc:
> > Subject: RE: RE: Renaming tablespace by updating SYS.TS$
> >
> >
> > my fault on the explanation. We also have 2 schemas. One is currently
> > active with a default tablespace of CURRENT_TABLESPACE.
> >
> > We have an inactive Schema, whose default tablespace becomse
> > NEWTABLESPACE. We then change the synonyms to point to the inactive
> > schema.
> >
> > Sorry about the confusion.
> > >
> > > From: "Khedr, Waleed" <Waleed.Khedr_at_FMR.COM>
> > > Date: 2003/05/09 Fri PM 04:29:01 EDT
> > > To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> > > Subject: RE: RE: Renaming tablespace by updating SYS.TS$
> > >
> > > Synonyms point to tables in schemas not Tablespaces.
> > > My guess is your app is using synonyms that could point to one of two
> > > schemas that are located in two different Tablespaces.
> > >
> > > By the way, you should know that the Tablespace name is also recorded in
> > the
> > > file headers, so I consider you lucky so far having no problems.
> > >
> > > Waleed
> >
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author:
> > INET: Jared.Still_at_radisys.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: 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).
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).Received on Sat May 10 2003 - 09:16:31 CDT
 |
 |