This discussion is archived
13 Replies Latest reply: Apr 11, 2013 4:43 PM by 548518 RSS

Question on 'BYTES' in dba_free_space

548518 Explorer
Currently Being Moderated
I have 11gr2 db.
SQL> select sum(bytes)/1024/1024/1024 Gbytes_free
  2                 from  sys.dba_free_space
  3  where tablespace_name = 'AI_TS';

GBYTES_FREE
-----------
  29.328003

we have 29GB free for tablespace AI_TS

Let's see what those datafiles in the tablespace:

SQL> select sum(bytes)/1024/1024/1024 Gbytes_alloc,
  2                                sum(maxbytes)/1024/1024/1024 Gbytes_max         
  
  3                 from sys.dba_data_files 
  4  where   tablespace_name= 'AI_TS';



GBYTES_ALLOC GBYTES_MAX
------------ ----------
   63.839844  63.999969

We are alomst FULL!


Question , what the 'BYTES' in dba_free_space ABOUT?

THANKS!
Edited by: msdba on Apr 11, 2013 4:36 PM
  • 1. Re: Question on 'BYTES' in dba_free_space
    548518 Explorer
    Currently Being Moderated
    I try to answer this myself, let me know if it is correct.

    "BYTES" in dba_free_space is the size of the extent in the FREE state.
    But the actual free space shall be obtained from dba_data_files.

    Let me know.
    Thanks
  • 2. Re: Question on 'BYTES' in dba_free_space
    dpapde Explorer
    Currently Being Moderated
    bytes in dba_free_space means total free extents in the tablespace AI_TS. 29GB may or may not be contiguous extents
    whereas
    bytes in dba_data_files means size of file in bytes which is 63G
    and
    maxbytes in dba_data_files is max file size which is also 63G in case of AI_TS tablespace.
  • 3. Re: Question on 'BYTES' in dba_free_space
    548518 Explorer
    Currently Being Moderated
    dpapde wrote:
    bytes in dba_free_space means total free extents in the tablespace AI_TS. 29GB may or may not be contiguous extents
    whereas
    bytes in dba_data_files means size of file in bytes which is 63G
    and
    maxbytes in dba_data_files is max file size which is also 63G in case of AI_TS tablespace.
    I think the "wall" -- sun(maxbtes) as shown in the dba_data_files.
    we are very close to the wall.
    we should waste no time to increase the quota of maxsiize if data file or adding a datafile.
    Let me if i took it wrong.
  • 4. Re: Question on 'BYTES' in dba_free_space
    Justin_Mungal Journeyer
    Currently Being Moderated
    msdba wrote:
    dpapde wrote:
    bytes in dba_free_space means total free extents in the tablespace AI_TS. 29GB may or may not be contiguous extents
    whereas
    bytes in dba_data_files means size of file in bytes which is 63G
    and
    maxbytes in dba_data_files is max file size which is also 63G in case of AI_TS tablespace.
    I think the "wall" -- sun(maxbtes) as shown in the dba_data_files.
    we are very close to the wall.
    we should waste no time to increase the quota of maxsiize if data file or adding a datafile.
    Let me if i took it wrong.
    Just remember that BYTES in DBA_DATA_FILES is simply the size of the file.

    When I'm checking to see if a tablespace needs more space, querying dba_tablespace_usage_metrics is quick and easy:
    SQL> select * from dba_tablespace_usage_metrics;
    
    TABLESPACE_NAME                USED_SPACE TABLESPACE_SIZE USED_PERCENT
    ------------------------------ ---------- --------------- ------------
    JUSTIN                                128          383710   .033358526
    SYSAUX                             155544          534750    29.087237
    SYSTEM                              94240          468190   20.1285803
    TEMP                                    0          385247            0
    UNDOTBS1                              160          384991   .041559413
    USERS                                2984           16640   17.9326923
    I'll then use a query to see how data files are allocated and configured in the tablespace, and make any adjustments that are necessary.
    column file_name format a60
    set linesize 150
    
    select t.tablespace_name, d.file_name, d.bytes/1024/1024 file_mb, d.maxbytes/1024/1024 max_file_mb,
    d.autoextensible, ((d.increment_by*t.block_size)/1024/1024) increment_mb
    from dba_data_files d, dba_tablespaces t
    where d.tablespace_name = t.tablespace_name
    and d.tablespace_name = '&tablespace';
    
    Enter value for tablespace: JUSTIN
    old   5: and d.tablespace_name = '&tablespace'
    new   5: and d.tablespace_name = 'JUSTIN'
    
    TABLESPACE_NAME                FILE_NAME                                                       FILE_MB MAX_FILE_MB AUT INCREMENT_MB
    ------------------------------ ------------------------------------------------------------ ---------- ----------- --- ------------
    JUSTIN                         /u01/app/oracle/oradata/ORCL/datafile/o1_mf_justin_8mtx19z1_        100       10240 YES          100
                                   .dbf
  • 5. Re: Question on 'BYTES' in dba_free_space
    548518 Explorer
    Currently Being Moderated
    I do not know what is dba_tablespace_usage_metrics About
    gave a try
    
    SQL> select count(TABLESPACE_NAME) from dba_tablespace_usage_metrics;
    
    COUNT(TABLESPACE_NAME)
    ----------------------
                         6
    
    SQL> select count(TABLESPACE_NAME) from dba_tablespaces;
    
    COUNT(TABLESPACE_NAME)
    ----------------------
                        12
    
    
    Did I miss anything?
  • 6. Re: Question on 'BYTES' in dba_free_space
    Justin_Mungal Journeyer
    Currently Being Moderated
    Well... why are you counting the rows in it? I selected all of the rows from the table, I didn't do any count operations.
  • 7. Re: Question on 'BYTES' in dba_free_space
    Justin_Mungal Journeyer
    Currently Being Moderated
    msdba wrote:
    I do not know what is dba_tablespace_usage_metrics About
    gave a try
    
    SQL> select count(TABLESPACE_NAME) from dba_tablespace_usage_metrics;
    
    COUNT(TABLESPACE_NAME)
    ----------------------
    6
    
    SQL> select count(TABLESPACE_NAME) from dba_tablespaces;
    
    COUNT(TABLESPACE_NAME)
    ----------------------
    12
    
    
    Did I miss anything?
    Mine are matching up... see what tablespaces are missing from yours.
    SQL> select tablespace_name from dba_tablespaces;
    
    TABLESPACE_NAME
    ------------------------------
    SYSTEM
    SYSAUX
    UNDOTBS1
    TEMP
    USERS
    JUSTIN
    
    6 rows selected.
    
    SQL> select tablespace_name from dba_tablespace_usage_metrics;
    
    TABLESPACE_NAME
    ------------------------------
    JUSTIN
    SYSAUX
    SYSTEM
    TEMP
    UNDOTBS1
    USERS
    
    6 rows selected.
  • 8. Re: Question on 'BYTES' in dba_free_space
    548518 Explorer
    Currently Being Moderated
    found that view does not count non local managed tablespaces.
  • 9. Re: Question on 'BYTES' in dba_free_space
    Justin_Mungal Journeyer
    Currently Being Moderated
    msdba wrote:
    found that view does not count non local managed tablespaces.
    I was wondering about that; the documentation doesn't say it excludes them though.

    Why would you still be using dictionary managed tablespaces in 11gR2... ?
  • 10. Re: Question on 'BYTES' in dba_free_space
    548518 Explorer
    Currently Being Moderated
    Justin Mungal wrote:
    msdba wrote:
    found that view does not count non local managed tablespaces.
    I was wondering about that; the documentation doesn't say it excludes them though.

    Why would you still be using dictionary managed tablespaces in 11gR2... ?
    good question. it is a 3rd party app started from 2000.
    so we have no role in the changes, except making the recommendations ...
    they do not take things from us well.
  • 11. Re: Question on 'BYTES' in dba_free_space
    Justin_Mungal Journeyer
    Currently Being Moderated
    msdba wrote:
    Justin Mungal wrote:
    msdba wrote:
    found that view does not count non local managed tablespaces.
    I was wondering about that; the documentation doesn't say it excludes them though.

    Why would you still be using dictionary managed tablespaces in 11gR2... ?
    good question. it is a 3rd party app started from 2000.
    so we have no role in the changes, except making the recommendations ...
    they do not take things from us well.
    Ok, well I guess dba_tablespace_usage_metrics isn't an option then... an alternative would be similar to the following. It uses an inline view, from dba_free_space, that is aggregated based on file_id. The inline view is then able to join to dba_data_files on that file_id.
    SELECT a.tablespace_name, a.file_name, 
     a.bytes/1024/1024 allocated_mb, 
     b.free_bytes/1024/1024 free_mb,
     round(((b.free_bytes/1024/1024)/(a.bytes/1024/1024))*100,2) free_pct
    FROM dba_data_files a, 
     (SELECT file_id, SUM(bytes) free_bytes FROM dba_free_space b GROUP BY file_id) b 
    WHERE a.file_id=b.file_id 
     and a.tablespace_name ='&tablespace'
    ORDER BY  b.free_bytes/1024/1024;
    Give it a shot and let me know if you have any problems. It's simple enough.

    Edited by: Justin Mungal on Apr 11, 2013 6:40 PM - Just FYI, I'm sure I found that query on the web somewhere years ago, and tweaked it a little. I can't find the source URL.
  • 12. Re: Question on 'BYTES' in dba_free_space
    548518 Explorer
    Currently Being Moderated
    marked it as resolved a while ago.
    thanks.
  • 13. Re: Question on 'BYTES' in dba_free_space
    Justin_Mungal Journeyer
    Currently Being Moderated
    No problem. Have a good one then.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points