【脚本】查找占用率超过95%的表空间




  • Rem Created date: 2006-04-21 12:00
    Rem Author : dbstyle
    Rem Name : datafile.sql
    Rem Description:
    Rem this script is used to get datafile information that used exceed 95%.

    Rem MODIFIED (MM/DD/YYYY)
    Rem dbstyle 04-21-2006 create script

    set pagesize 999
    col file_name format a40
    col tablespace_name format a10
    select df.file_name,df.tablespace_name,df.bytes/1024/1024 size_M from dba_data_files df,
    (select
    a.tablespace_name tsname,
    round(a.bytes,2) Total_Mb,
    round(b.bytes,2) Free_Mb,
    round(a.MAXsize,2) MAXSIZE_Gb,
    (1 - (b.bytes/a.bytes))*100 Pct_used
    from
    (
    select tablespace_name,sum(MAXBYTES/1024/1024/1024) MAXsize,sum(bytes)/1024/1024 bytes
    from dba_data_files
    group by tablespace_name
    ) a,
    (
    select tablespace_name,sum(bytes)/1024/1024 bytes
    from dba_free_space
    group by tablespace_name
    ) b
    where a.tablespace_name = b.tablespace_name
    and (1 - (b.bytes/a.bytes))*100>95) tn
    where tn.tsname=df.tablespace_name
    order by df.file_name;

    Speak Your Mind

    *