変更追跡

DB

 

フラッシュバックデータアーカイブ(Oracle Total Recall)

select flashback_on from v$database;

alter system set db_recovery_file_dest_size = 10G;
alter system set db_recovery_file_dest='/u01/app/oracle/oradata/orcl';

shutdown immediate
startup mount
alter database flashback on;
alter database open;

CREATE FLASHBACK ARCHIVE DEFAULT fla1 TABLESPACE users
QUOTA 10G RETENTION 1 YEAR;

CREATE TABLE tab4 (col1 int) FLASHBACK ARCHIVE;

SELECT col1 FROM tab4 AS OF TIMESTAMP TO_TIMESTAMP ('2019/03/21 13:20:00', 'YYYY/MM/DD HH24:MI:SS');

 

調べた限り見つからない

tablelogモジュール

http://pgsqldeepdive.blogspot.com/2019/02/tablelog-extension.html
https://kenpg.bitbucket.io/blog/201509/25.html


(9.4)

--PL/v8のインストール

yum list | grep plv8

yum -y install plv8_94

psql -c 'create extension plv8' test


--tablelogモジュールのインストール
unzip tablelog-master.zip
cd tablelog-master

su
env USE_PGXS=1 PATH=/usr/pgsql-9.4/bin:$PATH make install
exit

psql -c 'create extension tablelog' test

--動作検証

create table t (uid integer primary key, uname text);
select tablelog_enable_logging('public', 't');
\d t
insert into t values ( 1, 'name 1');
select * from __table_logs__ ;
update t set uname = 'uname 11';
delete from t;
select * from __table_logs__ ;

 

https://docs.microsoft.com/ja-jp/sql/relational-databases/track-changes/track-data-changes-sql-server?view=sql-server-2017

--1.変更データ キャプチャ
https://docs.microsoft.com/ja-jp/sql/relational-databases/track-changes/enable-and-disable-change-data-capture-sql-server?view=sql-server-2017

--データベースでの変更データ キャプチャの有効化

USE test;
GO
EXECUTE sys.sp_cdc_enable_db;
GO


--テーブルでの変更データ キャプチャの有効化
USE test
GO
create table tab1(col1 int primary key ,col2 int);
go

EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'tab1',
@role_name = NULL,
@supports_net_changes = 1
GO

--動作確認
insert into tab1 values(1,10);
update tab1 set col2 = 20;
insert into tab1 values(2,100);
update tab1 set col2 = 21;

DECLARE @from_lsn binary(10), @to_lsn binary(10);
SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_tab1');
SET @to_lsn = sys.fn_cdc_get_max_lsn();
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_tab1
(@from_lsn, @to_lsn, N'all');
GO

 


--2.変更の追跡
https://docs.microsoft.com/ja-jp/sql/relational-databases/track-changes/track-data-changes-sql-server?view=sql-server-2017


--データベースの変更の追跡を有効にする

ALTER DATABASE test
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)

 

--テーブルの変更の追跡を有効にする

create table tab1(col1 int primary key ,col2 int);
go

ALTER TABLE tab1
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);
go


--動作確認
insert into tab1 values(1,10);
update tab1 set col2 = 20;
insert into tab1 values(2,100);
update tab1 set col2 = 21;

SELECT T1.col1,T1.col2 ,T2.SYS_CHANGE_VERSION, T2.SYS_CHANGE_CONTEXT
FROM tab1 AS T1
CROSS APPLY CHANGETABLE
(VERSION tab1, (col1), (T1.col1)) AS T2;


DECLARE @last_sync_version bigint;
SET @last_sync_version = 2;
SELECT col1,
SYS_CHANGE_VERSION, SYS_CHANGE_OPERATION,
SYS_CHANGE_COLUMNS, SYS_CHANGE_CONTEXT
FROM CHANGETABLE (CHANGES tab1, @last_sync_version) AS C;