000036110 - The number of STX tables in RSA Identity Governance & Lifecycle database is increasing

Document created by RSA Customer Support Employee on Mar 6, 2018
Version 1Show Document
  • View in full screen mode

Article Content

Article Number000036110
Applies ToRSA Product Set: RSA Identity Governance & Lifecycle
RSA Version/Condition: All

 
IssueMore and more STX* tables are showing up in the RSA Identity Governance & Lifecycle database in the AVUSER schema.
CauseThe STX* tables are temporary tables used by collections and of the format STX_{DataType}_{CollectorId}_{RunId}.They are dropped by the collector process as the last step in a collection. If  there is a problem with the collection for any reason and the last step is not completed, these STX tables are not dropped. 
ResolutionThe resolution is to periodically clean up these tables, however STX tables that are being used by active collections should not be dropped. Therefore, the safest way to remove these tables is to shudown acm to ensure no collections are running. Steps to follow are:
  1. Shutdown acm


$ acm stop


  1. Login to SQL as the AVUSER.
  2. Execute the following SQL to find all the tables to be dropped:


SELECT table_name FROM user_catalog WHERE table_name LIKE 'STX%'


  1. Drop all the STX tables:


BEGIN
     FOR tablename IN (SELECT table_name FROM user_catalog WHERE table_name LIKE 'STX%') LOOP
      EXECUTE IMMEDIATE
     'DROP TABLE ' || tablename.table_name || ' purge';
     END LOOP; 
 END;


  1. Restart acm:


$ acm start

Attachments

    Outcomes