sql - 在没有 "lost update"的情况下正确归档数据

Question

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

这是我想做的:

INSERT INTO
SELECT FOR UPDATE

DELETE SELETED rows.

但是 INSERT INTO ... SELECT... 不支持语法 FOR UPDATE

是否可以在 PL/SQL 上仅使用 SQL 而不使用 Cursor 来解决问题？

---

例子

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;

结果有:

从权限中选择 *

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;

Answer 1

在 Oracle 12c 及更高版本中，您可以使用In-Database Archiving 实现此目的。在表上启用数据库内存档会导致添加系统生成的隐藏列，称为 ORA_ARCHIVE_STATE

它使用“标记为删除”的概念，因此数据保留在表中，但对应用程序不可见。

首先，为您的表启用数据库内存档。

ALTER TABLE yourtable ROW ARCHIVAL;

因此，为表创建了一个名为 ORA_ARCHIVE_STATE 的隐藏列，您可以使用 user_tab_cols

检查它

现在，让您的行对其他应用程序/ session 不可见，

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)