Version : 11.2.0.3
Noticed the below behaviour in both Solaris 10 and Linux 5.4
Trying to find the free space in a newly created datafile
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
I create a new tablespace. No one has started using it. But when i query dba_free_space.bytes column , it is showing the size I allocated for the SIZE clause in the CREATE TABLESPACE statement which is 2gb. Since it is an unused tablespace and hence an unused datafile, the free space should be showing 6gb.
What can be done to get the accurate info on free space in a data file?
SQL> create tablespace YDMTBS datafile '/u01/app/CLONE1/oradata/pcmbuat/ydmtbs01.dbf' SIZE 2G AUTOEXTEND ON next 20m maxsize 6G ;
Tablespace created.
SQL> desc dba_free_space
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLESPACE_NAME VARCHAR2(30)
FILE_ID NUMBER
BLOCK_ID NUMBER
BYTES NUMBER
BLOCKS NUMBER
RELATIVE_FNO NUMBER
SQL> select BYTES/1024/1024/1024 from dba_free_space where TABLESPACE_NAME = 'YDMTBS';
BYTES/1024/1024/1024
--------------------
1.99902344 ---------------> it should be 6gb . But this is showing 2gb which is the what i specified for SIZE clause in the CREATE TABLESPACE statement
SQL> set lines 300
SQL> col FILE_NAME format a50
SQL> select file_name, maxbytes/1024/1024 , bytes/1024/1024 , user_bytes/1024/1024 , autoextensible from dba_Data_Files where tablespace_name = 'YDMTBS';
FILE_NAME MAXBYTES/1024/1024 BYTES/1024/1024 USER_BYTES/1024/1024 AUT
-------------------------------------------------- ------------------ --------------- -------------------- ---
/u01/app/CLONE1/oradata/pcmbuat/ydmtbs01.dbf 6144 2048 2047 YES
http://docs.oracle.com/cd/E11882_01/server.112/e17110/statviews_3194.htm