Re: moving datafiles

From: L120bj <l120bj_at_aol.com>
Date: 1997/02/24
Message-ID: <19970224211001.QAA16981@ladder02.news.aol.com>#1/1

Personally I would never move the datafile first, I would always copy it to the new location and only remove the original once the ALTER DATABASE RENAME.... command had successfully executed. It looks as though Brian is alluding to that since although he used 'mv' in the first HOST command, he then did an 'rm' in the second HOST command, which would be redundant if the file had been moved.

Rob

---

---

Subject: Re: moving datafiles
From: "Brian M. Biggs" <bbiggs_at_cincom.com> Date: Mon, 24 Feb 1997 12:11:32 -0500
Message-ID: <3311CBC4.6FD8_at_cincom.com>

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/

Received on Mon Feb 24 1997 - 00:00:00 CST