Re: Undo ALTER TABLESPACE ADD DATAFILE

>>>>> 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> 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

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>

Both files must exist and it will only work for non-system tablespaces. The alter database command will work even if the old dbfile doesn't exist anymore (if your short on disk-space).

  mannhart> : Any help would be greatly appreciated. Thanks.

  mannhart> You're welcome
  mannhart> LM
  mannhart> : -- Rob


  mannhart> --
  mannhart> Leo Mannhart
  mannhart> Planning Office
  mannhart> University of Zurich                                phone: ++41 1 257 23 34
  mannhart> Kuenstlergasse 15                                     fax: ++41 1 257 22 12
  mannhart> CH-8001 Zurich, Switzerland                         eMail: mannhart_at_zuv.unizh.ch

--

Magnus Lonnroth
Tech.Sales & Consultant
Oracle Sweden
Mail: mloennro_at_oracle.com