代码之家 › 专栏 › 技术社区 › Z. Anton

正确归档数据而不“丢失更新”

oracle sql

Z. Anton · 技术社区 · 7 年前

如何在不“丢失更新”的情况下正确归档数据?

我要做的是:

INSERT INTO
SELECT FOR UPDATE

DELETE SELETED rows.

FOR UPDATE 中不支持 INSERT INTO ... SELECT...

在PL/SQL上只使用SQL而不使用游标可以解决这个问题吗?

create table authority(id number, key varchar2(128));
create table authority_arch(id number, key varchar2(128));

insert into authority(1, 'random_key1');
insert into authority(1, 'random_key2');
insert into authority(1, 'random_key3');
insert into authority(2, 'random_key4');
insert into authority(2, 'random_key5');

commit;

1次会议

insert into authority_arch
select * from authority where id=2;

-- in this moment 2 session make insert! 'Lose rows' in next delete

delete from authority where id=2;

2会议

insert into authority(2, 'random_key6', sysdate+1);
commit;

select * from authority

id  |  key
-----------
1   |   random_key1
1   |   random_key2
1   |   random_key3

但我只想删除选定的行

id  |  key
-----------
1   |   random_key1
1   |   random_key2
1   |   random_key3
2   |   random_key6

作为解决方案,我使用:

for rec in (select rowid as rid, a.* from authority a where id=2 FOR UPDATE nowait) loop
    insert into authority_arch values(rec.id, rec.key);
    delete from authority where rowid=rec.rid;
end loop;

3 回复 | 直到 7 年前

Kaushik Nayak 7 年前

在Oracle12c和更高版本中,可以使用 In-Database Archiving . 在表上启用数据库内存档会导致添加一个系统生成的隐藏列,该列名为 ORA_ARCHIVE_STATE

它使用了一个概念 “标记为删除”,因此数据仍然存在于表中,但对应用程序不可见。

ALTER TABLE yourtable ROW ARCHIVAL;

一个隐藏的列叫做 奥拉丘档案馆 为可以使用进行检查的表创建 user_tab_cols

UPDATE yourtable
SET    ORA_ARCHIVE_STATE = '1'
WHERE  id  BETWEEN 1 AND 10000;
COMMIT;

现在,您可以随时删除这些行。

参考 In-Database Archiving in Oracle Database 12c Release 1 (12.1)

ewramner 7 年前

你应该能够做一些类似的事情:

create table temp_keys as (select pk from yourtable where condition);
select pk from yourtable where pk in (select pk from temp_keys) for update;
insert into archivetable (columnlist)
    select columnlist from yourtable
        where pk in (select pk from temp_keys);
commit;
drop table temp_keys;

你也可以使用临时表来存放临时表,这样就不必每次都删除它。

编辑:使用添加的新信息,您可以跳过选择以进行更新。只需跟踪您为下面的删除复制的id。用id创建一个(可能是临时的)表,执行insert和delete操作,就完成了。

Chris Saxon 7 年前

这个 for update 条款在这里帮不了你。这只会锁定您查询的行,阻止其他人更新/删除它们。任何添加的新行都不会被锁定!所以delete将始终处理新行。

插入和删除在特定时间点存在的行
保存要存档的所有行的列表。然后使用这个列表而不是表本身来插入+删除。

declare
  insert_time pls_integer;
begin
  insert_time := dbms_flashback.get_system_change_number;
  insert into authority_arch
    select * from authority as of scn insert_time
    where  id = 2;

  dbms_lock.sleep(10); -- wait to allow insert in session 2

  delete authority
  where  ( id, key ) in (
    select id, key from authority as of scn insert_time
    where  id = 2 
  );
end;
/

第二种方法可以使用临时表。或者可以使用大容量收集将它们提取到数组中。所有插入+删除:

declare
  type auth_rec is table of authority%rowtype index by binary_integer;
  arch_recs auth_rec ;
begin

  select * 
  bulk collect into arch_recs
  from   authority
  where  id = 2;

  forall i in arch_recs.first .. arch_recs.last
    insert into authority_arch values arch_recs(i);

  dbms_lock.sleep(10); -- wait to allow insert in session 2

  forall i in arch_recs.first .. arch_recs.last
    delete authority
    where  id = arch_recs(i).id
    and    key = arch_recs(i).key;

end;
/