SET SERVEROUTPUT ON size 1000000;

DECLARE
   CURSOR c1
   IS
        SELECT   uc.owner,
                 uc.constraint_name,
                 uc.table_name,
                 ucc.column_name
          FROM   user_constraints uc, user_cons_columns ucc
         WHERE       uc.constraint_type = 'R'
                 AND UPPER (uc.table_name) NOT LIKE '%_JOIN'
                 AND uc.owner = ucc.owner
                 AND uc.constraint_name = ucc.constraint_name
      ORDER BY   uc.table_name, ucc.column_name;

   r_c1                c1%ROWTYPE;
   n_index             NUMBER := 0;
   n_count_existing    NUMBER := 0;
   b_unique_name       CHAR (1) := 'N';
   v_user              VARCHAR2 (30);
   v_previous_table    VARCHAR2 (30) := NULL;
   v_new_index_name    user_indexes.index_name%TYPE;
   v_tablespace_name   user_tablespaces.tablespace_name%TYPE;

   -- Create some variables to hold error data if one occurs.
   sql_stmt            VARCHAR2 (1000);
   error_num           VARCHAR2 (50);
   error_desc          VARCHAR2 (200);
   errmsg              VARCHAR2 (4000);
BEGIN
   DBMS_OUTPUT.enable (NULL);

   SELECT   UPPER (USER) INTO v_user FROM DUAL;

   SELECT   tablespace_name
     INTO   v_tablespace_name
     FROM   user_tablespaces
    WHERE   UPPER (tablespace_name) = v_user || '_INDEX';

   OPEN c1;

   LOOP
      FETCH c1 INTO   r_c1;

      EXIT WHEN c1%NOTFOUND;

      -- If the previous table variable is null or we are still adding indexes
      -- to the same table as before then increment the counter.
      IF (v_previous_table IS NULL OR v_previous_table = r_c1.table_name)
      THEN
         n_index := n_index + 1;
      ELSE
         -- Re-initialize the counter
         n_index := 1;
      END IF;

      -- Set the previous table value to the current table in the cursor.
      v_previous_table := r_c1.table_name;

      -- Generate the new index name
      v_new_index_name :=
            SUBSTR (REPLACE (r_c1.table_name, '_', ''), 1, 20)
         || '_'
         || n_index
         || '_IDX';


      -- Some tables may be named similiarly, and may result in the same index
      -- name being created.  To handle that, we will check for the existance
      -- of the index name in the all_indexes view.  If the name exists, we
      -- will loop until we have generated a unique index name.
      WHILE (b_unique_name = 'N')
      LOOP
         -- Get the count of indexes with the same name.
         SELECT   COUNT (index_name)
           INTO   n_count_existing
           FROM   all_indexes
          WHERE   index_name = v_new_index_name;

         -- If there exists an index with the same name, increment the counter,
         -- re-generate the index_name, and try again.
         IF (n_count_existing > 0)
         THEN
            n_index := n_index + 1;
            v_new_index_name :=
                  SUBSTR (REPLACE (r_c1.table_name, '_', ''), 1, 20)
               || '_'
               || n_index
               || '_IDX';
         ELSE
            -- The index name is unique, so we can flip the variable to exit
            -- the loop.
            b_unique_name := 'Y';
         END IF;
      END LOOP;

      -- Re-initialize the unique_name switch.
      b_unique_name := 'N';


      BEGIN
         -- Create the index creation SQL.
         sql_stmt :=
               'CREATE INDEX '
            || v_new_index_name
            || ' ON '
            || v_user
            || '.'
            || r_c1.table_name
            || ' ('
            || r_c1.column_name
            || ') NOLOGGING TABLESPACE '
            || v_tablespace_name
            || ' NOPARALLEL';

         -- Execute the SQL to create the index.
         EXECUTE IMMEDIATE (sql_stmt);

         DBMS_OUTPUT.put_line(   'Index '
                              || v_new_index_name
                              || ' on column '
                              || r_c1.column_name
                              || ' for table '
                              || r_c1.table_name
                              || ' created.');
      EXCEPTION
         WHEN OTHERS
         THEN
            error_desc := SUBSTR (SQLERRM, 1, 200);
            error_num := SQLCODE;

            IF (error_num = '-1408')
            THEN
               errmsg :=
                     'Index on column '
                  || r_c1.column_name
                  || ' for table '
                  || r_c1.table_name
                  || ' already exists.';
            ELSE
               errmsg :=
                     'Error   :'
                  || error_num
                  || ' - '
                  || error_desc
                  || ' while executing: '
                  || sql_stmt;
            END IF;

            DBMS_OUTPUT.put_line (errmsg);
      END;
   END LOOP;

   CLOSE c1;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      NULL;
   WHEN OTHERS
   THEN
      NULL;
END;
/