PL/Sql block or Oracle Stored proc for row counts

[Pankaj Mandalia]

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.

[Patti Johnson]

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]

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]

Thanks for sharing your solution.

Patti

[Yogeeraj_1]

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

[Jean-Luc Oudart]

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