Get the following error when renaming datafiles from inside a sql script

From: Subrahmanyam Arya <avsrk_at_mailcity.com>
Date: 15 Aug 2003 12:22:18 -0700
Message-ID: <25c1993e.0308151122.32672990@posting.google.com>

Dear oracle gurus,

I picked this cript from google itself but i get errors when i run this
pasting the script below

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 roon in the destination device
because this routine copies the datafile to a new file
before renaming
: After the file is copied and renamed, teh file is deleted
: however, you are prompted to make sure you want to delete
: If the rename or copy fails,DON'T DELETE THE ORIGINAL
: The HOST lines use UNIX commands

SET VERIFY OFF PROMPT Rename database file script

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

PROMPT Taking tablepsace 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

now i did sqlplus internal and executed this script @move_ts.sql

and i get the following error

Rename database file script
Enter tablepsace name: TEST
Enter old file name:
/opt/PlexView/Oracle/ora03/oradata/plxdb3/test01.dbf Enter new file name:
/opt/PlexView/Oracle/ora02/oradata/plxdb3/test01.dbf Taking tablepsace offline...
Copying datafile to new location...

Renaming datafile...
ALTER TABLESPACE TEST RENAME DATAFILE
'/opt/PlexView/Oracle/ora03/oradata/plxdb3/test01.dbf' TO '/opt/PlexView/Oracle/ora02/oradata/plxdb3/test01.dbf' *
ERROR at line 1:

ORA-01525: error in renaming data files
ORA-01121: cannot rename database file 7 - file is in use or recovery
ORA-01110: data file 7:

'/opt/PlexView/Oracle/ora03/oradata/plxdb3/test01.dbf'

Bringing tablespace TEST back online...
ALTER TABLESPACE TEST ONLINE
*
ERROR at line 1:
ORA-01116: error in opening database file 7 ORA-01110: data file 7:
'/opt/PlexView/Oracle/ora03/oradata/plxdb3/test01.dbf' ORA-27041: unable to open file
SVR4 Error: 2: No such file or directory Additional information: 3

--any clue how to rectify this

I also tried shutting down the instance using shutdown immediate so that any recovery sessions that are there will be ended

Actually i am looking at a way to automate renaming datafiles either via shell script or via sql script..so the end user does not really need to know the actual commands.

-your help is greatly appreicated.

avsrk Received on Fri Aug 15 2003 - 14:22:18 CDT