Capture DDL statements in Oracle DB

There are many cases where we need to capture the ddl statements in our production environment where application team have direct access for create or drop any object in the application schema in the production database .

What is DDL ??

DDL (Data Definition Language) is a language used by a database management system that allows users to define the database and specify data types, structures and constraints on the data. Examples DDL statements are: CREATE TABLE, CREATE INDEX, ALTER, and DROP.

Here we need to create a table where all ddl statements in database will be captured, so that we can identify the who has executed the DDL statement in the database.

--Table will be created by sys user with tbs_audit as tablespace as default tablespace for audit table AUDIT_DDL

CREATE TABLE AUDIT_DDL ( 
DDL_DATE date, 
OSUSER varchar2(255),
CURRENT_USER varchar2(255),
HOST varchar2(255), 
TERMINAL varchar2(255), 
IP_ADDRESS VARCHAR2(100), 
module varchar2(100),
owner varchar2(30), 
type varchar2(30), 
name varchar2(30), 
sysevent varchar2(30),
sql_txt varchar2(4000) ) 
tablespace tbs_audit ;

--- Trigger will be created at sys user, so that all statements will be captured in any schema in the database

create or replace trigger
sys.audit_ddl_trg after ddl on database
declare
sql_text ora_name_list_t;
stmt VARCHAR2(4000) := '';
n number;
begin
n:=ora_sql_txt(sql_text);
for i in 1..n
loop
stmt:=substr(stmt||sql_text(i),1,4000);
end loop;
insert into audit_ddl(
DDL_DATE,osuser,
current_user,host,
terminal,
ip_address,module,
owner,
type,name,sysevent,sql_txt)
values(
sysdate,
sys_context('USERENV','OS_USER') ,
sys_context('USERENV','CURRENT_USER') ,
sys_context('USERENV','HOST') ,
sys_context('USERENV','TERMINAL') ,
UTL_INADDR.get_host_name('USERENV'),
sys_context('USERENV','MODULE') ,
ora_dict_obj_owner,
ora_dict_obj_type,
ora_dict_obj_name,
ora_sysevent,
stmt
);
end;
/
-- Sql Query can be used to lookup the captured ddl in the audit_ddl table.
col type format a10
col name format a10
col host format a16
col IP_ADDRESS format a10
col terminal format a20
col owner format a15
col sql_txt format a20
col SYSEVENT format a10
col CURRENT_USER format a10
col osuser format a10
select * from AUDIT_DDL;

So finally here we have deployed the mechanism to capture the ddl’s in the entire database.

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Arun Gupta’s LinkedIn:   www.linkedin.com/in/agoradba/

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

Anuradha’s LinkedIn: Anuradha’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp