Does Oracle have something like changing data collection in SQL Server 2008?

Question

Change Data Capture is a new feature in SQL Server 2008. From MSDN:

A data capture change provides information about historical changes for a user table, recording both the fact that DML changes were made and the actual data that was changed. changes are captured using an asynchronous process that reads the transaction log and has little effect on the system

This is very nice - it no longer adds the CreateDate and LastModifiedBy columns manually.

Does Oracle have something like this?

Answer 1

Of course. Oracle actually has a number of technologies for this kind of thing, depending on business requirements.

• Oracle (8i ) Oracle - Workspace Manager, . , , .
• 11.1 ( ), Oracle Total Recall, , , . Total Recall .
• Oracle LogMiner, . , LogMiner, .
• , .

Answer 2

Oracle , , , , . , procs .

DBMS_CHANGE_NOTIFICATION.

: http://www.oracle-base.com/articles/10g/dbms_change_notification_10gR2.php

Oracle #, ODP.Net( Oracles.Net) Change Data Notification, #, Oracle - kewl. , - , ODP.Net wala, # . Woot!

Answer 3

" CreateDate LastModifiedBy "... .

. , , .

, , Oracle 10g + flashback. : http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10002.htm#i2112847

Oracle Datapump .

Answer 4

, Oracle 8i, , , , .

Oracle 8i , , 9i Fine Grained Auditing, . 10/11g.

. http://www.oracle.com/technology/deploy/security/database-security/fine-grained-auditing/index.html.

11 Oracle Audit Vault, , DBA ( Oracle, ). http://www.oracle.com/technology/deploy/security/database-security/fine-grained-auditing/index.html.

Answer 5

Oracle , Flashback Data Archive. :

Oracle Flashback Query , .

Flashback data archive provides the ability to track and save all transactional changes in the table during its existence. You no longer need to create this intelligence in your application. The Flashback data archive is useful for complying with milestone policies and audit reports.

CREATE TABLESPACE SPACE_FOR_ARCHIVE 
datafile 'C:\ORACLE DB12\ARCH_SPACE.DBF'size 50G;

CREATE FLASHBACK ARCHIVE longterm
TABLESPACE space_for_archive
RETENTION 1 YEAR;

ALTER TABLE EMPLOYEES FLASHBACK ARCHIVE LONGTERM;


select EMPLOYEE_ID, FIRST_NAME, JOB_ID, VACATION_BALANCE,
        VERSIONS_STARTTIME TS,
        nvl(VERSIONS_OPERATION,'I') OP
from EMPLOYEES
versions between timestamp  timestamp '2016-01-11 08:20:00' and systimestamp
where EMPLOYEE_ID = 100
order by EMPLOYEE_ID, ts;