186 lines
9.0 KiB
SQL
186 lines
9.0 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display Tablespace usage
|
|
* Parameters : 1 - tablespace_name (Use % as wildcard, Default value '%')
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 14-Jul-15 Vishal Gupta Added tablespace creation_time (Approximated from earliest datafile time)
|
|
* 11-Mar-13 Vishal Gupta Fixed temp space calculations
|
|
* 02-Apr-12 Vishal Gupta Bug fixes
|
|
* 05-Aug-04 Vishal Gupta First Draft
|
|
*/
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE tablespace_name
|
|
DEFINE tablespace_name="&&1"
|
|
|
|
set term off
|
|
COLUMN _TABLESPACE_NAME NEW_VALUE TABLESPACE_NAME NOPRINT
|
|
|
|
SELECT UPPER(DECODE('&&TABLESPACE_NAME','','%','&&TABLESPACE_NAME')) "_TABLESPACE_NAME"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
|
|
/************************************
|
|
* CONFIGURATION PARAMETERS
|
|
************************************/
|
|
DEFINE BYTES_FORMAT="999,999"
|
|
--DEFINE BYTES_HEADING="KB"
|
|
--DEFINE BYTES_DIVIDER="1024"
|
|
--DEFINE BYTES_HEADING="MB"
|
|
--DEFINE BYTES_DIVIDER="1024/1024"
|
|
DEFINE BYTES_HEADING="GB"
|
|
DEFINE BYTES_DIVIDER="1024/1024/1024"
|
|
|
|
DEFINE ROUND_PRECISION=2
|
|
DEFINE STAR=4 --Defines how much %usage is represented by one star on Graph
|
|
|
|
set term off
|
|
SET FEED OFF
|
|
SELECT (100/&&STAR+2) UsageGraphWidth
|
|
FROM DUAL;
|
|
SET FEED ON
|
|
set term on
|
|
|
|
|
|
PROMPT *****************************************************************
|
|
PROMPT * T A B L E S P A C E U S A G E R E P O R T
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - Tablespace Name = '&&TABLESPACE_NAME'
|
|
PROMPT *****************************************************************
|
|
|
|
|
|
COLUMN UsageGraphWidth NOPRINT old_value UGWidth
|
|
COLUMN tablespace_name FORMAT a23
|
|
COLUMN creation_time HEADING "Creation Time" FORMAT a15
|
|
COLUMN alloc HEADING "Alloc(&&BYTES_HEADING)" FORMAT 9,999,999 ON
|
|
COLUMN used HEADING "Used&&BYTES_HEADING)" FORMAT 9,999,999 ON
|
|
COLUMN free HEADING "Free|(&&BYTES_HEADING)" FORMAT 999,999 ON
|
|
COLUMN pct_Used HEADING "%Used" FORMAT 999 ON
|
|
COLUMN pct_Free HEADING "%Free" FORMAT 999 ON
|
|
COLUMN MAXSIZE HEADING "MaxSize|(&&BYTES_HEADING)" FORMAT 9,999,999 ON
|
|
COLUMN Maxfree HEADING "MaxFree|(&&BYTES_HEADING)" FORMAT 9,999,999 ON
|
|
COLUMN Max_pct_Used HEADING "MAX|%Used" FORMAT 999 ON
|
|
COLUMN Max_pct_Free HEADING "MAX|%Free" FORMAT 999 ON
|
|
COLUMN "Usage Graph" FORMAT a27
|
|
COLUMN "MaxUsage Graph" FORMAT a27
|
|
|
|
BREAK ON REPORT
|
|
|
|
COMPUTE SUM LABEL 'Total' OF alloc FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF used FORMAT 99,999,999.9 ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF free FORMAT 99,999,999.9 ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF maxsize FORMAT 9,999,999.9 ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF maxfree FORMAT 9,999,999.9 ON REPORT
|
|
|
|
SELECT /*+ CHOOSE */ c.tablespace_name
|
|
, c.alloc
|
|
, c.used
|
|
, c.free
|
|
, c.pct_used
|
|
, c.pct_free
|
|
, '|' || RPAD(NVL(LPAD(' ',CEIL(NVL(c.pct_used,0)/&&STAR),'*'),' '),CEIL(100/&&STAR)) || '|' "Usage Graph"
|
|
, c.MAXSIZE
|
|
, c.maxfree
|
|
, c.max_pct_used
|
|
, c.max_pct_free
|
|
, '|' || RPAD(NVL(LPAD(' ',CEIL(NVL(c.max_pct_used,0)/&&STAR),'*'),' '),CEIL(100/&&STAR)) || '|' "MaxUsage Graph"
|
|
, TO_CHAR(c.creation_time,'DD-MON-YY HH24:MI') creation_time
|
|
FROM (
|
|
SELECT /*+ CHOOSE */ a.tablespace_name
|
|
, c.creation_time
|
|
, ROUND(a.bytes_alloc / &&BYTES_DIVIDER, &&ROUND_PRECISION) alloc
|
|
, ROUND((a.bytes_alloc - NVL(b.bytes_free, 0)) / &&BYTES_DIVIDER, &&ROUND_PRECISION ) used
|
|
, ROUND(NVL(b.bytes_free, 0) / &&BYTES_DIVIDER, &&ROUND_PRECISION) free
|
|
, 100 - ROUND((NVL(b.bytes_free, 0) / a.bytes_alloc) * 100, &&ROUND_PRECISION) pct_Used
|
|
, ROUND((NVL(b.bytes_free, 0) / a.bytes_alloc) * 100, &&ROUND_PRECISION) pct_Free
|
|
, ROUND(a.maxbytes/ &&BYTES_DIVIDER, &&ROUND_PRECISION) MAXSIZE
|
|
, ROUND( (maxbytes - a.bytes_alloc + NVL(b.bytes_free, 0) ) / &&BYTES_DIVIDER , &&ROUND_PRECISION) Maxfree
|
|
, ROUND(((a.bytes_alloc - NVL(b.bytes_free, 0)) / a.maxbytes) * 100, &&ROUND_PRECISION) Max_pct_Used
|
|
, ROUND( ((a.maxbytes - a.bytes_alloc + NVL(b.bytes_free, 0) ) / a.maxbytes) * 100, &&ROUND_PRECISION) Max_pct_Free
|
|
FROM ( SELECT /*+ CHOOSE */
|
|
f.tablespace_name
|
|
, SUM(f.bytes) bytes_alloc
|
|
, SUM(DECODE(f.autoextensible, 'YES',GREATEST(f.maxbytes,f.bytes),'NO', f.bytes)) maxbytes
|
|
FROM DBA_DATA_FILES f
|
|
WHERE f.tablespace_name LIKE '&&tablespace_name'
|
|
GROUP BY tablespace_name
|
|
) a
|
|
, ( SELECT /*+ CHOOSE */
|
|
f.tablespace_name
|
|
, SUM(f.bytes) bytes_free
|
|
FROM DBA_FREE_SPACE f
|
|
WHERE f.tablespace_name LIKE '&&tablespace_name'
|
|
GROUP BY tablespace_name
|
|
) b
|
|
, (SELECT t.name tablespace_name, min(creation_time) creation_time
|
|
FROM v$tablespace t , v$datafile f
|
|
WHERE t.ts# = f.ts#
|
|
GROUP BY t.name) c
|
|
WHERE a.tablespace_name = b.tablespace_name (+)
|
|
AND a.tablespace_name = c.tablespace_name (+)
|
|
UNION ALL
|
|
SELECT /*+ CHOOSE */
|
|
ts.tablespace_name
|
|
, c.creation_time
|
|
--, ROUND((ts.bytes_used + ts.bytes_free) / &&BYTES_DIVIDER , &&ROUND_PRECISION) alloc
|
|
, ROUND( tf.bytes / &&BYTES_DIVIDER , &&ROUND_PRECISION) alloc
|
|
, ROUND((ts.bytes_used - ss.free_bytes) / &&BYTES_DIVIDER , &&ROUND_PRECISION) used
|
|
, ROUND(((ts.bytes_free) + ss.free_bytes ) / &&BYTES_DIVIDER , &&ROUND_PRECISION) free
|
|
, ROUND(((ts.bytes_used - ss.free_bytes ) / (ts.bytes_used + ts.bytes_free))
|
|
* 100, &&ROUND_PRECISION) pct_Used
|
|
, 100 - ROUND(((ts.bytes_used - ss.free_bytes) / (ts.bytes_used + ts.bytes_free)) * 100, &&ROUND_PRECISION) pct_Free
|
|
, ROUND( tf.max_bytes / &&BYTES_DIVIDER , &&ROUND_PRECISION) MaxSize
|
|
, ROUND( ((ts.bytes_free) + ss.free_bytes + (tf.max_bytes - (ts.bytes_used + ts.bytes_free) )
|
|
) / &&BYTES_DIVIDER , &&ROUND_PRECISION) Maxfree
|
|
, ROUND( ( (ts.bytes_used - ss.free_bytes )/tf.max_bytes) * 100, &&ROUND_PRECISION) Max_pct_Used
|
|
, 100 - ROUND( ((ts.bytes_used - ss.free_bytes ) /tf.max_bytes )* 100, &&ROUND_PRECISION ) Max_pct_Free
|
|
FROM (SELECT tablespace_name
|
|
, sum(bytes_used) bytes_used
|
|
, sum(bytes_free) bytes_free
|
|
FROM v$temp_space_header
|
|
WHERE tablespace_name LIKE '&&tablespace_name'
|
|
GROUP BY tablespace_name
|
|
) ts
|
|
, (SELECT tablespace_name
|
|
, SUM(bytes) bytes
|
|
, SUM(DECODE(autoextensible, 'YES',GREATEST(maxbytes,bytes),'NO', bytes)) max_bytes
|
|
FROM dba_temp_files
|
|
WHERE tablespace_name LIKE '&&tablespace_name'
|
|
GROUP BY tablespace_name
|
|
) tf
|
|
, (SELECT ss.tablespace_name
|
|
, sum(ss.free_blocks * t.block_size) free_bytes
|
|
FROM gv$sort_segment ss
|
|
, dba_tablespaces t
|
|
WHERE ss.tablespace_name = t.tablespace_name
|
|
AND ss.tablespace_name LIKE '&&tablespace_name'
|
|
GROUP BY ss.tablespace_name
|
|
) ss
|
|
, dba_tablespaces t
|
|
, (SELECT t.name tablespace_name, min(creation_time) creation_time
|
|
FROM v$tablespace t , v$tempfile f
|
|
WHERE t.ts# = f.ts#
|
|
GROUP BY t.name) c
|
|
WHERE t.tablespace_name = ts.tablespace_name
|
|
AND t.tablespace_name = tf.tablespace_name
|
|
AND t.tablespace_name = ss.tablespace_name (+)
|
|
AND t.tablespace_name = c.tablespace_name (+)
|
|
AND t.tablespace_name LIKE '&&tablespace_name'
|
|
) c
|
|
ORDER BY 1 asc;
|
|
|
|
UNDEFINE tablespace_name
|
|
|
|
@@footer
|