Re: Undo ALTER TABLESPACE ADD DATAFILE

In article <MLOENNRO.93Nov18100400_at_demo1.se.oracle.com> mloennro_at_oracle.com writes:
>>>>>> Regarding Re: Undo ALTER TABLESPACE ADD DATAFILE; mannhart_at_zuv.unizh.ch () adds:
>
> mannhart> Rob Ryan (rryan_at_panix.com) wrote:
> mannhart> : On Oracle7 on a Sun, we've just added a datafile to a tablespace with the
> mannhart> : ALTER TABLESPACE ADD DATAFILE command. We discovered that we put the file
> mannhart> : on the wrong file system and want to move it. But it doesn't appear to
> mannhart> : be that easy. I assume that I could detach that tablespace, move the
> mannhart> : datafile to where it belongs, and then define a symbolic link, but that's
> mannhart> : a little cumbersome.
>
> mannhart> Thats easy (RTFM) [oracle dba guide V6.0 p. G-2]
>
>I wouldn't rely on a v6 manual for a v7 database. In this case the
>commands are the same though.
>
> mannhart> Shutdown your database
> mannhart> move the datafile (or redo log file) to where you want it to stay
> mannhart> startup nomount
> mannhart> mount exclusive
> mannhart> ALTER DATABASE RENAME 'old_filename' TO 'new_filename'
> mannhart> shutdown
> mannhart> startup
>
>Or, if you don't want to shutdown the database:
>
>SQL> !cp <dbfile> <new_dbfile>
>SQL> alter tablespace xxx offline;
>SQL> alter tablespace xxx rename datafile 'xxx' to 'yyy';
>SQL> alter tablespace xxx online;
>SQL> select * from dba_data_files; /* Verify ! */
>SQL> !rm -f <dbfile>
>

Probably wiser to take the tablespace offline BEFORE the copy is performed otherwise media recover will be needed!

Regards Shane

-- 
----------------------------------------------------------------------------
Shane Hocking				Email :	srh_at_scammell.ecos.tne.oz.au
Information Technology Group
Telecom Australia