PL/Scope and user_identifiers
How to find where a column is used
PL/Scope is a tool that uses the compiler to collect information about where particular columns and variables are used in stored PL/SQL code.
Traditionally, you would query user_source to find where a column is used. However, a text search cannot distinguish between columns
with the same name from different tables, so you then have to check the results and filter out the rows you are not interested in, along with
comments and quoted strings that happen to match your search pattern. The PL/SQL compiler, however, knows exactly which identifier is which
without the need for any text searches. PL/Scope uses the compiler to capture the locations of PL/SQL and SQL identifiers as well as SQL statements
in stored PL/SQL code.
To do this, you compile your program unit with an extra plscope_settings clause to extract identifiers of database objects or PL/SQL code items:
alter package xyz compile plscope_settings = 'IDENTIFIERS:ALL';
From Oracle 12.2, you can have it extract SQL statements as well:
alter package xyz compile plscope_settings = 'STATEMENTS:ALL';
To undo it and clear stored identifiers, replace ALL with NONE. (There's also a PUBLIC setting for PL/SQL identifiers if you want to exclude details from package or type bodies.)
If you are not sure which code units to compile, you can use user/all/dba_dependencies to find which ones use a particular table.
I've used alter session set plscope_settings = 'IDENTIFIERS:ALL, STATEMENTS:ALL'1
STATEMENTS requires Oracle 12.2. Earlier versions support only IDENTIFIERS.
to capture everything, even though here I only want to see where columns are used, but check the documentation for your version to see what
more specific settings are available. As PL/Scope captures a lot of information, it's recommended to do this only for code that you
are interested in, and obviously only in development and test systems and not production. Execute the block below to compile
all stored PL/SQL that references the table you are interested in.
begin
execute immediate q'[alter session set plscope_settings = 'IDENTIFIERS:ALL, STATEMENTS:ALL']'; --
for r in (
select distinct 'alter '||replace(d.type, ' BODY')||' '||d.name||' compile' as sql_text
from user_dependencies d
join user_plsql_object_settings o
on o.name = d.name
and o.type = d.type
where d.referenced_name = '%&Table%'
and d.referenced_type = 'TABLE'
)
loop
dbms_output.put_line(r.sql_text);
execute immediate r.sql_text;
end loop;
end;
/
You can check what got compiled by querying user_plsql_object_settings:
select s.name, s.type, s.plscope_settings from user_plsql_object_settings s where s.plscope_settings <> 'IDENTIFIERS:NONE' order by 1,2;
Here is a demo package using the 'HR' test schema, with two procedures referring to tables EMPLOYEES, DEPARTMENTS, LOCATIONS and REGIONS. In a real system of course you might have dozens or hundreds of packages, procedures, functions and object types, and impact analysis for a particular table or column can be challenging.
create or replace package hr_demo
as
procedure add_department
( p_department_name in departments.department_name%type
, p_manager_id in departments.manager_id%type
, p_location_id in departments.location_id%type
, p_out_department_id out departments.department_id%type );
procedure report_employees
( p_results out sys_refcursor );
end hr_demo;
/
create or replace package body hr_demo
as
procedure add_department
( p_department_name in departments.department_name%type
, p_manager_id in departments.manager_id%type
, p_location_id in departments.location_id%type
, p_out_department_id out departments.department_id%type )
is
begin
insert into departments
( department_id
, department_name
, manager_id
, location_id )
values
( departments_seq.nextval
, p_department_name
, p_manager_id
, p_location_id )
returning department_id into p_out_department_id;
end add_department;
procedure report_employees
( p_results out sys_refcursor )
is
begin
open p_results for
select e.employee_id
, e.first_name
, e.last_name
, d.department_name
, l.city
, c.country_name
, r.region_name
from employees e
join departments d on d.department_id = e.department_id
join locations l on l.location_id = d.location_id
join countries c on c.country_id = l.country_id
join regions r on r.region_id = c.region_id;
end report_employees;
end hr_demo;
/
Now you can query user_identifiers to see where the DEPARTMENTS table is used:
with column_names as
( select /*+ materialize */ s.object_name as table_name, s.name as column_name, s.signature
from user_identifiers s
where s.type = 'COLUMN'
and s.object_type = 'TABLE'
and s.usage = 'DECLARATION'
and s.object_name = '&Table' )
, subprograms (name, type, line, subprogram) as
( select /*+ materialize */ s.name, s.type, s.line, upper(regexp_substr(s.text, '[^ ().]+', 1, 2))
from user_source s
where regexp_like(s.text,'^ *(PROCEDURE|FUNCTION) ', 'i')
and s.name in (select i.object_name from user_identifiers i) )
, identifiers as
( select /*+ materialize */ name, object_name, object_type, line, signature
from user_identifiers
where type = 'COLUMN'
and usage = 'REFERENCE' )
select /*+ dynamic_sampling(11) no_push_subq */ c.table_name, c.column_name, i.object_type, i.object_name
, ( select sp.subprogram
from subprograms sp
where sp.type = i.object_type
and sp.name = i.object_name
and sp.line < i.line
order by line desc
fetch first row only ) as subprogram
, i.line
from identifiers i
join column_names c on c.signature = i.signature
order by i.name, i.object_name, i.object_type, i.line;
You can also find SQL statements. Notice that it gives you SQL IDs, which link to all the v$sql* views, SQL Monitor, ASH, AWR etc:
select s.type
, s.object_name
, s.object_type
, s.line
, s.col
, s.sql_id
, s.text
from user_statements s
where s.object_name = 'HR_DEMO'
and s.object_type like 'PACKAGE%'
and s.sql_id is not null
order by s.line, s.col;
