Efficient Tips for Finding Unused Oracle Segments!!

Efficient Tips for Finding Unused Oracle Segments!!
Oracle provides a number of options for finding unused or read only Oracle segments. AWR, Auditing, Statspack reports or Oracle views can be used for fining unused oracle segments. However you must be careful about the retention of data you use for the purpose of analysis. Whatever option you are using but it must be ensured that the last instance startup time is enough behind for the analysis need.
If you analyze say 20 days data for finding unused/read-only Oracle segments where you have set the retention to default 7 days then you will result in wrong analysis. Therefore it is very important to control the retention of your Statspack or AWR reports. Usually the default 7 days (1440 min) retention seems to be to short in many cases and therefore it is advised to set the retention to 30 days (43200 min).
Retention:
You can check current AWR retention by running below command.
SELECT RETENTION FROM DBA_HIST_WR_CONTROL;
Similarly you can run below command to set new retention value.
EXECUTE DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(INTERVAL => 60, RETENTION => 43200);
Oracle 10g Automatic Workload Repository:
Oracle DBA_HIST_SQL_PLAN displays SQL execution plans whereas DBA_HIST_SQLSTAT displays historical information about SQL statistics.
SELECT .. FROM DBA_HIST_SQL_PLAN P, DBA_HIST_SQLSTAT S WHERE P.SQL_ID = S.SQL_ID;
Oracle DBA_HIST_SEG_STAT displays historical information about segment-level statistics. Whereas the DBA_HIST_SEG_STAT_OBJ displays all the names of the segments captured in the workload repository. Oracle DBA_HIST_SEG_STAT_OBJ view is used with the DBA_HIST_SEG_STAT view.
SELECT .. FROM DBA_HIST_SEG_STAT SS, DBA_HIST_SEG_STAT_OBJ SO
WHERE SS.OBJ# = SO.OBJ# AND SS.DATAOBJ# = SO.DATAOBJ# AND SS.DBID = SO.DBID;
Standard Oracle Auditing:
AUDIT_TRAIL=DB
AUDIT SELECT ON BY ACCESS;
SELECT .. FROM SYS.AUD$ WHERE .. ;
Fine-grained Oracle Auditing:
Oracle fine-grained auditing helps us solve the problem of capturing read access. This type of auditing is managed by DBMS_FGA package. A PL /SQL procedure is executed whenever a match is made with the predicate.
BEGIN
DBMS_FGA.ADD_POLICY (OBJECT_SCHEMA=>'..', OBJECT_NAME=>'..', POLICY_NAME=>'..');
COMMIT;
END;
/
SELECT .. FROM DBA_FGA_AUDIT_TRAIL SA, DBA_OBJECTS SO
WHERE SA.OBJECT_NAME = SO.OBJECT_NAME AND SA.OBJECT_SCHEMA = SO.OWNER;
DBA_TAB_MODIFICATIONS:
Oracle DBA_TAB_MODIFICATIONS describes modifications to all tables in the database that have been modified since the last time statistics were gathered on the tables. Since the information in DBA_TAB_MODIFICATIONS is reset after statistics gathering therefore you can use it to capture the modification amounts for your segments from the last statistics gathering time.
SELECT .. FROM DBA_TAB_MODIFICATIONS WHERE .. ;
Oracle Statspack:
SELECT ..
COUNT(DECODE(SS.COMMAND_TYPE, 2, 1, 6, 1, 7, 1, 189, 1)) SQL_CHANGE,
COUNT(DECODE(SS.COMMAND_TYPE, 3, 1)) SQL_READ
FROM PERFSTAT.STATS$SQLTEXT SS WHERE SS.SQL_TEXT LIKE ‘% ‘||T.TABLE_NAME||' %';
You can also use STATS$SEG_STAT and STATS$SEG_STAT_OBJ in Statspack PERFSTAT .
SELECT .. FROM PERFSTAT.STATS$SEG_STAT SS, PERFSTAT.STATS$SEG_STAT_OBJ SO
WHERE SS.OBJ# = SO.OBJ# AND SS.DATAOBJ# = SO.DATAOBJ# AND SS.DBID = SO.DBID;
Oracle V$ACCESS view:
Oracle V$ACCESS view can be used to display objects in the database that are currently locked and the sessions that are accessing them.
SELECT .. FROM V$ACCESS WHERE .. ;