Oracle - Monitor Unused Objects

Unused Sequences

SELECT SEQUENCE_NAME, LAST_NUMBER FROM DBA_SEQUENCES WHERE SEQUENCE_OWNER = '[TableSpace Name]';
Run the SQL on a regular interval. Compare the LAST_NUMBER value with the previous run. If it has increased then the sequence is used else it is not.

Unused Idexes

Firstly, enable monitoring on Index using the script 
ALTER INDEX "' || i.owner || '"."' || i.index_name || '" MONITORING USAGE;

Note: Create a script which will run for all the indexes you want

Then, run the below script to see if the Indexes are used or not
SELECT segment_name, segment_type, bytes/1024/1024 meg, bytes/1024/1024/1024 gig
 FROM user_segments seg
 JOIN v$object_usage obj ON obj.index_name = seg.segment_name
WHERE used = 'NO';

Unused Tables

Use the auditing feature of Oracle to monitor the usage

1. Enable to auditing AUDIT INSERT ON [Table Name];

2. Audits are stored in USER_AUDIT_OBJECT table.

3. To turn off auditing use
NOAUDIT ALL ON [Table Name];

Tables With Statistics
Auditing on all the tables could be to find the unused tables is not required. If the stats exists for the table, then it means the table is being used. If the stats is not present or if the stats is too old, then those tables can be the candidates for unused tables. Monitor or Audit only these tables to find the unused tables.

SELECT A.*, B.CREATED
FROM (
    SELECT TABLE_NAME, MAX(LAST_STAT_TIME) LAST_STAT_TIME
     FROM (
        SELECT TABLE_NAME, MAX(TIMESTAMP) LAST_STAT_TIME
         FROM (
            SELECT DISTINCT B.TABLE_NAME
              FROM DBA_HIST_SQL_PLAN A JOIN DBA_INDEXES B ON B.INDEX_NAME = A.OBJECT_NAME
             WHERE OBJECT_OWNER = 'PD2' AND OBJECT_TYPE LIKE 'INDEX%' -- Indexes
            MINUS
             SELECT OBJECT_NAME TABLE_NAME
              FROM DBA_HIST_SQL_PLAN
             WHERE OBJECT_OWNER = 'PD2' AND OBJECT_TYPE LIKE 'TABLE%' --Tables
            )A
           JOIN DBA_HIST_SQL_PLAN B ON A.TABLE_NAME = B.OBJECT_NAME
          GROUP BY TABLE_NAME
        UNION
        SELECT OBJECT_NAME TABLE_NAME, MAX(TIMESTAMP) LAST_STAT_TIME
          FROM DBA_HIST_SQL_PLAN
         WHERE OBJECT_OWNER = 'PD2' AND OBJECT_TYPE LIKE 'TABLE%' --Tables
         GROUP BY OBJECT_NAME
        )
    GROUP BY TABLE_NAME
    )A
JOIN DBA_OBJECTS B ON A.TABLE_NAME = B.OBJECT_NAME AND B.OBJECT_TYPE = 'TABLE'
ORDER BY 1

Unused Procedures

Again, use the auditing feature of Oracle

AUDIT EXECUTE ON [Procedure] BY SESSION;

Auditing

/* *******************************************************************************************
 * Description: This script switch on the Audit for Procedures, Functions, Packages, Tables
 * ******************************************************************************************/
 
BEGIN
    FOR obj IN (SELECT DISTINCT(OBJECT_NAME) OBJECT_NAME, OBJECT_TYPE 
                  FROM DBA_OBJECTS
                 WHERE OWNER = 'PD2'
                   AND OBJECT_TYPE IN ('PACKAGE', 'PROCEDURE', 'FUNCTION', 'TABLE')
                   AND OBJECT_NAME NOT IN ('') -- Mention any objects to be excluded for auditing. For Ex: If we are finding unused object and if a objects is sure that it is used, then no need to audit those objects
               )
    LOOP
        BEGIN
            IF obj.OBJECT_TYPE = 'TABLE' THEN
                EXECUTE IMMEDIATE 'AUDIT INSERT, UPDATE, DELETE ON ' || obj.OBJECT_NAME;
            ELSE
                EXECUTE IMMEDIATE 'AUDIT EXECUTE ON ' || obj.OBJECT_NAME;
            END IF;
            DBMS_OUTPUT.PUT_LINE('Auditing On: ' || obj.OBJECT_NAME);
        EXCEPTION 
            WHEN OTHERS THEN 
                DBMS_OUTPUT.PUT_LINE('Error Auditing: ' || obj.OBJECT_NAME);
        END;
    END LOOP;
END;

/* **************************************************************************************
 * Description: This script switch off the Audit for Procedures, Functions, Packages, 
 *              Tables, Triggers, Views if it is found begin used
 *          Ex: If we are monitoring Unused objects, then use this scrit to disable 
 *              auditing once it is found that it is used
 * *************************************************************************************/
BEGIN
    FOR X IN (SELECT DISTINCT(OBJECT_NAME) OBJECT_NAME FROM DBA_OBJECTS
               WHERE OWNER = 'PD2'
                 AND OBJECT_TYPE IN ('PACKAGE', 'PROCEDURE', 'FUNCTION', 'TABLE')
              INTERSECT
              SELECT OBJ_NAME FROM USER_AUDIT_OBJECT)
    LOOP
        EXECUTE IMMEDIATE 'NOAUDIT ALL ON ' || X.OBJECT_NAME;
        DBMS_OUTPUT.PUT_LINE('Audit Off: ' || X.OBJECT_NAME);
    END LOOP;
END;

/* *******************************************************************************************
 * Description: Script to find unused objects
 * *******************************************************************************************/
SELECT OBJECT_NAME, OBJECT_TYPE 
  FROM DBA_OBJECTS WHERE OWNER = 'PD2'  
   AND OBJECT_TYPE IN ('PACKAGE', 'PROCEDURE', 'FUNCTION', 'TABLE')
   AND OBJECT_NAME NOT IN (SELECT OBJ_NAME FROM USER_AUDIT_OBJECT)