Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2005
    Location
    Stockholm, Sweden
    Posts
    6

    Unanswered: Check freespace on the latest databasefile

    Hello!

    We have a database with several databasefiles assigned to a couple of tables.
    For example,
    The datafiles '/db/oracle/testtbl01.dbf' and '/db/oracle/testtbl02.dbf' is create for and only
    for the tablespace TESTTBL

    I need a SQL statement to check how much usage the latest file have.

    select max(name) from v$datafile_header group by tablespace_name;
    Give us the filename of the latest file, as far as good.
    But i fail to use it as a subquery to get information about free blocks (from dba_free_space)
    and total blocks (also from v$datafile_header).

    The reason of this problem is our database monitoring tool checks free space from the WHOLE tablespace,
    and if the tablespace is 57GB and the monitoring tool alert us when it's 5,7GB free (10%) it's not really
    any alert.

    Excuse my bad english (to much work, to litle coffee

    Thank you for your answer :-)

    //Robert Olsson

  2. #2
    Join Date
    Sep 2004
    Posts
    60

    Lightbulb

    Hi,

    As I have under stood the question, you want to free space for every file of each tablespace. You can try following query to find so..
    -----------------------------------------------------------
    COL A HEADING "FILE NAME" FORMAT A15
    COL B HEADING "TABLESPACE NAME" FORMAT A15
    COL c HEADING "SPACE ALLOCATED:MB" FORMAT A18
    COL D HEADING "FREE SPACE:MB" FORMAT A14
    COL E HEADING "% FREE SPACE" FORMAT A12

    SELECT
    ddf.FILE_NAME A ,
    ddf.TABLESPACE_NAME B,
    TO_CHAR(ddf.bytes/1048576,'999,999.99') C,
    TO_CHAR(dfs.bytes/1048576,'999,999.99') D,
    TO_CHAR(((dfs.bytes/1048576)/(ddf.bytes/1048576))*100,'99,999.99')E
    FROM dba_data_files ddf, dba_free_space dfs
    WHERE ddf.file_id = dfs.file_id(+)
    order by ddf.TABLESPACE_NAME
    /
    -----------------------------------------------------------

    Any comments are welcome.

    @Abhi

  3. #3
    Join Date
    Jan 2004
    Posts
    370
    Quote Originally Posted by abhishekdixit
    Hi,

    As I have under stood the question, you want to free space for every file of each tablespace. You can try following query to find so..
    -----------------------------------------------------------
    COL A HEADING "FILE NAME" FORMAT A15
    COL B HEADING "TABLESPACE NAME" FORMAT A15
    COL c HEADING "SPACE ALLOCATED:MB" FORMAT A18
    COL D HEADING "FREE SPACE:MB" FORMAT A14
    COL E HEADING "% FREE SPACE" FORMAT A12

    SELECT
    ddf.FILE_NAME A ,
    ddf.TABLESPACE_NAME B,
    TO_CHAR(ddf.bytes/1048576,'999,999.99') C,
    TO_CHAR(dfs.bytes/1048576,'999,999.99') D,
    TO_CHAR(((dfs.bytes/1048576)/(ddf.bytes/1048576))*100,'99,999.99')E
    FROM dba_data_files ddf, dba_free_space dfs
    WHERE ddf.file_id = dfs.file_id(+)
    order by ddf.TABLESPACE_NAME
    /
    -----------------------------------------------------------

    Any comments are welcome.

    @Abhi

    You will get one row returned for every free extent in a file.

    To see the free extents :

    Code:
    select  df.file_name, sum(fs.bytes)
    from    dba_free_space fs,
              dba_data_files df
    where  df.file_id = fs.file_id
    group by df.file_name, fs.block_id
    To see the total free space:

    Code:
    select df.file_name, sum(fs.bytes)
    from   dba_free_space fs,
             dba_data_files df
    where df.file_id = fs.file_id
    group by df.file_name
    You can filter out which file you want using file_name or file_id

    But I'm not sure this answers the OP's question though.

    .

  4. #4
    Join Date
    Dec 2005
    Location
    Stockholm, Sweden
    Posts
    6
    Hi
    We are monitoring a lot of databases on lot of hosts.
    And if we use a generic monitor value (in this case, alert when free space is lower than 10%) we got
    very diffrent answers depending if the tablespace size is 200MB or 50GB.
    (If there is 5GB free in tablespace, it's not really an alert).

    Therefor we got a more independent monitoring enviroment if we only checks the last database file.

    I got the SQL statement to get the latest file in a tablespace, but i don't know how to get further.
    (i don't get the join's to work)

    select max(name) from v$database_header group by tablespace_name;

    And the whole sql statement should be something like:
    select vdh.tablespace_name, vdh.name "filename", vdh.bytes "total_bytes", dfs.bytes "free_bytes"
    from (select max(name) "name", tablespace_name, bytes from v$database_header) vdh,
    dba_free_space dfs
    where
    vdh.name = dfs.name;

    (i at home now and don't have the last sql statement i tried on my notepad, so i typed above statement from
    head as well).

  5. #5
    Join Date
    Dec 2005
    Location
    Stockholm, Sweden
    Posts
    6
    Quote Originally Posted by SkyWriter
    You will get one row returned for every free extent in a file.

    To see the free extents :

    Code:
    select  df.file_name, sum(fs.bytes)
    from    dba_free_space fs,
              dba_data_files df
    where  df.file_id = fs.file_id
    group by df.file_name, fs.block_id
    To see the total free space:

    Code:
    select df.file_name, sum(fs.bytes)
    from   dba_free_space fs,
             dba_data_files df
    where df.file_id = fs.file_id
    group by df.file_name
    You can filter out which file you want using file_name or file_id

    But I'm not sure this answers the OP's question though.

    .
    Thank you, I'll try it to work tomorrow, i don't have time to setup an oracle at home with a couple of
    tablespaces to test on right now

  6. #6
    Join Date
    Dec 2005
    Location
    Stockholm, Sweden
    Posts
    6
    None of the above answers helped me.

    select max(name) from v$datafile_header group by tablespace_name;
    62 rows selected.

    select name from v$datafile_header;
    70 rows selected.

    I don't need the freespace etc on all filenames, just on the last one i every tablespace.

    //Robban

  7. #7
    Join Date
    Jan 2004
    Posts
    370
    Only the free space in the last file added to a tablespace, determined by the sort order of the file name?

    OK. Have you tried:

    Code:
    select df.file_name, sum(fs.bytes)
    from   dba_free_space fs,
             dba_data_files df
    where df.file_id = fs.file_id
    and   df.file_name in (select max(name) 
    		   from v$datafile_header 
    		   group by tablespace_name)
    group by df.file_name

  8. #8
    Join Date
    Dec 2005
    Location
    Stockholm, Sweden
    Posts
    6
    Quote Originally Posted by SkyWriter
    Only the free space in the last file added to a tablespace, determined by the sort order of the file name?

    OK. Have you tried:

    Code:
    select df.file_name, sum(fs.bytes)
    from   dba_free_space fs,
             dba_data_files df
    where df.file_id = fs.file_id
    and   df.file_name in (select max(name) 
    		   from v$datafile_header 
    		   group by tablespace_name)
    group by df.file_name
    Thank you! You are my hero! :-)

  9. #9
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Are you sure this worked for you as you're expecting it to ? Don't know, given the question, I see something wrong with the query.

  10. #10
    Join Date
    Dec 2005
    Location
    Stockholm, Sweden
    Posts
    6
    It helpded me go further and solve the quest.
    I had a lot stuff to fix the past days so i couldn't see your post until today (tonight to be exact
    Here my sql code to fix it, now it's only to create a PL/SQL proc. around this so HP OpenView can
    use it as a UDM.

    <code>
    select
    df.tablespace_name "Tablespace"
    , df.file_name "FileName"
    , sum(df.bytes) "TotBlocks"
    , sum(fs.bytes) "FreeBlocks"
    , sum((fs.bytes * 100) / df.bytes) "Percent"
    from dba_free_space fs, dba_data_files df
    where
    df.file_id = fs.file_id
    and
    df.file_name in
    (select max(name) from v$datafile_header group by tablespace_name)
    group by df.file_name,df.tablespace_name
    </code>

    If you see any failures in above, please let me know, i'm far away from a sql guru.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •