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