I created a tablespace using below command -
SQL> CREATE TABLESPACE tbsoff DATAFILE '/u02/oradata/ORCALL/3D3F7C4A105E424DE0634000000AB352/datafile/tbsoff.dbf' SIZE 20M OFFLINE;
Tablespace created.
SQL>
Then I checked the status and size of this datafile using dba_data_files view but it is showing null as shown below -
SQL> SELECT TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024 SIZE_OF_FILE,BLOCKS,AUTOEXTENSIBLE,MAXBYTES/1024/1024 MAXSIZE_OF_DATAFILE,ONLINE_STATUS FROM DBA_DATA_FILES;
TABLESPACE_NAME FILE_NAME SIZE_OF_FILE BLOCKS AUT MAXSIZE_OF_DATAFILE ONLINE_
------------------------------ ------------------------------------------------------------------------------------------ ------------ ---------- --- ------------------- -------
SYSAUX /u02/oradata/ORCALL/3D3F7C4A105E424DE0634000000AB352/datafile/o1_mf_sysaux_nbtmrtw9_.dbf 320 40960 YES 32767.9844 ONLINE
SYSTEM /u02/oradata/ORCALL/3D3F7C4A105E424DE0634000000AB352/datafile/o1_mf_system_nbtmrtw3_.dbf 270 34560 YES 32767.9844 SYSTEM
UNDOTBS1 /u02/oradata/ORCALL/3D3F7C4A105E424DE0634000000AB352/datafile/o1_mf_undotbs1_nbtmrtwb_.dbf 100 12800 YES 32767.9844 ONLINE
USERS /u02/oradata/ORCALL/3D3F7C4A105E424DE0634000000AB352/datafile/o1_mf_users_nbtmsbbk_.dbf 5 640 YES 32767.9844 ONLINE
XXLKQ_WFA4 /u02/oradata/ORCALL/3D3F7C4A105E424DE0634000000AB352/datafile/xxlkq_wfa4.dbf 100 12800 YES 200 ONLINE
TBSOFF /u02/oradata/ORCALL/3D3F7C4A105E424DE0634000000AB352/datafile/tbsoff.dbf OFFLINE
6 rows selected.
SQL>
Then I made this tablespace online using below command -
alter tablespace tbsoff online;
and then again I queried the dba_data_files view but still it is showing null in place of size, why?
SQL> SELECT TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024 SIZE_OF_FILE,BLOCKS,AUTOEXTENSIBLE,MAXBYTES/1024/1024 MAXSIZE_OF_DATAFILE,ONLINE_STATUS FROM DBA_DATA_FILES;
TABLESPACE_NAME FILE_NAME SIZE_OF_FILE BLOCKS AUT MAXSIZE_OF_DATAFILE ONLINE_
------------------------------ ------------------------------------------------------------------------------------------ ------------ ---------- --- ------------------- -------
SYSAUX /u02/oradata/ORCALL/3D3F7C4A105E424DE0634000000AB352/datafile/o1_mf_sysaux_nbtmrtw9_.dbf 320 40960 YES 32767.9844 ONLINE
SYSTEM /u02/oradata/ORCALL/3D3F7C4A105E424DE0634000000AB352/datafile/o1_mf_system_nbtmrtw3_.dbf 270 34560 YES 32767.9844 SYSTEM
UNDOTBS1 /u02/oradata/ORCALL/3D3F7C4A105E424DE0634000000AB352/datafile/o1_mf_undotbs1_nbtmrtwb_.dbf 100 12800 YES 32767.9844 ONLINE
USERS /u02/oradata/ORCALL/3D3F7C4A105E424DE0634000000AB352/datafile/o1_mf_users_nbtmsbbk_.dbf 5 640 YES 32767.9844 ONLINE
XXLKQ_WFA4 /u02/oradata/ORCALL/3D3F7C4A105E424DE0634000000AB352/datafile/xxlkq_wfa4.dbf 100 12800 YES 200 ONLINE
TBSOFF /u02/oradata/ORCALL/3D3F7C4A105E424DE0634000000AB352/datafile/tbsoff.dbf ONLINE
6 rows
SQL>