12 Replies Latest reply: Dec 31, 2012 1:21 PM by John Spencer RSS

    daafiles used and free space

    941949
      hii everybody,
      i want to check the used and free space of all datafiles of my database(10.2.0.3),platform redhat 5.7 .
      your help is highly appreciated thanks.

      Edited by: 938946 on Dec 31, 2012 4:38 AM
        • 1. Re: daafiles used and free space
          jeneesh
          SELECT
             a.tablespace_name,
             a.file_name,
             round(a.bytes/1024/1024) allocated_bytes_mb,
             b.free_bytes
          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
          ORDER BY
             a.tablespace_name;
          Edited by: jeneesh on Dec 31, 2012 6:13 PM
          http://www.dba-oracle.com/t_free_space_script.htm
          • 2. Re: daafiles used and free space
            941949
            how can i get the SCRIPT RESULT in MB? THANKS.
            • 3. Re: daafiles used and free space
              hitgon
              SELECT
              a.tablespace_name,
              a.file_name,
              a.bytes allocated_bytes,
              (b.free_bytes/1024)/1024 free_bytes_mb
              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
              ORDER BY
              a.tablespace_name;
              • 4. Re: daafiles used and free space
                user503635
                Hi, try this
                select file_name,
                   file_id,
                   table_space,
                   bytes/1024/1024 Mbytes
                   user_bytes/1024/1024 User_Mbytes
                from dba_data_files
                order by 1
                Hope this helps
                • 5. Re: daafiles used and free space
                  jeneesh
                  938946 wrote:
                  how can i get the SCRIPT RESULT in MB? THANKS.
                  Just divide -
                   a.bytes/1024/1024
                  Updated in the code..
                  • 6. Re: daafiles used and free space
                    941949
                    i used the script shown below to check tablespace used and free space.why SHOWING TEMP tablespace 4 times and why 'temp' %used value is in negative?.


                    SELECT/* + RULE */ df.tablespace_name "Tablespace",
                    df.bytes / (1024 * 1024) "Size (MB)",
                    SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
                    Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
                    Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
                    FROM dba_free_space fs,
                    (SELECT tablespace_name,SUM(bytes) bytes
                    FROM dba_data_files
                    GROUP BY tablespace_name) df
                    WHERE fs.tablespace_name (+) = df.tablespace_name
                    GROUP BY df.tablespace_name,df.bytes
                    UNION ALL
                    SELECT /* + RULE */ df.tablespace_name tspace,
                    fs.bytes / (1024 * 1024),
                    SUM(df.bytes_free) / (1024 * 1024),
                    Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
                    Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
                    FROM dba_temp_files fs,
                    (SELECT tablespace_name,bytes_free,bytes_used
                    FROM v$temp_space_header
                    GROUP BY tablespace_name,bytes_free,bytes_used) df
                    WHERE fs.tablespace_name (+) = df.tablespace_name
                    GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
                    ORDER BY 4 DESC;

                    ITS OUTPUT IS AS FOLLOWS:
                    -----------------------------------------
                    Tablespace Size (MB) Free (MB) % Free % Used
                    ------------------------------ ---------- ---------- ---------- ----------
                    TEMP 22213.375 22148.375 100 0
                    TEMP 22213.375 1035.125 100 95
                    ATS 500 499.9375 100 0
                    APPS_TS 500 499.9375 100 0
                    TEMP 1100 22148.375 94 -1913
                    TEMP 1100 1035.125 94 6
                    ODP 100 90.4375 90 10
                    • 7. Re: daafiles used and free space
                      John Spencer
                      Your query is needlessly complicated, and has the aggregation messed up.

                      The views dba_data_files, dba_temp_files and v$temp_space_header all have one row per data file per tablespace. So, you need to aggregate the values based on tablespace name.

                      Similarly, dba_free_space has one row per "block" of free space per tablespace. So, if your apps_ts tablespace has say 4 blocks of free space, then it will have 4 rows for the apps_ts tablespace. Again, you need to aggregate based on tablespace name before doing anything else.

                      The temp tablespace will almost never have any free space because if the way Oracle works. Once an extent is created in the temp tablespace it is never deallocated. When the transaction that "created" the extent is finished using it, it is marked as being available for use by other sessions, but is no longer free space.

                      The query below does what I believe you are looking for for the regular tablespaces. If you really need to show the temp tablespace, then you can add a third query to the union all sub-query to look at dba_temp_files. If there is any free space in the temp tablespace it will show up in dba_free_space.
                      select tablespace_name,
                             round(sum(allocated_space) / 1024 / 1024, 2) "Size (MB)",
                             round(sum(free_space) / 1024 / 1024, 2) "Free (MB)",
                             round((sum(free_space)/sum(allocated_space)) * 100, 2) "% Free",
                             round(((sum(allocated_space) - sum(free_space))/sum(allocated_space)) * 100, 2) "%Used"
                      from (select tablespace_name, bytes allocated_space, 0 free_space
                            from dba_data_files
                            union all
                            select tablespace_name, 0 allocated_space, bytes free_space
                            from dba_free_space)
                      group by tablespace_name
                      Order by 4
                      John
                      • 8. Re: daafiles used and free space
                        941949
                        can you please tell me why temp tablespace's %used is showing in negative.

                        (tablespace name)TEMP (size in MB)1100221 (Free in MB)48.375 (%Free)94 (%Used)-1913
                        • 9. Re: daafiles used and free space
                          Solomon Yakobson
                          938946 wrote:
                          can you please tell me why temp tablespace's %used is showing in negative.
                          How did you manage to get TEMP tablespace in query results? Tablespace TEMP consists of temp files, not data files and is not present in DBA_DATA_FILES.

                          SY.
                          • 10. Re: daafiles used and free space
                            Solomon Yakobson
                            In any case, assuming you created data tablespace called TEMP, most likely it is a rounding error. Query APC posted rounds up accocated space and free space, therefore it should truncate %Used.

                            SY.
                            • 11. Re: daafiles used and free space
                              Solomon Yakobson
                              938946 wrote:
                              (tablespace name)TEMP (size in MB)1100221 (Free in MB)48.375 (%Free)94 (%Used)-1913
                              Actually, something doesn't add up. If TEMP size is 1100221MB and free is 48.375MB then % free should be 0:
                              SQL> select round(48.375 / 1100221 * 100,2) from dual;
                              
                              ROUND(48.375/1100221*100,2)
                              ---------------------------
                                                        0
                              
                              SQL> 
                              So how did you get 94? Post SQL*Plus snippet showing exact query you ran and query results.

                              SY.
                              • 12. Re: daafiles used and free space
                                John Spencer
                                938946 wrote:
                                can you please tell me why temp tablespace's %used is showing in negative.

                                (tablespace name)TEMP (size in MB)1100221 (Free in MB)48.375 (%Free)94 (%Used)-1913
                                Without seeing what you actually have, I can only guess, but as I said, you have the aggregations messed up in both halves of your query.

                                The second half of your original query (below the UNION ALL) is the bit that calculates the space for the temp tablespace. It looks to me as if you have multiple files in your temp tablespace and that the files actually have at least two different sizes. This is from one of my databases the has that situation, and builds up to the query that you are using to get the temporary tablespace information.

                                The basic dba_temp_files query:
                                SQL> select tablespace_name, bytes
                                  2  from dba_temp_files;
                                
                                TABLESPACE_NAME                     BYTES
                                ------------------------------ ----------
                                TMP                            2146500608
                                TMP                            2146500608
                                TMP                            1072758784
                                The basic v$temp_space_header query:
                                SQL> SELECT tablespace_name,bytes_free,bytes_used
                                  2  FROM v$temp_space_header;
                                
                                TABLESPACE_NAME                BYTES_FREE BYTES_USED
                                ------------------------------ ---------- ----------
                                TMP                                     0 2146500608
                                TMP                                     0 1072758784
                                TMP                                     0 2146500608
                                Your query against v$temp_space_header
                                SQL> SELECT tablespace_name,bytes_free,bytes_used
                                  2  FROM v$temp_space_header
                                  3  GROUP BY tablespace_name,bytes_free,bytes_used;
                                
                                TABLESPACE_NAME                BYTES_FREE BYTES_USED
                                ------------------------------ ---------- ----------
                                TMP                                     0 1072758784
                                TMP                                     0 2146500608
                                The basic join of your two queries without the aggregation. I added the file_id and bytes columns from dba_temp_files and the bytes_free and bytes_used columns from v$temp_space_header to make what is happening clearer.
                                SQL> SELECT df.tablespace_name tspace, fs.file_id, fs.bytes, df.bytes_free,
                                  2         df.bytes_used, fs.bytes / (1024 * 1024) bytes_mb,
                                  3         df.bytes_free / (1024 * 1024) bytes_free_mb,
                                  4         ((fs.bytes - df.bytes_used) * 100) / fs.bytes bytes_minus_used,
                                  5         ((fs.bytes - df.bytes_free) * 100) / fs.bytes bytes_minus_free
                                  6  FROM dba_temp_files fs,
                                  7       (SELECT tablespace_name,bytes_free,bytes_used
                                  8        FROM v$temp_space_header
                                  9        GROUP BY tablespace_name,bytes_free,bytes_used) df
                                 10  WHERE fs.tablespace_name  = df.tablespace_name
                                 11  ORDER BY 1, 3, 4, 5;
                                
                                TSPACE        FILE_ID      BYTES BYTES_FREE BYTES_USED   BYTES_MB BYTES_FREE_MB BYTES_MINUS_USED BYTES_MINUS_FREE
                                ---------- ---------- ---------- ---------- ---------- ---------- ------------- ---------------- ----------------
                                TMP                 3 1072758784          0 1072758784  1023.0625             0                0              100  -- Group 1
                                TMP                 3 1072758784          0 2146500608  1023.0625             0       -100.09164              100  -- Group 2
                                TMP                 1 2146500608          0 1072758784  2047.0625             0       50.0228987              100  -- Group 3
                                TMP                 2 2146500608          0 1072758784  2047.0625             0       50.0228987              100  -- Group 3
                                TMP                 1 2146500608          0 2146500608  2047.0625             0                0              100  -- Group 4
                                TMP                 2 2146500608          0 2146500608  2047.0625             0                0              100  -- Group 4
                                The comments at the end show which group the rows will fall into when they are aggregated. Now, if we do the math manually based on your query we get:
                                Group 1
                                   Bytes_mb = 1072758784/1024/1024 = 1023.0625
                                   Bytes_free_mb = 0/1024/1024 = 0
                                   pct_free = ((1072758784 - 1072758784)*100)/1072758784 = 0
                                   pct_used = ((1072758784 - 0)*100/100 = 100
                                Group 2
                                   Bytes_mb = 1072758784/1024/1024 = 1023.0625
                                   Bytes_free_mb = 0/1024/1024 = 0
                                   pct_free = (1072758784 - 2146500608)/1024/1024 = -100
                                   pct_used = (1072758784 - 0)/1024/1024 = 100
                                Group 3
                                   Bytes_mb = 2146500608/1024/1024 = 2047.0625
                                   Bytes_free_mb = SUM(0+0)/1024/1024 = 0
                                   pct_free = ((SUM(2146500608+2146500608) - 1072758784)*100)/2146500608 = 150
                                   pct_used = ((SUM(2146500608+2146500608) - 0)*100)/2146500608 = 200
                                Group 3
                                   Bytes_mb = 2146500608/1024/1024 = 2047.0625
                                   Bytes_free_mb = SUM(0+0)/1024/1024 = 0
                                   pct_free = ((SUM(2146500608+2146500608) - 2146500608)*100)/2146500608 = 100
                                   pct_used = ((SUM(2146500608+2146500608) - 0)*100)/2146500608 = 200
                                and actually running the query:
                                SQL> SELECT df.tablespace_name tspace,
                                  2         fs.bytes / (1024 * 1024) bytes,
                                  3         SUM(df.bytes_free) / (1024 * 1024) bytes_free,
                                  4         Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1) pct_free,
                                  5         Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes) pct_used
                                  6  FROM dba_temp_files fs,
                                  7       (SELECT tablespace_name,bytes_free,bytes_used
                                  8        FROM v$temp_space_header
                                  9        GROUP BY tablespace_name,bytes_free,bytes_used) df
                                 10  WHERE fs.tablespace_name  = df.tablespace_name
                                 11  GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
                                 12  ORDER BY 4 DESC;
                                
                                TSPACE          BYTES BYTES_FREE   PCT_FREE   PCT_USED
                                ---------- ---------- ---------- ---------- ----------
                                TMP         2047.0625          0        150        200  -- Group 3
                                TMP         2047.0625          0        100        200  -- Group 4
                                TMP         1023.0625          0          0        100  -- Group 1
                                TMP         1023.0625          0       -100        100  -- Group 2
                                John