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.

    execute immediate q'[alter session set plscope_settings = 'IDENTIFIERS:ALL, STATEMENTS:ALL']';  --

    for r in (
        select distinct 'alter '||replace(d.type, ' BODY')||' '||||' compile' as sql_text
        from   user_dependencies d
               join user_plsql_object_settings o
                    on =
                    and o.type = d.type
        where  d.referenced_name = '%&Table%'
        and    d.referenced_type = 'TABLE'
        execute immediate r.sql_text;
    end loop;

You can check what got compiled by querying user_plsql_object_settings:

select, 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
    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
    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 )
        insert into departments
          ( department_id
          , department_name
          , manager_id
          , location_id )
          ( 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 )
        open p_results for
            select e.employee_id
                 , e.first_name
                 , e.last_name
                 , d.department_name
                 , 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, 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.type, s.line, upper(regexp_substr(s.text, '[^     ().]+', 1, 2)) 
         from   user_source s 
         where  regexp_like(s.text,'^ *(PROCEDURE|FUNCTION) ', 'i') 
         and 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 = 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.object_name, i.object_type, i.line;

Table column usage listing

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;
SQL statement listing