The following code denotes both Sql Server and Oracle both to create a temporal Solution. For Oracle the following article is the basis.
---/// Temporal Table SQL Server ///---
CREATE TABLE dbo.Employee(
EMPNO INT
,ENAME VARCHAR(10)
,JOB VARCHAR(9)
,MGR INT
,HIREDATE DATE
,SAL NUMERIC(7,2)
,COMM NUMERIC(7,2)
,DEPTNO INT
CONSTRAINT EMP_PK PRIMARY KEY (EMPNO)
,SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL
,SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL
,PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime))
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory, DATA_CONSISTENCY_CHECK = ON));
INSERT INTO dbo.Employee (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)values
(7369, 'SMITH', 'CLERK', 7902, '02-MAR-1970', 8000, NULL, 20),
(7499, 'ALLEN', 'SALESMAN', 7698, '20-MAR-1971', 1600, 3000, 30),
(7521, 'WARD', 'SALESMAN', 7698, '07-FEB-1983', 1250, 5000, 30),
(7566, 'JONES', 'MANAGER', 7839, '02-JUN-1961', 2975, 50000, 20),
(7654, 'MARTIN', 'SALESMAN', 7698, '28-FEB-1971', 1250, 14000, 30),
(7698, 'BLAKE', 'MANAGER', 7839, '01-JAN-1988', 2850, 12000, 30),
(7782, 'CLARK', 'MANAGER', 7839, '09-APR-1971', 2450, 13000, 10),
(7788, 'SCOTT', 'ANALYST', 7566, '09-DEC-1982', 3000, 1200, 20),
(7839, 'KING', 'PRESIDENT', NULL, '17-JUL-1971', 5000, 1456, 10),
(7844, 'TURNER', 'SALESMAN', 7698, '08-AUG-1971', 1500, 0, 30),
(7876, 'ADAMS', 'CLERK', 7788, '12-MAR-1973', 1100, 0, 20),
(7900, 'JAMES', 'CLERK', 7698, '03-NOV-1971', 950, 0, 30),
(7902, 'FORD', 'ANALYST', 7566, '04-MAR-1961', 3000, 0, 20),
(7934, 'MILLER', 'CLERK', 7782, '21-JAN-1972', 1300, 0, 10)
SELECT * FROM Employee
SELECT * FROM EmployeeHistory --Would return 0 rows.
UPDATE EMPLOYEE
SET SAL=SAL+2000
WHERE EMPNO=7369
SELECT * FROM Employee
SELECT * FROM EmployeeHistory --Would return 1 rows. prior to change.
UPDATE EMPLOYEE
SET SAL=SAL+2000
WHERE EMPNO=7369
SELECT * FROM Employee
SELECT * FROM EmployeeHistory --Would return 2 rows. prior to change.
--if you want to check the list of changes which have happend you could simply query the history table .
SELECT * FROM EmployeeHistory WHERE EMPNO = 7396
union all
SELECT * FROM Employee WHERE EMPNO = 7396
ORDER BY SysStartTime DESC
---/// Temporal Table Oracle ///---
---/// ORACLE (ASSUME AUTOCOMMIT IS ON). ///---
CREATE TABLESPACE STATIC_DATA DATAFILE 'static_data.dbf' SIZE 10m AUTOEXTEND ON MAXSIZE 1g ;
CREATE TABLESPACE STATIC_ARCHIVE DATAFILE 'static_archive.dbf' SIZE 10m AUTOEXTEND ON MAXSIZE 1g ;
CREATE FLASHBACK ARCHIVE STATIC_ARCHVIE_FB TABLESPACE STATIC_ARCHIVE QUOTA 1 G RETENTION 7 YEAR; --7 YEARS RETENTION.
CREATE TABLE TEST.Employee(
EMPNO INT
,ENAME VARCHAR(10)
,JOB VARCHAR(9)
,MGR INT
,HIREDATE DATE
,SAL NUMERIC(7,2)
,COMM NUMERIC(7,2)
,DEPTNO INT
CONSTRAINT EMP_PK PRIMARY KEY (EMPNO)
)
TABLESPACE STATIC_DATA;
ALTER TABLE TEST.Employee ADD PERIOD FOR History;
ALTER TABLE TEST.Employee FLASHBACK ARCHIVE STATIC_ARCHVIE_FB;
INSERT INTO TEST.Employee (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)values
(7369, 'SMITH', 'CLERK', 7902, '02-MAR-1970', 8000, NULL, 20),
(7499, 'ALLEN', 'SALESMAN', 7698, '20-MAR-1971', 1600, 3000, 30),
(7521, 'WARD', 'SALESMAN', 7698, '07-FEB-1983', 1250, 5000, 30),
(7566, 'JONES', 'MANAGER', 7839, '02-JUN-1961', 2975, 50000, 20),
(7654, 'MARTIN', 'SALESMAN', 7698, '28-FEB-1971', 1250, 14000, 30),
(7698, 'BLAKE', 'MANAGER', 7839, '01-JAN-1988', 2850, 12000, 30),
(7782, 'CLARK', 'MANAGER', 7839, '09-APR-1971', 2450, 13000, 10),
(7788, 'SCOTT', 'ANALYST', 7566, '09-DEC-1982', 3000, 1200, 20),
(7839, 'KING', 'PRESIDENT', NULL, '17-JUL-1971', 5000, 1456, 10),
(7844, 'TURNER', 'SALESMAN', 7698, '08-AUG-1971', 1500, 0, 30),
(7876, 'ADAMS', 'CLERK', 7788, '12-MAR-1973', 1100, 0, 20),
(7900, 'JAMES', 'CLERK', 7698, '03-NOV-1971', 950, 0, 30),
(7902, 'FORD', 'ANALYST', 7566, '04-MAR-1961', 3000, 0, 20),
(7934, 'MILLER', 'CLERK', 7782, '21-JAN-1972', 1300, 0, 10)
SELECT * FROM TEST.Employee
-- ??? any Equivalent in Oracle for this -> SELECT * FROM EmployeeHistory .
UPDATE TEST.Employee
SET SAL=SAL+2000
WHERE EMPNO=7369
UPDATE TEST.Employee
SET SAL=SAL+1000
WHERE EMPNO != 7369
SELECT * FROM TEST.Employee --//Would return the current dataset.
SELECT employee_id FROM TEST.Employee AS OF PERIOD FOR history 'Date_to_be_replaced_with_Scn_in_your_database'
--// The above would retun the data before update all records.
Question 1: I can see the changes only at a point in time. If the record is updated multiple times in a day how do i get the changes to show the history?
Question 2: Is flashback not a temporary table and may get purged when certain events occur in db?
I tried querying between specific date ranges but the query is only point in time and would not give me all the history of the records like sql server.
I will if this does not work need to write trigger on each static table and insert it to my own history table. Trying to avoid triggers.
If you're looking at a complete history, then flashback data archive is probably the solution you want to be exploring, combined with the VERSIONS BETWEEN clause. For example, for a (single) row in the DEPT table you could write
SQL> SELECT deptno, dname,
2 VERSIONS_STARTTIME
3 ,VERSIONS_XID
4 ,VERSIONS_OPERATION
5 FROM dept
6 VERSIONS BETWEEN TIMESTAMP
7 SYSTIMESTAMP - INTERVAL '20:00' MINUTE TO SECOND
8 AND SYSTIMESTAMP
9 WHERE deptno = 10;
DEPTNO DNAME VERSIONS_STARTTIME VERSIONS_XID V
--------- -------------- ---------------------- ---------------- -
10 UNKNOWN 06-MAY-20 11.53.45 PM 0200100060040000 U
10 MONEY GRABBERS 06-MAY-20 11.53.36 PM 0600050065040000 U
10 FINANCE 06-MAY-20 11.53.24 PM 09000D001D050000 U
10 BEAN COUNTERS 06-MAY-20 11.53.12 PM 01001A00EA030000 U
10 ACCOUNTING
Here's a video explaining the "behind the scenes" on FDA
and if you do end up going down the trigger route, I've got an implementation that lets you auto-generate them for tables here