1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55
| select
* from (
SELECT o.OBJECT_NAME, o.OBJECT_ID ,'' short_name, t.partitioned,t.iot_type, o.OWNER OBJECT_OWNER, o.CREATED, o.LAST_DDL_TIME
FROM SYS.ALL_OBJECTS O ,sys.all_tables t
WHERE O.OWNER = :SCHEMA
and o.owner = t.owner
and o.object_name = t.table_name
AND O.OBJECT_TYPE = 'TABLE'
AND O.GENERATED = 'N'
AND NOT EXISTS (SELECT 1 FROM SYS.ALL_MVIEWS WHERE MVIEW_NAME = O.OBJECT_NAME AND OWNER = O.OWNER)
AND NOT EXISTS (SELECT 1 from all_queue_tables WHERE QUEUE_TABLE = O.OBJECT_NAME AND OWNER = O.OWNER)
AND not ( object_name like 'AQ$_%_G'
or object_name like 'AQ$_%_H'
or object_name like 'AQ$_%_I'
or object_name like 'AQ$_%_S'
or object_name like 'AQ$_%_T' )
union all
SELECT OBJECT_NAME, OBJECT_ID , syn.SYNONYM_NAME short_NAME,
t.partitioned,t.iot_type, SYN.TABLE_OWNER OBJECT_OWNER, o.CREATED, o.LAST_DDL_TIME
FROM SYS.ALL_OBJECTS O, sys.user_synonyms syn,sys.all_tables t
WHERE syn.table_owner = o.owner
and syn.TABLE_NAME = o.object_NAME
and o.object_name = t.table_name
and o.owner = t.owner
and o.object_type = 'TABLE'
and :INCLUDE_SYNS = 1
and :SCHEMA = USER
AND O.GENERATED = 'N' |
Partager