[adsense id=”0514458240″ width=”468″ height=”60″]
When managing many schemas in the one database, we do from time to time need to get running totals from a particular table name in each of the schema. There are many ways of achieving this from writing a script with each SELECT statement against each schema and table. However, if one is forgotten then your results may be misleading.
Another approach is to read the schema and table, output this and generate a script based on the results. Again this is a 2 phased approach to read each of the schema owners and tables and then write a separate script that generates your SQL statement.
My preferred way of doing this is to use a CURSOR and to have PL/SQL handle the entire process. In the code below we fetch row by row from the cursor the schema owner and table name and we dynamically generate a SELECT COUNT statement. The result is stored in the l_count variable which can then be added to the l_total variable to keep a running total from each table.
The only thing that needs to be done is to make sure that the schema owner running this piece of code has SELECT access on the schema_owner and table_name.
SET SERVEROUTPUT ON
DECLARE
CURSOR c_tables IS SELECT owner, table_name FROM all_tables WHERE table_name = 'TESTVALUES';
l_rec c_tables%ROWTYPE;
l_total INTEGER := 0;
l_count INTEGER;
BEGIN
OPEN c_tables;
FETCH c_tables INTO l_rec;
WHILE c_tables%FOUND LOOP
EXECUTE IMMEDIATE 'SELECT count(1) FROM '||l_rec.owner||'.'||l_rec.table_name INTO l_count;
l_total := l_total + l_count;
DBMS_OUTPUT.PUT_LINE('Schema: ' || l_rec.owner || ' - ' || l_count);
FETCH c_tables INTO l_rec;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Grand total: ' || l_total);
END;
/
Putting this in action we get the following:
shell> sqlplus username/password @cursor.sql SQL*Plus: Release 10.2.0.1.0 - Production on Thu Sep 27 17:53:05 2012 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production Schema: TEST2 - 0 Schema: TEST1 - 0 Grand total: 0