Column Actions

An improved method to determine dependencies for database objects, including dependencies in Apex applications.

Unfortunately, the dba_dependencies view doesn't include every possible object dependency:

• Apex applications:
  This is handled here by table ted_p1109_apex_dependencies, which is filled manually using the Utilities -> Database Object Dependencies report
  Note: Database Object Dependency Report removed in Apex 20.2 !!!
• Dynamic SQL:
  This is a manual step, alas. Table ted_p1109_dynamic_sql is included in the ted_p1109_v view. The table has to be filled manually; view ted_p1109_dynamic_sql_v can be used to determine its content, but you may want to add other sources to that.
• Foreign keys:
  I use dba_constraints in the ted_p1109_v view to add those.
• Replicated tables, updatable materialized view tables and master tables of a materialized view for which a materialized view log has been created:
  Trying to rename these results in an ORA-26563, so I exclude these using dba_mview_logs.
  

The ted_p1109_v view includes columns for table/index size, plus SQL and EXP statements you can use to drop/rename/export the objects.

To get the objects to compile, you need these privileges: grant select on dba_mview_logs to tedstruik; grant select on dba_objects to tedstruik; grant select on dba_segments to tedstruik; grant select on dba_indexes to tedstruik; grant select on dba_synonyms to tedstruik; grant select on dba_mviews to tedstruik; grant select on v_$database to tedstruik;
Note 1: This method is better than just using the dba_dependecies view, but it is not 100% complete. Use at your own risk.
To illustrate the fact that this method is also not perfect: in the example above, some objects seem to have 0 dependencies, e.g. package ted_p1026. However this package is used in Apex, but only via a call to the ted_page_summary package (which queries some dba views to show the package source etc.). Therefore, since it is not called directly, it will not show up in the Apex Database Object Dependencies report.

Note 2: The Database Object Dependencies report is not complete. It ignores PL/SQL code in Dynamic Actions, and references to tables/view/materialized views which are used in processes like Automated Row Fetch. There may of course be even more ignored dependencies - you don't know what you don't know... :-)
This is a query to add these ignored dependencies to the ted_p1109_apex_dependencies table mentioned above: with d0 as ( select * from all_objects where owner in ('TEDSTRUIK') ) -- Dynamic Actions select 'insert into ted_p1109_apex_dependencies (owner, referenced_name, referenced_type, reference_count, app_id)' || ' values (' || '''' || b.owner || '''' || ',''' || b.object_name || '''' || ',''' || b.object_type || '''' || ',' || trim(count(*)) || ',' || trim(b.application_id) || ');' || ' -- Dynamic Action, Page ' || trim(b.page_id) as sql_insert from ( select d.owner , d.object_name , d.object_type , a.application_id , da.page_id from apex_application_page_da_acts a , apex_application_page_da da , ( select * from d0 where object_type not in ('PACKAGE BODY') ) d where a.dynamic_action_id = da.dynamic_action_id and upper(a.attribute_01) like '%' || d.object_name || '%' and a.action_code = 'NATIVE_EXECUTE_PLSQL_CODE' and a.application_id in (25384) ) b group by b.owner , b.object_name , b.object_type , b.application_id , b.page_id union all -- Automated Row Fetch select 'insert into ted_p1109_apex_dependencies (owner, referenced_name, referenced_type, reference_count, app_id)' || ' values (' || '''' || b.owner || '''' || ',''' || b.object_name || '''' || ',''' || b.object_type || '''' || ',' || trim(count(*)) || ',' || trim(b.application_id) || ');' || ' -- Row Process, Page ' || trim(b.page_id) as sql_insert from ( select d.owner , d.object_name , d.object_type , p.application_id , p.page_id from apex_application_page_proc p , ( select * from d0 where object_type in ('TABLE', 'VIEW', 'MATERIALIZED VIEW') ) d where replace( regexp_substr(to_char(p.process_source), ':[^:]+:'), ':', '') like '%' || d.object_name || '%' and p.process_type_code like 'DML\_%' escape '\' --' and p.application_id in (25384) ) b group by b.owner , b.object_name , b.object_type , b.application_id , b.page_id

Region

Identification
	Sequence	10
	Title	Object Dependencies
	Type	Interactive Report
Source
	Source Type	DYNAMIC_QUERY
	Region Source	select * from ted_p1109_v order by n_total desc , target_type , target_owner , target_name

Table

Table
	Name	ted_p1109_apex_dependencies
	DDL	CREATE TABLE "TEDSTRUIK"."TED_P1109_APEX_DEPENDENCIES" ( "OWNER" VARCHAR2(100) NOT NULL ENABLE, "REFERENCED_NAME" VARCHAR2(100) NOT NULL ENABLE, "REFERENCED_TYPE" VARCHAR2(100) NOT NULL ENABLE, "REFERENCE_COUNT" NUMBER NOT NULL ENABLE, "APP_ID" NUMBER NOT NULL ENABLE ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS"

Table

Table
	Name	ted_p1109_dynamic_sql
	DDL	CREATE TABLE "TEDSTRUIK"."TED_P1109_DYNAMIC_SQL" ( "OWNER" VARCHAR2(100) NOT NULL ENABLE, "NAME" VARCHAR2(100) NOT NULL ENABLE, "TYPE" VARCHAR2(100) NOT NULL ENABLE, "R_OWNER" VARCHAR2(100) NOT NULL ENABLE, "R_NAME" VARCHAR2(100) NOT NULL ENABLE, "R_TYPE" VARCHAR2(100) NOT NULL ENABLE ) SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS"

View

View
	Name	ted_p1109_dynamic_sql_v
	DDL	with s2 as ( select s.* , lead(s.line , 1) over (partition by s.owner, s.name, s.type order by s.line ) as line_lead , lead(s.openfor, 1) over (partition by s.owner, s.name, s.type order by s.line ) as openfor_lead from ( select soe.owner , soe.name , soe.type , soe.line , soe.text , ( case when lower(soe.text) like 'open %' or lower(soe.text) like '% open %' or lower(soe.text) like '% open' then 'open' end ) || ( case when lower(soe.text) like 'for %' or lower(soe.text) like '% for %' or lower(soe.text) like '% for' then 'for' end ) as openfor from dba_source soe where soe.owner in ('TEDSTRUIK') and ( lower(soe.text) like 'open %' or lower(soe.text) like '% open %' or lower(soe.text) like '% open' or lower(soe.text) like 'for %' or lower(soe.text) like '% for %' or lower(soe.text) like '% for' ) and not ( trim(soe.text) like '--%' ) and not ( trim(soe.text) like '/*%' ) ) s ) select a."OWNER",a."NAME",a."TYPE",a."LINE",a."TEXT" from ( -- dbms_sql.execute -- execute immediate select soe.owner , soe.name , soe.type , soe.line , soe.text from dba_source soe where soe.owner in ('TEDSTRUIK') and ( lower(soe.text) like '%dbms_sql.execute%' or lower(soe.text) like '%execute immediate%' ) and not ( lower(soe.text) like '%execute immediate%alter%' or lower(soe.text) like '%execute immediate%truncate%' or lower(soe.text) like '%execute immediate%disable_%' or lower(soe.text) like '%execute immediate%enable_%' or lower(soe.text) like '%execute immediate%lock%' ) union -- open for using select s2.owner , s2.name , s2.type , s2.line , rtrim ( max( case when s2.openfor in ('openfor', 'open') then s2.text else null end ) || ' # ' || max( case when s2.openfor in ('for' ) then s2.text else null end ) , ' #' ) as text from s2 where ( ( s2.openfor = 'open' and s2.openfor_lead = 'for' and s2.line_lead = s2.line + 1 ) -- on 2 lines (never seems to happen...) or ( s2.openfor = 'openfor' ) -- on 1 line ) group by s2.owner , s2.name , s2.type , s2.line ) a order by a.owner , a.name , a.type , a.line

View

View
	Name	ted_p1109_v
	DDL	with t0 as ( -- all relevant objects 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') -- limit the schemas and t.object_type in ( 'MATERIALIZED VIEW' , 'TABLE' , 'FUNCTION' , 'PACKAGE' , 'PACKAGE BODY' , 'PROCEDURE' , 'SEQUENCE' , 'SYNONYM' , 'TYPE' , 'TYPE BODY' , 'VIEW' ) -- include: and ( t.object_type like '%' and t.object_name like 'TED\_%' escape '\' ) --' Only my objects -- exclude: 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' ) --' TOAD and not ( t.object_type = 'PROCEDURE' and t.object_name like '\_%' escape '\' ) --' e.g. _RW_Parse_CDA and t.object_name not in ( -- Replicated Tables -- Prevent "ORA-26563: renaming this table is not allowed" -- Cause: Attempt to rename a replicated table, an updatable materialized view table or the master table of a materialized view for which a materialized view log has been created. select mvl.master from dba_mview_logs mvl ) ) ta where (n = 1 and object_type <> 'MATERIALIZED VIEW') -- all materialized views show as tables in dba_objects - we don't need those tables... or (n = 2 and object_type = 'TABLE') -- ... but we do need the corresponding materialized views ) , t0_mb as ( -- estimated size in MB per object (table + indexes) 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 ( -- all objects plus all synonyms of those objects 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 ( -- all dependencies of the objects 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 ( -- database dependencies of the objects 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 ) -- Package specs always reference their own bodies, so exclude those 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 ) -- Materialized Views always reference their own table, so exclude those union all -- apex dependencies of the objects 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) -- Materialized Views are called tables here... 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 -- Dynamic SQL 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 -- Foreign keys 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 ) -- exclude "loops" to the table itself ) ) , b as ( -- all objects that have dependencies 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 -- don't make the column too large group by a.target_type , a.target_owner , a.target_name , a.status , a.n_total , a.n_unique ) , c as ( -- all objects that have dependencies select b.* from b union all -- plus all objects that don't have dependencies 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 -- the N=2 tables are really materialized views , 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 || ';' -- 'rename ' || c.target_name || ' to ' || 'ZZZ_' || substr(c.target_name,5) || ';' -- alternative to drop: rename TED_TABLENAME to ZZZ_TABLENAME 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