oracleops-support: Tracking DDL changes in 11g

As you all know Oracle provides DDL triggers to track changes to the database.Data from these triggers are used for auditing, change controls etc.

But Oracle 11g also provides specific event for tracking DDL changes.It uses target called SQL_DDL.

These events can be enabled on session or system level.This is shortest command:

SQL> alter session set events ‘trace[SQL_DDL]’;

and to turn off:

SQL> alter session set events ‘trace[SQL_DDL] off’;

System level:

SQL> alter system set events ‘trace[SQL_DDL]’;

System altered.

SQL> alter system set events ‘trace[SQL_DDL] off’;

This command can be extended to trace disk , memory or use different resolution etc.List is below:

[get_time=DISABLE | DEFAULT | SEQ | HIGHRES | SEQ_HIGHRES ]

[get_stack=DISABLE | DEFAULT | ENABLED ]

For my test I have created simple logon trigger whic will trace only my userid after logon on database:

CREATE OR REPLACE TRIGGER SYS.trace_ddl
after logon on database
begin
if user like ‘MILADIN’ then
execute immediate ‘alter session set timed_statistics=true’;
execute immediate ‘alter session set max_dump_file_size=unlimited’;
execute immediate ‘alter session set tracefile_identifier=”MILADIN”’;
execute immediate ‘alter session set events ”trace [SQL_DDL]”’;
end if;
end;
/

Here are few examples.

First trace is coming from create statement.These traces can be used not only to track DDL changes but they are also very
educational especially if you interested into Oracle internals.

Most useful part of course is DDL sql statement but also I can see from trace below that Oracle internal function called ctcdrv is involved with create table statement.This function will be part of call stack for this operation.
Beside sqlid there is object id and transaction properties.

DDL begin in opiprs
session id 127 inc 3293 pgadep 0 sqlid 2k18f2uj8d9xh oct 1 txn 0x907da308 autocommit 1
—– Current SQL Statement for this session (sql_id=2k18f2uj8d9xh) —–
create table test (a number, b number)

ctcdrv
session id 127 inc 3293 pgadep 0 sqlid 2k18f2uj8d9xh DDL on 72076 op-alter_table 0
Creating segment
session id 127 inc 3293 pgadep 0 sqlid 2k18f2uj8d9xh objn 72076 objd 72076 tsn 4 rdba 0x010000c2
DDL end in opiexe
session id 127 inc 3293 pgadep 0 sqlid 2k18f2uj8d9xh txn 0x907da308 autocommit 1 commited 1

Second example is coming from ALTER TABLE command.Oracle is placing exclusive lock in mode 3 and using function ktagetg_ddl.

DDL begin in opiprs
session id 137 inc 49870 pgadep 0 sqlid 22xpbw2mmxhg5 oct 15 txn 0x8f849888 autocommit 1
—– Current SQL Statement for this session (sql_id=22xpbw2mmxhg5) —–
alter table test add ( c varchar2(20))

Lock statement for DDL
session id 137 inc 49870 pgadep 0 sqlid 22xpbw2mmxhg5 string LOCK TABLE “TEST” IN ROW EXCLUSIVE MODE NOWAIT
ktagetg_ddl sessionid 137 inc 49870 pgadep 0 txn 0x8f849888 table 72076 mode 3
atbdrv
session id 137 inc 49870 pgadep 0 sqlid 22xpbw2mmxhg5 atbdrv DDL on 72076
DDL end in opiexe
session id 137 inc 49870 pgadep 0 sqlid 22xpbw2mmxhg5 txn 0x8f849888 autocommit 1 commited 1

and last is DROP TABLE segment.

Note here that Oracle is renaming table ( placing in recycle bin ) and using lock mode 6 .

DDL begin in opiprs
session id 127 inc 3251 pgadep 0 sqlid 0000000000000 oct 12 txn 0x8f834b98 autocommit 1
—– Current SQL Statement for this session (sql_id=0000000000000) —–
drop table test

ktagetg_ddl sessionid 127 inc 3251 pgadep 0 txn 0x8f834b98 table 72076 mode 6
ktagetg_ddl sessionid 127 inc 3251 pgadep 0 txn 0x8f834b98 table 72076 mode 6
ktagetg_ddl sessionid 127 inc 3251 pgadep 0 txn 0x8f834b98 table 72076 mode 6
ktagetg_ddl sessionid 127 inc 3251 pgadep 0 txn 0x8f834b98 table 72076 mode 6
ktagetg_ddl sessionid 127 inc 3251 pgadep 0 txn 0x8f834b98 table 72076 mode 6
DDL begin in opiprs
session id 127 inc 3251 pgadep 1 sqlid 8qmxb0kjzac3z oct 15 txn 0x8f834b98 autocommit 0
—– Current SQL Statement for this session (sql_id=8qmxb0kjzac3z) —–
ALTER TABLE “MILADIN”.”TEST” RENAME TO “BIN$cZh5wRSSvx7gQAB/AQBWVA==$0”

ktagetg_ddl sessionid 127 inc 3251 pgadep 1 txn 0x8f834b98 table 72076 mode 6
atbdrv
session id 127 inc 3251 pgadep 1 sqlid 8qmxb0kjzac3z atbdrv DDL on 72076
DDL end in opiexe
session id 127 inc 3251 pgadep 1 sqlid 8qmxb0kjzac3z txn 0x8f834b98 autocommit 0 commited 0
DDL end in opiexe
session id 127 inc 3251 pgadep 0 sqlid 0000000000000 txn 0x8f834b98 autocommit 1 commited 1

References:

Oracle diagnostic events Part 2: http://oraclue.com/2009/03/12/oracle-diagnostic-events-part-2/

Metalink: Oracle Database Lock Matrix Doc ID: 749779.1