support/oracle
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
oracle/star/tbs.sql
root 93039b8489 2026-03-12 20:23:15
2026-03-12 21:23:47 +01:00

183 lines
9.0 KiB
SQL
Raw Permalink Blame History

/*
*
* 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
Reference in New Issue View Git Blame Copy Permalink