Tablespace Capacity


--
--  Filename:  tbsp_capacity.sql
--
--  Execute As:  DBA Privileged User (i.e., system)
--
--  Purpose:   This script will list the current tablespaces with their
--             corresponding datafile names along with their current size
--             and unused space.
--
-- =========================================================================
set linesize 130
rem  its datafile.
col tablespace_name     format a20              head 'TABLESPACE|NAME'
col file_name           format a50              head 'DATAFILE NAME'
col pct_full            format 999.99           head '% USED'
col pct_free            format 999.99           head '% FREE'
col dbf_size            format 9,999,999.99     head 'DBF ALLOCATED|(in MB)'
col dbf_avail           format 9,999,999.99     head 'DBF FREE|(in MB)'
col dbf_used            format 9,999,999.99     head 'DBF USED|(in MB)'

set space 1;
set pages 65;

ttitle '||CURRENT SYSTEM SPACE USAGE BY TABLESPACE||';

break on report

compute sum of dbf_size on report
compute sum of dbf_avail on report
compute sum of dbf_used on report

REM
UNDEFINE capacity_log
COLUMN col NOPRINT NEW_VALUE capacity_log
select '/oracle/admin/scripts/logs/'||'&&1'||'_tablespace_capacity.log' col
  from dual;
SPOOL &capacity_log

select a.tablespace_name, a.file_name,
        (((a.bytes - b.total_free)/a.bytes)*100) pct_full,
        (b.total_free/a.bytes)*100 pct_free,
        (a.bytes/1024)/1024 dbf_size,
        (b.total_free/1024)/1024 dbf_avail,
        ((a.bytes - b.total_free)/1024/1024)dbf_used
from dba_data_files a, (select tablespace_name, file_id, sum(bytes) total_free
                        from dba_free_space
                        group by tablespace_name, file_id
                        UNION
                        select tablespace_name, file_id, 0
                        from dba_data_files
                        where tablespace_name not in
                          (select tablespace_name
                           from dba_free_space)) b
where a.tablespace_name = b.tablespace_name
and b.tablespace_name in ('SYSTEM','SYSAUX','TOOLS','UNDO','UNDOTBS1','UNDOTBS2','UNDOTBS3','USERS')
 and  a.file_id = b.file_id
order by a.tablespace_name;

col tablespace_name     format a20              head 'TABLESPACE|NAME'
col file_name           format a50              head 'DATAFILE NAME'
col pct_full            format 999.99           head '% USED'
col pct_free            format 999.99           head '% FREE'
col dbf_size            format 9,999,999.99     head 'DBF ALLOCATED|(in MB)'
col dbf_avail           format 9,999,999.99     head 'DBF FREE|(in MB)'
col dbf_used            format 9,999,999.99     head 'DBF USED|(in MB)'

set space 1;
set pages 65;

ttitle '||CURRENT DATA SPACE USAGE BY TABLESPACE||';

break on report

compute sum of dbf_size on report
compute sum of dbf_avail on report
compute sum of dbf_used on report

select a.tablespace_name, a.file_name,
        (((a.bytes - b.total_free)/a.bytes)*100) pct_full,
        (b.total_free/a.bytes)*100 pct_free,
        (a.bytes/1024)/1024 dbf_size,
        (b.total_free/1024)/1024 dbf_avail,
        ((a.bytes - b.total_free)/1024/1024)dbf_used
from dba_data_files a, (select tablespace_name, file_id, sum(bytes) total_free
                        from dba_free_space
                        group by tablespace_name, file_id
                        UNION
                        select tablespace_name, file_id, 0
                        from dba_data_files
                        where tablespace_name not in
                          (select tablespace_name
                           from dba_free_space)) b
where a.tablespace_name = b.tablespace_name
and b.tablespace_name not in ('SYSTEM','SYSAUX','TOOLS','UNDO','UNDOTBS1','UNDOTBS2','UNDOTBS3','USERS')
and  a.file_id = b.file_id
order by a.tablespace_name;

col temp_tbsp_name      format a20              head 'TABLESPACE|NAME'
col temp_file_name      format a50              head 'DATAFILE NAME'
col temp_alloc          format 9,999,999.99     head 'TEMP ALLOCATED|(in MB)'


ttitle '||CURRENT TEMP SPACE ALLOCATED||';

compute sum of temp_alloc on report

select tablespace_name,file_name,(bytes/1024/1024) temp_alloc from dba_temp_files;

col group_no            format 999999               head 'Group#'
col thread_no           format 9999999              head 'Thread#'
col fsize               format 9,999,999.99  head 'Size|(in MB)'

ttitle '||CURRENT REDO SPACE ALLOCATED||';

compute sum of fsize on report

  SELECT  l.group# group_no, l.thread# thread_no, ((bytes)/1024/1024) fsize
    FROM
          v$log l, v$logfile f
   WHERE  f.group# = l.group#
ORDER BY  l.group# , l.thread# , bytes;

spool off
REM
UNDEFINE capacity_log

Leave a Reply

Your email address will not be published. Required fields are marked *