Announcement

Collapse

http://progeeking.com

See more
See less

Tracking DDL changes in 11g

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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:

    Code:
     
    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:

    Code:
     
    [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:

    Code:
    CREATE OR REPLACE TRIGGER SYS.trace_ddl
    Code:
    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 0×907da308 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 0×010000c2
    DDL end in opiexe
    session id 127 inc 3293 pgadep 0 sqlid 2k18f2uj8d9xh txn 0×907da308 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 0×8f849888 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 0×8f849888 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 0×8f849888 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 0×8f834b98 autocommit 1

    —– Current SQL Statement for this session (sql_id=0000000000000) —–
    drop table test
    ktagetg_ddl sessionid 127 inc 3251 pgadep 0 txn 0×8f834b98 table 72076 mode 6
    ktagetg_ddl sessionid 127 inc 3251 pgadep 0 txn 0×8f834b98 table 72076 mode 6
    ktagetg_ddl sessionid 127 inc 3251 pgadep 0 txn 0×8f834b98 table 72076 mode 6
    ktagetg_ddl sessionid 127 inc 3251 pgadep 0 txn 0×8f834b98 table 72076 mode 6
    ktagetg_ddl sessionid 127 inc 3251 pgadep 0 txn 0×8f834b98 table 72076 mode 6
    DDL begin in opiprs
    session id 127 inc 3251 pgadep 1 sqlid 8qmxb0kjzac3z oct 15 txn 0×8f834b98 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 0×8f834b98 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 0×8f834b98 autocommit 0 commited 0
    DDL end in opiexe
    session id 127 inc 3251 pgadep 0 sqlid 0000000000000 txn 0×8f834b98 autocommit 1 commited 1
    References:
    Oracle diagnostic events Part 2: http://oraclue.com/2009/03/12/oracle...events-part-2/
    Metalink: Oracle Database Lock Matrix Doc ID: 749779.1

  • #2
    Why is my package being invalidated? This is the question I asked myself a few times those days. In order to find out what is happening in my schema, I created this simple DDL trigger which tracks all DDL changes in my schema.
    CREATE TABLE AUDIT_DDL ( d date, OSUSER varchar2(255), CURRENT_USER varchar2(255), HOST varchar2(255), TERMINAL varchar2(255), owner varchar2(30), type varchar2(30), name varchar2(30), sysevent varchar2(30), sql_txt varchar2(4000)); create or replace trigger audit_ddl_trg after ddl on schema declare sql_text ora_name_list_t; stmt VARCHAR2(4000) := ''; n number; begin if (ora_sysevent='TRUNCATE') then null; else 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(d, osuser,current_user,host,terminal,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') , ora_dict_obj_owner, ora_dict_obj_type, ora_dict_obj_name, ora_sysevent, stmt ); end if; end; /

    Comment

    Working...
    X