104 lines
3.7 KiB
SQL
104 lines
3.7 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display datafile usage information (without high water mark)
|
|
* Currently as of upto 10.2.0.3 there is bug (5029334) which slows down queries
|
|
* on dba_extents view for locally managed tablespaces.
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 06-Jul-15 Vishal Gupta Upper case input tablespace_name parameter value
|
|
* 05-Aug-04 Vishal Gupta First Draft
|
|
*/
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
DEFINE tablespace_name="&&1"
|
|
|
|
COLUMN _TABLESPACE_NAME NEW_VALUE TABLESPACE_NAME NOPRINT
|
|
|
|
set term off
|
|
SELECT UPPER(DECODE('&&TABLESPACE_NAME','','%','&&TABLESPACE_NAME')) "_TABLESPACE_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 *******************************************
|
|
|
|
COLUMN tablespace FORMAT a22
|
|
COLUMN alloc HEADING "Alloc|MB" FORMAT 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 99,999
|
|
COLUMN freeable HEADING "FreeAble|MB" FORMAT 99,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 999
|
|
COLUMN file_name HEADING "Datafile name" FORMAT a60
|
|
|
|
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
|
|
|
|
|
|
select * from
|
|
(
|
|
Select /*+ RULE*/ t.tablespace_name
|
|
, d.status "Status"
|
|
, GREATEST(d.maxbytes,d.bytes)/1024/1024 maxsize
|
|
, d.bytes/1024/1024 alloc
|
|
, ROUND((d.bytes)/1024/1024 - (decode(f.bytes, NULL,0, f.bytes)/1024/1024),2) used
|
|
, ROUND(decode(f.bytes, NULL,0, f.bytes)/1024/1024,2) free
|
|
, d.INCREMENT_BY/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 tablespace_name
|
|
, file_id
|
|
, sum(bytes) bytes
|
|
FROM DBA_FREE_SPACE f
|
|
WHERE tablespace_name LIKE '%&&tablespace_name%'
|
|
GROUP BY tablespace_name, file_id) f
|
|
WHERE t.tablespace_name = d.tablespace_name
|
|
AND f.tablespace_name(+) = d.tablespace_name
|
|
AND f.file_id(+) = d.file_id
|
|
AND d.tablespace_name LIKE '%&&tablespace_name%'
|
|
UNION ALL
|
|
SELECT tf.tablespace_name
|
|
, tf.status
|
|
, GREATEST(tf.maxbytes,tf.bytes)/1024/1024 maxsize
|
|
, (tf.bytes/1024/1024) alloc
|
|
, ROUND(ts.bytes_used/1024/1024) used
|
|
, ROUND(ts.bytes_free/1024/1024) free
|
|
, tf.INCREMENT_BY/1024/1024 Increment_by
|
|
, 0 pct_increase
|
|
, tf.file_id
|
|
, SUBSTR(tf.file_name,1,80) file_name
|
|
FROM dba_temp_files tf, sys.V_$TEMP_SPACE_HEADER ts
|
|
WHERE ts.file_id = tf.file_id
|
|
and ts.tablespace_name LIKE '%&&tablespace_name%'
|
|
) c
|
|
ORDER BY c.tablespace_name,c.file_id asc;
|
|
|
|
undefine tablespace_name
|
|
|
|
@@footer
|
|
|