Dynamic query to find all the table DML activity in all the table oracle

Question

Hi I need to search the DML activity of specified list of tables in a schema

single query

select max(ora_rowscn),SCN_TO_TIMESTAMP(max(ora_rowscn)) FROM 'TABLE_NAME'

since it is time consuming and hard to run individual queries ,so i am trying to prepare a dynamic sql to fetch max(ora_rowscn),SCN_TO_TIMESTAMP(max(ora_rowscn)) from all the tables so that i can use a filter and select a set of tables

Query Template

select 'with tmp(table_name, row_number) as (' from dual 
union all 
select 'select '''||table_name||''',count(*) from '||table_name||' union  ' from USER_TABLES 
union all
select 'select '''',0 from dual) select table_name,row_number from tmp order by row_number desc ;' from dual;

how do i used the max(ora_rowscn),SCN_TO_TIMESTAMP(max(ora_rowscn)) for all the tables

any suggestions to correct the syntax of the query ?

Answer 1

You can use such a PLSQL code containing EXECUTE IMMEDIATE in order to get the desired values through Dynamic SQL

SET SERVEROUTPUT ON

DECLARE        
  v_rowscn NUMBER;
  v_tmstp  TIMESTAMP;
BEGIN
 FOR c IN 
 (SELECT t.table_name FROM user_tables t)
 LOOP
  BEGIN 
    EXECUTE IMMEDIATE 'SELECT max(ora_rowscn),SCN_TO_TIMESTAMP(max(ora_rowscn)) FROM '||
                               c.table_name INTO v_rowscn, v_tmstp;
    DBMS_OUTPUT.PUT_LINE( c.table_name||' - max_scn : '|| v_rowscn||
                         ' - max_scn_timestamp : '|| v_tmstp );
   EXCEPTION WHEN others THEN  DBMS_OUTPUT.PUT_LINE( sqlerrm );
  END;
 END LOOP;
END;
/ 

as long as any exception does not occur for each individual table.