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

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s