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层次来进行,操作如下
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.
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 的
--------------------------------------
Regards,
FangwenYu