DDLトリガー と イベント属性ファンクション で CREATE されるオブジェクトのオーナー名とオブジェクト名を記録する。(Oracle Database)

表題の通り Oracle Database の DDLトリガー と イベント属性ファンクションで、CREATE されるオブジェクトのオーナー名とオブジェクト名を記録してみますやで。
彡(ﾟ)(ﾟ)

1. 参考マニュアル

以下のマニュアルを参考にしました。イベント属性ファンクションが肝ですね。

Oracle Database データベース PL/SQL言語リファレンス 19c

9.15.1 イベント属性ファンクション
https://docs.oracle.com/cd/F19136_01/lnpls/plsql-triggers.html#GUID-266DBF6D-AA74-490C-ADE5-962C10708C2D
ora_dict_obj_name ... VARCHAR2(128): DDL操作が発生したディクショナリ・オブジェクトの名前
ora_dict_obj_owner ... VARCHAR2(128): DDL操作が発生したディクショナリ・オブジェクトの所有者
ora_dict_obj_type ... VARCHAR2(20) : DDL操作が発生したディクショナリ・オブジェクトの型
　
9.5.3 INSTEAD OF CREATEトリガー
https://docs.oracle.com/cd/F19136_01/lnpls/plsql-triggers.html#GUID-7B32FE4B-AB8E-48B0-A74C-599584A485A7
　
14.12 CREATE TRIGGER文 ※構文図
https://docs.oracle.com/cd/F19136_01/lnpls/CREATE-TRIGGER-statement.html#GUID-AF9E33F1-64D1-4382-A6A4-EC33C36F237B

2. 事前準備(記録用テーブル と DDLトリガー作成)

記録用テーブル と DDLトリガー を作成します。SYSユーザーで作成します。
今回は ayshibatスキーマ の CREATE を記録するようにしました。

CONNECT SYS/xxxxxxxx@ORCL AS SYSDBA

CREATE TABLE ayshibat.tbl_create_object_logging (
    OBJECT_OWNER     VARCHAR2(128)
  , OBJECT_NAME      VARCHAR2(128)
  , OBJECT_TYPE      VARCHAR2(23)
  , CREATE_TIMESTAMP TIMESTAMP
);

CREATE OR REPLACE TRIGGER ayshibat.trg_create_object_logging
INSTEAD OF CREATE ON ayshibat.SCHEMA
BEGIN
  INSERT INTO ayshibat.tbl_create_object_logging
  VALUES (
      ora_dict_obj_owner
    , ora_dict_obj_name
    , ora_dict_obj_type
    , SYSTIMESTAMP
  );
END;
/

3. オブジェクト作成 と 記録結果の確認

対象スキーマ(今回は ayshibatスキーマ)でテーブルを CREATE してみます。

CONNECT ayshibat/xxxxxxxx@ORCL;

CREATE TABLE TAB1(ID NUMBER);

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY/MM/DD HH24:MI:SS.FF';
SET LINESIZE 170;
COLUMN OBJECT_OWNER FORMAT A30;
COLUMN OBJECT_NAME  FORMAT A30;
COLUMN CREATE_TIMESTAMP FORMAT A30;
SELECT * FROM ayshibat.tbl_create_object_logging;

結果は以下の通りです。

SQL> CONNECT ayshibat/xxxxxxxx@ORCL;
Connected.

SQL> CREATE TABLE TAB1(ID NUMBER);

Table created.

SQL> ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY/MM/DD HH24:MI:SS.FF';

Session altered.

SQL> SET LINESIZE 170;
SQL> COLUMN OBJECT_OWNER FORMAT A30;
SQL> COLUMN OBJECT_NAME  FORMAT A30;
SQL> COLUMN CREATE_TIMESTAMP FORMAT A30;
SQL> SELECT * FROM ayshibat.tbl_create_object_logging;

OBJECT_OWNER                   OBJECT_NAME                    OBJECT_TYPE             CREATE_TIMESTAMP
------------------------------ ------------------------------ ----------------------- ------------------------------
AYSHIBAT                       TAB1                           TABLE                   2022/05/30 10:11:44.580414

オーナー名とオブジェクト名を記録できました。

4. まとめ

CREATE されたオブジェクトのオーナー名やオブジェクト名を記録できました。
当初はイベント属性ファンクションに辿り着けず、教えて頂いたN家さんに感謝感激雨霰
彡(^)(^)ｱﾘｶﾞﾄｺﾞｻﾞﾏｰｽ