今天在启动虚拟机测试库时，数据库报了ORA-01157和ORA-01110错误，提示找不到一个数据文件。我一看文件名就知道问题在哪儿了，是另一台虚拟机没有启动导致的，因为这个数据库通过dnfs创建了一个“远程”表空间。由于暂时不想启动那台虚拟机（节省内存），干脆就把这个表空间删除，熟悉一下trouble-shooting的过程。
SQL>
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL>
2.启动数据库报错
在启动数据库过程中，报了ORA-01157和ORA-01110错误，提示找不到数据文件。
SYS@HOEGH>startup
?
ORACLE instance started.
Total System Global Area 941600768 bytes
Fixed Size 1348860 bytes
Variable Size 524290820 bytes
Database Buffers 411041792 bytes
Redo Buffers 4919296 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 9 - see DBWR trace file
ORA-01110: data file 9: \'/u02/oradata/HOEGH/test_tbs01.dbf\'
SYS@HOEGH>select status from v$instance;
STATUS
------------
MOUNTED
SYS@HOEGH>
由于另外一台虚拟机没有启动，数据库在启动过程中后台进程不能找到相应的数据文件或者不能锁定相应的数据文件，数据库将禁止访问这些数据文件而其他的数据文件则没有影响。ORA-01157错误一般和ORA-01110错误一起出现。
?
3.重启数据库到mount状态，删除数据文件
启动数据库时，nomount状态读取参数文件，mount状态读取控制文件，在mount状态下可以删除数据文件。
SYS@HOEGH>
?
SYS@HOEGH>startup nomount
ORACLE instance started.
Total System Global Area 941600768 bytes
Fixed Size 1348860 bytes
Variable Size 524290820 bytes
Database Buffers 411041792 bytes
Redo Buffers 4919296 bytes
SYS@HOEGH>
SYS@HOEGH>
SYS@HOEGH>alter database mount;
Database altered.
SYS@HOEGH>alter database datafile \'/u02/oradata/HOEGH/test_tbs01.dbf\' offline drop;
Database altered.
?
4.打开数据库，删除表空间
启动数据库到open状态，查找数据文件对应的表空间名称；然后，删除表空间。
SYS@HOEGH>alter database open;
?
Database altered.
SYS@HOEGH>
SYS@HOEGH>select file_id,tablespace_name from dba_data_files;
? FILE_ID TABLESPACE_NAME
---------- ------------------------------
? ? ? ? 4 USERS
? ? ? ? 3 UNDOTBS1
? ? ? ? 2 SYSAUX
? ? ? ? 1 SYSTEM
? ? ? ? 5 TEST1
? ? ? ? 6 TEST2
? ? ? ? 7 TEST3
? ? ? ? 8 TEST
? ? ? ? 9 TEST_TBS
9 rows selected.
SYS@HOEGH>col file_name for a50
SYS@HOEGH>col file_id for 99
SYS@HOEGH>col tablespace_name for a10
SYS@HOEGH>col status for a10
SYS@HOEGH>col online_status for a20
SYS@HOEGH>select file_name,file_id,TABLESPACE_NAME,STATUS,ONLINE_STATUS from dba_data_files;
FILE_NAME FILE_ID TABLESPACE STATUS ONLINE_STATUS
-------------------------------------------------- ------- ---------- ---------- --------------------
/u01/app/oracle/oradata/HOEGH/users01.dbf 4 USERS AVAILABLE ONLINE
/u01/app/oracle/oradata/HOEGH/undotbs01.dbf 3 UNDOTBS1 AVAILABLE ONLINE
/u01/app/oracle/oradata/HOEGH/sysaux01.dbf 2 SYSAUX AVAILABLE ONLINE
/u01/app/oracle/oradata/HOEGH/system01.dbf 1 SYSTEM AVAILABLE SYSTEM
/u01/app/oracle/oradata/HOEGH/TEST101.dbf 5 TEST1 AVAILABLE ONLINE
/u01/app/oracle/oradata/HOEGH/TEST201.dbf 6 TEST2 AVAILABLE ONLINE
/u01/app/oracle/oradata/HOEGH/TEST301.dbf 7 TEST3 AVAILABLE ONLINE
/u01/app/oracle/oradata/HOEGH/test.dbf 8 TEST AVAILABLE ONLINE
/u02/oradata/HOEGH/test_tbs01.dbf 9 TEST_TBS AVAILABLE RECOVER
9 rows selected.
SYS@HOEGH>drop tablespace TEST_TBS including contents;
Tablespace dropped.
SYS@HOEGH>
SYS@HOEGH>select file_name,file_id,TABLESPACE_NAME,STATUS,ONLINE_STATUS from dba_data_files;
FILE_NAME FILE_ID TABLESPACE STATUS ONLINE_STATUS
-------------------------------------------------- -------