Get Count of All PeopleSoft Tables with one SQL

by Prashant on September 16, 2015

in Database, PeopleSoft Administration, PeopleSoft Maintenance

While performing upgrades or applying maintenance to your PeopleSoft applications, you might want to get row count of all your PeopleSoft tables. There are few ways of doing it which we will discuss below and finally the one which I think is the easiest and most efficient way.

Option 1: Running UPGCOUNT AE

UPGCOUNT is a delivered Application Engine program that can be run via application designer or command line. This program will output a list with count of all PeopleSoft tables. You can read more about UPGCOUNT here.

Option 2: Get Count from DBA_TABLES (in ORACLE)

DBA_TABLES contains the row count of all the tables in a Oracle database. You can run below SQL to get the row count for PeopleSoft tables where Access ID or Owner ID is SYSADM. If PeopleSoft Owner ID is different in your case, please change the value of OWNER accordingly.

SELECT TABLE_NAME, NUM_ROWS FROM DBA_TABLES WHERE OWNER='SYSADM';

Above SQL will give you the row count of all tables in SYSADM schema. BUT this count may not always be right. Values in this table are populated based on whether statistics have been updated or not. Even if you perform update stats, still the count could be different than actual count. (try running count(*) on few tables and compare).

Option 3 (BEST OPTION): SQL using both DBA_TABLES and COUNT(*) to give you correct output.

We have found this one to be the best option. This SQL will get just the table names from DBA_TABLES and then run the count(*) on all the PeopleSoft tables. This is your best bet to get the actual row count.

Also Read:  How to Install Oracle 32 bit Client on Windows 64 bit and avoid the error INS-30131

select table_name, to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from '||table_name)),'/ROWSET/ROW/C')) count from DBA_tables where owner='SYSADM';

Please let us know which option worked best for you or if you have any other efficient way to get the count of PeopleSoft tables.

{ 4 comments… read them below or add one }

Sathyavathi December 3, 2014 at 10:26 am

The tip was very useful. Thanks PRASHANT

Reply

Prashant December 3, 2014 at 4:29 pm

Sathyavathi
I’m glad it was helpful. Thanks.

Reply

Ramesh Kummara December 15, 2014 at 6:57 am

Thanks for the Useful tip sir 🙂

Reply

stephane February 3, 2016 at 3:41 am

great tip !
very useful for me (in my case, no output for UPGCOUNT AE)

Reply

Cancel reply

Leave a Comment

Previous post:

Next post: