Re: moving datafiles

Richard G. Ramirez wrote:
>
> > Use ALTER DATABASE RENAME 'filename' TO 'filename';
> >
> > This does not actually rename the file, it just tells Oracle what the
> > new name is. The database must be mounted, but not open to do this.
>
> It is better than that. All you need is to have the space offline. The
> database can be open. Thus, you can take the tablespace offline,
> copy the file to the desired location, use the ALTER ... RENAME ....
> and the bring the tablespace back online.

Try the following script:

• File : move_ts.sql
• Purpose : Rename a datafile in a tablespace
• Params : None. User will be prompted for required input.
• Run As : SYS, SYSTEM or DBA equivalent
• Notes : Be sure there is enough room on the destination device because
• this routine copies the datafile to a new file before renaming.
• After the file is copied and renamed, the file is deleted;
• however, you are prompted to make sure you want to delete it.
• If the rename or copy fails, DON'T DELETE THE ORIGINAL FILE!!!
• The HOST lines use UNIX commands.

SET VERIFY OFF PROMPT Rename database file script.

ACCEPT tablespace_name PROMPT "Enter tablespace name: " ACCEPT old_file PROMPT "Enter old file name: " ACCEPT new_file PROMPT "Enter new file name: "

PROMPT Taking tablespace offline...
ALTER TABLESPACE &tablespace_name OFFLINE;

PROMPT Copying datafile to new location... HOST mv &old_file &new_file

PROMPT Renaming datafile...
ALTER TABLESPACE &tablespace_name RENAME DATAFILE '&old_file' to '&new_file';

PROMPT Bringing tablespace &tablespace_name back online... ALTER TABLESPACE &tablespace_name ONLINE;

HOST rm -i &old_file

UNDEFINE tablespace_name
UNDEFINE old_file
UNDEFINE new_file

Regards,
Brian

-- 
Brian M. Biggs				mailto:bbiggs_at_cincom.com
Cincom Systems, Inc.			voice: (513) 677-7661
http://www.cincom.com/