Loop to get count(*) from all tables

[thekashyap]

Hi,

I want to get the count(*) from all tables under a schema. How to can do this using a loop?

I'm a noob at PL/SQL.

I'm unable to find how to use the loop variable in the select statement.

Here is what I've tried so far:

spool $DD/get_oid_dump.log;
SET SERVEROUTPUT ON
DECLARE
	dummy sys.dbms_debug_vc2coll; -- results (unused)
	errm VARCHAR2(10000);
	my_sql VARCHAR2(1000);
	my_count VARCHAR(100);
BEGIN
	FOR t IN (SELECT  t.table_name, t.owner FROM  all_tables t where owner = 'PM_CMP_MODEL')
	LOOP
		my_sql := 'select count(*) from ' || t.owner || '.' || t.table_name || ';' ;
		dbms_output.put_line ('executing: ' || my_sql);
		DBMS_DEBUG.EXECUTE (my_sql, -1, 0, dummy, errm);
		dbms_output.put_line (errm);
	END LOOP;
EXCEPTION
	WHEN OTHERS THEN
	dbms_output.put_line('ERROR!! -- '  || SQLCODE || '--' || sqlerrm || '--' || errm || '--');
END;
/

Output is:

[root]# sqlplus -S system/manager @$DD/get_oid_dump.sql
executing: select count(*) from PM_CMP_MODEL.FOLDER_REPORT_LINK
ERROR!! -- 1--User-Defined Exception----

PL/SQL procedure successfully completed.

[root@helsinki kash]#

If I change line # 11 thru 13 with " select count(*) from || t.owner || '.' || t.table_name; " I get following error:

select count(*) from || t.owner || '.' || t.table_name;
                                     *
ERROR at line 9:
ORA-06550: line 9, column 24:
PL/SQL: ORA-00903: invalid table name
ORA-06550: line 9, column 3:
PL/SQL: SQL Statement ignored

Should I be using some other function than DBMS_DEBUG.EXECUTE()? I checked the list of functions and I could only find this one to be fitting my usecase.

Thanks.

[debasisdas]

Try this.

SELECT TABLE_NAME,NUM_ROWS FROM USER_TABLES

[thekashyap]

Try this.

SELECT TABLE_NAME,NUM_ROWS FROM USER_TABLES

Thanks. But doesn't seem to work.
1. My tables are inside a user defined schema called PM_CMP_MODEL (see line #9 in my code). select table_name from user_tables; doesn't list my tables from PM_CMP_MODEL schema.
2. Although in this the code I posted I'm trying to only get count(*), I would later want to get a dump of the actual contents. Each table in this schema contains a column named OID. I want to take a dump of it.

[debasisdas]

then go for this

SELECT TABLE_NAME,NUM_ROWS  FROM ALL_TABLES
WHERE OWNER = 'PM_CMP_MODEL'

[debasisdas]

For the 2nd part of your question, you need to loop through all the tables in the desired schema and run the count() query using EXECUTE IMMDEIATE.

What exactly you mean by take dump of data ?

[thekashyap]

For the 2nd part of your question, you need to loop through all the tables in the desired schema and run the count() query using EXECUTE IMMDEIATE.

What exactly you mean by take dump of data ?

By take a dump of data I mean execute:
select OID from <table_name>;
for each table and redirect output to a spool file.

EXECUTE IMMDEIATE seems to be the exact answer I was looking for. I modified my code but now I see that it only executes it but prints no output. I see some syntax with "select xxx into var" so I guess that if I want the output of the select stmt I would have to do select into some var of type array. Is that so? or is there a simpler way?

spool $DD/oid_dump.log;
SET SERVEROUTPUT ON;
SET PAGESIZE 50000;
set numf 9999999999999999999999;

DECLARE
	my_sql VARCHAR(1000);
BEGIN
	FOR t IN (SELECT  t.table_name, t.owner FROM  all_tables t where owner = 'PM_CMP_MODEL' and table_name not like '%LINK%' )
	LOOP
		my_sql := 'select oid from ' || t.owner || '.' || t.table_name ;
		dbms_output.put_line('executing: ' || my_sql);
		EXECUTE IMMEDIATE my_sql;
	END LOOP;
EXCEPTION
	WHEN OTHERS THEN
	dbms_output.put_line('ERROR!! -- '  || SQLCODE || '-- ' || sqlerrm || ' --' );
END;
/

First part: Seems to be working with ALL_TABLES. Always used it to find the tables but never looked at all the columns in that table. :)

[debasisdas]

yes you have to use SELECT INTO....

[thekashyap]

Got it.. had to struggle real hard to find the correct placement of BULK COLLECT but it worked.
Thanks.

#!/bin/bash

DD=`dirname $0`

rm -f $DD/get_oid_dump.sql /tmp/tmp_oid_file

cat << EO_SQL >> $DD/get_oid_dump.sql
spool $DD/oid_dump.log;
SET SERVEROUTPUT ON;
SET PAGESIZE 50000;
set numf 9999999999999999999999;
-- create TYPE my_type IS table of NUMBER(38);
CREATE OR REPLACE DIRECTORY MYDIR AS '/tmp';

DECLARE
	my_sql VARCHAR(5000);
	TYPE my_type IS table of NUMBER(38);
	oids my_type;
	outFile UTL_FILE.FILE_TYPE;
BEGIN
	outFile := UTL_FILE.FOPEN('MYDIR', 'tmp_oid_file', 'w');
	FOR t IN (SELECT  t.table_name, t.owner FROM  all_tables t where owner = 'PM_CMP_MODEL' and table_name not like '%LINK%')
	LOOP
		my_sql := 'select oid from ' || t.owner || '.' || t.table_name;
		-- dbms_output.put_line('executing: ' || my_sql);
		EXECUTE IMMEDIATE  my_sql BULK COLLECT INTO oids;
		
		dbms_output.put_line('No. of OIDs ' || TO_CHAR(oids.count, '9999999') || ' in table: ' || t.table_name );
		IF oids.count > 0 THEN
			FOR i in oids.FIRST..oids.LAST LOOP
				UTL_FILE.PUTF(outFile, TO_CHAR(oids(i), '9999999999999999999999') || '\n');
			END LOOP;
		END IF;
		
	END LOOP;
	UTL_FILE.FCLOSE(outFile);
EXCEPTION
	WHEN OTHERS THEN
	dbms_output.put_line('ERROR!! -- '  || SQLCODE || '-- ' || sqlerrm || ' --' );
END;
/

exit
EO_SQL

sqlplus -S system/manager @$DD/get_oid_dump.sql

mv /tmp/tmp_oid_file ./oid_dump.log

sed -i 's/^[ ]*//' ./oid_dump.log 

echo -e "\nOutput in: oid_dump.log."