Here is a small PL\SQL program to get the number of rows in all the tables accessible from a scheam. It uses dyanmic SQL in the program.
*************************************************************************************
Rem This program get the number of rows in each table that is accessible from a schema.
SET FEEDBACK OFF;
SET TERM OFF
SET ECHO OFF;
SET SERVEROUTPUT ON size 1000000
SPOOL countall.csv
--CREATE OR REPLACE PROCEDURE countall AS
DECLARE
t_c1_tname all_tables.table_name%TYPE;
t_owner_name all_tables.owner%TYPE;
t_command varchar2(200);
t_cid integer;
t_total_records number(10);
stat integer;
row_count integer;
CURSOR c1 IS SELECT owner , table_name
FROM all_tables
WHERE owner = user
ORDER BY table_name;
BEGIN
DBMS_OUTPUT.PUT_LINE('OWNER,TABLE NAME,NUMBER OF ROWS');
FOR r_c1 IN c1
LOOP
t_command := 'SELECT COUNT(0) FROM '||r_c1.owner||'.'||r_c1.table_name;
t_cid := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(t_cid,t_command,dbms_sql.native);
DBMS_SQL.DEFINE_COLUMN(t_cid,1,t_total_records);
stat := DBMS_SQL.EXECUTE(t_cid);
row_count := DBMS_SQL.FETCH_ROWS(t_cid);
DBMS_SQL.COLUMN_VALUE(t_cid,1,t_total_records);
DBMS_OUTPUT.PUT_LINE(r_c1.owner||','||r_c1.table_name||','||t_total_records);
DBMS_SQL.CLOSE_CURSOR(t_cid);
END LOOP;
END;
/
SPOOL OFF;
SET TERM ON;
SET ECHO ON;
*************************************************************************************
No comments:
Post a Comment