RSA Identity Governance and Lifecycle - Purge monitor tasks
Originally Published: 2017-06-05
Article Number
Applies To
RSA Product/Service Type: Appliance
RSA Version/Condition: 6.9.1
Product Name: Identity Governance and Lifecycle
Issue
This data is stored in the database table T_DATA_RUN_TASKS. As this table fills up and it takes Oracle longer to return the rows, the time taken to load the data into the screen above also increases.
.
Tasks
Resolution
The data pump utility requires that a directory object be defined in Oracle which it will use to write the file. If a data pump directory object does not already exist it may be created as follows:
acm-691:~ # su - oracle
oracle@acm-691:~> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu May 11 05:56:39 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
With the Automatic Storage Management option
SQL> create directory dpump_dir1 as '/u01/app/oracle/AVDB/dpdump';
Directory created.
We need to grant read and write permission on the directory object to AVUSER.
SQL> grant read,write on directory dpump_dir1 to avuser;
Grant succeeded.
The table data may now be exported with the data pump utility as follows:
oracle@acm-691:~> expdp avuser/<password> DIRECTORY=dpump_dir1 dumpfile=t_data_run_tasks.dmp TABLES=avuser.t_data_run_tasks
Export: Release 11.2.0.3.0 - Production on Thu May 11 07:00:14 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
With the Automatic Storage Management option
Starting "AVUSER"."SYS_EXPORT_TABLE_01": avuser/******** DIRECTORY=dpump_dir1 dumpfile=t_data_run_tasks.dmp TABLES=avuser.t_data_run_tasks
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "AVUSER"."T_DATA_RUN_TASKS" 12.56 KB 9 rows
Master table "AVUSER"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for AVUSER.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/AVDB/dpdump/t_data_run_tasks.dmp
Job "AVUSER"."SYS_EXPORT_TABLE_01" successfully completed at 07:00:44
When the export has completed without errors the data may be purged. The following example purges all data older than 30 days:
SQL> delete from avuser.t_data_run_tasks
where trunc(start_time) < sysdate - 30;
9 rows deleted.
SQL> commit;
Commit complete.
Note: To purge all of the data the truncate command could be used in place of delete, it will be faster where a large data set is involved. [Deleted data can be rolled back with the rollback command before it is committed if a mistake is made. Truncated data can only be recovered from the export backup].The System -> Monitoring screen should now be empty.
If the exported data needs to be restored it can be done as follows:
oracle@acm-691:~> impdp avuser/<password> DIRECTORY=dpump_dir1 dumpfile=t_data_run_tasks.dmp TABLES=avuser.t_data_run_tasks TABLE_EXISTS_ACTION=REPLACE
Import: Release 11.2.0.3.0 - Production on Thu May 11 07:13:33 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
With the Automatic Storage Management option
Master table "AVUSER"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "AVUSER"."SYS_IMPORT_TABLE_01": avuser/******** DIRECTORY=dpump_dir1 dumpfile=t_data_run_tasks.dmp TABLES=avuser.t_data_run_tasks TABLE_EXISTS_ACTION=REPLACE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "AVUSER"."T_DATA_RUN_TASKS" 12.56 KB 9 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Job "AVUSER"."SYS_IMPORT_TABLE_01" successfully completed at 07:13:41
