管理表空间（六）--改变表空间状态

 

1、改为只读状态

表空间处于read only 状态，不可以做dml 操作，但可以删除对象。

09:20:53 SQL> create table t01 (id int) tablespace cuug;

Table created.

09:25:46 SQL> alter tablespace cuug read only;

Tablespace altered.

09:25:53 SQL> insert into t01 values (1);

insert into t01 values (1)

*

ERROR at line 1:

ORA-00372: file 9 cannot be modified at this time

ORA-01110: data file 9: '/u01/app/oracle/oradata/lx02/cuug01.dbf'

09:26:03 SQL> drop table t01 purge;

Table dropped.

2、改为读写状态

09:26:09 SQL> alter tablespace cuug read write;

Tablespace altered.

3、表空间脱机

09:02:25 SQL> select tablespace_name ,status from dba_tablespaces;

TABLESPACE_NAME                STATUS

------------------------------ ---------

SYSTEM                         ONLINE

UNDOTBS1                       ONLINE

SYSAUX                         ONLINE

TEMP                           ONLINE

USERS                          ONLINE

EXAMPLE                        ONLINE

USERS01                        ONLINE

USERS02                        ONLINE

BIG_TBS                        ONLINE

UNDOTBS02                      ONLINE

TEMP01                         ONLINE

TEMP02                         ONLINE

TEMP03                         ONLINE

TBS_16K                        ONLINE

14 rows selected.

09:04:26 SQL> alter tablespace tbs_16k offline;

Tablespace altered.

09:04:28 SQL> select tablespace_name ,status from dba_tablespaces;

TABLESPACE_NAME                STATUS

------------------------------ ---------

SYSTEM                         ONLINE

UNDOTBS1                       ONLINE

SYSAUX                         ONLINE

TEMP                           ONLINE

USERS                          ONLINE

EXAMPLE                        ONLINE

USERS01                        ONLINE

USERS02                        ONLINE

BIG_TBS                        ONLINE

UNDOTBS02                      ONLINE

TEMP01                         ONLINE

TEMP02                         ONLINE

TEMP03                         ONLINE

TBS_16K                        OFFLINE

14 rows selected.

注意：System、sysaux、默认临时表空间、正在使用的undo不能脱机。

4、表空间联机

09:04:30 SQL> alter tablespace tbs_16k online;

Tablespace altered.

09:08:25 SQL> select tablespace_name ,status from dba_tablespaces;

TABLESPACE_NAME                STATUS

------------------------------ ---------

SYSTEM                         ONLINE

UNDOTBS1                       ONLINE

SYSAUX                         ONLINE

TEMP                           ONLINE

USERS                          ONLINE

EXAMPLE                        ONLINE

USERS01                        ONLINE

USERS02                        ONLINE

BIG_TBS                        ONLINE

UNDOTBS02                      ONLINE

TEMP01                         ONLINE

TEMP02                         ONLINE

TEMP03                         ONLINE

TBS_16K                        ONLINE

14 rows selected.