Tuesday, December 4, 2012

TABLESPACE Status Not showing as OFFLINE when brought to OFFLINE state from READ ONLY mode




##########################
## TABLESPACE Status Not showing as OFFLINE when brought to OFFLINE state from READ ONLY mode 
##########################

Yesterday one of my friend was asking that when a TABLESPACE was brought into OFFLINE mode from READ ONLY mode, Command succeeded but still DBA_TABLESPACE shows as READ ONLY and DBA_DATA_FILES shows as AVAILABLE.

So i wanna check this. Lets see how we can find the correct status of the tablespace.

##########################
## Place the TABLESPACE in READ ONLY mode
##########################


SQL> Alter tablespace users read only;

Tablespace altered.

SQL> Select tablespace_name,status from dba_tablespaces where tablespace_name='USERS';

TABLESPACE_NAME                STATUS
------------------------------ ---------
USERS                          READ ONLY

SQL> select tablespace_name,status from dba_data_files where tablespace_name='USERS';

TABLESPACE_NAME                STATUS
------------------------------ ---------
USERS                          AVAILABLE



##########################
## Place the TABLESPACE in OFFLINE mode
##########################



SQL> alter tablespace users offline;

Tablespace altered.

SQL> Select tablespace_name,status from dba_tablespaces where tablespace_name='USERS';

TABLESPACE_NAME                STATUS
------------------------------ ---------
USERS                          READ ONLY

SQL> select tablespace_name,status from dba_data_files where tablespace_name='USERS';

TABLESPACE_NAME                STATUS
------------------------------ ---------
USERS                          AVAILABLE

So When a TABLESPACE mode is changed from READ-ONLY to OFFLINE, it doesnt show up in DBA_DATA_FILES or DBA_TABLESPACES.



##########################
## How to check the correct Status of the TABLESPACE
##########################

  1. Use V$DATAFILE to know the correct status as the status of the tablespace is recorded in Controlfile.

Select tablespace_name,df.status, name "Datafile"from dba_data_files ddf,v$datafile df where ddf.file_id=df.file# and ddf.tablespace_name='USERS';

TABLESPACE_NAME                STATUS  Datafile
------------------------------ ------- ---------------------------------------------------------------------------
USERS                          OFFLINE H:\APP\ADMINISTRATOR\ORADATA\ORA11G\USERS01.DBF


SQL> select tablespace_name,status from dba_data_files where tablespace_name='USERS';

TABLESPACE_NAME                STATUS
------------------------------ ---------
USERS                          AVAILABLE

SQL> Select tablespace_name,status from dba_tablespaces where tablespace_name='USERS';

TABLESPACE_NAME                STATUS
------------------------------ ---------
USERS                          READ ONLY

No comments: