SET SERVEROUTPUT ON
DECLARE
    PROCEDURE print_all (s VARCHAR2)
    IS
    BEGIN
        NULL;
        DBMS_OUTPUT.put_line (s);
    END;

    PROCEDURE print_ddl (s VARCHAR2)
    IS
    BEGIN
        NULL;
        DBMS_OUTPUT.put_line (s);
    END;
BEGIN
    DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform,
                                       'SEGMENT_ATTRIBUTES',
                                       FALSE);

    FOR a
        IN (  SELECT COUNT (*)                                       samples,
                     event,
                     p1,
                     p2,
                     o.owner                                         c_owner,
                     o.object_name                                   c_object_name,
                     p.object_owner                                  p_owner,
                     p.object_name                                   p_object_name,
                     id,
                     operation,
                     MIN (p1 - 1414332420 + 4)                       lock_mode,
                     MIN (sample_time)                               min_time,
                     MAX (sample_time)                               max_time,
                     CEIL (10 * COUNT (DISTINCT sample_id) / 60)     minutes
                FROM dba_hist_active_sess_history
                     LEFT OUTER JOIN dba_hist_sql_plan p USING (dbid, sql_id)
                     LEFT OUTER JOIN dba_objects o ON object_id = p2
                     LEFT OUTER JOIN dba_objects po
                         ON po.object_id = current_obj#
               WHERE     event LIKE 'enq: TM%'
                     AND p1 >= 1414332420
                     AND sample_time > SYSDATE - 15
                     AND p.id = 1
                     AND operation IN ('DELETE', 'UPDATE', 'MERGE')
            GROUP BY event,
                     p1,
                     p2,
                     o.owner,
                     o.object_name,
                     p.object_owner,
                     p.object_name,
                     po.owner,
                     po.object_name,
                     id,
                     operation
            ORDER BY COUNT (*) DESC)
    LOOP
        print_ddl (
               '--  '
            || a.operation
            || ' on '
            || a.p_owner
            || '.'
            || a.p_object_name
            || ' has locked '
            || a.c_owner
            || '.'
            || a.c_object_name
            || ' in mode '
            || a.lock_mode
            || ' for '
            || a.minutes
            || ' minutes between '
            || TO_CHAR (a.min_time, 'dd-mon hh24:mi')
            || ' and '
            || TO_CHAR (a.max_time, 'dd-mon hh24:mi'));

        FOR s
            IN (SELECT DISTINCT
                       REGEXP_REPLACE (
                           CAST (SUBSTR (sql_text, 1, 2000) AS VARCHAR2 (60)),
                           '[^a-zA-Z ]',
                           ' ')    sql_text
                  FROM dba_hist_active_sess_history
                       JOIN dba_hist_sqltext t USING (dbid, sql_id)
                 WHERE     event LIKE 'enq: TM%'
                       AND p2 = a.p2
                       AND sample_time > SYSDATE - 15)
        LOOP
            print_all ('--      ' || 'blocked statement: ' || s.sql_text);
        END LOOP;

        FOR c
            IN (WITH
                    c
                    AS
                        (SELECT p.owner          p_owner,
                                p.table_name     p_table_name,
                                c.owner          c_owner,
                                c.table_name     c_table_name,
                                c.delete_rule,
                                c.constraint_name
                           FROM dba_constraints  p
                                JOIN dba_constraints c
                                    ON (    c.r_owner = p.owner
                                        AND c.r_constraint_name =
                                            p.constraint_name)
                          WHERE     p.constraint_type IN ('P', 'U')
                                AND c.constraint_type = 'R')
                    SELECT c_owner                     owner,
                           constraint_name,
                           c_table_name,
                              CONNECT_BY_ROOT (p_owner || '.' || p_table_name)
                           || SYS_CONNECT_BY_PATH (
                                     DECODE (delete_rule,
                                             'CASCADE', '(cascade delete)',
                                             'SET NULL', '(cascade set null)',
                                             ' ')
                                  || ' '
                                  || c_owner
                                  || '"."'
                                  || c_table_name,
                                  ' referenced by')    foreign_keys
                      FROM c
                     WHERE     LEVEL <= 10
                           AND c_owner = a.c_owner
                           AND c_table_name = a.c_object_name
                CONNECT BY NOCYCLE     p_owner = PRIOR c_owner
                                   AND p_table_name = PRIOR c_table_name
                                   AND (   LEVEL = 1
                                        OR PRIOR delete_rule IN
                                               ('CASCADE', 'SET NULL'))
                START WITH     p_owner = a.p_owner
                           AND p_table_name = a.p_object_name)
        LOOP
            print_all (
                   '--      '
                || 'FK chain: '
                || c.foreign_keys
                || ' ('
                || c.owner
                || '.'
                || c.constraint_name
                || ')'
                || ' unindexed');

            FOR l
                IN (SELECT *
                      FROM dba_cons_columns
                     WHERE     owner = c.owner
                           AND constraint_name = c.constraint_name)
            LOOP
                print_all ('--         FK column ' || l.column_name);
            END LOOP;

            print_ddl (
                   '--      Suggested index: '
                || REGEXP_REPLACE (
                       TRANSLATE (
                           DBMS_METADATA.get_ddl ('REF_CONSTRAINT',
                                                  c.constraint_name,
                                                  c.owner),
                           CHR (10) || CHR (13),
                           '  '),
                       'ALTER TABLE ("[^"]+"[.]"[^"]+") ADD CONSTRAINT ("[^"]+") FOREIGN KEY ([(].*[)]).* REFERENCES ".*',
                       'CREATE INDEX ON \1 \3;'));

            FOR x
                IN (SELECT RTRIM (
                               TRANSLATE (
                                   DBMS_METADATA.get_ddl ('INDEX',
                                                          index_name,
                                                          index_owner),
                                   CHR (10) || CHR (13),
                                   '  '))    ddl
                      FROM dba_ind_columns
                     WHERE     (index_owner, index_name) IN
                                   (SELECT owner, index_name
                                      FROM dba_indexes
                                     WHERE     owner = c.owner
                                           AND table_name = c.c_table_name)
                           AND column_name IN
                                   (SELECT column_name
                                      FROM dba_cons_columns
                                     WHERE     owner = c.owner
                                           AND constraint_name =
                                               c.constraint_name))
            LOOP
                print_ddl ('--      Existing candidate indexes ' || x.ddl);
            END LOOP;

            FOR x
                IN (SELECT RTRIM (
                               TRANSLATE (
                                   DBMS_METADATA.get_ddl ('INDEX',
                                                          index_name,
                                                          index_owner),
                                   CHR (10) || CHR (13),
                                   '  '))    ddl
                      FROM dba_ind_columns
                     WHERE (index_owner, index_name) IN
                               (SELECT owner, index_name
                                  FROM dba_indexes
                                 WHERE     owner = c.owner
                                       AND table_name = c.c_table_name))
            LOOP
                print_all ('--       Other existing Indexes: ' || x.ddl);
            END LOOP;
        END LOOP;
    END LOOP;
END;
/