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.
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.
Log into the RSA Identity Governance & Lifecycle Oracle database server.
Download the attached segmentAdvisor.zip to /home/oracle.
Create a temporary directory under /home/oracle and move segmentAdvisor.zip to the temporary directory.
cd /home/oraclemkdir tempSA
mv segmentAdvisor.zip tempSA
Go to the temporary directory
Unzip the zip file. Inside is segmentAdvisor.txt and segmentAdvisor.sql
Copy segmentAdvisor.txt to segmentAdvisor.sh
mv segmentAdvisor.txt segmentAdvisor.sh
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.
Run segment advisor.
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
This last command will tell you the tables that have the most space to reclaim (if any).
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.