查询系统当前表空间以及相应的数据文件 SQL> select a.name as tablespace,b.file#,b.status,b.name as datafile from v$tablespace a,v$datafile b where a.ts#=b.ts#; TABLESPACE                          FILE# STATUS  DATAFILE ------------------------------ ---------- ------- ------------------------------ SYSTEM                                  1 SYSTEM  /oracle/CRM2/CRM/system01.dbf SYSAUX                                  3 ONLINE  /oracle/CRM2/CRM/sysaux01.dbf USERS                                   4 ONLINE  /oracle/CRM2/CRM/users01.dbf UNDOTBS2                                6 ONLINE  /oracle/CRM2/CRM/undotbs2.dbf ZX                                      5 ONLINE  /oracle/CRM2/CRM/zx1.dbf ZX                                      2 ONLINE  /oracle/CRM2/CRM/zx2.dbf SQL> alter database datafile 2 offline; Database altered. 不能用offline normal正常offline 因为表空间zx数据文件2已经offline状态 SQL> alter tablespace zx offline; alter tablespace zx offline oracle 11g

* ERROR at line 1: ORA-01191: file 2 is already offline - cannot do a normal offline ORA-01110: data file 2: '/oracle/CRM2/CRM/zx2.dbf' 用offline temporary 离线 SQL> alter tablespace zx offline temporary; Tablespace altered. SQL> alter tablespace zx online; alter tablespace zx online * ERROR at line 1: ORA-01113: file 2 needs media recovery ORA-01110: data file 2: '/oracle/CRM2/CRM/zx2.dbf' SQL> recover datafile 2; Media recovery complete. 使zx表空间online SQL> alter tablespace zx online; Tablespace altered.