得到tablespace使用率的脚本

用PLSQL procedure:

CREATE OR REPLACE PROCEDURE GET_TABLESPACE_USAGE (
        m_limit in number default 0
)
AS
BEGIN
    DECLARE
         RunDate      date;
         DatabaseName v$database.name%type;
         TabSpaceName dba_tablespaces.tablespace_name%type;
         TabSpaceType dba_tablespaces.contents%type;
         TotalSpace   dba_data_files.bytes%type;
         FreeSpace    dba_free_space.bytes%type;
         SumTotal     dba_data_files.bytes%type;
         SumFree      dba_data_files.bytes%type;
         SumUsed      dba_data_files.bytes%type;
         CURSOR name_cursor IS
              select tablespace_name,contents from dba_tablespaces order by 1;
    BEGIN
        select sysdate into RunDate from dual;
        select name into DatabaseName from v$database;
        dbms_output.put_line ('Database : '|| DatabaseName);
        dbms_output.put_line ('Run Date : '|| to_char(RunDate,'YYYY-MM-DD HH24:MI:SS'));
        dbms_output.new_line;
        dbms_output.new_line;
        dbms_output.put_line
         ('---------------------------  ---------  --------  --------    -----    -----');
        dbms_output.put_line
         ('TableSpace Name              Total (M)  Used (M)  Free (M)    %Used    %Free');
        dbms_output.put_line
         ('---------------------------  ---------  --------  --------    -----    -----');
        SumTotal := 0;
        SumFree  := 0;
        SumUsed  := 0;
        FOR name_cursor_rec IN name_cursor LOOP
            TabSpaceName := name_cursor_rec.tablespace_name;
            TabSpaceType := name_cursor_rec.contents;
            if TabSpaceType = 'TEMPORARY' then
                    select nvl(sum(bytes)/1048576,0) into TotalSpace
                    from dba_temp_files
                    where tablespace_name = TabSpaceName;
                    select nvl(sum(bytes_free)/1048576,0) into FreeSpace
                    from v$temp_space_header
                    where tablespace_name = TabSpaceName;
            else
                    select nvl(sum(bytes)/1048576,0) into TotalSpace
                    from dba_data_files
                    where tablespace_name = TabSpaceName;
                    select nvl(sum(bytes)/1048576,0) into FreeSpace
                    from dba_free_space
                    where tablespace_name = TabSpaceName;
            end if;
            SumTotal := SumTotal + TotalSpace;
            SumFree  := SumFree  + FreeSpace;
            SumUsed  := SumUsed  + (TotalSpace-FreeSpace);
            if (TotalSpace-FreeSpace)/TotalSpace*100 >= m_limit then
            dbms_output.put_line
            (
              rpad(TabSpaceName,30)
              || '   '||
              to_char(TotalSpace,'999,999')
              || '  '||
              to_char(TotalSpace-FreeSpace,'999,999')
              || '  '||
              to_char(FreeSpace,'999,999')
              || '  '||
              to_char ((TotalSpace-FreeSpace)/TotalSpace*100,'999.99')
              || '  '||
              to_char (FreeSpace/TotalSpace*100,'999.99')
            );
            end if;
        END LOOP;
        dbms_output.put_line
         ('---------------------------------------------------------------------------- ');
        dbms_output.put_line
         (
             'TOTAL                          '
             || '  '||
             to_char(SumTotal,'999,999')
             || '  '||
             to_char(SumUsed,'999,999')
             || '  '||
             to_char(SumFree,'999,999')
             || '  '||
             to_char (SumUsed/SumTotal*100,'999.99')
             || '  '||
             to_char (SumFree/SumTotal*100,'999.99')
         );
        dbms_output.put_line
         ('---------------------------------------------------------------------------- ');
    END;
END;

 

 

 

 

用SQL语句：

col free for a10
col usage for a10
col tablespace_name for a30
col total for a10
set pagesize 100
with 
s_total as (
select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name
union all
select tablespace_name, sum(bytes) bytes
from dba_temp_files
group by tablespace_name
),
s_free as (
select tablespace_name, sum(bytes) bytes
from dba_free_space
group by tablespace_name
union all
select tablespace_name,bytes_free
from v$temp_space_header
)
select m.tablespace_name
, case
 when m.bytes < 1024 then m.bytes || 'B' 
 when m.bytes >= 1024 and m.bytes < (1024 *1024-1) then round(m.bytes / 1024) || 'KB'
 when m.bytes >= 1024 * 1024 and m.bytes < (1024 * 1024 * 1024-1) then round(m.bytes / 1024 / 1024) || 'MB'
 when m.bytes >= 1024 * 1024 * 1024and m.bytes < (1024 * 1024 * 1024 * 1024-1) then round(m.bytes / 1024 / 1024 / 1024) || 'GB'
end as TOTAL
, case
 when f.bytes < 1024 or f.bytes is null then nvl(f.bytes,0) || 'B'
 when f.bytes >= 1024 and f.bytes < (1024 * 1024 -1) then round (f.bytes / 1024) || 'KB'
 when f.bytes >= 1024 * 1024 and f.bytes < (1024 * 1024 * 1024-1) then round (f.bytes / 1024 / 1024) || 'MB'
 when f.bytes >= 1024 * 1024 * 1024and f.bytes < (1024 * 1024 * 1024 * 1024-1) then round (f.bytes / 1024 / 1024 / 1024) || 'GB'
end as FREE
, 100 - round(nvl(f.bytes,0) / m.bytes * 100 ) || '%' USAGE
from s_TOTAL M, S_FREE F
where m.tablespace_name = f.tablespace_name(+)
order by usage desc;

 

其实主要就是用dba_data_files(找总的size)和dba_free_space(找未使用的size)这两个view