选择过去 10 分钟内创建数据的所有表

Question

在 Oracle 中，我可以通过以下方式获取过去 10 分钟内创建的数据

SELECT * FROM MY_TABLE WHERE  created_dt > sysdate - interval '10' minute and created_dt < sysdate;

但是，我需要找出过去 10 分钟内创建数据的所有表。

Answer 1

好的，根据我的理解，您想要列出过去 10 分钟内添加/创建数据的所有表。

这是我的方法：

1. 首先获取包含此列"created_Dt"的所有表的列表。

2. 遍历所有这些表，并检查过去 10 分钟内是否添加了数据。

，然后显示添加到每个表中的行数。

SET SERVEROUTPUT ON;
DECLARE
CURSOR ALL_TABLES_NAME IS
SELECT * FROM ALL_TABLES "ALL"
WHERE EXISTS(SELECT 1 FROM all_cons_columns WHERE table_name = "ALL".TABLE_NAME AND COLUMN_NAME=UPPER('created_dt'));
ALL_TABLES_NAME_REC ALL_TABLES_NAME%rowtype;
row_count number;
BEGIN
OPEN ALL_TABLES_NAME;
LOOP
FETCH ALL_TABLES_NAME INTO ALL_TABLES_NAME_REC;
EXIT WHEN ALL_TABLES_NAME%notfound;
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || ALL_TABLES_NAME_REC.TABLE_NAME || ' WHERE created_dt > (SYSDATE-10/24/60)' INTO row_count;
DBMS_OUTPUT.PUT_LINE('Table '|| ALL_TABLES_NAME_REC.table_name || ' has been updated with '|| row_count || ' new rows');

END LOOP;
CLOSE ALL_TABLES_NAME;
END;
/

Answer 2

一种方法是启用闪回，然后您可以执行以下操作：

SELECT * FROM table1
MINUS
SELECT * FROM table1 AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '10' MINUTE;

您可以查询数据字典以查找所有表的列表，并为每个表生成动态查询，并使用EXECUTE IMMEDIATE遍历所有表。

如果未启用闪回，则需要在每个表上都有一个created_dt列(或等效列(。

Answer 3

如果你的表有MONITORING，一种方法是只使用数据字典来查看表的修改。 如果MONITORING处于打开状态，下面的方法应该可以让您查看在给定时间段内哪些表接收了INSERTs(如果需要，UPDATEs 或DELETEs(。

下面的方法有一个缺点，即实际上必须等待 10 分钟才能检查已创建的内容，但它不需要表具有任何特定列。

更新：根据Ikrom的评论，最初的答案是有缺陷的，因为只检查一次数据字典。 此答案已更新为检查两次以检测更改。

此方法将记录基线表修改计数，然后在 10 分钟后回来检查是否有任何更改。 它使用两个工作表，一个用于参考ALL_TAB_MODIFICATIONS_REF，另一个用于在 10 分钟ALL_TAB_MODIFICATIONS_COMPARE后查看修改后的表：

CREATE GLOBAL TEMPORARY TABLE
ALL_TAB_MODIFICATIONS_REF
ON COMMIT PRESERVE ROWS
AS SELECT * FROM ALL_TAB_MODIFICATIONS WHERE ROWNUM < 0;
CREATE GLOBAL TEMPORARY TABLE
ALL_TAB_MODIFICATIONS_COMPARE
ON COMMIT PRESERVE ROWS
AS SELECT * FROM ALL_TAB_MODIFICATIONS WHERE ROWNUM < 0;
Table created.
Table created.

创建这些工作表后，可以通过获取初始状态并在 10 分钟后与最终状态进行比较来检测任何更改。 运行下面的块后，可以查询ALL_TAB_MODIFICATIONS_COMPARE以查看INSERT了哪些表。

BEGIN
--Flush monitoring info, to get a baseline.
DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();
--Capture the initial state.
INSERT INTO ALL_TAB_MODIFICATIONS_REF
SELECT * FROM ALL_TAB_MODIFICATIONS;
COMMIT;
-- Wait ten minutes.
DBMS_LOCK.SLEEP(600);
--Flush monitoring info, to get a snapshot for comparison.
DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();
-- Capture the final state.
INSERT INTO ALL_TAB_MODIFICATIONS_COMPARE
SELECT * FROM ALL_TAB_MODIFICATIONS
WHERE NOT EXISTS(SELECT 1 FROM ALL_TAB_MODIFICATIONS_REF
WHERE ALL_TAB_MODIFICATIONS_REF.TABLE_OWNER = ALL_TAB_MODIFICATIONS.TABLE_OWNER
AND ALL_TAB_MODIFICATIONS_REF.TABLE_NAME = ALL_TAB_MODIFICATIONS.TABLE_NAME
AND ALL_TAB_MODIFICATIONS_REF.INSERTS >= ALL_TAB_MODIFICATIONS.INSERTS);
COMMIT;
END;
/

运行块后，检查具有INSERTs 的表：

SELECT TABLE_NAME FROM ALL_TAB_MODIFICATIONS_COMPARE;

下面是一个示例，用于检测一个表在 10 分钟内的更改，但排除另一个表，因为它自上次INSERT以来已经过去了 10+ 分钟：

创建测试表：

CREATE TABLE RECENT_DML (THE_DATA NUMBER, DML_TIME TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL); 
CREATE TABLE NO_RECENT_DML (THE_DATA NUMBER, DML_TIME TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL);
Table created.
Table created.

并在两个表中添加一些初始测试数据。

INSERT INTO RECENT_DML (THE_DATA) VALUES (1);
INSERT INTO NO_RECENT_DML (THE_DATA) VALUES (1);
COMMIT;
1 row inserted.
1 row inserted.
Commit complete.

然后在上面的一个会话中运行上面的监视块，以在 10 分钟后检测更改。

当块在第一个会话中运行时，在第二个会话中添加更多数据以仅RECENT_DML。

INSERT INTO RECENT_DML (THE_DATA) VALUES (1);
COMMIT;
1 row inserted.
Commit complete.

在第一个会话中完成块后，查询修改后的表：

SELECT TABLE_NAME FROM ALL_TAB_MODIFICATIONS_COMPARE;
TABLE_NAME                    
RECENT_DML