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