CODEWITHSUNDEEP
oracle-database
Moudiz
Moudiz

Reputation: 7377

Is there a way to log table creation and column modification in a table with the one who execute it, in oracle schema?

I am searching for a way, to store only the tables and columns that are added in the database with the one who created it (nachine_name) in a log table.

I tried to add a trigger on sys table user_tab_cols but I cannot do that Why cannot I create triggers on objects owned by SYS?

The system table user_objects will give me the date when a table created, but I want also to know which machine created it. and I also want to track the column creation and modification and log them in a table.

Is that possible ? is there a way for that ?

Upvotes: 0

Views: 66

Answers (1)

schurik
schurik

Reputation: 7928

you can create a database event trigger:

CREATE OR REPLACE TRIGGER log_ddl_event_trg
  AFTER DDL
  ON DATABASE
DECLARE
  v_sql_list  ora_name_list_t;
  v_sql_txt   VARCHAR2(2500);
BEGIN
  FOR i in 1..ORA_SQL_TXT(v_sql_list) LOOP
    v_sql_txt := v_sql_txt || v_sql_list(i);
    EXIT WHEN length(v_sql_txt ) >= 2000;
  END LOOP;

 ...
END;
/

in the Trigger, you can get the executed ddl-statement using the ORA_SQL_TXT() Funktion, and then log it in the table together with the other data (log_date, user etc.).

Upvotes: 1

Related Questions