“What is the difference between Read-Only tablespace and Offline tablespace, in terms of SCNs??”This was asked to me long time back.As per the Books,making a tablespace read-only prevents write operations on the datafiles in the tablespace.Bringing an online tablespace offline, makes the schema objects within the tablespace unavailable to the database users.But still i think i am not able to find the answer (in terms of SCNs).It would be great if someone can help me find the answer.I did try few things and below is the observation :-
Created two tablespaces test_off and test_ro
06:27:38 SQL> create tablespace test_off;
Tablespace created.
06:27:40 SQL> select TABLESPACE_NAME,STATUS,CONTENTS,LOGGING,SEGMENT_SPACE_MANAGEMENT,RETENTION from dba_tablespaces
06:27:47 2 where TABLESPACE_NAME=upper('&tbs_name');
Enter value for tbs_name: test_off
old 2: where TABLESPACE_NAME=upper('&tbs_name')
new 2: where TABLESPACE_NAME=upper('test_off')
TABLESPACE_NAME STATUS CONTENTS LOGGING SEGMEN RETENTION
------------------------------ --------- --------- --------- ------ -----------
TEST_OFF ONLINE PERMANENT LOGGING AUTO NOT APPLY
06:27:50 SQL> create tablespace test_ro;
Tablespace created.
06:28:03 SQL> select TABLESPACE_NAME,STATUS,CONTENTS,LOGGING,SEGMENT_SPACE_MANAGEMENT,RETENTION from dba_tablespaces
06:28:06 2 where TABLESPACE_NAME=upper('&tbs_name');
Enter value for tbs_name: test_ro
old 2: where TABLESPACE_NAME=upper('&tbs_name')
new 2: where TABLESPACE_NAME=upper('test_ro')
TABLESPACE_NAME STATUS CONTENTS LOGGING SEGMEN RETENTION
------------------------------ --------- --------- --------- ------ -----------
TEST_RO ONLINE PERMANENT LOGGING AUTO NOT APPLY
06:28:10 SQL> select name,format,fuzzy,recover,checkpoint_change#,to_char(checkpoint_time,'DD-MM-YY HH24:MI:SS')chkpt_time,last_dealloc_scn
06:33:51 2 from v$datafile_header where TABLESPACE_NAME in ('TEST_OFF','TEST_RO')
06:33:52 3 ;
NAME FORMAT FUZ REC CHECKPOINT_CHANGE# CHKPT_TIME LAST_DEALLOC_SCN
-------------------------------------------------- --------------- --- --- ------------------ ----------------- ----------------
+DATA3/vxcap/datafile/test_off.2376.718698459 0 YES NO 22540994019 11-05-10 06:27:40
+DATA3/vxcap/datafile/test_ro.7047.718698483 0 YES NO 22540994057 11-05-10 06:28:03
Forced a checkpoint
06:34:11 SQL> alter system checkpoint;
System altered.
06:34:21 SQL>
06:34:22 SQL> select name,format,fuzzy,recover,checkpoint_change#,to_char(checkpoint_time,'DD-MM-YY HH24:MI:SS')chkpt_time,last_dealloc_scn
06:34:43 2 from v$datafile_header where TABLESPACE_NAME in ('TEST_OFF','TEST_RO');
NAME FORMAT FUZ REC CHECKPOINT_CHANGE# CHKPT_TIME LAST_DEALLOC_SCN
--------------------------------------------- --------------- --- --- ------------------ ----------------- ----------------
+DATA3/vxcap/datafile/test_off.2376.718698459 0 YES NO 22540994225 11-05-10 06:34:21
+DATA3/vxcap/datafile/test_ro.7047.718698483 0 YES NO 22540994225 11-05-10 06:34:21
Switched the redo log files
06:34:44 SQL> alter system switch logfile;
System altered.
06:34:57 SQL>
06:34:58 SQL>
06:34:58 SQL> select name,format,fuzzy,recover,checkpoint_change#,to_char(checkpoint_time,'DD-MM-YY HH24:MI:SS')chkpt_time,last_dealloc_scn
06:35:09 2 from v$datafile_header where TABLESPACE_NAME in ('TEST_OFF','TEST_RO');
NAME FORMAT FUZ REC CHECKPOINT_CHANGE# CHKPT_TIME LAST_DEALLOC_SCN
--------------------------------------------- --------------- --- --- ------------------ ----------------- ----------------
+DATA3/vxcap/datafile/test_off.2376.718698459 0 YES NO 22540994239 11-05-10 06:34:57
+DATA3/vxcap/datafile/test_ro.7047.718698483 0 YES NO 22540994239 11-05-10 06:34:57
Made the tablespace TEST_OFF offline
06:35:11 SQL> alter tablespace test_off offline; Tablespace altered. 06:37:15 SQL> 06:37:16 SQL> select TABLESPACE_NAME,STATUS,CONTENTS,LOGGING,SEGMENT_SPACE_MANAGEMENT,RETENTION from dba_tablespaces 06:37:38 2 where TABLESPACE_NAME='TEST_OFF'; TABLESPACE_NAME STATUS CONTENTS LOGGING SEGMEN RETENTION ------------------------------ --------- --------- --------- ------ TEST_OFF OFFLINE PERMANENT LOGGING AUTO NOT APPLY
Lets check the Datafile Header
06:37:52 SQL> select name,format,fuzzy,recover,checkpoint_change#,to_char(checkpoint_time,'DD-MM-YY HH24:MI:SS')chkpt_time,last_dealloc_scn
06:38:02 2 from v$datafile_header where TABLESPACE_NAME in ('TEST_OFF','TEST_RO');
NAME FORMAT FUZ REC CHECKPOINT_CHANGE# CHKPT_TIME LAST_DEALLOC_SCN
--------------------------------------------- --------------- --- --- ------------------ ----------------- ----------------
+DATA3/vxcap/datafile/test_ro.7047.718698483 0 YES NO 22540994239 11-05-10 06:34:57
No row for the TEST_OFF tablespace which was just made offline in v$datafile_header.
Changing the read-write tablespace TEST_RO to read-only
06:38:04 SQL> alter tablespace test_ro read only; Tablespace altered. 06:40:00 SQL> select TABLESPACE_NAME,STATUS,CONTENTS,LOGGING,SEGMENT_SPACE_MANAGEMENT,RETENTION from dba_tablespaces 06:40:29 2 where TABLESPACE_NAME='TEST_RO'; TABLESPACE_NAME STATUS CONTENTS LOGGING SEGMEN RETENTION ------------------------------ --------- --------- --------- ------ TEST_RO READ ONLY PERMANENT LOGGING AUTO NOT APPLY 06:40:29 SQL>
Now, lets check the datafile header status from v$datafile_header
06:40:29 SQL>
06:40:30 SQL> select name,format,fuzzy,recover,checkpoint_change#,to_char(checkpoint_time,'DD-MM-YY HH24:MI:SS')chkpt_time,last_dealloc_scn
06:40:44 2 from v$datafile_header where TABLESPACE_NAME in ('TEST_OFF','TEST_RO');
NAME FORMAT FUZ REC CHECKPOINT_CHANGE# CHKPT_TIME LAST_DEALLOC_SCN
--------------------------------------------- --------------- --- --- ------------------ ----------------- ----------------
+DATA3/vxcap/datafile/test_ro.7047.718698483 0 NO NO 22540994360 11-05-10 06:40:00
06:40:45 SQL>
Interesting to notice the value for column “fuzzy”has changed from “YES” to “NO”
For read-Only tablespaces, the datafile headers are not updated
06:40:46 SQL> alter system checkpoint;
System altered.
06:40:56 SQL>
06:40:57 SQL> select name,format,fuzzy,recover,checkpoint_change#,to_char(checkpoint_time,'DD-MM-YY HH24:MI:SS')chkpt_time,last_dealloc_scn
06:41:06 2 from v$datafile_header where TABLESPACE_NAME in ('TEST_OFF','TEST_RO');
NAME FORMAT FUZ REC CHECKPOINT_CHANGE# CHKPT_TIME LAST_DEALLOC_SCN
--------------------------------------------- --------------- --- --- ------------------ ----------------- ----------------
+DATA3/vxcap/datafile/test_ro.7047.718698483 0 NO NO 22540994360 11-05-10 06:40:00
06:41:07 SQL>
06:41:08 SQL> alter system switch logfile;
System altered.
06:41:19 SQL>
06:41:20 SQL> select name,format,fuzzy,recover,checkpoint_change#,to_char(checkpoint_time,'DD-MM-YY HH24:MI:SS')chkpt_time,last_dealloc_scn
06:41:33 2 from v$datafile_header where TABLESPACE_NAME in ('TEST_OFF','TEST_RO');
NAME FORMAT FUZ REC CHECKPOINT_CHANGE# CHKPT_TIME LAST_DEALLOC_SCN
--------------------------------------------- --------------- --- --- ------------------ ----------------- ----------------
+DATA3/vxcap/datafile/test_ro.7047.718698483 0 NO NO 22540994360 11-05-10 06:40:00
06:41:34 SQL>
Conclusion from my observation :-
1. Once a tablespace is made offline, it is simply unavailable to the database.Hence, no information is present in the v$datafile_header.
2. When a tablespace is made read-only, the checkpoint_change# (Datafile checkpoint change#)visible in v$datafile_header freezes.
3. The Fuzzy cloumn value changes to “NO” ,when the tablespace is made Read-Only.
For more information on FUZZY refer http://www.freelists.org/post/oracle-l/FUZZY-column-in-VDATAFILE-HEADER,4
Let me know your views on this 🙂