This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Sep 4, 2008 7:11 AM by 626479 RSS

query to check tablespace size and freespace

547581 Newbie
Currently Being Moderated
hi experts
i am using oracle 9i .
want ot check freespace and size of tablespace could u plz tell me the query

thanks
varun
  • 1. Re: query to check tablespace size and freespace
    ViragSharma Newbie
    Currently Being Moderated
    col "Tablespace" for a22
    col "Used MB" for 99,999,999
    col "Free MB" for 99,999,999
    col "Total MB" for 99,999,999

    select df.tablespace_name "Tablespace",
    totalusedspace "Used MB",
    (df.totalspace - tu.totalusedspace) "Free MB",
    df.totalspace "Total MB",
    round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
    "Pct. Free"
    from
    (select tablespace_name,
    round(sum(bytes) / 1048576) TotalSpace
    from dba_data_files
    group by tablespace_name) df,
    (select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
    from dba_segments
    group by tablespace_name) tu
    where df.tablespace_name = tu.tablespace_name ;
  • 2. Re: query to check tablespace size and freespace
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    Virag,

    What about a free tablespace (no segment) ? You may want to add an external join.

    What we can do :
    select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
    from  (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
           from dba_free_space
           group by tablespace_name) a,
          (select tablespace_name, sum(bytes)/1024/1024 as tbs_size
           from dba_data_files
           group by tablespace_name) b
    where a.tablespace_name(+)=b.tablespace_name;
    Nicolas.
  • 3. Re: query to check tablespace size and freespace
    44863 Newbie
    Currently Being Moderated
    add following for temp files -

    select sum(bytes)/1024 kbytes_alloc, tablespace_name
    from sys.dba_temp_files
    group by tablespace_name

    the new query will be

    select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
    from
    (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
    from dba_free_space group by tablespace_name) a,
    (select tablespace_name, sum(bytes)/1024/1024 as tbs_size
    from dba_data_files group by tablespace_name
    UNION
    select tablespace_name, sum(bytes)/1024/1024 tbs_size
    from dba_temp_files
    group by tablespace_name ) b
    where a.tablespace_name(+)=b.tablespace_name;


    cheers
  • 4. Re: query to check tablespace size and freespace
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    Good point, but who does matter the free space of temp tbs ?

    Nicolas.
  • 5. Re: query to check tablespace size and freespace
    44863 Newbie
    Currently Being Moderated
    It is about the size of DB.
  • 6. Re: query to check tablespace size and freespace
    ViragSharma Newbie
    Currently Being Moderated
    Thanks Nicolas,

    I use this script for monitoring purpose ( tablespace usage should not cross 90%)
    I never thought of this possibility/case, that tablespace can exists without any segments.

    Thanks once again

    Cheer,
    Virag
  • 7. Re: query to check tablespace size and freespace
    593075 Newbie
    Currently Being Moderated
    Hello,

    sorry to warm up this thread, but i was searching for something similar. What i want to ask is:

    When i query dba_free_space it get values that dont care about the datafiles being in autoextend mode or not.

    So lets say you have a datafile with maxsize 2048MB, that has a current size of 1024 and dba_free_space reports 512 MB for the tablespace for this datafile (lets assume its the only file
    for the tablespace).

    So now dba_free_space tells me that only 512 MB are left, but actually (depends on the way you see it...) there are 1.500 MB left , so in most cases not need to add another datafile...

    What what you recommend to get a "more true" value when trying to find out the space left ?
  • 8. Re: query to check tablespace size and freespace
    593075 Newbie
    Currently Being Moderated
    Hello,

    sorry for re-asking again, but i'm really stuck at this. I cannot find a convinient way to find out how much space is left in my db. At least no way that try's to address things like datafiles being in autoextend mode...

    Don't you have some hints for me ?
  • 9. Re: query to check tablespace size and freespace
    566491 Explorer
    Currently Being Moderated
    Is it that there is only 512MB of space left on the filesystem where the datafile with auto-extend is?
  • 10. Re: query to check tablespace size and freespace
    593075 Newbie
    Currently Being Moderated
    No,

    there is enougth space on the filesystem for the datafile to be able to expand to its maxsize of 2048 MB.

    But currently the size of the datafile is only 1024 MB, and only about the half of it (512 MB) are filled.

    Now it seems when dba_free_space tries to find out how much space is left, it igonres the autoextend-capability of this file, telling me that only 512 MB are left (but actually i could still relax and wait till the file has grown to its 2048 MB - there is no need to add another datafile now...)
  • 11. Re: query to check tablespace size and freespace
    652656 Newbie
    Currently Being Moderated
    Hello,

    This script will show u tablespace datafile, freespace ,availiable and autoextend or not.

    set pagesize 100

    column file_name format a32
    column tablespace_name format a15
    column status format a3 trunc
    column t format 999,999.000 heading "Total MB"
    column a format a4 heading "Aext"
    column p format 990.00 heading "% Free"

    SELECT df.file_name,
    df.tablespace_name,
    df. status,
    (df.bytes/1024000) t,
    (fs.s/df.bytes*100) p,
    decode (ae.y,1,'YES','NO') a
    FROM dba_data_files df,
    (SELECT file_id,SUM(bytes) s
    FROM dba_free_space
    GROUP BY file_id) fs,
    (SELECT file#, 1 y
    FROM sys.filext$
    GROUP BY file#) ae
    WHERE df.file_id = fs.file_id
    AND ae.file#(+) = df.file_id
    ORDER BY df.tablespace_name, df.file_id;

    column file_name clear
    column tablespace_name clear
    column status clear
    column t clear
    column a clear
    column p clear
    ttitle off

    Regards

    kaunain
  • 12. Re: query to check tablespace size and freespace
    593075 Newbie
    Currently Being Moderated
    Hello Kaunain,

    thank you very much for your answer.

    But still i dont get all i need from this script. I'll try to explain what i mean on one sample output line:

    X:\BASE\DATA\ABA\USER\USERS14.ORA USER_DATA AVA 512.000 99.99 YES

    This datafile USERS14.ORA is set to autoextend as has a maxsize of 2048 MB. I see that autoextend is set to "YES", but i don't see how big the file could grow here.
  • 13. Re: query to check tablespace size and freespace
    626479 Newbie
    Currently Being Moderated
    here , check this one out

    show all tablespaces usage

    Code:

    set linesize 209
    set pagesize 26
    SET FEEDBACK OFF
    SET HEADING ON
    set timing off
    set echo off
    SET UNDERLINE =

    variable xxx char(200)

    column "Tablespace" format A16
    column "Initial extent(Mb)" format 999,999
    column "Status" format a7
    column "Next extent(MB)" format 999,999
    column "Max extents" format 99,999,999,999
    column "Pct_increase" format 999
    COLUMN "USED SIZE(Mb)" FORMAT 9,999,999,999
    COLUMN "USED BLOCKS" FORMAT 999,999,999
    COLUMN "USED USAGE" FORMAT A10
    COLUMN "FREE SIZE(Mb)" FORMAT 9,999,999,999
    COLUMN "FREE BLOCKS" FORMAT 999,999,999
    COLUMN "FREE USAGE" FORMAT A10
    COLUMN "TOTAL SIZE(Mb)" FORMAT 99,999,999,999
    COLUMN "TOTAL BLOCKS" FORMAT 9999,999,999


    break on "Tablespace"
    declare
    cursor c1 is select 'Print Date:'||to_char(sysdate,'yyyy/mm/dd hh24:mi:ss')||' ***** Tablespace Information from DataBase :'||name||' Instance:'||instance||' *****' xxx
    from v$database,v$thread
    where rownum=1;
    begin
    open c1;
    fetch c1 into :xxx;
    close c1;
    end;
    /

    set pagesize 1
    print xxx
    set pagesize 24

    TTITLE RIGHT FORMAT 999 'Page:' SQL.PNO
    BTITLE CENTER '================================================================================================================================================================================================================='


    select substr(A.tablespace_name,1,16) "Tablespace",
    MAX(A.contents) "Type",
    MAX(A.status) "Status",
    MAX(A.initial_extent)/1024 "Initial extent(Kb)",
    MAX(A.next_extent)/1024 "Next extent(Kb)",
    MAX(A.max_extents) "Max extents",
    MAX(A.pct_increase) "Pct_increase",
    (SUM(B.BYTES)*COUNT(DISTINCT B.FILE_ID)/COUNT(B.FILE_ID)/1024/1024)-(ROUND(SUM(C.BYTES)/1024/1024/COUNT(DISTINCT B.FILE_ID))) "USED SIZE(Mb)",
    (SUM(B.BLOCKS)*COUNT(DISTINCT B.FILE_ID)/COUNT(B.FILE_ID))-(SUM(C.BLOCKS)/COUNT(DISTINCT B.FILE_ID)) "USED BLOCKS",
    TO_CHAR(100-(SUM(C.BLOCKS)*100*COUNT(B.FILE_ID)/(SUM(B.BLOCKS)*COUNT(DISTINCT B.FILE_ID)))/COUNT(DISTINCT B.FILE_ID),'999.99')||'%' "USED USAGE",
    ROUND(SUM(C.BYTES)/1024/1024/COUNT(DISTINCT B.FILE_ID)) "FREE SIZE(MB)",
    SUM(C.BLOCKS)/COUNT(DISTINCT B.FILE_ID) "FREE BLOCKS",
    TO_CHAR((SUM(C.BLOCKS)*100*COUNT(B.FILE_ID)/(SUM(B.BLOCKS)*COUNT(DISTINCT B.FILE_ID)))/COUNT(DISTINCT B.FILE_ID),'999.99')||'%' "FREE USAGE",
    SUM(B.BYTES)*COUNT(DISTINCT B.FILE_ID)/COUNT(B.FILE_ID)/1024/1024 "TOTAL SIZE(Mb)",
    SUM(B.BLOCKS)*COUNT(DISTINCT B.FILE_ID)/COUNT(B.FILE_ID) "TOTAL BLOCKS"
    from dba_tablespaces A,
    DBA_DATA_FILES B,
    DBA_FREE_SPACE C
    WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME
    AND A.TABLESPACE_NAME=C.TABLESPACE_NAME
    GROUP BY A.TABLESPACE_NAME
    order by 1;


    TTITLE OFF
    BTITLE OFF
    SET FEEDBACK ON
  • 14. Re: query to check tablespace size and freespace
    652656 Newbie
    Currently Being Moderated
    U want to see till what maxumum size the autoextend datafile can grow RIGHT??

    eg:alter database datafile c:/user.dbf size 100m autoextend on next 2m maxsize 2000M

    U want to see 2000m in the query??
1 2 Previous Next