Hey there! Let's walk through exactly how to track every ALTER TABLE statement in your multi-user Oracle database, log those details to a history table, and retrieve them later. This is straightforward with Oracle's DDL triggers—perfect for auditing schema changes.

You'll need a table to store all the ALTER TABLE details. Let's make one with all the key info you'll want:

CREATE TABLE ddl_alter_table_log (
    log_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    operation_timestamp TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
    user_name VARCHAR2(128) DEFAULT USER NOT NULL,
    object_type VARCHAR2(30) NOT NULL,
    object_name VARCHAR2(128) NOT NULL,
    ddl_statement CLOB NOT NULL
);

This table captures when the change happened, who did it, which table was modified, and the full SQL statement.

Next, we'll build a database-level trigger that fires whenever an ALTER TABLE is executed, and inserts the details into our log table.

CREATE OR REPLACE TRIGGER capture_alter_table_trigger
AFTER ALTER ON DATABASE
DECLARE
    v_sql_text CLOB;
BEGIN
    -- Only capture ALTER TABLE operations
    IF ORA_DICT_OBJ_TYPE = 'TABLE' THEN
        -- Concatenate the SQL text array into a single CLOB
        FOR i IN 1..ORA_SQL_TXT.COUNT LOOP
            v_sql_text := v_sql_text || ORA_SQL_TXT(i);
        END LOOP;
        
        -- Insert the details into the log table
        INSERT INTO ddl_alter_table_log (
            object_type,
            object_name,
            ddl_statement
        ) VALUES (
            ORA_DICT_OBJ_TYPE,
            ORA_DICT_OBJ_NAME,
            v_sql_text
        );
    END IF;
END;
/

Quick Tips: #

• You'll need the ADMINISTER DATABASE TRIGGER privilege to create this trigger (reach out to your DBA if you don't have it).
• If you only want to track specific users, add a condition like AND USER = 'YOUR_TARGET_USER' inside the IF block.

Run a test ALTER TABLE statement to make sure everything works:

-- Replace with your actual table name
ALTER TABLE your_test_table ADD COLUMN new_test_col VARCHAR2(100);

Then check the log table—you should see the entry for this change right away.

To view all the captured ALTER TABLE actions, just query the log table. Here's a useful query that sorts by the most recent changes first:

SELECT
    TO_CHAR(operation_timestamp, 'YYYY-MM-DD HH24:MI:SS') AS operation_time,
    user_name,
    object_name AS table_name,
    ddl_statement
FROM ddl_alter_table_log
ORDER BY operation_timestamp DESC;

This will show you exactly who modified which table, when, and the full SQL they used.

内容的提问来源于stack exchange，提问作者Nguyễn Thái Duy