I have a requirement where I have to identify the Database Objects such as Package Bodies,Procedures,Functions,Triggers etc., that uses Select and/or DML on a table(Only Table not View) inside it.I require this for an audit purpose.
Do we have any data dictionaries to identify which object uses which table for Select/DML Operations? Or is there any scripts available to identify these.
The idea is to identify these objects and modify the code to use views instead of tables as per the standards followed.
Thanks in advance.
You can use *_dependencies to find static references to tables. Though this is limited in what it tells you:
create table t (
x int
);
create or replace package pkg as
procedure p ( p int );
function f ( p int )
return int;
end;
/
create or replace package body pkg as
procedure p ( p int ) as
begin
insert into t values (p);
end;
function f ( p int )
return int as
retval int;
begin
select x into retval
from t where x = p;
return retval;
end f;
end;
/
create or replace view vw as
select * from t;
select name, type
from user_dependencies
where referenced_name = 'T'
and referenced_type = 'TABLE';
NAME TYPE
VW VIEW
PKG PACKAGE BODY In 12.2 we extended PL/Scope to capture more details about static SQL in PL/SQL. So you can see where it's used in packages, procedures, etc. But not views:
select object_name, object_type, usage, line, col
from user_identifiers
where name = 'T'
and type = 'TABLE';
OBJECT_NAME OBJECT_TYPE USAGE LINE COL
PKG PACKAGE BODY REFERENCE 12 10
PKG PACKAGE BODY REFERENCE 4 17
T TABLE DECLARATION 1 15
Note these only apply to static SQL. If you're using dynamic SQL, the table won't show up in either case:
create or replace procedure p ( p int ) as
begin
execute immediate 'insert into t values (:p)' using p;
end p;
/
select name, type
from user_dependencies
where referenced_name = 'T'
and referenced_type = 'TABLE';
NAME TYPE
PKG PACKAGE BODY
VW VIEW
select object_type, usage, line, col
from user_identifiers
where name = 'T'
and type = 'TABLE';
OBJECT_TYPE USAGE LINE COL
PACKAGE BODY REFERENCE 12 10
PACKAGE BODY REFERENCE 4 17
TABLE DECLARATION 1 15
So you need to inspect *_source to look for references.