pl/sql script to needed count rows. . .

[seagreg]

Company has sprung an April surprize upon me & it's CRUNCH-time & I've no time to invent a wheel here... What I need is a pl/sql script to list table name, owner & rows counted, for all tables in a database.

We're undertaking purging tables of older data & need these counts in order to know which tables to target within 5 seperate databases (about 35,000 total tables)... Can anyone help me with this?

I realize how simple it is to get count(*) from a given table but I need it to go against the ALL_TABLES then preceed to get the counts and report the results back into a flat file & print.

These particular datases are Oracle DBs and we use ANSI std PL/SQL here running in a UNIX environment on HPs & IBM AIX boxes.

Many thanks & a signed autograph picture of my pet cats to anyone who can help!!!

 

[CTucker]

There are DBA views that should have this information - you may have to analyze your tables for this first ... the following query can be run as system to generate a script that will give you the results you are after:

SELECT 'SELECT ''' | | owner | | ''' owner' | | CHR(10) | |
', ''' | | table_name | | ''' table_name' | | CHR(10) | |
', count(*)' | | CHR(10) | |
'FROM ' | | owner | | '.' | | table_name | | chr(10) | |
'UNION ALL'
FROM all_tables

 

[patch]

Is this what you are looking for?

----
SET SERVEROUTPUT ON
SET BUFFER 10000000
DECLARE
CURSOR c_tables
IS
SELECT atab.Owner, atab.table_name
FROM all_tables atab
WHERE ( owner = 'SCOTT'
OR owner = 'PATCH')
ORDER BY atab.owner, atab.table_name;

lv_count NUMBER;
BEGIN
FOR rc_tables IN c_tables LOOP
EXECUTE IMMEDIATE 'select count(*) from '| |rc_tables.owner| |'.'| |rc_tables.table_name INTO lv_count;
dbms_output.put_line(rc_tables.owner | | '.' | | rc_tables.table_name | | ' => '| |to_char(lv_count));
END LOOP;
END;
/
----
Make sure you buffer size is large enough, or run this script for less owners.
Another option would be to not write the results to DBMS_Output, but have them written into a table.

Good Luck,
Patrick

------------------
Check out: http://www.oracledeveloper.nl