Home > ORA-XXX > ORA-01157: cannot identify/lock data file 7 – see DBWR trace file

ORA-01157: cannot identify/lock data file 7 – see DBWR trace file

November 28, 2012 Leave a comment Go to comments

I dropped a tablespace, its corresponding data file was missing in the system. It showed that

SQL> drop tablespace SOE;
drop tablespace SOE
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 7 – see DBWR trace file
ORA-01110: data file 7: ‘/opt/oracle/product/11.2.0.3/db_1/dbs/soe.dbf’

SQL> !ls -l /opt/oracle/product/11.2.0.3/db_1/dbs/soe.dbf
/opt/oracle/product/11.2.0.3/db_1/dbs/soe.dbf: No such file or directory <– missing
Finally, I dropped it successfully by the following procedures

1. SQL> alter database datafile 7 offline drop;

Database altered.

2. SQL> select name from v$datafile;

NAME
——————————————————————————–
+DATA1/testdb/datafile/system.504.800535271
+DATA1/testdb/datafile/sysaux.505.800535271
+DATA1/testdb/datafile/undotbs1.506.800535273
+DATA1/testdb/datafile/users.507.800535273
+DATA1/testdb/datafile/example.519.800535661
+DATA1/testdb/datafile/undotbs2.520.800536373
/opt/oracle/product/11.2.0.3/db_1/dbs/soe.dbf

7 rows selected.

3. SQL> drop tablespace soe;

Tablespace dropped.

SQL>  select name from v$datafile;

NAME
——————————————————————————–
+DATA1/testdb/datafile/system.504.800535271
+DATA1/testdb/datafile/sysaux.505.800535271
+DATA1/testdb/datafile/undotbs1.506.800535273
+DATA1/testdb/datafile/users.507.800535273
+DATA1/testdb/datafile/example.519.800535661
+DATA1/testdb/datafile/undotbs2.520.800536373

Advertisements
Categories: ORA-XXX
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: