Oracle

Read Only Vs Offline Tablespace

“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 πŸ™‚

Leave a Reply