Is there any changelog column maintained in Oracle DB like in DS LDAP there is a changelog column maintained?

If not how can we maintain a column performing this task? It is required to be able to detect changes on DB when new records are created or existing records are updated. If there are any links which explain how this can be don please share. I was not able to get any useful links for this.

CodePudding user response：

No, unless you have:

• enabled auditing on a table.

  AUDIT insert, update, delete ON table_name BY ACCESS
  

• manually created a method of logging (usually with a separate logging table and a user-defined trigger to capture the changes).

An example of manually logging changes, if you have the table:

CREATE TABLE table_name (
  id    NUMBER
        GENERATED ALWAYS AS IDENTITY
        PRIMARY KEY,
  value VARCHAR2(20)
);

then you can create a logging table:

CREATE TABLE table_name__log (
  old_id NUMBER,
  new_id NUMBER,
  value  VARCHAR2(20),
  usr    VARCHAR2(30),
  datetime DATE
);

and a trigger to push the changes:

CREATE TRIGGER table_name__log__trg
  AFTER INSERT OR UPDATE OR DELETE ON table_name
  FOR EACH ROW
BEGIN
  INSERT INTO table_name__log (old_id, new_id, value, usr, datetime)
  VALUES(:OLD.id, :NEW.id, :NEW.value, USER, SYSDATE);
END;
/

Then if you do:

INSERT INTO table_name (value) VALUES ('abc');

UPDATE table_name
SET value = 'def'
WHERE id = 1;

DELETE FROM table_name WHERE id = 1;

Then the logging table will contain:

OLD_ID	NEW_ID	VALUE	USR	DATETIME
null	1	abc	FIDDLE_BFRZBVBGKHCOAEIXUMRW	2022-05-20 14:43:30
1	1	def	FIDDLE_BFRZBVBGKHCOAEIXUMRW	2022-05-20 14:43:30
1	null	null	FIDDLE_BFRZBVBGKHCOAEIXUMRW	2022-05-20 14:43:30

db<>fiddle here

If you want a single column with the changes then you just need to change the structure of the logging table and the adjust the trigger to whatever format (text, XML, JSON, etc.) you want to push the result into the log.