RE: Move tablespace with no downtime?

Yeah seems like a moot point. Why would you try this with tempfiles anyway? With tempfiles you just add and drop to your heart's content.

Any kind of maintenance you might want to do to a temporary tablespace, regardless of the file type used, can be easily accomplished without downtime, by creating a second temporary tablespace and assigning all the users to it while you make any changes to the original one.

--
Jeremiah Wilton
http://www.speakeasy.net/~jwilton

On Thu, 25 Oct 2001, [iso-8859-1] Connor McDonald wrote:



> True - but with tempfiles that is just about

> instantaneous anyway.

>

>  --- Glenn Travis <Glenn.Travis_at_sas.com> wrote: > FYI:

>  This will not work with TEMP tablespaces which

> > are locally managed.  You must drop the tablespace

> > and recreate it with the new filename.  Check

> > Metalink - it is documented that temporary datafiles

> > (tempfiles) cannot be renamed.

> >

> > > -----Original Message-----

> > > From: Jeremiah Wilton

> > [mailto:jwilton_at_speakeasy.net]

> > > Sent: Thursday, October 25, 2001 11:40 AM

> > > To: Multiple recipients of list ORACLE-L

> > > Subject: Re: Move tablespace with no downtime?

> > >

> > >

> > > Here's a trick I like to reduce the downtime

> > associated with moving

> > > large datafiles:

> > >

> > > 1. Place the tablespace that contains the file(s)

> > you want to move

> > >    into backup mode:

> > >    - alter tablespace <name> begin backup;

> > >

> > > 2. Copy the file(s):

> > >    - cp <oldfilespec> <newfilespec>

> > >    - If you are moving multiple files, you can do

> > this in parallel

> > >

> > > 3. Take the tablespace out of backup mode:

> > >    - alter tablespace <name> end backup;

> > >

> > > 4. (Now the very brief downtile begins)

> > >    Take the tablespace offline immediate

> > >    - alter tablespace <name> offline immediate;

> > >

> > > 5. Issue the rename:

> > >    - alter database rename file <oldfilespec> to

> > <newfilespec>;

> > >

> > > 6. Recover the new file(s) or the whole tablespace

> > (should be quite

> > >    brief)

> > >    - recover [datafile|tablespace] <name>;

> > >

> > > 7. (Now the very brief downtime ends)

> > >    Bring the tablespace online again:

> > >    - alter tablespace <name> online;

> > >

> > > --

> > > Jeremiah Wilton

> > > http://www.speakeasy.net/~jwilton

> > >

> > > On Thu, 25 Oct 2001, Rachel Carmichael wrote:

> > >

> > > > depends on what you mean by downtime.

> > > >

> > > > yes you can do it without taking down the

> > database and the instance,

> > > > no, you can't do it without at least taking the

> > tablespace offline.

> > > >

> > > > alter tablespace x offline;

> > > > in os, make a copy of all datafiles in the new

> > location

> > > > alter database move file 'y' to 'z';

> > > >   <repeat for all datafiles>

> > > > alter tablespace online;

> > > >

> > > >

> > > > --- Greg Moore <sqlgreg_at_pacbell.net> wrote:

> > > > > Can I move a tablespace and all it's

> > associated datafiles to a new

> > > > > disk

> > > > > device with no downtime?


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jeremiah Wilton
  INET: jwilton_at_speakeasy.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).