確認方法：
データ更新、テーブル追加、データ追加しながらダンプを取得する
取得したダンプからリストアし、ダンプの一貫性を確認する
(12cR1)
--準備
create directory ORA_DIR as '/home/oracle';
grant all on directory ORA_DIR to public;
drop user test cascade;
create user test identified by test;
grant dba to test;
conn test/test
drop table tab2;
create table tab2(col1 date);
insert into tab2 values(sysdate);
commit;
alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
select * from tab2;
--DB更新
declare
vSQL varchar2(100);
vtabname varchar2(100);
vnow varchar2(100);
i integer;
cursor cur1 is select table_name
from user_tables
where table_name like 'T\_%' escape '\'
order by to_number(replace(substr(table_name,3),'_',''));
begin
i := 0;
while true loop
i := i+1;
--データ更新
update tab2 set col1 = sysdate;
commit;
--テーブル追加
select to_char(sysdate,'YYYYMMDDHH24MISS') into vnow from dual;
vtabname := 'T_' || vnow ||'_' || to_char(i);
vSQL := 'create table ' || vtabname || ' (col1 int)';
execute immediate vSQL;
--データ追加
for c1 in cur1 loop
vSQL := 'insert into ' || c1.table_name || ' values(1)';
execute immediate vSQL;
commit;
end loop;
end loop;
end;
/
--ダンプ取得
date;expdp test/test directory=ORA_DIR dumpfile=a.dmp logfile=a.exp.log schemas=test REUSE_DUMPFILES=yes LOGTIME=all ;date
--ダンプリストア
drop user test cascade;
create user test identified by test;
grant dba to test;
impdp test/test directory=ORA_DIR dumpfile=a.dmp logfile=a.imp.log schemas=test
--確認
select 'select ''' || table_name ||''', count(*) from ' || table_name || ';'
from user_tables
where table_name like 'T\_%' escape '\'
order by to_number(replace(substr(table_name,3),'_',''));
→
一貫性なし
データがエクスポートされるテーブルは「オブジェクト型SCHEMA_EXPORT/TABLE/TABLE_DATAの処理中です」の時点
定義がエクスポートされるテーブルは「オブジェクト型SCHEMA_EXPORT/TABLE/TABLEの処理中です」の時点
エクスポートされるデータは「エクスポートされました」の時点
(5.6)
--準備
drop database test;
create database test;
use test;
drop table tab2;
create table tab2(col1 timestamp);
insert into tab2 values(CURRENT_TIMESTAMP);
select * from tab2;
--DB更新
drop procedure proc1;
delimiter //
create procedure proc1()
begin
declare vSQL varchar(100);
declare vtabname varchar(100);
declare vnow varchar(100);
declare i int;
set i = 0;
while true do
set i = i + 1;
-- データ更新
update tab2 set col1 = CURRENT_TIMESTAMP;
-- テーブル追加
select DATE_FORMAT(CURRENT_TIMESTAMP(3),'%Y%m%d%H%i%s%f') into vnow;
select concat('t_',vnow,'_',i) into vtabname;
select concat('create table ',vtabname,' (col1 int)') into vSQL;
set @q := vSQL;
prepare stmt from @q;
execute stmt;
deallocate prepare stmt;
-- データ追加
begin
declare vdone int DEFAULT 0;
DECLARE cur1 CURSOR FOR
select table_name
from information_schema.tables
where table_name like 't\_%'
order by cast(replace(substring(table_name,3,20),'_','') as decimal(30));
DECLARE continue handler FOR sqlstate '02000' SET vdone = 1;
open cur1;
fetch cur1 into vtabname;
while vdone != 1 do
select concat('insert into ',vtabname,' values(1)') into vSQL;
set @q := vSQL;
prepare stmt from @q;
execute stmt;
deallocate prepare stmt;
fetch cur1 into vtabname;
end while;
close cur1;
end;
end while;
end
//
delimiter ;
call proc1();
--ダンプ取得
date;mysqldump -u root test -p'Aaa!1234' --quick --master-data=2 --flush-logs --single-transaction --triggers --routines --events > a.dmp;date
--ダンプリストア
drop database test;
create database test;
mysql test < a.dmp
--確認
grep -C 3 tab2 a.dmp
grep CREATE a.dmp
select concat('select ''',table_name,''', count(*) from ',table_name,';')
from information_schema.tables
where table_name like 't\_%'
order by cast(replace(substring(table_name,3,20),'_','') as decimal(30))
;
→
更新時刻とテーブル作成時刻が一致しており、データ件数が想定どおりのため、一貫性はあると思われる
(9.4)
--準備
drop database test;
create database test;
\c test
drop table tab2;
create table tab2(col1 timestamp);
insert into tab2 values(clock_timestamp());
commit;
select * from tab2;
--DB更新
drop function fun1(int);
create or replace function fun1(int)
returns void as
$$
DECLARE
vSQL varchar(100);
vtabname varchar(100);
vnow varchar(100);
i integer;
BEGIN
i := $1;
-- データ更新
update tab2 set col1 = clock_timestamp();
-- テーブル追加
select to_char(clock_timestamp(),'YYYYMMDDHH24MISSMS') into vnow;
vtabname := 'T_' || vnow ||'_' || i;
vSQL := 'create table ' || vtabname || ' (col1 int)';
execute vSQL;
END
$$ language 'plpgsql';
drop function fun2();
create or replace function fun2(int)
returns void as
$$
DECLARE
vSQL varchar(100);
r record;
voffset int;
BEGIN
voffset := $1 - 1;
-- データ追加
for r in execute 'select table_name
from information_schema.tables
where table_name like ''t\_%''
order by to_number(replace(substr(table_name,3),''_'',''''),''999999999999999999999999999'')
limit 1 offset ' || voffset
loop
vSQL := 'insert into ' || r.table_name || ' values(1)';
execute vSQL;
end loop;
END
$$ language 'plpgsql';
drop function fun3();
create or replace function fun3()
returns integer as
$$
DECLARE
vcount int;
BEGIN
vcount := 0;
select count(*) into vcount
from information_schema.tables
where table_name like 't\_%';
return vcount;
END
$$ language 'plpgsql';
----------
i=1
while true;do
SQL="psql -c 'select fun1("${i}")' test"
eval ${SQL}
#対象テーブル件数を取得
count=`psql -q -t -c 'select fun3()' test `
for k in `seq ${count}`
do
SQL="psql -c 'select fun2("${k}")' test"
eval ${SQL}
done
i=`expr $i + 1 `
done
----------
--ダンプ取得
date;pg_dump -Fp test > a.dmp ;date
--ダンプリストア
drop database test;
create database test;
psql test < a.dmp
--確認
grep -C 10 tab2 a.dmp
grep CREATE a.dmp
select concat('select ''',table_name,''', count(*) from ',table_name,';')
from information_schema.tables
where table_name like 't\_%'
order by to_number(replace(substr(table_name,3),'_',''),'999999999999999999999999999')
;
→
更新時刻とテーブル作成時刻が一致しており、データ件数が想定どおりのため、一貫性はあると思われる
(2014)
--準備
use master;
drop database test;
create database test;
use test;
drop table tab2;
create table tab2(col1 datetime2);
insert into tab2 values(GETDATE());
select * from tab2;
--DB更新
declare @vSQL varchar(100);
declare @vtabname varchar(100);
declare @vnow varchar(100);
declare @i integer;
SET @i = 1;
WHILE 1=1
BEGIN
-- データ更新
update tab2 set col1 = getdate();
-- テーブル追加
set @vnow = (select convert(nvarchar,GETDATE(),112) + replace(convert(nvarchar,GETDATE(),114),':',''));
set @vtabname = 't_' + @vnow + '_' + cast( @i as varchar);
set @vSQL = 'create table ' + @vtabname + ' (col1 int)';
execute(@vSQL);
-- データ追加
declare cur1 cursor for
select name from sys.tables where name like 't\_%' escape '\'
order by convert(bigint,substring(name,3,17)),convert(bigint,substring(name,21,6))
open cur1;
fetch next from cur1 into @vtabname;
while @@fetch_status = 0
begin
set @vSQL = 'insert into ' + @vtabname + ' values(1)';
execute(@vSQL);
fetch next from cur1 into @vtabname;
end
close cur1;
deallocate cur1;
SET @i = @i + 1;
END
→
データベースレベルで一括取得するダンプツールはない模様のため、検証不可
