General
cancel
Showing results for 
Search instead for 
Did you mean: 

PL/Sql block or Oracle Stored proc for row counts

Pankaj Mandalia
Occasional Contributor

PL/Sql block or Oracle Stored proc for row counts

Hi :
I need to get a row count in specific tables using the % (like operator) of some tables that change records frequently. Using the generation of stats and taking num_rows from user_tables requires generating stats frequently and still does not serve my purpose. Does any one have a PL/SQL block of code or stored proc that I can use to get my row counts?
Your help is much appreciated.
Best regards.
-Pankaj.
5 REPLIES
Patti Johnson
Respected Contributor

Re: PL/Sql block or Oracle Stored proc for row counts

If you don't want to use the num_rows feature of stats then you will need to do a count on each table that you are interested in. I've used the attached script to generate a script that I can run to show the table_name and the row count. Just change the value for the "table_name like '%'" to match your table names.
Run this script - then run the script created by the spool statement.

Pankaj Mandalia
Occasional Contributor

Re: PL/Sql block or Oracle Stored proc for row counts

Hi Pattie:
I have created several "scripts to create scripts" for my end use in the past and I was wanting to use the PL/SQL for this.
Attached is the code I found that can be used/modified to get the row counts of any table(s)
-Pankaj
Patti Johnson
Respected Contributor

Re: PL/Sql block or Oracle Stored proc for row counts

Thanks for sharing your solution.

Patti
Yogeeraj_1
Honored Contributor

Re: PL/Sql block or Oracle Stored proc for row counts

hi pankaj,

i would still prefer to use DBMS_STATS to gather the statistics. I do that regularly using a scheduled database procedure (DBMS_JOB).

exec dbms_stats.gather_schema_stats( user, options =>
'gather stale');

kind regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Jean-Luc Oudart
Honored Contributor

Re: PL/Sql block or Oracle Stored proc for row counts

Hi,

you can find attached a script we use to count records for tables for a specific schema owner.

Amend schema owner and cursor definition to filter the list of tables yuo want to work with.

Regards
Jean-Luc
fiat lux