199 lines
6.8 KiB
MySQL
199 lines
6.8 KiB
MySQL
@@header
|
|
|
|
REM
|
|
REM N O T F I N I S H E D Y E T
|
|
REM
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Search for objects
|
|
* Parameters : 1 - owner (% - wildchar, \ - escape char)
|
|
* 2 - Object_NAME (% - wildchar, \ - escape char)
|
|
* 3 - Object_Type (% - wildchar, \ - escape char)
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 21-Feb-13 Vishal Gupta Fixed hierarchy when parent is not present
|
|
* 16-May-12 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
UNDEFINE owner
|
|
UNDEFINE object_name
|
|
UNDEFINE object_type
|
|
|
|
DEFINE owner="&&1"
|
|
DEFINE object_name="&&2"
|
|
DEFINE object_type="&&3"
|
|
|
|
|
|
|
|
COLUMN _owner NEW_VALUE owner NOPRINT
|
|
COLUMN _object_name NEW_VALUE object_name NOPRINT
|
|
COLUMN _subobject_name NEW_VALUE subobject_name NOPRINT
|
|
COLUMN _object_type NEW_VALUE object_type 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"
|
|
, DECODE(UPPER('&&object_type'),'','%','&&object_type') "_object_type"
|
|
FROM DUAL;
|
|
|
|
SELECT SUBSTR(UPPER('&&owner'), 1 , CASE INSTR('&&owner','.') WHEN 0 THEN LENGTH ('&&owner') ELSE INSTR('&&owner','.') - 1 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"
|
|
, CASE
|
|
WHEN INSTR('&&owner','.') != 0 THEN DECODE(UPPER('&&object_name'),'','%',UPPER('&&object_name'))
|
|
ELSE DECODE(UPPER('&&object_type'),'','%',UPPER('&&object_type'))
|
|
END "_object_type"
|
|
FROM DUAL;
|
|
|
|
set term on
|
|
|
|
|
|
PROMPT
|
|
PROMPT ***************************************************************************************************
|
|
PROMPT * D E P E N D E N C Y
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - Owner = '&&owner'
|
|
PROMPT * - ObjectName = '&&object_name'
|
|
PROMPT * - ObjectType = '&&object_type'
|
|
PROMPT ***************************************************************************************************
|
|
|
|
|
|
|
|
set pages 1000
|
|
|
|
COLUMN hierarchy HEADING "Hierarchy" FORMAT a70
|
|
COLUMN hierarchy_parent HEADING "Hierarchy (Parents)" FORMAT a60
|
|
COLUMN hierarchy_children HEADING "Hierarchy (Children)" FORMAT a60
|
|
COLUMN type HEADING "ObjectType" FORMAT a20
|
|
COLUMN referenced_type HEADING "ObjectType" FORMAT a20
|
|
COLUMN status HEADING "Status" FORMAT a10
|
|
COLUMN last_ddl_time HEADING "LastDDLTime" FORMAT a18
|
|
COLUMN last_specification_change HEADING "Last|Specification|Change" FORMAT a18
|
|
COLUMN created HEADING "Created" FORMAT a18
|
|
COLUMN dependency_type HEADING "DepType" FORMAT a7
|
|
COLUMN last_analyzed HEADING "LastAnalyzed" FORMAT a18
|
|
COLUMN num_rows HEADING "NumRows" FORMAT 99,999,999,999
|
|
|
|
|
|
WITH parent as
|
|
(
|
|
SELECT /*+ no_merge */
|
|
level hlevel
|
|
, d.owner
|
|
, d.name
|
|
, d.type
|
|
, o.status
|
|
, o.last_ddl_time
|
|
, o.timestamp
|
|
, o.created
|
|
, d.dependency_type
|
|
FROM dba_dependencies d
|
|
, dba_objects o
|
|
WHERE d.owner = o.owner
|
|
AND d.name = o.object_name
|
|
AND d.type = o.object_type
|
|
CONNECT BY NOCYCLE PRIOR d.owner = d.referenced_owner
|
|
AND PRIOR d.name = d.referenced_name
|
|
AND PRIOR DECODE(d.type,'MATERIALIZED VIEW','TABLE',d.type) = DECODE(d.referenced_type,'MATERIALIZED VIEW','TABLE',d.referenced_type)
|
|
START WITH d.referenced_owner LIKE '&&owner' ESCAPE '\'
|
|
AND d.referenced_name LIKE '&&object_name' ESCAPE '\'
|
|
AND d.referenced_type LIKE '&&object_type' ESCAPE '\'
|
|
)
|
|
, parent_level as
|
|
(SELECT NVL(min(hlevel),0) min_parent_level
|
|
, NVL(max(hlevel),1) max_parent_level
|
|
from parent)
|
|
, children as
|
|
(
|
|
SELECT /*+ no_merge */
|
|
level + max_parent_level hlevel
|
|
, d.referenced_owner
|
|
, d.referenced_name
|
|
, d.referenced_type
|
|
, o.status
|
|
, o.last_ddl_time
|
|
, o.timestamp
|
|
, o.created
|
|
, d.dependency_type
|
|
FROM dba_dependencies d
|
|
, dba_objects o
|
|
, parent_level
|
|
WHERE d.referenced_owner = o.owner
|
|
AND d.referenced_name = o.object_name
|
|
AND d.referenced_type = o.object_type
|
|
CONNECT BY NOCYCLE d.owner = PRIOR d.referenced_owner
|
|
AND d.name = PRIOR d.referenced_name
|
|
-- For materialized views dependency view show dependency on underlying MV table.
|
|
AND DECODE(d.type,'MATERIALIZED VIEW','TABLE',d.type) = PRIOR DECODE(d.referenced_type,'MATERIALIZED VIEW','TABLE',d.referenced_type)
|
|
START WITH d.owner LIKE '&&owner' ESCAPE '\'
|
|
AND d.name LIKE '&&object_name' ESCAPE '\'
|
|
AND d.type LIKE '&&object_type' ESCAPE '\'
|
|
)
|
|
, hierarchy as
|
|
(
|
|
SELECT /*+ no_merge */ --LPAD('-',(p.hlevel-1) * 4 ,'|---') || p.owner || '.' || p.name hierarchy
|
|
p.hlevel
|
|
, p.owner
|
|
, p.name object_name
|
|
, p.type
|
|
, p.status
|
|
, p.last_ddl_time
|
|
, p.timestamp
|
|
, p.created
|
|
, p.dependency_type
|
|
FROM parent p
|
|
UNION ALL
|
|
SELECT /*+ no_merge */
|
|
--LPAD('-', (min_parent_level ) * 4 ,'|---') || o.owner || '.' || o.object_name
|
|
min_parent_level
|
|
, o.owner
|
|
, o.object_name
|
|
, o.object_type
|
|
, o.status
|
|
, o.last_ddl_time
|
|
, o.timestamp
|
|
, o.created
|
|
, ''
|
|
FROM dba_objects o
|
|
, parent_level
|
|
WHERE o.owner LIKE '&&owner' ESCAPE '\'
|
|
AND o.object_name LIKE '&&object_name' ESCAPE '\'
|
|
UNION ALL
|
|
SELECT /*+ no_merge */ -- LPAD('-',(c.hlevel-1)*4 ,'|---') || c.referenced_owner || '.' || c.referenced_name hierarchy
|
|
c.hlevel
|
|
, c.referenced_owner
|
|
, c.referenced_name
|
|
, c.referenced_type type
|
|
, c.status
|
|
, c.last_ddl_time
|
|
, c.timestamp
|
|
, c.created
|
|
, c.dependency_type
|
|
FROM children c
|
|
)
|
|
SELECT LPAD('-',(h.hlevel-1)*4 ,'|---') || h.owner || '.' || h.object_name hierarchy
|
|
, h.type
|
|
, h.status
|
|
, TO_CHAR(h.last_ddl_time,'DD-MON-YY HH24:MI:SS') last_ddl_time
|
|
, TO_CHAR(TO_DATE(h.timestamp,'YYYY-MM-DD HH24:MI:SS'),'DD-MON-YY HH24:MI:SS') last_specification_change
|
|
, TO_CHAR(h.created,'DD-MON-YY HH24:MI:SS') created
|
|
, h.dependency_type
|
|
, TO_CHAR(t.last_analyzed,'DD-MON-YY HH24:MI:SS') last_analyzed
|
|
, t.num_rows
|
|
FROM hierarchy h
|
|
LEFT OUTER JOIN dba_tables t ON t.owner = h.owner AND t.table_name = h.object_name AND h.type = 'TABLE'
|
|
;
|
|
|
|
@@footer
|