144 lines
7.5 KiB
SQL
144 lines
7.5 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display Tablespace usage history from AWR repository
|
|
* Parameters : 1 - tablespace_name (Use % as wildcard, Default value '%')
|
|
* 2 - Number of days (Default value '3')
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 12-Apr-16 Vishal Gupta Changed script to make it compatible with 10g
|
|
* 03-Sep-14 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
|
|
UNDEFINE tablespace_name
|
|
UNDEFINE days
|
|
UNDEFINE WHERECLAUSE
|
|
|
|
DEFINE tablespace_name="&&1"
|
|
DEFINE days="&&2"
|
|
DEFINE WHERECLAUSE="&&3"
|
|
|
|
set term off
|
|
COLUMN _TABLESPACE_NAME NEW_VALUE TABLESPACE_NAME NOPRINT
|
|
COLUMN _DAYS NEW_VALUE DAYS NOPRINT
|
|
|
|
SELECT UPPER(DECODE('&&TABLESPACE_NAME','','%','&&TABLESPACE_NAME')) "_TABLESPACE_NAME"
|
|
, UPPER(DECODE('&&DAYS','','3','&&DAYS')) "_DAYS"
|
|
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 LARGE_BYTES_FORMAT="9,999,999"
|
|
--DEFINE LARGE_BYTES_HEADING="KB"
|
|
--DEFINE LARGE_BYTES_DIVIDER="1024"
|
|
DEFINE LARGE_BYTES_HEADING="MB"
|
|
DEFINE LARGE_BYTES_DIVIDER="1024/1024"
|
|
--DEFINE LARGE_BYTES_HEADING="GB"
|
|
--DEFINE LARGE_BYTES_DIVIDER="1024/1024/1024"
|
|
|
|
|
|
PROMPT *****************************************************************
|
|
PROMPT * T A B L E S P A C E U S A G E H I S T O R Y
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - Tablespace Name = '&&TABLESPACE_NAME'
|
|
PROMPT * - Days = '&&DAYS'
|
|
PROMPT * - WHERECLAUSE = "&&WHERECLAUSE"
|
|
PROMPT *****************************************************************
|
|
|
|
|
|
COLUMN end_interval_time HEADING "Timestamp" FORMAT a15
|
|
COLUMN tsname HEADING "Tablespace Name" FORMAT a30
|
|
COLUMN alloc HEADING "Alloc|(&&LARGE_BYTES_HEADING)" FORMAT &&LARGE_BYTES_FORMAT ON
|
|
COLUMN used HEADING "Used|(&&BYTES_HEADING)" FORMAT &&BYTES_FORMAT ON
|
|
COLUMN free HEADING "Free|(&&BYTES_HEADING)" FORMAT &&BYTES_FORMAT ON
|
|
COLUMN MAXAlloc HEADING "Max|Alloc|(&&LARGE_BYTES_HEADING)" FORMAT &&LARGE_BYTES_FORMAT ON
|
|
COLUMN MAXUsed HEADING "Max|Used|(&&BYTES_HEADING)" FORMAT &&BYTES_FORMAT ON
|
|
COLUMN MAXFree HEADING "Max|Free|(&&BYTES_HEADING)" FORMAT &&BYTES_FORMAT ON
|
|
COLUMN used_percent HEADING "Used|(%)" FORMAT 999.9 ON
|
|
COLUMN free_percent HEADING "Free|(%)" FORMAT 999.9 ON
|
|
COLUMN max_used_percent HEADING "Max|Used|(%)" FORMAT 999.9 ON
|
|
COLUMN max_free_percent HEADING "Max|Free|(%)" FORMAT 999.9 ON
|
|
COLUMN prev_alloc HEADING "Prev|Alloc|(&&BYTES_HEADING)" FORMAT &&BYTES_FORMAT ON
|
|
COLUMN prev_Used HEADING "Prev|Used|(&&BYTES_HEADING)" FORMAT &&BYTES_FORMAT ON
|
|
COLUMN prev_MAXSize HEADING "Prev|MaxSize|(&&BYTES_HEADING)" FORMAT &&BYTES_FORMAT ON
|
|
COLUMN size_diff HEADING "Alloc|Increase|(&&BYTES_HEADING)" FORMAT &&BYTES_FORMAT ON
|
|
COLUMN usedsize_diff HEADING "Used|Increase|(&&BYTES_HEADING)" FORMAT &&BYTES_FORMAT ON
|
|
COLUMN maxsize_diff HEADING "MaxSize|Increase|(&&BYTES_HEADING)" FORMAT &&BYTES_FORMAT ON
|
|
COLUMN separator HEADING "!|!|!" FORMAT A1 ON
|
|
|
|
WITH tbspc_space_usage AS
|
|
(SELECT su.*
|
|
, t.tsname
|
|
, p.VALUE block_size
|
|
, LAG (su.tablespace_size) OVER (PARTITION BY su.dbid, su.tablespace_id ORDER BY TO_DATE(su.rtime,'MM/DD/YYYY HH24:MI:SS') ) prev_tablespace_size
|
|
, LAG (su.tablespace_usedsize) OVER (PARTITION BY su.dbid, su.tablespace_id ORDER BY TO_DATE(su.rtime,'MM/DD/YYYY HH24:MI:SS') ) prev_tablespace_usedsize
|
|
, LAG (su.tablespace_maxsize) OVER (PARTITION BY su.dbid, su.tablespace_id ORDER BY TO_DATE(su.rtime,'MM/DD/YYYY HH24:MI:SS') ) prev_tablespace_maxsize
|
|
FROM dba_hist_tbspc_space_usage su
|
|
, dba_hist_tablespace t
|
|
, v$system_parameter p
|
|
, v$database d
|
|
WHERE su.dbid = d.dbid
|
|
AND su.dbid = t.dbid
|
|
AND su.tablespace_id = t.ts#
|
|
AND p.NAME = 'db_block_size'
|
|
AND UPPER(t.tsname) LIKE UPPER('&&TABLESPACE_NAME') ESCAPE '\'
|
|
AND TO_DATE(su.rtime,'MM/DD/YYYY HH24:MI:SS') > SYSDATE - &&DAYS
|
|
)
|
|
SELECT /* Not using hints --FIRST_ROWS NO_MERGE USE_NL(su t ) LEADING(su) */
|
|
TO_CHAR(TO_DATE(su.rtime,'MM/DD/YYYY HH24:MI:SS'),'DD-MON-YY HH24:MI') end_interval_time
|
|
, su.tsname
|
|
, '!' separator
|
|
, (ROUND(su.tablespace_size*su.block_size/&&LARGE_BYTES_DIVIDER)) alloc
|
|
, (ROUND(su.tablespace_usedsize*su.block_size/&&BYTES_DIVIDER)) Used
|
|
, (ROUND((su.tablespace_size - su.tablespace_usedsize)*su.block_size/&&BYTES_DIVIDER)) free
|
|
, ROUND((su.tablespace_usedsize/su.tablespace_size)*100,2) used_percent
|
|
, ROUND(((su.tablespace_size - su.tablespace_usedsize)/su.tablespace_size)*100,2) free_percent
|
|
, '!' separator
|
|
, (ROUND(su.tablespace_maxsize*su.block_size/&&LARGE_BYTES_DIVIDER)) MAXAlloc
|
|
, (ROUND((su.tablespace_maxsize - su.tablespace_usedsize)*su.block_size/&&BYTES_DIVIDER)) MAXFree
|
|
, ROUND((su.tablespace_usedsize/su.tablespace_maxsize)*100,2) max_used_percent
|
|
, ROUND(((su.tablespace_maxsize - su.tablespace_usedsize)/su.tablespace_maxsize)*100,2) max_free_percent
|
|
--, (ROUND(su.prev_tablespace_size*su.block_size/&&BYTES_DIVIDER)) prev_alloc
|
|
--, (ROUND(su.prev_tablespace_usedsize*su.block_size/&&BYTES_DIVIDER)) prev_Used
|
|
--, (ROUND(su.prev_tablespace_maxsize*su.block_size/&&BYTES_DIVIDER)) prev_MAXSize
|
|
, '!' separator
|
|
, ROUND((su.tablespace_size - su.prev_tablespace_size)*su.block_size/&&BYTES_DIVIDER) size_diff
|
|
, ROUND((su.tablespace_usedsize - su.prev_tablespace_usedsize)*su.block_size/&&BYTES_DIVIDER) usedsize_diff
|
|
, ROUND((su.tablespace_maxsize - su.prev_tablespace_maxsize)*su.block_size/&&BYTES_DIVIDER) maxsize_diff
|
|
FROM tbspc_space_usage su
|
|
WHERE 1=1
|
|
AND ( ( ROUND((su.tablespace_size - su.prev_tablespace_size)*su.block_size/1024/1024) <> 0
|
|
OR ROUND((su.tablespace_usedsize - su.prev_tablespace_usedsize)*su.block_size/1024/1024) <> 0
|
|
OR ROUND((su.tablespace_maxsize - su.prev_tablespace_maxsize)*su.block_size/1024/1024) <> 0
|
|
)
|
|
OR
|
|
TO_CHAR(TO_DATE(su.rtime,'MM/DD/YYYY HH24:MI:SS'),'HH24:MI') = '00:00'
|
|
)
|
|
&&WHERECLAUSE
|
|
ORDER BY TO_DATE(su.rtime,'MM/DD/YYYY HH24:MI:SS')
|
|
;
|
|
|
|
@@footer
|