Select all tables where data was created in the last 10 minutes

Question

In Oracle, I can get data created in the last 10 minutes with

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

However, I need to find out all tables where data was created in the last 10 minutes.

Answer 1

Okay so as per my understanding you want list of all the tables where data was added/created in the last 10 minutes.

Here's my approach :

1. First get a list of all the tables which contain this column"created_Dt" first.

2. Loop over all these tables and check if data was added in the past 10 minutes.

& then Display number of rows added to each one of these tables.

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

One way would be to enable flashback and then you can do:

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

You can query the data dictionary to find a list of all your tables and build a dynamic query for each and use EXECUTE IMMEDIATE to loop through all the tables.

If you don't have flashback enabled then you will need to have a created_dt column (or equivalent) on every table.

Answer 3

If your tables have MONITORING, one approach is to just use the data dictionary to see table modifications. Provided MONITORING is on, the below approach should let you see what tables have received INSERTs (or UPDATEs or DELETEs, if desired) over a given time period.

The below approach has the disadvantage that one must actually wait 10 minutes before checking what has been created, but it doesn't require tables to have any particular column.

Update: as per Ikrom's comment, the original answer was flawed by checking the data dictionary only once. This answer has been updated to check twice to detect changes.

This approach will record baseline table modification counts, then check back for any changes 10 minutes later. It uses two work-tables one for reference ALL_TAB_MODIFICATIONS_REF and one to view the modified tables after 10 min 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.

After creating these work tables, one can detect any changes by taking initial state and comparing to final state after 10 minutes. After running the below block, one can query ALL_TAB_MODIFICATIONS_COMPARE to see what tables have been INSERTed.

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;
/

After running the block, check for tables with INSERTs:

SELECT TABLE_NAME FROM ALL_TAB_MODIFICATIONS_COMPARE;

Here's an example to detect changes to one table over 10 mins, but to exclude the other since it has been 10+ minutes since its last INSERT:

Create test tables:

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.

And add some initial test-data in two tables.

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.

Then run the monitoring block above in one session above to detect changes after 10 minutes.

While the block is running in the first session, add more data in a second session to only RECENT_DML.

INSERT INTO RECENT_DML (THE_DATA) VALUES (1);
COMMIT;

1 row inserted.
Commit complete.

After the block has completed in the first session, query for the modified tables:

SELECT TABLE_NAME FROM ALL_TAB_MODIFICATIONS_COMPARE;

TABLE_NAME                    
RECENT_DML