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 ?
Advertisement
Answer
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.