with t0 as
(
select *
from
(
select t.object_type
, t.owner
, t.object_name
, t.status
, count(*) over (partition by t.owner,t.object_name order by t.object_type) as n
from dba_objects t
where t.owner in ('TEDSTRUIK')
and t.object_type in
( 'MATERIALIZED VIEW'
, 'TABLE'
, 'FUNCTION'
, 'PACKAGE'
, 'PACKAGE BODY'
, 'PROCEDURE'
, 'SEQUENCE'
, 'SYNONYM'
, 'TYPE'
, 'TYPE BODY'
, 'VIEW'
)
and ( t.object_type like '%' and t.object_name like 'TED\_%' escape '\' ) --' Only my objects
and not ( t.object_type = 'TYPE' and t.object_name like 'SYS\_PLSQL\_%' escape '\' ) --' Types for pipelined table functions
and not ( t.object_type = 'TABLE' and t.object_name like 'MLOG$%' escape '\' ) --' Materialized view log
and not ( t.object_type = 'TABLE' and t.object_name like 'RUPD$%' escape '\' ) --' Materialized view log
and not ( t.object_type = 'TABLE' and t.object_name like '%JAVA$%' escape '\' ) --' Java
and not ( t.object_type like '%' and t.object_name like 'PLSQL\_%' escape '\' ) --' PL/SQL Developer
and not ( t.object_type like '%' and t.object_name like 'TOAD\_%' escape '\' ) --' TOAD
and not ( t.object_type like '%' and t.object_name like 'AQ$%' escape '\' ) --' Advanced Queue
and not ( t.object_type = 'TABLE' and t.object_name like 'DR$%' escape '\' ) --' Oracle Text index tables
and not ( t.object_type like '%' and t.object_name like 'JSON\_%' escape '\' ) --' PL-JSON
and not ( t.object_type = 'TABLE' and t.object_name = 'PLAN_TABLE' )
and not ( t.object_type = 'PROCEDURE' and t.object_name like '\_%' escape '\' ) --' e.g. _RW_Parse_CDA
and t.object_name not in
(
select mvl.master
from dba_mview_logs mvl
)
) ta
where (n = 1 and object_type <> 'MATERIALIZED VIEW')
or (n = 2 and object_type = 'TABLE')
)
, t0_mb as
(
select owner
, object_name
, object_type
, n
, sum(mb_table) as mb_table
, sum(mb_index) as mb_index
, sum(mb_table + mb_index) as mb_total
from
(
select t0.owner
, t0.object_name
, t0.object_type
, t0.n
, round(sum(seg.bytes) / (1024 * 1024), 2) as mb_table
, 0 as mb_index
from dba_segments seg
, t0
where seg.owner = t0.owner
and seg.segment_name = t0.object_name
group by t0.owner
, t0.object_name
, t0.object_type
, t0.n
union all
select t0.owner
, t0.object_name
, t0.object_type
, t0.n
, 0 as mb_table
, round(sum(seg.bytes) / (1024 * 1024), 2) as mb_index
from dba_segments seg
, dba_indexes idx
, t0
where seg.owner = idx.owner
and seg.segment_name = idx.index_name
and idx.table_owner = t0.owner
and idx.table_name = t0.object_name
group by t0.owner
, t0.object_name
, t0.object_type
, t0.n
)
group by owner
, object_name
, object_type
, n
)
, s0 as
(
select t0.object_type as object_type
, t0.owner as object_owner
, t0.object_name as object_name
, t0.object_type as target_type
, t0.owner as target_owner
, t0.object_name as target_name
, t0.status
from t0
union all
select 'SYNONYM' as object_type
, s.owner as object_owner
, s.synonym_name as object_name
, t0.object_type as target_type
, t0.owner as target_owner
, t0.object_name as target_name
, t0.status
from dba_synonyms s
, t0
where s.table_owner = t0.owner
and s.table_name = t0.object_name
)
, a as
(
select target_type
, target_owner
, target_name
, status
, descr
, count(*) over (partition by target_type, target_owner, target_name order by descr rows between unbounded preceding and current row) as n
, sum(reference_count) over (partition by target_type, target_owner, target_name) as n_total
, count(*) over (partition by target_type, target_owner, target_name) as n_unique
from
(
select s0.target_type
, s0.target_owner
, s0.target_name
, s0.status
, d.owner || '.' || d.name || ' (' || d.type || ')' as descr
, 1 as reference_count
from s0
, dba_dependencies d
where s0.object_type = d.referenced_type
and s0.object_owner = d.referenced_owner
and s0.object_name = d.referenced_name
and not ( d.type = 'PACKAGE BODY'
and d.referenced_type = 'PACKAGE'
and d.owner = d.referenced_owner
and d.name = d.referenced_name
)
and not ( d.type in ('MATERIALIZED VIEW')
and d.referenced_type in ('TABLE')
and d.owner = d.referenced_owner
and d.name = d.referenced_name
)
union all
select s0.target_type
, s0.target_owner
, s0.target_name
, s0.status
, '[APEX]' || trim(app.app_id) as descr
, sum(app.reference_count) as reference_count
from s0
, ted_p1109_apex_dependencies app
where s0.object_owner = app.owner
and (case when s0.object_type = 'MATERIALIZED VIEW' then 'TABLE' else s0.object_type end ) = upper(app.referenced_type)
and s0.object_name = app.referenced_name
group by s0.target_type
, s0.target_owner
, s0.target_name
, s0.status
, app.app_id
union all
select s0.target_type
, s0.target_owner
, s0.target_name
, s0.status
, '[DYNSQL]' || dsl.owner || '.' || dsl.name || '(' || dsl.type || ')' as descr
, 1 as reference_count
from s0
, ted_p1109_dynamic_sql dsl
where s0.object_owner = dsl.r_owner
and s0.object_name = dsl.r_name
and s0.target_type = dsl.r_type
union all
select s0.target_type
, s0.target_owner
, s0.target_name
, s0.status
, '[FK]' || c.owner || '.' || c.table_name as descr
, 1 as reference_count
from s0
, dba_constraints c
, dba_constraints c_r
where s0.object_owner = c_r.owner
and s0.object_name = c_r.table_name
and c.r_constraint_name = c_r.constraint_name
and c.constraint_type = 'R'
and not ( c.owner = c_r.owner and c.table_name = c_r.table_name )
)
)
, b as
(
select a.target_type
, a.target_owner
, a.target_name
, a.status
, a.n_total
, a.n_unique
, listagg(a.descr, ', ') within group (order by a.n)
|| ( case when a.n_unique > 50 then ' [...]' else null end )
as objects
from a
where a.n <= 50
group by a.target_type
, a.target_owner
, a.target_name
, a.status
, a.n_total
, a.n_unique
)
, c as
(
select b.*
from b
union all
select t0.object_type as target_type
, t0.owner as target_owner
, t0.object_name as target_name
, t0.status
, 0 as n_total
, 0 as n_unique
, null as objects
from t0
where (t0.object_type, t0.owner, t0.object_name) not in (select target_type, target_owner, target_name from b)
)
select ( case t0_mb.n when 2 then 'MATERIALIZED VIEW' else c.target_type end ) as target_type
, c.target_owner
, c.target_name
, ( case when m.mview_name is not null then to_char(m.last_refresh_date, 'YYYY-MM-DD') else c.status end ) as status
, c.n_total
, c.objects
, t0_mb.mb_table
, t0_mb.mb_index
, t0_mb.mb_total
, ( case
when ( case t0_mb.n when 2 then 'MATERIALIZED VIEW' else c.target_type end ) in ('TABLE') then
'drop ' || 'TABLE' || ' ' || c.target_name || ';'
when c.target_type in ('PACKAGE BODY') then
'drop ' || 'PACKAGE' || ' ' || c.target_name || ';'
when c.target_type in ('TYPE BODY') then
'drop ' || 'TYPE' || ' ' || c.target_name || ';'
when c.target_type not in ('PACKAGE BODY', 'TYPE BODY') then
'drop ' || ( case t0_mb.n when 2 then 'MATERIALIZED VIEW' else c.target_type end ) || ' ' || c.target_name || ';'
else null
end
) as sql_statement
, ( case
when c.target_type in ('TABLE') then
'C:\oracle\product\11.2.0\client_1\bin\exp'
|| ' username/password@' || ( select name from v$database )
|| ' file=' || c.target_owner || '.' || c.target_name || '.dmp'
|| ' tables=' || c.target_owner || '.' || c.target_name
|| ' statistics=NONE'
else null
end
) as exp_statement
from c
, dba_mviews m
, t0_mb
where c.target_owner = m.owner (+)
and c.target_name = m.mview_name (+)
and c.target_owner = t0_mb.owner (+)
and c.target_name = t0_mb.object_name (+)
and c.target_type = t0_mb.object_type (+)
order by c.n_total
, c.target_type
, c.target_owner
, c.target_name