How to run Segment Advisor in versions 7.0.2 or earlier of RSA Identity Governance & Lifecycle

RSA Product Set: RSA Identity Governance & Lifecycle 
RSA Product/Service Type: Appliance and soft appliance
RSA Version/Condition: 6.x, 7.0.x

This article explains how to run segment advisor on RSA Identity Governance & Lifecycle 7.0.2 and earlier for RSA-supplied databases. If you have a non-RSA supplied database, please contact your Oracle DBA for assistance in running segment advisor. 

If you are on RSA Identity Governance & Lifecycle 7.1.0 or above, please see the section entitled Database Segment Maintenance on page 32 of the RSA Identity Governance & Lifecycle Database Setup and Management Guide for V7.1.1 for instructions on running segment advisor. (Note: Even if you are on 7.1.0, use the 7.1.1 guide.)

Please see RSA Knowledge Base Article 000038168 -- How to manage database growth in RSA Identity Governance & Lifecycle for an explanation on when to use segment advisor

NOTE: The segment advisor script file runs as the SYS user. Make sure you have are able to connect to the database as the SYS user before performing these steps.

1. Log into the RSA Identity Governance & Lifecycle Oracle database server. 
2. Download the attached segmentAdvisor.zip to /home/oracle.
3. Create a temporary directory under /home/oracle and move segmentAdvisor.zip to the temporary directory.

cd /home/oracle
mkdir tempSA
mv segmentAdvisor.zip tempSA

4. Go to the temporary directory 
5. Unzip the zip file. Inside is segmentAdvisor.txt and segmentAdvisor.sql
6. Copy segmentAdvisor.txt to segmentAdvisor.sh

cd tempSA
unzip segmentAdvisor.zip
mv segmentAdvisor.txt segmentAdvisor.sh

7. Before running segmentAdvisor.sh, read through the script files (segmentAdvisor.sh and segmentAdvisor.sql) to see what they do. The segmentAdvisor.sh file explains how to run the script for the entire database or one table at a time. It also provides instructions on how to reclaim the freed up space.

8. Run segment advisor.

sh segmentAdvisor.sh

9. Once the script completes, there will be a file called RESULTS.TXT. Use the below command to see the segment advisor results:

grep -i estimate -A1 -B3 RESULTS.TXT

10. Review the results and follow the instructions in the segmentAdvisor.sh file to reclaim unused space.

NOTE: Reclaim space during slow periods when the database is being minimally used to avoid contention.