Re: Quick move tablespace

From: Eugene Pokopac <epokopac_at_gmail.com>
Date: Fri, 8 Jan 2010 06:37:29 -0800 (PST)
Message-ID: <3665f52c-e655-4e1c-9cd5-6af6b58b3b51_at_m26g2000yqb.googlegroups.com>

On Jan 7, 3:06 pm, Mladen Gogala <n..._at_email.here.invalid> wrote:
> On Thu, 07 Jan 2010 06:29:02 -0800, Eugene Pokopac wrote:
> > 1 - ALTER TABLESPACE <ts_name> OFFLINE ;
>
> > 2 - At the server - Issue appropriate commands to copy ALL files in the
> > tablespace from the OLD location to the NEW location:
>
> >     Unix example: mv /u015/ORACLE/ofdvdata/<filename> /u004/ORACLE/
> > ofdv/<filename>
>
> > 3 - ALTER TABLESPACE <ts_name> RENAME DATAFILE
> >          /u015/ORACLE/ofdvdata/<filename>    TO
> >          /u004/ORACLE/ofdv/<filename> ;
>
> > 4 - ALTER TABLESPACE <ts_name> ONLINE ;
>
> Eugene, how about doing a little testing?
>
> --http://mgogala.byethost5.com

Oops! Forgot the quotes around the "u015" and "u004" specs in step 3. Should read:

3 - ALTER TABLESPACE <ts_name> RENAME DATAFILE

          '/u015/ORACLE/ofdvdata/<filename>'    TO
          '/u004/ORACLE/ofdv/<filename>' ;

Real life example from a working script (can be done totally within SQL*Plus):

ALTER TABLESPACE apps_ts_archive OFFLINE ;

HOST mv /u015/ORACLE/ofdvdata/APPS_TS_ARCHIVE01.dbf /u005/ORACLE/ofdv/ APPS_TS_ARCHIVE01.dbf

ALTER TABLESPACE apps_ts_archive RENAME DATAFILE   '/u015/ORACLE/ofdvdata/APPS_TS_ARCHIVE01.dbf' TO   '/u005/ORACLE/ofdv/APPS_TS_ARCHIVE01.dbf' ;

ALTER TABLESPACE apps_ts_archive ONLINE ; Received on Fri Jan 08 2010 - 08:37:29 CST