Re: Need help moving Oracle data files

In article <Dec.6.23.25.14.1993.29983_at_andromeda.rutgers.edu> holowcza_at_andromeda.rutgers.edu (Richard D Holowczak) writes:
>Hi folks. I've "inherited" an Oracle 6 installation on
>SunOS which is in need of some help. It seems the installers
>used a hard coded path when defining the additional datafiles.
>For example, I have:
>
>TSNAME FNAME
>------------- ----------------------------
>SYSTEM /oracle/dbs/dbsoracle1.dbf
>SYSTEM /oracle/dbs/dbsoracle2.dbf
>
>
>
>We want to move Oracle on to a new disk with more space.
>I know we can play tricks by mounting the new drive
>under /oracle or something like that. What I'd like to
>do is to somehow assign some new names with a variable
>in place of the path such as:
>
>TSNAME FNAME
>--------------- -------------------------
>SYSTEM $ORACLE_HOME/dbs/dbsoracle1.dbf
>
>
>I'm wondering if I can just go in and change this in the database ?
>(i.e. UPDATE SYSFILES set FNAME = . . . . . .)
>
>I'm hesitant to do so.
>
>Another thought was to export the whole DB, break the thing down and
>recreate new datafiles with the proper names.
>
>I'm sure this is in the DBA book but I'm at home now .. . . . . :)
>
>Any suggestions ?
>
>Thanks
>
>Rich Holowczak
>Rutgers U.
>holowcza_at_andromeda.rutgers.edu

It is a good thing that you did not attemp to use UPDATE. It does not work. To do this, use the following:

	alter tablesace TS_NAME offline;
	!mv file_spec new_file_spec  (This is in unix)
	alter tablespace rename TS_NAME
              datafile 'file_spec' to 'new_file_spec';
	alter tablespace TS_NAME online;

BUT, the SYSTEM tablespace cannot be changed since it cannot be taken offline. Environment variables are okay but I had some problems with using them in version 6.0.27. Have'nt used them since.

If you want to change the files in the system tablespace, do as you said by recreating the database and importing. However, watchout to not import the tablespace definitions; create them first.

Regards,
S. Tedjarati
Unisys HK & China Limited Received on Tue Dec 07 1993 - 08:57:16 CET