OCP 1Z0 052 144

144. You execute the following command to change the status of the SALES tablespace: 
SQL> ALTER TABLESPACE sales OFFLINE; 
Which statements describe the effect of the command? (Choose all that apply.) 
A.The tablespace would require recovery to go back online. 
B.A checkpoint is taken on all data files that are associated with the SALES tablespace. 
C.The sessions that subsequently try to access the objects in the SALES tablespace receive an error. 
D.The new status of the SALES tablespace is recorded in the control file when the database instance is 
closed. 
Answer: BC


offline之前的事物，不受影响（包括没提交的）
offline之后不能执行事物

Online and Offline Tablespaces

A tablespace can be online (accessible) or offline (not accessible) whenever the database is open. A tablespace is usually online so that its data is available to users. The SYSTEM tablespace and temporary tablespaces cannot be taken offline.

A tablespace can go offline automatically or manually. For example, you can take a tablespace offline for maintenance or backup and recovery. The database automatically takes a tablespace offline when certain errors are encountered, as when the database writer (DBW) process fails in several attempts to write to a data file. Users trying to access tables in an offline tablespace receive an error.

When a tablespace goes offline, the database does the following:

• The database does not permit subsequent DML statements to reference objects in the offline tablespace. An offline tablespace cannot be read or edited by any utility other than Oracle Database.

• Active transactions with completed statements that refer to data in that tablespace are not affected at the transaction level.

• The database saves undo data corresponding to those completed statements in a deferred undo segment in the SYSTEM tablespace. When the tablespace is brought online, the database applies the undo data to the tablespace, if needed.
  
  

测试案例如下

SELECT * FROM v$mystat WHERE ROWNUM <= 1;

       SID STATISTIC#      VALUE
---------- ---------- ----------
       133          0          0

SELECT * FROM v$mystat WHERE ROWNUM <= 1;

       SID STATISTIC#      VALUE
---------- ---------- ----------
       147          0          0

SQL> create tablespace ts_offline;

Tablespace created.

 

 

SQL> create table t_offline (c1 number) tablespace ts_offline;

Table created.

SQL> insert into t_offline(c1) select level from dual connect by level <=100;

100 rows created.

SQL> alter tablespace ts_offline offline;

Tablespace altered.

 

 

SQL> commit;

Commit complete.

SQL> insert into t_offline(c1) select level from dual connect by level <=100;
insert into t_offline(c1) select level from dual connect by level <=100
            *
ERROR at line 1:
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: 'K:\ORADATA\ORCL\DATAFILE\O1_MF_TS_OFFLI_9T7OGPDP_.DBF'

SQL> alter tablespace ts_offline online;

Tablespace altered.

 

 

SQL> rollback;

Rollback complete.

SQL> select count(*) from t_offline;

  COUNT(*)
----------
       100