查询系统当前表空间以及相应的数据文件
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 http://www.cuug.com/
*
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.