Objective: Ways to track DML changes in a table
Detailed objective:
We should be able to track the changes in the table at any point in time; say last 24hrs or last 12 hrs or between 2 different time frames
Options:
1. Trigger to fire on every insert or update or delete statements
2. Log miner, in case force logging is turned on.
3. DML tracking feature of oracle by default provides this info to some extent we can relate
4. Flashback data archive
Reference: https://blogs.oracle.com/connect/post/a-fresh-look-at-auditing-row-changes
Option 1:
Trigger [let us use most optimal setup - composite trigger]
Option 2:
Log Miner
Option 3:
DML tracking feature of oracle
select * from dba_Tab_modifications;
Option 4: Flashback data archive
Step 0)
Create the employees table and load some data
CREATE TABLE EMPLOYEES (
EMPLOYEE_ID NUMBER(6),
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(25) NOT NULL,
EMAIL VARCHAR2(25) NOT NULL,
PHONE_NUMBER VARCHAR2(20),
HIRE_DATE DATE NOT NULL,
JOB_ID VARCHAR2(10) NOT NULL,
SALARY NUMBER(8,2),
MANAGER_ID NUMBER(6),
DEPARTMENT_ID NUMBER(4),
VACATION_BALANCE NUMBER(6,2)
) tablespace tbspc_hr;
INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID, VACATION_BALANCE) VALUES (101, 'John', 'Doe', 'john.doe@example.com', '555-123-4567', TO_DATE('2020-01-15', 'YYYY-MM-DD'), 'IT_PROG', 60000.00, 100, 60, 20.50);
INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID, VACATION_BALANCE) VALUES (102, 'Jane', 'Smith', 'jane.smith@example.com', '555-987-6543', TO_DATE('2019-03-22', 'YYYY-MM-DD'), 'SA_REP', 85000.00, 101, 80, 25.00);
INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID, VACATION_BALANCE) VALUES (103, 'Peter', 'Jones', 'peter.jones@example.com', '555-111-2222', TO_DATE('2021-07-01', 'YYYY-MM-DD'), 'FI_ACCOUNT', 72000.00, 102, 90, 18.75);
INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID, VACATION_BALANCE) VALUES (104, 'Alice', 'Brown', 'alice.brown@example.com', '555-333-4444', TO_DATE('2018-11-10', 'YYYY-MM-DD'), 'AD_VP', 120000.00, NULL, 10, 30.00);
INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID, VACATION_BALANCE) VALUES (105, 'Robert', 'Davis', 'robert.davis@example.com', '555-555-6666', TO_DATE('2022-02-28', 'YYYY-MM-DD'), 'PU_CLERK', 35000.00, 103, 30, 15.00);
INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID, VACATION_BALANCE) VALUES (106, 'Maria', 'Garcia', 'maria.garcia@example.com', '555-777-8888', TO_DATE('2017-09-05', 'YYYY-MM-DD'), 'HR_REP', 50000.00, 104, 40, 22.00);
INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID, VACATION_BALANCE) VALUES (107, 'David', 'Miller', 'david.miller@example.com', '555-000-1111', TO_DATE('2023-04-18', 'YYYY-MM-DD'), 'MK_MAN', 95000.00, 101, 20, 28.00);
INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID, VACATION_BALANCE) VALUES (108, 'Sarah', 'Wilson', 'sarah.wilson@example.com', '555-222-3333', TO_DATE('2019-12-01', 'YYYY-MM-DD'), 'AC_MGR', 110000.00, 102, 100, 26.50);
INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID, VACATION_BALANCE) VALUES (109, 'Michael', 'Moore', 'michael.moore@example.com', '555-444-5555', TO_DATE('2020-06-20', 'YYYY-MM-DD'), 'SH_CLERK', 40000.00, 105, 50, 19.00);
INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID, VACATION_BALANCE) VALUES (110, 'Emily', 'Taylor', 'emily.taylor@example.com', '555-666-7777', TO_DATE('2021-01-01', 'YYYY-MM-DD'), 'PR_REP', 55000.00, 106, 70, 21.00);
commit;
Actual op:
SQL> create user hr identified by "hr";
User created.
SQL> grant create table, resource, create session, connect to hr;
Grant succeeded.
SQL> create tablespace tbspc_hr datafile size 10m autoextend on next 10m;
Tablespace created.
SQL> alter session set current_schema=HR;
Session altered.
SQL> alter user hr quota unlimited on tbspc_hr;
User altered.
SQL> CREATE TABLE EMPLOYEES (
EMPLOYEE_ID NUMBER(6),
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(25) NOT NULL,
EMAIL VARCHAR2(25) NOT NULL,
PHONE_NUMBER VARCHAR2(20),
HIRE_DATE DATE NOT NULL,
JOB_ID VARCHAR2(10) NOT NULL,
SALARY NUMBER(8,2),
MANAGER_ID NUMBER(6),
DEPARTMENT_ID NUMBER(4),
VACATION_BALANCE NUMBER(6,2)
) tablespace tbspc_hr; 2 3 4 5 6 7 8 9 10 11 12 13
Table created.
SQL>
SQL> INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID, VACATION_BALANCE) VALUES (101, 'John', 'Doe', 'john.doe@example.com', '555-123-4567', TO_DATE('2020-01-15', 'YYYY-MM-DD'), 'IT_PROG', 60000.00, 100, 60, 20.50);
1 row created.
SQL> INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID, VACATION_BALANCE) VALUES (102, 'Jane', 'Smith', 'jane.smith@example.com', '555-987-6543', TO_DATE('2019-03-22', 'YYYY-MM-DD'), 'SA_REP', 85000.00, 101, 80, 25.00);
1 row created.
...
SQL> commit;
Commit complete.
Step 1) Create a tablespace to storage flashback data archive
create tablespace SPACE_FOR_ARCHIVE
datafile size 10M AUTOEXTEND ON NEXT 10M;
Output:
SQL> create tablespace SPACE_FOR_ARCHIVE
datafile size 10M AUTOEXTEND ON NEXT 10M; 2
Tablespace created.
SQL>
Step 2) Create a new structure to retain the information for a defined period (in our case 1 year)
CREATE FLASHBACK ARCHIVE longterm
TABLESPACE space_for_archive
RETENTION 1 YEAR;
Output:
SQL> CREATE FLASHBACK ARCHIVE longterm
TABLESPACE space_for_archive
RETENTION 1 YEAR; 2 3
Flashback archive created.
SQL>
Step 3) Associate the needed table with the flashback archive.
ALTER TABLE EMPLOYEES FLASHBACK ARCHIVE LONGTERM;
Output:
SQL> ALTER TABLE EMPLOYEES FLASHBACK ARCHIVE LONGTERM;
Table altered.
SQL>
Step 4) Check all the tables in our current schema
select * from tab;
Step 5) Capture contextual info as well
exec dbms_flashback_archive.set_context_level('ALL');
Output:
SQL> exec dbms_flashback_archive.set_context_level('ALL');
PL/SQL procedure successfully completed.
SQL>
Step 6) Check now the DML info on the table
select EMPLOYEE_ID, FIRST_NAME, VACATION_BALANCE,
VERSIONS_STARTTIME TS,
nvl(VERSIONS_OPERATION,'I') OP
-- dbms_flashback_archive.get_sys_context(versions_xid, 'USERENV','SESSION_USER') AS who,
-- dbms_flashback_archive.get_sys_context(versions_xid, 'USERENV','MODULE') AS program
from EMPLOYEES
versions between timestamp timestamp '2025-07-22 01:03:00' and systimestamp
-- where EMPLOYEE_ID = 100
order by EMPLOYEE_ID, ts;
after initial loading:
SQL> select EMPLOYEE_ID, FIRST_NAME, VACATION_BALANCE,
VERSIONS_STARTTIME TS,
nvl(VERSIONS_OPERATION,'I') OP
-- dbms_flashback_archive.get_sys_context(versions_xid, 'USERENV','SESSION_USER') AS who,
-- dbms_flashback_archive.get_sys_context(versions_xid, 'USERENV','MODULE') AS program
from EMPLOYEES
versions between timestamp timestamp '2025-07-22 01:03:00' and systimestamp
-- where EMPLOYEE_ID = 100
order by EMPLOYEE_ID, ts; 2 3 4 5 6 7 8 9
EMPLOYEE_ID FIRST_NAME VACATION_BALANCE TS O
----------- -------------------- ---------------- --------------------------------------------------------------------------- -
101 Jack 20.5 22-JUL-25 01.07.17 AM U
101 John 20.5 I
102 Jane 25 I
103 Peter 18.75 I
104 Alice 30 I
105 Robert 15 I
106 Maria 22 I
107 David 28 I
108 Sarah 26.5 I
109 Michael 19 I
110 Emily 21 I
11 rows selected.
SQL>
upd:
update employees set FIRST_NAME='Jack' where EMPLOYEE_ID=101;
Output:
SQL> update EMPLOYEES set FIRST_NAME='Jack' where EMPLOYEE_ID=101;
1 row updated.
SQL> commit;
Commit complete.
SQL>
SQL> select EMPLOYEE_ID, FIRST_NAME, VACATION_BALANCE,
VERSIONS_STARTTIME TS,
nvl(VERSIONS_OPERATION,'I') OP
-- dbms_flashback_archive.get_sys_context(versions_xid, 'USERENV','SESSION_USER') AS who,
-- dbms_flashback_archive.get_sys_context(versions_xid, 'USERENV','MODULE') AS program
from EMPLOYEES
versions between timestamp timestamp '2025-07-22 01:03:00' and systimestamp
-- where EMPLOYEE_ID = 100
order by EMPLOYEE_ID, ts; 2 3 4 5 6 7 8 9
EMPLOYEE_ID FIRST_NAME VACATION_BALANCE TS O
----------- -------------------- ---------------- --------------------------------------------------------------------------- -
101 Jack 20.5 22-JUL-25 01.07.17 AM U
101 John 20.5 I
102 Jane 25 I
103 Peter 18.75 I
104 Alice 30 I
105 Robert 15 I
106 Maria 22 I
107 David 28 I
108 Sarah 26.5 I
109 Michael 19 I
110 Emily 21 I
11 rows selected.
Now update 1 more record
select EMPLOYEE_ID, FIRST_NAME, VACATION_BALANCE,
VERSIONS_STARTTIME TS,
nvl(VERSIONS_OPERATION,'I') OP,
dbms_flashback_archive.get_sys_context(versions_xid, 'USERENV','SESSION_USER') AS who,
dbms_flashback_archive.get_sys_context(versions_xid, 'USERENV','MODULE') AS program
from EMPLOYEES
versions between timestamp timestamp '2025-07-22 01:06:00' and systimestamp
-- where EMPLOYEE_ID = 100
where VERSIONS_STARTTIME is not null
order by EMPLOYEE_ID, ts;
update employees set FIRST_NAME='Rose' where EMPLOYEE_ID=102;
Output:
01:10:13 SQL> update employees set FIRST_NAME='Rose' where EMPLOYEE_ID=102;
1 row updated.
Elapsed: 00:00:00.01
01:10:24 SQL> select EMPLOYEE_ID, FIRST_NAME, VACATION_BALANCE,
VERSIONS_STARTTIME TS,
nvl(VERSIONS_OPERATION,'I') OP
-- dbms_flashback_archive.get_sys_context(versions_xid, 'USERENV','SESSION_USER') AS who,
-- dbms_flashback_archive.get_sys_context(versions_xid, 'USERENV','MODULE') AS program
from EMPLOYEES
versions between timestamp timestamp '2025-07-22 01:06:00' and systimestamp
-- where EMPLOYEE_ID = 100
where VERSIONS_STARTTIME is not null
order by EMPLOYEE_ID, ts;01:10:29 2 01:10:29 3 01:10:29 4 01:10:29 5 01:10:29 6 01:10:29 7 01:10:29 8 01:10:29 9 01:10:29 10
EMPLOYEE_ID FIRST_NAME VACATION_BALANCE TS O
----------- -------------------- ---------------- --------------------------------------------------------------------------- -
101 Jack 20.5 22-JUL-25 01.07.17 AM U
Elapsed: 00:00:00.01
01:10:31 SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
01:10:34 SQL>
01:10:36 SQL> select EMPLOYEE_ID, FIRST_NAME, VACATION_BALANCE,
VERSIONS_STARTTIME TS,
nvl(VERSIONS_OPERATION,'I') OP
-- dbms_flashback_archive.get_sys_context(versions_xid, 'USERENV','SESSION_USER') AS who,
01:10:41 2 01:10:41 3 01:10:41 4 01:10:41 5 -- dbms_flashback_archive.get_sys_context(versions_xid, 'USERENV','MODULE') AS program
from EMPLOYEES
versions between timestamp timestamp '2025-07-22 01:06:00' and systimestamp
-- where EMPLOYEE_ID = 100
where VERSIONS_STARTTIME is not null
order by EMPLOYEE_ID, ts;01:10:41 6 01:10:41 7 01:10:41 8 01:10:41 9 01:10:41 10
EMPLOYEE_ID FIRST_NAME VACATION_BALANCE TS O
----------- -------------------- ---------------- --------------------------------------------------------------------------- -
101 Jack 20.5 22-JUL-25 01.07.17 AM U
102 Rose 25 22-JUL-25 01.10.30 AM U
Elapsed: 00:00:00.01
01:10:42 SQL>
With context:
01:11:09 SQL> select EMPLOYEE_ID, FIRST_NAME, VACATION_BALANCE,
VERSIONS_STARTTIME TS,
nvl(VERSIONS_OPERATION,'I') OP,
dbms_flashback_archive.get_sys_context(versions_xid, 'USERENV','SESSION_USER') AS who,
dbms_flashback_archive.get_sys_context(versions_xid, 'USERENV','MODULE') AS program
from EMPLOYEES
versions between timestamp timestamp '2025-07-22 01:06:00' and systimestamp
-- where EMPLOYEE_ID = 100
where VERSIONS_STARTTIME is not null
order by EMPLOYEE_ID, ts;01:11:18 2 01:11:18 3 01:11:18 4 01:11:18 5 01:11:18 6 01:11:18 7 01:11:18 8 01:11:18 9 01:11:18 10
EMPLOYEE_ID FIRST_NAME VACATION_BALANCE TS O
----------- -------------------- ---------------- --------------------------------------------------------------------------- -
WHO
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PROGRAM
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
101 Jack 20.5 22-JUL-25 01.07.17 AM U
SYS
sqlplus@vcentos79-oracle-ggtgt (TNS V1-V3)
102 Rose 25 22-JUL-25 01.10.30 AM U
SYS
sqlplus@vcentos79-oracle-ggtgt (TNS V1-V3)
Elapsed: 00:00:00.02
01:11:19 SQL>
>> All set
Objective: I have a table description available, but I need the table DDL for the same.
I can manually write it, ideally it will take 10 to 15mins. But using AI tools, we should be able to generate this no time.
Example:
Table1: EMPLOYEES
SQL> desc EMPLOYEES
Name Null? Type
————————————————————————————— ———————— ————————————
EMPLOYEE_ID NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
VACATION_BALANCE NUMBER(6,2)
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
VACATION_BALANCE NUMBER(6,2)
DDL:
CREATE TABLE EMPLOYEES (
EMPLOYEE_ID NUMBER(6),
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(25) NOT NULL,
EMAIL VARCHAR2(25) NOT NULL,
PHONE_NUMBER VARCHAR2(20),
HIRE_DATE DATE NOT NULL,
JOB_ID VARCHAR2(10) NOT NULL,
SALARY NUMBER(8,2),
MANAGER_ID NUMBER(6),
DEPARTMENT_ID NUMBER(4),
VACATION_BALANCE NUMBER(6,2)
);
Table2: AUDIT_EMPLOYEES
SQL> desc AUDIT_EMPLOYEES
Name Null? Type
————————————————————————————— ———————— ————————————
AUD_WHO VARCHAR2(20)
AUD_WHEN DATE
AUD_OPERATION VARCHAR2(1)
AUD_MODULE VARCHAR2(30)
EMPLOYEE_ID NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
VACATION_BALANCE NUMBER(6,2)
Name Null? Type
----------------------------------------- -------- ----------------------------
AUD_WHO VARCHAR2(20)
AUD_WHEN DATE
AUD_OPERATION VARCHAR2(1)
AUD_MODULE VARCHAR2(30)
EMPLOYEE_ID NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
VACATION_BALANCE NUMBER(6,2)
Output:
CREATE TABLE AUDIT_EMPLOYEES (
AUD_WHO VARCHAR2(20),
AUD_WHEN DATE,
AUD_OPERATION VARCHAR2(1),
AUD_MODULE VARCHAR2(30),
EMPLOYEE_ID NUMBER(6),
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(25) NOT NULL,
EMAIL VARCHAR2(25) NOT NULL,
PHONE_NUMBER VARCHAR2(20),
HIRE_DATE DATE NOT NULL,
JOB_ID VARCHAR2(10) NOT NULL,
SALARY NUMBER(8,2),
MANAGER_ID NUMBER(6),
DEPARTMENT_ID NUMBER(4),
VACATION_BALANCE NUMBER(6,2)
);
>> Result: Success. We generated the DDL from table description using Gemini.
