000030276 - How to truncate the DESKTOPS table in the RSA Adaptive Authentication Core Database

Document created by RSA Customer Support Employee on Jun 14, 2016Last modified by RSA Customer Support Employee on Apr 21, 2017
Version 2Show Document
  • View in full screen mode

Article Content

Article Number000030276
Applies ToRSA Product Set: Adaptive Authentication (OnPrem)
RSA Product/Service Type: Core Database
RSA Version/Condition: 7.1
Platform: Oracle or MS SQL Database
Issue

"We are seeing rapid growth of the DESKTOPS table size in our Pre-Production environment. DB maintenance jobs are not able to keep up with the growth rate.  Please suggest if we can truncate the Desktops table in Pre-Production to improve service performance and limit the table space growth. "  Customer was creating approximately 5 million new Desktop records each day.  Nightly offline cleanup tasks were unable to keep up with table growth rate.  Customer had approximately 50 million records.
If there is a need to truncate the DESKTOPS table in a QA or Development environment, you will need to follow the steps outlined below.   In order to truncate this table you will need to temporarily disable the constraints which "bind" this table to other tables in the database.  If you do not, you will probably see the following error:


SQL> truncate table RSA_CORE_AA.DESKTOPS ; 
truncate table RSA_CORE_AA.DESKTOPS 

ERROR at line 1: 
ORA-02266: unique/primary keys in table referenced by enabled foreign keys 

 

The constraints that need to be disabled are found in RSA_CORE_AA tables which have foreign keys associated with the Desktops table.  These would be the USERCHANGEHISTORY and RSASESSION tables.  The RSATRANSACTION has a foreign key tied to the RSASESSION table, so it is included in the script as well.  
TasksPlease Note:  These SQL commands should work in both Oracle or MS SQL databases, however, the syntax may vary.  The constraints listed below represent AA Core database table property defaults, Customer environment may have been modified.
Please find below the SQL scripts which contain two different options.  The first is to truncate only the Desktops table.

alter table RSA_CORE_AA.USERCHANGEHISTORY disable constraint FK_USRCNHIST_DEVID_DKTP_PMID;
alter table RSA_CORE_AA.RSATRANSACTION disable constraint FK_TRANS_SESS_ID;
alter table RSA_CORE_AA.RSASESSION disable constraint FK_SESS_DEV_ID;
alter table RSA_CORE_AA.BINDINGS disable constraint FK_BNDS_PMID_DKTP_PMID;
truncate table RSA_CORE_AA.DESKTOPS reuse storage;
alter table RSA_CORE_AA.USERCHANGEHISTORY enable constraint FK_USRCNHIST_DEVID_DKTP_PMID;
alter table RSA_CORE_AA.BINDINGS enable constraint FK_BNDS_PMID_DKTP_PMID;
alter table RSA_CORE_AA.RSATRANSACTION enable constraint FK_TRANS_SESS_ID;
alter table RSA_CORE_AA.RSASESSION enable constraint FK_SESS_DEV_ID;

Alternatively, you can use the second SQL script to truncate the RSASESSION, RSATRANSACTION, SESSIONACSP tables along with DESKTOPS table.  Because this script needs to be run when the AA application is shutdown anyway, i.e., there is no AA traffic being sent to the database, you can truncate these additional tables at the same time as Desktops.
 
alter table RSA_CORE_AA.USERCHANGEHISTORY disable constraint FK_USRCNHIST_DEVID_DKTP_PMID;
alter table RSA_CORE_AA.RSATRANSACTION disable constraint FK_TRANS_SESS_ID;
alter table RSA_CORE_AA.RSASESSION disable constraint FK_SESS_DEV_ID;
alter table RSA_CORE_AA.BINDINGS disable constraint FK_BNDS_PMID_DKTP_PMID;
truncate table RSA_CORE_AA.RSASESSION reuse storage;
truncate table RSA_CORE_AA.RSATRANSACTION reuse storage;
truncate table RSA_CORE_AA.SESSIONACSP reuse storage;
truncate table RSA_CORE_AA.DESKTOPS reuse storage;
alter table RSA_CORE_AA.USERCHANGEHISTORY enable constraint FK_USRCNHIST_DEVID_DKTP_PMID;
alter table RSA_CORE_AA.BINDINGS enable constraint FK_BNDS_PMID_DKTP_PMID;
alter table RSA_CORE_AA.RSATRANSACTION enable constraint FK_TRANS_SESS_ID;
alter table RSA_CORE_AA.RSASESSION enable constraint FK_SESS_DEV_ID;
ResolutionIf you encounter any errors on the queries to disable and enable constraints, e.g., FK_TRANS_SESS_ID  on table  RSATRANSACTION, these can be ignored.  
SQL> alter table RSA_CORE_AA.RSATRANSACTION disable constraint FK_TRANS_SESS_ID; 
alter table RSA_CORE_AA.RSATRANSACTION disable constraint FK_TRANS_SESS_ID 

ERROR at line 1: 
ORA-02431: cannot disable constraint (FK_TRANS_SESS_ID) - no such constraint

It is possible that the properties of the RSATRANSACTION table have been modified and that constraint was removed.  You can just skip those steps in future.
 

Attachments

    Outcomes