Tablespace Offline, Read Only, etc

在创建Oracle 10g数据库的时候，会默认创建以下表空间，

- SYSTEM

- SYSAUX

- TEMP

- UNDOTBS1

- USER

- Example

 

sys@ORCL> select tablespace_name, status, extent_management from dba_tablespaces;

TABLESPACE_NAME                STATUS    EXTENT_MAN
------------------------------ --------- ----------
SYSTEM                         ONLINE    LOCAL
SYSAUX                         ONLINE    LOCAL
UNDOTBS1                       ONLINE    LOCAL
TEMP                           ONLINE    LOCAL
USERS                          ONLINE    LOCAL
EXAMPLE                        ONLINE    LOCAL

 

 

 现在想知道哪些表空间可以被置成offline，或者read only状态，测试如下....

 

(1) Take tablespace offline

 

sys@ORCL> alter tablespace system offline;
alter tablespace system offline
*
ERROR at line 1:
ORA-01541: system tablespace cannot be brought offline; shut down if necessary


sys@ORCL> alter tablespace sysaux offline;

Tablespace altered.

sys@ORCL> alter tablespace undotbs1 offline;
alter tablespace undotbs1 offline
*
ERROR at line 1:
ORA-30042: Cannot offline the undo tablespace


sys@ORCL> alter tablespace temp offline;
alter tablespace temp offline
*
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE


sys@ORCL> alter tablespace users offline;

Tablespace altered.

sys@ORCL> alter tablespace example offline;

Tablespace altered.

sys@ORCL> select tablespace_name, status, extent_management from dba_tablespaces;

TABLESPACE_NAME                STATUS    EXTENT_MAN
------------------------------ --------- ----------
SYSTEM                         ONLINE    LOCAL
SYSAUX                         OFFLINE   LOCAL
UNDOTBS1                       ONLINE    LOCAL
TEMP                           ONLINE    LOCAL
USERS                          OFFLINE   LOCAL
EXAMPLE                        OFFLINE   LOCAL

 

 

可以看到，

 1） SYSTEM, UNDO 是不可以被take offline的

 2） SYSAUX, USERS, EXMPALE是可以take offline的

 3） TEMP表空间需要特殊处理，因为给出的错误信息是invalid option

 

sys@ORCL> alter tablespace temp offline;
alter tablespace temp offline
*
ERROR at line 1:

ORA-03217: invalid option for alter of TEMPORARY TABLESPACE 

 

从Oracle官方文档查到如下内容：

You cannot take a temporary tablespace offline. Instead, you take its tempfile offline. The view V$TEMPFILE displays online status for a tempfile. 

 

 也即是说对于临时表空间来说，不可以直接在表空间层次来进行take offline操作，应该从tempfile层次来进行，操作如下

 

 

sys@ORCL> alter tablespace temp tempfile offline;
alter tablespace temp tempfile offline
*
ERROR at line 1:
ORA-12905: default temporary tablespace cannot be brought OFFLINE

 

 

 给出的信息表示，默认的临时表空间是不可以被taken offline的。 

 

关于表空间是否可以被taken offline, 总结如下：

 

1） SYTEM, UNDO表空间是不可以offline的，默认的TEMP表空间是不可以offline的

2） SYSAUX 和其他自定义的表空间的可以offline的。

 

 虽然直接将临时表空间take offline失败了，但是可以通过alter database命令来直接将临时表空间依赖的临时文件take offline，从而使得临时表空间“事实上”offline. 

 

 

sys@ORCL> select * from dba_temp_files;

FILE_NAME
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS    RELATIVE_FNO AUT   MAXBYTE
---------- ------------------------------ ---------- ---------- --------- ------------ --- ---------
E:\APP\FANGYU\ORADATA\ORCL\TEMP01.DBF
         1 TEMP                             28311552       3456 AVAILABLE            1 YES 3.4360E+1


sys@ORCL> alter database tempfile 1 offline;

Database altered.

 

 

但是显然这样做很不好~！

 

将以上操作全部还原，将表空间都置成online状态。 

 

（2）  Set Tablespace Read Only

 

sys@ORCL> alter tablespace system read only;
alter tablespace system read only
*
ERROR at line 1:
ORA-01643: system tablespace can not be made read only


sys@ORCL> alter tablespace sysaux read only;
alter tablespace sysaux read only
*
ERROR at line 1:
ORA-13505: SYSAUX tablespace can not be made read only


sys@ORCL> alter tablespace undotbs1 read only;
alter tablespace undotbs1 read only
*
ERROR at line 1:
ORA-30021: Operation not allowed on undo tablespace


sys@ORCL> alter tablespace temp read only;
alter tablespace temp read only
*
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE


sys@ORCL> alter tablespace users read only;

Tablespace altered.

sys@ORCL> alter tablespace example read only;

Tablespace altered.

sys@ORCL>

 

 

 可以看到： 

1） SYSTEM, SYSAUX, UNDO， TEMP 表空间是不可以read only的 

2）自定义表空间是可以read only 的