This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Sep 4, 2008 7:11 AM by 626479 Go to original post RSS
  • 15. Re: query to check tablespace size and freespace
    626479 Newbie
    Currently Being Moderated
    sorry , still the same as before ... :(
  • 16. Re: query to check tablespace size and freespace
    593075 Newbie
    Currently Being Moderated
    Well...somehow yes :)

    I want to know when its time to do something (add datafile, expand datafile etc)

    When a datafile with autoextend set to "on" and a maxsize of 2048 GB i do not want to get active when this file has a current size of lets say 1024 MB and is filled with 512 MB. However, queries on dba_free_space for example will return that only 512 MB of space are left...but in my opinion its 1500 MB...
  • 17. Re: query to check tablespace size and freespace
    626479 Newbie
    Currently Being Moderated
    this script should do it

    select
    a.TABLESPACE_NAME,
    a.total,nvl(b.used,0) USED,
    nvl((b.used/a.total)*100,0) PCT_USED
    from
    (select TABLESPACE_NAME,
    sum(maxbytes)/(1024*1024) total
    from sys.dba_data_files
    group by TABLESPACE_NAME) a,
    (select TABLESPACE_NAME,bytes/(1024*1024) used
    from sys.SM$TS_USED) b
    where a.TABLESPACE_NAME=b.TABLESPACE_NAME(+);


    select file_name, bytes/1024/1024 current_size_MB, increment_by*8192/1024/1024 autoextend_size_MB, maxbytes/1024/1024 max_size_MB
    from dba_data_files
    where bytes>(maxbytes*0.90)
    and autoextensible = 'YES'
    or bytes/1024/1024+increment_by*8192/1024/1024>maxbytes/1024/1024*0.9
    and autoextensible = 'YES';

    if you prefer GUI, LAB128 has it

    Edited by: zs_hzh on Sep 4, 2008 7:05 AM
1 2 Previous Next