--
-- 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