1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
| select distinct req1.*, a1.synonym_name, a1.table_owner, a1.table_name, a2.synonym_name, a2.table_owner, a2.table_name from (
SELECT SQL_ID as sql_id
, DBMS_LOB.SUBSTR ( SQL_TEXT, 4000, 1) as c1
FROM sys.wrh$_SQLTEXT w
where
(
(upper(DBMS_LOB.SUBSTR ( SQL_TEXT, 4000, 1)) like '%FROM%')
or (upper(DBMS_LOB.SUBSTR ( SQL_TEXT, 4000, 4000)) like '%FROM%')
or (upper(DBMS_LOB.SUBSTR ( SQL_TEXT, 4000, 8000)) like '%FROM%')
or (upper(DBMS_LOB.SUBSTR ( SQL_TEXT, 4000, 12000)) like '%FROM%')
or (upper(DBMS_LOB.SUBSTR ( SQL_TEXT, 4000, 16000)) like '%FROM%')
or (upper(DBMS_LOB.SUBSTR ( SQL_TEXT, 4000, 20000)) like '%FROM%')
or (upper(DBMS_LOB.SUBSTR ( SQL_TEXT, 4000, 24000)) like '%FROM%')
)
) req1
, all_synonyms a1, all_synonyms a2
where req1.c1 like ('%' || a1.synonym_name || '%')
and req1.c1 like ('%' || a2.synonym_name || '%')
and a1.table_owner in ('S1')
and a1.owner = 'PUBLIC'
and a2.table_owner not in ('S1','SYS','SYSTEM','SYSMAN','PERFSTAT')
and a2.owner = 'PUBLIC'
; |
Partager