142 lines
5.5 KiB
SQL
142 lines
5.5 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display datafile usage information
|
|
* Parameters : 1 - Tablespace Name (Use '%' as wildcard, Default is %)
|
|
* 2 - File ID (Use '%' as wildcard, Default is %)
|
|
* 3 - File Name (Use '%' as wildcard, Default is %)
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 06-Jan-16 Vishal Gupta Increase file_name column width
|
|
* 06-Jul-15 Vishal Gupta Upper case input tablespace_name parameter value
|
|
* 08-Apr-13 Vishal Gupta Fixed increment_by column calculation to multiply
|
|
* number of blocks by tablespace blocks size.
|
|
* 05-Aug-04 Vishal Gupta Added file_id as input parameter
|
|
* 05-Aug-04 Vishal Gupta Created
|
|
*/
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE tablespace_name
|
|
UNDEFINE file_id
|
|
|
|
DEFINE tablespace_name="&&1"
|
|
DEFINE file_id="&&2"
|
|
DEFINE file_name="&&3"
|
|
|
|
set term off
|
|
COLUMN _TABLESPACE_NAME NEW_VALUE TABLESPACE_NAME NOPRINT
|
|
COLUMN _file_id NEW_VALUE file_id NOPRINT
|
|
COLUMN _file_name NEW_VALUE file_name NOPRINT
|
|
SELECT UPPER(DECODE('&&TABLESPACE_NAME','','%','&&TABLESPACE_NAME')) "_TABLESPACE_NAME"
|
|
, DECODE('&&file_id','','%','&&file_id') "_file_id"
|
|
, DECODE('&&file_name','','%','&&file_name') "_file_name"
|
|
FROM DUAL
|
|
;
|
|
|
|
set term on
|
|
|
|
PROMPT *******************************************
|
|
PROMPT F I L E S U S A G E R E P O R T
|
|
PROMPT
|
|
PROMPT Input Parameters
|
|
PROMPT Tablespace Name = '&&tablespace_name'
|
|
PROMPT File Id = '&&file_id'
|
|
PROMPT File Name = '&&file_name'
|
|
PROMPT *******************************************
|
|
|
|
COLUMN tablespace FORMAT a22
|
|
COLUMN alloc HEADING "Alloc|MB" FORMAT 9,999,999
|
|
COLUMN maxsize HEADING "MaxSize|MB" FORMAT 999,999
|
|
COLUMN hwm HEADING "HWM|MB" FORMAT 999,999
|
|
COLUMN used HEADING "Used|MB" FORMAT 999,999
|
|
COLUMN free HEADING "Free|MB" FORMAT 999,999
|
|
COLUMN freeable HEADING "FreeAble|MB" FORMAT 999,999
|
|
COLUMN initial_extent HEADING "InitExt|MB" FORMAT 9,999
|
|
COLUMN increment_by HEADING "IncBy|MB" FORMAT 9,999
|
|
COLUMN min_extents HEADING "MinExts" FORMAT 999
|
|
COLUMN max_extents HEADING "MaxExts" FORMAT 99,999
|
|
COLUMN pct_increase HEADING "%Inc" FORMAT 999
|
|
COLUMN file_id HEADING "F#" FORMAT 9999
|
|
COLUMN file_name HEADING "Datafile name" FORMAT a95
|
|
|
|
BREAK ON report
|
|
COMPUTE SUM LABEL 'Total' OF alloc FORMAT 99,999 ON report
|
|
COMPUTE SUM LABEL 'Total' OF used FORMAT 99,999 ON report
|
|
COMPUTE SUM LABEL 'Total' OF hwm FORMAT 99,999 ON report
|
|
COMPUTE SUM LABEL 'Total' OF free FORMAT 99,999 ON report
|
|
COMPUTE SUM LABEL 'Total' OF freeable FORMAT 99,999 ON report
|
|
|
|
|
|
select /*+ CHOOSE */ * from
|
|
(
|
|
Select /*+ CHOOSE */ t.tablespace_name
|
|
, d.status "Status"
|
|
, GREATEST(d.maxbytes,d.bytes)/1024/1024 maxsize
|
|
, d.bytes/1024/1024 alloc
|
|
, NVL((hwm.bytes)/1024/1024,0) HWM
|
|
, ROUND((d.bytes - NVL(f.bytes,0))/1024/1024,2) used
|
|
, ROUND(decode(f.bytes, NULL,0, f.bytes)/1024/1024,2) free
|
|
, ROUND( (d.bytes - GREATEST( NVL(hwm.bytes,0), (d.bytes - NVL(f.bytes,0)) ) ) /1024/1024,2) freeable
|
|
, d.INCREMENT_BY * t.block_size/1024/1024 Increment_by
|
|
, t.pct_increase pct_increase
|
|
, d.file_id
|
|
, SUBSTR(d.file_name,1,80) file_name
|
|
FROM DBA_DATA_FILES d , DBA_TABLESPACES t
|
|
, (SELECT /*+ CHOOSE */ tablespace_name
|
|
, file_id
|
|
, sum(bytes) bytes
|
|
FROM DBA_FREE_SPACE f
|
|
WHERE tablespace_name LIKE '&&tablespace_name'
|
|
AND file_id LIKE '&&file_id'
|
|
GROUP BY tablespace_name, file_id) f
|
|
, (Select /*+ CHOOSE */ file_id,
|
|
NVL( max((block_id + blocks - 1 ) * p.value),0) bytes
|
|
from dba_extents
|
|
, v$system_parameter p
|
|
WHERE tablespace_name LIKE '&&tablespace_name'
|
|
AND p.name = 'db_block_size'
|
|
AND file_id LIKE '&&file_id'
|
|
GROUP BY file_id
|
|
) hwm
|
|
WHERE t.tablespace_name = d.tablespace_name
|
|
AND f.tablespace_name(+) = d.tablespace_name
|
|
AND f.file_id(+) = d.file_id
|
|
AND hwm.file_id(+) = d.file_id
|
|
AND d.tablespace_name LIKE '&&tablespace_name'
|
|
AND d.file_id LIKE '&&file_id'
|
|
AND d.file_name LIKE '&&file_name'
|
|
UNION ALL
|
|
SELECT /*+ CHOOSE */ tf.tablespace_name
|
|
, tf.status
|
|
, GREATEST(tf.maxbytes,tf.bytes)/1024/1024 maxsize
|
|
, (tf.bytes/1024/1024) alloc
|
|
, (tf.bytes/1024/1024) HWM
|
|
, ROUND(ts.bytes_used/1024/1024) used
|
|
, ROUND(ts.bytes_free/1024/1024) free
|
|
, ROUND((tf.bytes - tf.bytes)/1024/1024,2) freeable
|
|
, tf.INCREMENT_BY * t.block_size/1024/1024 Increment_by
|
|
, 0 pct_increase
|
|
, tf.file_id
|
|
, SUBSTR(tf.file_name,1,80) file_name
|
|
FROM dba_temp_files tf, V$TEMP_SPACE_HEADER ts, dba_tablespaces t
|
|
WHERE ts.tablespace_name = t.tablespace_name
|
|
AND ts.file_id = tf.file_id
|
|
and ts.tablespace_name LIKE '&&tablespace_name'
|
|
AND tf.file_id LIKE '&&file_id'
|
|
AND tf.file_name LIKE '&&file_name'
|
|
) c
|
|
ORDER BY c.tablespace_name,c.file_id asc;
|
|
|
|
|
|
|
|
@@footer
|
|
|