moving datafiles

Can someone explain to me why this won't work?

I need to move a datafile on a very busy tablespace. The objects in the tablespace are only updated once a day, after that it's 100% read. I want to move the file during this 100% read time. The conventional method is to take the tablespace offline, move the file in the o/s, rename it in Oracle, and bring the tablespace back online. I want to keep the tablespace available throughout the entire operation so I thought I would try the following.

o    	Place the tablespace in read only mode.
o    	Copy the datafile to the new filesystem
o    	Rename the datafile in Oracle.
o    	Bring the tablespace back online
o    	Delete the original file from the operating system.

This is pretty much what's documented in the 8i SQL reference page 8-73 (for those who require proof you've actually read the documentation).

"Once a tablespace is read only, you can copy its files to read-only media. You must then rename the datafiles in the control file to point to the new location by using the SQL statement ALTER DATABASE ... RENAME." The only difference is I'm copying it to another read/write media, not read-only media. When I try it however I get the following error when I try to rename the file in oracle. Why?

SQL> alter database
  2 rename file '/u23/oradata/DBATOOL/test01.dbf'   3 to '/u22/oradata/DBATOOL/test01.dbf'; alter database
*
ERROR at line 1:

ORA-01511: error in renaming log/data files
ORA-01121: cannot rename database file 11 - file is in use or recovery
ORA-01110: data file 11: '/u23/oradata/DBATOOL/test01.dbf'

-- 
Chuck
Remove "_nospam" to reply by email