78 lines
2.6 KiB
SQL
78 lines
2.6 KiB
SQL
@@header
|
|
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE owner
|
|
UNDEFINE object_name
|
|
UNDEFINE subobject_name
|
|
|
|
DEFINE owner="&&1"
|
|
DEFINE object_name="&&2"
|
|
DEFINE subobject_name="&&3"
|
|
|
|
|
|
COLUMN _owner NEW_VALUE owner NOPRINT
|
|
COLUMN _object_name NEW_VALUE object_name NOPRINT
|
|
COLUMN _subobject_name NEW_VALUE subobject_name NOPRINT
|
|
|
|
set term off
|
|
|
|
SELECT DECODE(UPPER('&&owner'),'','%','&&owner') "_owner"
|
|
, DECODE(UPPER('&&object_name'),'','%','&&object_name') "_object_name"
|
|
, DECODE(UPPER('&&subobject_name'),'','%','&&subobject_name') "_subobject_name"
|
|
FROM DUAL;
|
|
|
|
SELECT CASE
|
|
WHEN INSTR('&&owner','.') != 0 THEN SUBSTR(UPPER('&&owner'),1,INSTR('&&owner','.')-1)
|
|
ELSE DECODE(UPPER('&&owner'),'','%',UPPER('&&owner'))
|
|
END "_owner"
|
|
, CASE
|
|
WHEN INSTR('&&owner','.') != 0 THEN SUBSTR(UPPER('&&owner'),INSTR('&&owner','.')+1)
|
|
ELSE DECODE(UPPER('&&object_name'),'','%',UPPER('&&object_name'))
|
|
END "_object_name"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
|
|
PROMPT *****************************************************************
|
|
PROMPT * S G A O B J E C T S L I S T
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - Owner = '&&owner'
|
|
PROMPT * - Object Name = '&&object_name'
|
|
PROMPT * - SubObject Name = '&&subobject_name'
|
|
PROMPT *****************************************************************
|
|
|
|
set lines 1000
|
|
set numf 999,999,999,999
|
|
set pages 80
|
|
|
|
|
|
COLUMN owner FORMAT a20
|
|
COLUMN object_name FORMAT a70
|
|
COLUMN subobject_name FORMAT a30
|
|
|
|
BREAK ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF "Total (Bytes)" FORMAT 99,999,999,999 ON REPORT
|
|
|
|
|
|
SELECT /*+ choose */ o.OWNER || '.' || o.object_name || NVL2(o.subobject_name,':' || o.subobject_name,'') object_name
|
|
, SUM(DECODE(bitand(flag,1),1,0,a.blksize)) "Not Dirty (Bytes)"
|
|
, SUM(DECODE(bitand(flag,1),1,a.blksize,0)) "Dirty (Bytes)"
|
|
, SUM(dirty_queue* a.blksize) "On Dirty (Bytes)"
|
|
, SUM(a.blksize) "Total (Bytes)"
|
|
FROM x$bh x
|
|
, DBA_OBJECTS o
|
|
, (SELECT VALUE blksize FROM v$system_parameter WHERE NAME = 'db_block_size') a
|
|
WHERE x.obj = o.OBJECT_ID
|
|
AND o.owner like '&&owner' ESCAPE '\'
|
|
AND o.object_name like '&&object_name' ESCAPE '\'
|
|
AND NVL(o.subobject_name,'%') like '&&subobject_name' ESCAPE '\'
|
|
GROUP BY o.OWNER, o.object_name, o.subobject_name
|
|
ORDER BY SUM(a.blksize) asc
|
|
/
|
|
|
|
@@footer |