Hi,
I added a datafile and then realized that I needed to rename it immediately.
I made it offline but I couldn't rename or make it online.
I don't think there is an important data inside but it is required to be recovered.
I want to drop this datafile on online......Please help me!
SQL> select version from v$instance;
VERSION
-----------------
10.2.0.5.0
SQL>alter tablespace users drop datafile '/sd2/db1/user03.dbf' offline;
Database altered.
SQL>alter tablespace users drop datafile '/sd2/db1/user03.dbf' offline drop;
Database altered.
still can see the datafile.............
SQL> SELECT FILE#, STATUS, ERROR, RECOVER, TABLESPACE_NAME, NAME FROM V$DATAFILE_HEADER
2 WHERE RECOVER = 'YES' OR (RECOVER IS NULL AND ERROR IS NOT NULL);
FILE# STATUS
---------- -------
ERROR REC
----------------------------------------------------------------- ---
TABLESPACE_NAME
------------------------------
NAME
--------------------------------------------------------------------------------
39 OFFLINE
YES
USERS
/sd2/db1/user03.dbf
SQL> SELECT name,status FROM V$DATAFILE where file#=39;
NAME STATUS
-------------------- --------------------
/sd2/db1/user03.dbf RECOVER
SQL>alter tablespace users drop datafile '/sd2/db1/user03.dbf';
*
ERROR at line 1:
ORA-03264: cannot drop offline datafile of locally managed tablespace
SQL> ALTER DATABASE
2 DATAFILE '/sd2/db1/user03.dbf' ONLINE;
ALTER DATABASE
*
ERROR at line 1:
ORA-01113: file 39 needs media recovery if it was restored from backup, or END
BACKUP if it was not
ORA-01110: data file 39: '/sd2/db1/user03.dbf'
then, I followed the instruction from this. but it was 9i.
after offline drop datafile, v$datafile.status='RECOVER'
SQL> alter database create datafile '/sd2/oracle/admin/product/10g/dbs/unnamed00039.dbf'
2 as
3 '/sd2/db1/user03.dbf';
alter database create datafile '/sd2/oracle/admin/product/10g/dbs/unnamed00039.dbf'
*
ERROR at line 1:
ORA-01516: nonexistent log file, datafile, or tempfile
"/sd2/oracle/admin/product/10g/dbs/unnamed00039.dbf"