How to determine real-time row counts of each table in the RSA Via Lifecycle & Governance AVUSER schema

[#1]

Article Number

000033256

Applies To

RSA Product Set: RSA Via Lifecycle & Governance (RSA Via L&G)
 

Issue

There may be times when you want to know the number of rows in each table of the RSA Via L&G AVUSER schema. There are a number of methods to do this:

1. The Statistics Report (ASR) under System > Diagnostics.
2. Querying dba_tables as in:

$ sqlplus / avuser
SQL> SELECT table_name, num_rows FROM dba_tables WHERE owner='AVUSER' AND num_rows IS NOT NULL;

However, neither of these methods are real time. The row counts are based on the last time database statistics (db stats) ran. Furthermore, db stats does not count the rows in every single table. This article addresses a third method that allows you to create a SQL script file that calculates and reports the current row counts for every table in the AVUSER schema. You may also modify the file to include only those tables for which you want row counts.

Task

1. Create a SQL command file that will count the number of rows in each table in the AVUSER schema. Please note you may modify these steps to include any schema of your choosing.

$ sqlplus / as sysdba
SQL> SPOOL <filename>.sql
SQL> SET linesize 300;
SQL> COLUMN ||TABLE_NAME|| FORMAT a200;
SQL> COLUMN COUNT(*) FORMAT a10;
SQL> SELECT 'SELECT '''||table_name||''',COUNT(*) FROM '||table_name||';' FROM dba_tables WHERE owner='AVUSER';
SQL> SPOOL OFF;
SQL> EXIT

 

2. Modify the SQL command file created in step 1 so that it runs without error.  Using an editor of your choosing:
   1. Replace all occurrences of 'SELECT'''||TABLE_NAME||''',COUNT(*)FROM'||TABLE_NAME||';' with --, which is the SQL comment line command.  Below is a code snippet from such a file before making the change:
   2. Remove the top and bottom lines prefaced with 'SQL>'. Also remove 'no. of rows selected' from the bottom of the file.

'SELECT'''||TABLE_NAME||''',COUNT(*)FROM'||TABLE_NAME||';'                      
--------------------------------------------------------------------------------
SELECT 'T_AV_ROLE_TYPES',COUNT(*) FROM T_AV_ROLE_TYPES;                         
SELECT 'T_AV_VIOLATIONS',COUNT(*) FROM T_AV_VIOLATIONS;                         
SELECT 'T_AV_ROLEVER_VIOLATIONS',COUNT(*) FROM  T_AV_ROLEVER_VIOLATIONS;         
SELECT 'T_AV_USER_RULE_VIOLATIONS',COUNT(*) FROM  T_AV_USER_RULE_VIOLATIONS;     
SELECT 'T_AV_EXEMPTIONS',COUNT(*) FROM  T_AV_EXEMPTIONS;                
...

3. After making the change:

--                      
--------------------------------------------------------------------------------
SELECT 'T_AV_ROLE_TYPES',COUNT(*) FROM T_AV_ROLE_TYPES;                         
SELECT 'T_AV_VIOLATIONS',COUNT(*) FROM T_AV_VIOLATIONS;                         
SELECT 'T_AV_ROLEVER_VIOLATIONS',COUNT(*) FROM T_AV_ROLEVER_VIOLATIONS;         
SELECT 'T_AV_USER_RULE_VIOLATIONS',COUNT(*) FROM T_AV_USER_RULE_VIOLATIONS;     
SELECT 'T_AV_EXEMPTIONS',COUNT(*) FROM T_AV_EXEMPTIONS;                
...

3. Save the file.
4. Execute the file:

$ sqlplus avuser/secret
SQL> @<filename>