一.只读表空间和SCN的关系
--执行完检查点后，创建两个表空间，分记录下创建时的SCN
A>alter system checkpoint;
System altered.
A>select NAME,FILE#,CHECKPOINT_CHANGE# from v$datafile;
NAME FILE# CHECKPOINT_CHANGE#
-------------------------------------------------- ---------- ------------------
+FRA/orcl/datafile/system.256.847546913 1 779052
+FRA/orcl/datafile/sysaux.257.847546983 2 779052
+FRA/orcl/datafile/undotbs1.258.847547051 3 779052
+FRA/orcl/datafile/users.260.848268619 4 779052
+FRA/orcl/datafile/test.261.848268689 5 779052
A>set time on
20:24:35 A>create tablespace readonly datafile size 4m;
Tablespace created.
20:25:40 A>create tablespace readwrite datafile size 4m;
Tablespace created.
20:25:48 A>select NAME,FILE#,CHECKPOINT_CHANGE# from v$datafile;
NAME FILE# CHECKPOINT_CHANGE#
-------------------------------------------------- ---------- ------------------
+FRA/orcl/datafile/system.256.847546913 1 779052
+FRA/orcl/datafile/sysaux.257.847546983 2 779052
+FRA/orcl/datafile/undotbs1.258.847547051 3 779052
+FRA/orcl/datafile/users.260.848268619 4 779052
+FRA/orcl/datafile/test.261.848268689 5 779052
+FRA/orcl/datafile/readonly.262.848348705 6 779090
+FRA/orcl/datafile/readwrite.264.848348743 7 779377
7 rows selected.
SCN是与时间有关的单调递增函数，反映在这里就是，readonly表空间的SCN小于readwrite表空间的SCN，因为readonly创建在前
--将readonly表空间设为只读，并记录下相应的SCN——可以看出，SCN就是表示某种操作开始或完成时那一刻的时间，只不过不是用时分秒的形式而已
20:28:07 SYS>alter tablespace readonly read only;
Tablespace altered.
20:28:13 SYS>select NAME,FILE#,CHECKPOINT_CHANGE# from v$datafile;
NAME FILE# CHECKPOINT_CHANGE#
-------------------------------------------------- ---------- ------------------
+FRA/orcl/datafile/system.256.847546913 1 779052
+FRA/orcl/datafile/sysaux.257.847546983 2 779052
+FRA/orcl/datafile/undotbs1.258.847547051 3 779052
+FRA/orcl/datafile/users.260.848268619 4 779052
+FRA/orcl/datafile/test.261.848268689 5 779052
+FRA/orcl/datafile/readonly.262.848348705 6 779696 //readonly表空间设为只读那一刻的SCN，此后将保持不变
+FRA/orcl/datafile/readwrite.264.848348743 7 779377
7 rows selected.
A>select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
TEST ONLINE
READONLY READ ONLY
READWRITE ONLINE
8 rows selected.
--再来一次检查点，发现只有readonly表空间没动（块发生变化了，SCN才变，只读块不可能变，所以SCN也保持不变）
SYS>alter system checkpoint;
System altered.
SYS>select NAME,FILE#,CHECKPOINT_CHANGE# from v$datafile;
NAME FILE# CHECKPOINT_CHANGE#
-------------------------------------------------- ---------- ------------------
+FRA/orcl/datafile/system.256.847546913 1 779784
+FRA/orcl/datafile/sysaux.257.847546983 2 779784
+FRA/orcl/datafile/undotbs1.258.847547051 3 779784
+FRA/orcl/datafile/users.260.848268619 4 779784
+FRA/orcl/datafile/test.261.848268689 5 779784
+FRA/orcl/datafile/readonly.262.848348705 6 779696
+FRA/orcl/datafile/readwrite.264.848348743 7 779784
7 rows selected.
--分别将两个表空间置为离线，观察SCN的变化，发现readwrite表空间变了，readonly仍然没变
SYS>alter tablespace readonly offline;
Tablespace altered.
SYS>select NAME,FILE#,CHECKPOINT_CHANGE# from v$datafile;
NAME FILE# CHECKPOINT_CHANGE#
-------------------------------------------------- ---------- ------------------
+FRA/orcl/datafile/system.256.847546913 1 779784
+FRA/orcl/datafile/sysaux.257.847546983 2 779784
+FRA/orcl/datafile/undotbs1.258.847547051 3 779784
+FRA/orcl/datafile/users.260.848268619 4 779784
+FRA/orcl/datafile/test.261.848268689 5 779784
+FRA/orcl/datafile/readonly.262.848348705 6 779696
+FRA/orcl/datafile/readwrite.264.848348743 7 779784
7 rows selected.
SYS>alter tablespace readwrite offline;
Tablespace altered.
SYS>select NAME,FILE#,CHECKPOINT_CHANGE# from v$datafile;
NAME FILE# CHECKPOINT_CHANGE#
-------------------------------------------------- ---------- ------------------
+FRA/orcl/datafile/system.256.847546913 1 779784
+FRA/orcl/datafile/sysaux.257.847546983 2 779784
+FRA/orcl/datafile/undotbs1.258.847547051 3 779784
+FRA/orcl/datafile/users.260.848268619 4 779784
+FRA/orcl/datafile/test.261.848268689 5 779784
+FRA/orcl/datafile/readonly.262.848348705 6 779696
+FRA/orcl/datafile/readwrite.264.848348743 7 779880
7 rows selected.
--离线后，检查点不起作用
SYS>alter system checkpoint;
System altered.
SYS>select NAME,FILE#,CHECKPOINT_CHANGE# from v$datafile;
NAME FILE# CHECKPOINT_CHANGE#
-------------------------------------------------- ---------- ------------------
+FRA/orcl/datafile/system.256.847546913 1 779909
+FRA/orcl/datafile/sysaux.257.847546983 2 779909
+FRA/orcl/datafile/undotbs1.258.847547051 3 779909
+FRA/orcl/datafile/users.260.848268619 4 779909
+FRA/orcl/datafile/test.261.848268689 5 779909
+FRA/orcl/datafile/readonly.262.848348705 6 779696
+FRA/orcl/datafile/readwrite.264.848348743 7 779880
7 rows selected.
--上线操作只对readwrite表空间有影响
SYS>alter tablespace readonly online;
Tablespace altered.
SYS>select NAME,FILE#,CHECKPOINT_CHANGE# from v$datafile;
NAME FILE# CHECKPOINT_CHANGE#
-------------------------------------------------- ---------- ------------------
+FRA/orcl/datafile/system.256.847546913 1 779909
+FRA/orcl/datafile/sysaux.257.847546983 2 779909
+FRA/orcl/datafile/undotbs1.258.847547051 3 779909
+FRA/orcl/datafile/users.260.848268619 4 779909
+FRA/orcl/datafile/test.261.848268689 5 779909
+FRA/orcl/datafile/readonly.262.848348705 6 779696
+FRA/orcl/datafile/readwrite.264.848348743 7 779880
7 rows selected.
SYS>alter tablespace readwrite online;
Tablespace altered.
SYS>select NAME,FILE#,CHECKPOINT_CHANGE# from v$datafile;
NAME FILE# CHECKPOINT_CHANGE#
-------------------------------------------------- ---------- ------------------
+FRA/orcl/datafile/system.256.847546913 1 779909
+FRA/orcl/datafile/sysaux.257.847546983 2 779909
+FRA/orcl/datafile/undotbs1.258.847547051 3 779909
+FRA/orcl/datafile/users.260.848268619 4 779909
+FRA/orcl/datafile/test.261.848268689 5 779909
+FRA/orcl/datafile/readonly.262.848348705 6 779696
+FRA/orcl/datafile/readwrite.264.848348743 7 779925
7 rows selected.
综上，只读表空间不管进行什么操作，永远保持其被设为只读寻一刻的SCN
而普通表空间只在离线后不受检查点影响，离线操作本身仍然会修改SCN
二.只读表空间和DML,DDL的关系
A>create table t(id int) tablespace readonly;
Table created.
A>insert into t values(1);
1 row created.
A>commit;
Commit complete.
A>create index t_ind on t(id) tablespace readonly;
Index created.
--表空间设为只读后，插入/更新/删除都不行
SYS>alter tablespace readonly read only;
Tablespace altered.
A>insert into t values(2);
insert into t values(2)
*
ERROR at line 1:
ORA-00372: file 6 cannot be modified at this time
ORA-01110: data file 6: '+FRA/orcl/datafile/readonly.262.848348705'
A>update t set id=2;
update t set id=2
*
ERROR at line 1:
ORA-00372: file 6 cannot be modified at this time
ORA-01110: data file 6: '+FRA/orcl/datafile/readonly.262.848348705'
A>delete from t;
delete from t
*
ERROR at line 1:
ORA-00372: file 6 cannot be modified at this time
ORA-01110: data file 6: '+FRA/orcl/datafile/readonly.262.848348705'
--truncate也不行，还是有点费解的
A>truncate table t;
truncate table t
*
ERROR at line 1:
ORA-00372: file 6 cannot be modified at this time
ORA-01110: data file 6: '+FRA/orcl/datafile/readonly.262.848348705'
--同样是建表，为什么t2可以，t3不行呢？
A>create table t2(id int) tablespace readonly;
Table created.
A>create table t3 tablespace readonly as select * from t;
create table t3 tablespace readonly as select * from t
*
ERROR at line 1:
ORA-01647: tablespace 'READONLY' is read-only, cannot allocate space in it
--原因在于段的分配上，t2的段被延迟分配了，所以只是个定义，并没有实质性的分配段的操作，也就不用动表空间
DBMS_METADATA.GET_DDL(UPPER('TABLE'),UPPER('T2'))
--------------------------------------------------------------------------------
CREATE TABLE "A"."T2"
( "ID" NUMBER(*,0)
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "READONLY"
A>alter table t add name varchar2(10);
Table altered.
A>desc t
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(38)
NAME VARCHAR2(10)
--本来以为ID列有数据，所以不能删，没想到NAME列没数据也不能删
A>alter table t drop column id;
alter table t drop column id
*
ERROR at line 1:
ORA-12985: tablespace 'READONLY' is read only, cannot drop column
A>alter table t drop column name;
alter table t drop column name
*
ERROR at line 1:
ORA-12985: tablespace 'READONLY' is read only, cannot drop column
--删除索引和表没问题
A>drop index t_ind;
Index dropped.
A>drop table t;
Table dropped.
综上，表空间设为只读后，凡是跟数据有关的操作都不允许，比较特殊的有两个地方，一是truncate，另一个是删除空列
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29646619/viewspace-1168628/ ，如需转载，请注明出处，否则将追究法律责任。
