Oracle: How to define a new object name in the trigger "AFTER ALTER"?

Question

Suppose I have a trigger AFTER ALTERin my Oracle database and I rename some database object ( ALTER ... RENAME TO ...). Inside the trigger, how to determine the new name of the database object? It seems that the functions ORA_DICT_OBJ_OWNER, ORA_DICT_OBJ_NAMEand ORA_DICT_OBJ_TYPEreturn the values ​​of the old database object.

For example:

CREATE OR REPLACE TRIGGER ADAM_BEFORE_AFTER BEFORE ALTER ON DATABASE
BEGIN
  DBMS_OUTPUT.put_line('Before alter: ' || ora_dict_obj_owner || '.' || ora_dict_obj_name || ' (' || ora_dict_obj_type || ')');
END;

CREATE OR REPLACE TRIGGER ADAM_AFTER_ALTER AFTER ALTER ON DATABASE
BEGIN
  DBMS_OUTPUT.put_line('After alter: ' || ora_dict_obj_owner || '.' || ora_dict_obj_name || ' (' || ora_dict_obj_type || ')');
END;

Suppose I renamed a table:

ALTER TABLE USELESS_TABLE9 RENAME TO USELESS_TABLE10

The database displays this:

Before alter: DEVELOPER.USELESS_TABLE9 (TABLE)
After alter: DEVELOPER.USELESS_TABLE9 (TABLE)

: , . BEFORE DDL AFTER DDL, , BEFORE RENAME AFTER RENAME. , BEFORE RENAME AFTER RENAME ...

: , BEFORE RENAME AFTER RENAME , BEFORE ALTER AFTER ALTER. .

Answer 1

ALTER RENAME , RENAME x TO y .

, , DDL , :

CREATE OR REPLACE TRIGGER MK_BEFORE_RENAME BEFORE RENAME ON SCHEMA 
DECLARE 
  sql_text ora_name_list_t;
  v_stmt VARCHAR2(2000);
  n PLS_INTEGER; 
BEGIN  
  n := ora_sql_txt(sql_text);
  FOR i IN 1..n LOOP
   v_stmt := v_stmt || sql_text(i);
  END LOOP;

  Dbms_Output.Put_Line( 'Before: ' || regexp_replace( v_stmt, 'rename[[:space:]]+([a-z0-9_]+)[[:space:]]+to.*', '\1', 1, 1, 'i' ) );
  Dbms_Output.Put_Line( 'After: ' || regexp_replace( v_stmt, 'rename[[:space:]]+.*[[:space:]]+to[[:space:]]+([a-z0-9_]+)', '\1', 1, 1, 'i' ) );
END;

, :

RENAME 
mktestx
TO                 mktesty;

Before: mktestx
After: mktesty

. :

CREATE OR REPLACE TRIGGER MK_AFTER_ALTER AFTER ALTER ON SCHEMA 
DECLARE 
  sql_text ora_name_list_t;
  v_stmt VARCHAR2(2000);
  n PLS_INTEGER; 
BEGIN  
  n := ora_sql_txt(sql_text);
  FOR i IN 1..n LOOP
   v_stmt := v_stmt || sql_text(i);
  END LOOP;

  Dbms_Output.Put_Line( 'Before: ' || regexp_replace( v_stmt, 'alter[[:space:]]+table[[:space:]]+([a-z0-9_]+)[[:space:]]+rename[[:space:]]+to.*', '\1', 1, 1, 'i' ) );
  Dbms_Output.Put_Line( 'After: ' || regexp_replace( v_stmt, 'alter[[:space:]]+table[[:space:]]+.*to[[:space:]]+([a-z0-9_]+)', '\1', 1, 1, 'i' ) );
END;