Home > Internals > Tracking DDL changes in 11g

Tracking DDL changes in 11g

As you 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:

[disk=DISABLE | LOW | LOWEST | MEDIUM | HIGH | HIGHEST]

[memory=DISABLE | LOW | LOWEST | MEDIUM | HIGH | HIGHEST]

[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

About these ads
Categories: Internals
  1. Theo Stienissen
    August 31, 2009 at 5:28 pm | #1

    Hallo Miladin,

    I advise you to never set max_dump_file_size to unlimited as this can completely fill your filesystem.

  2. August 31, 2009 at 8:41 pm | #2

    Hi Theo,

    Thanks for reading my blog.Every time I set max_dump_file_size to unlimited I watch filesystem and I also I ensure that I have plenty of free space.Hit that issue long time ago :)

    Thanks,

    Miladin

  3. September 7, 2009 at 3:25 pm | #3

    This is an awesome sharing to see what is going on behind the scenes. Thank you very much for this post.

  4. suresh
    January 5, 2010 at 3:09 pm | #4

    Thanks, i learn lot of things from you.you are excellent.

  5. January 5, 2010 at 5:23 pm | #5

    Hi Suresh,

    I am glad that you like my blog.
    Thanks,

    Miladin :)

  1. August 23, 2009 at 8:52 pm | #1

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 41 other followers

%d bloggers like this: