13 Replies Latest reply: Apr 11, 2013 6:44 PM by Justin_Mungal RSS

    Question on 'BYTES' in dba_free_space

    548518
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        found that view does not count non local managed tablespaces.
                        • 9. Re: Question on 'BYTES' in dba_free_space
                          Justin_Mungal
                          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
                            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
                              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
                                marked it as resolved a while ago.
                                thanks.
                                • 13. Re: Question on 'BYTES' in dba_free_space
                                  Justin_Mungal
                                  No problem. Have a good one then.