Archive for January, 2012

count all updated tables in a database, oracle version

January 30, 2012

OUTPUT – this produces a script that you can execute that produces text to the output window with each table and a count of recent updates and additions.


set serveroutput on format WRAPPED;

--CREATE GLOBAL TEMPORARY TABLE my_temp_table ( TableName varchar(50), CountRecentUpdates INTEGER) ON COMMIT DELETE ROWS;

declare
t_name varchar(150);
c_name varchar(150);
query_base varchar(250);
squery varchar(500);
TempTableInsert varchar(80) := ' insert into my_temp_table ( TableName , CountRecentUpdates ) ' ;

cursor ci is
select table_name , column_name from all_tab_columns
where rownum SYSDATE-2 ;';

query_base := 'select ''QQQTABLE'' , count(*) as CountRecentUpdates into st, k from QQQTABLE where QQQCOLUMN > SYSDATE-1 ; DBMS_OUTPUT.put_line (st || '' '' || TO_CHAR(k) );';

open ci;

LOOP
fetch ci into t_name , c_name;

exit when ci%NOTFOUND;

-- http://psoug.org/reference/translate_replace.html
squery := replace(query_base , 'QQQTABLE' , t_name );
squery := replace(squery , 'QQQCOLUMN' , c_name );
-- query to write to temp table
-- squery := TempTableInsert || squery ;

-- print the query
DBMS_OUTPUT.put_line ( squery );
-- execute the query
-- EXEC SQL EXECUTE IMMEDIATE :squery ;

end loop;
close ci;

DBMS_OUTPUT.put_line ( ' END; ' );

END;