SET SERVEROUTPUT ON SIZE 1000000

DECLARE

   CURSOR c_table (cp_owner IN VARCHAR2)
   IS
      SELECT dt.table_name table_name, MIN (LEVEL) lvl
      FROM   dba_tables dt,
             dba_constraints dc
      WHERE  dt.owner = cp_owner
      AND    dt.owner = dc.owner (+)
      AND    dt.table_name = dc.table_name (+)
      START WITH  dt.table_name NOT IN 
                  (SELECT dc1.table_name 
                   FROM   dba_constraints dc1
                   WHERE  dc1.owner = cp_owner
                   AND    dc1.r_constraint_name IS NOT NULL)
      CONNECT BY PRIOR dc.constraint_name = dc.r_constraint_name
      GROUP  BY dt.table_name 
      ORDER BY lvl;

  CURSOR c_index (cp_owner      IN VARCHAR2,
                  cp_new_owner  IN VARCHAR2,
                  cp_table_name IN VARCHAR2)
   IS
      SELECT di.index_name
      FROM   dba_indexes di
      WHERE  di.owner = cp_owner
      AND    di.index_name NOT LIKE 'SYS%'
      AND    di.table_name = cp_table_name
      AND    NOT EXISTS (SELECT 1
                         FROM   dba_indexes di1
                         WHERE  di1.owner = cp_new_owner
                         AND    di.index_name = di1.index_name
                         AND    di.table_name = di1.table_name);

   CURSOR c_trigger (cp_owner      IN VARCHAR2,
                     cp_new_owner  IN VARCHAR2,
                     cp_table_name IN VARCHAR2)
   IS
      SELECT dt.trigger_name
      FROM   dba_triggers dt
      WHERE  dt.owner = cp_owner
      AND    dt.table_name = cp_table_name
      AND    NOT EXISTS (SELECT 1
                         FROM   dba_triggers dt1
                         WHERE  dt1.owner = cp_new_owner
                         AND    dt.trigger_name = dt1.trigger_name
                         AND    dt.table_name = dt1.table_name);

   CURSOR c_seq (cp_owner IN VARCHAR2)
   IS
      SELECT do.object_name sequence_name
      FROM   dba_objects do
      WHERE  do.owner = cp_owner
      AND    do.object_type = 'SEQUENCE';

   CURSOR c_view (cp_owner IN VARCHAR2)
   IS
      SELECT do.object_name view_name
      FROM   dba_objects do
      WHERE  do.owner = cp_owner
      AND    do.object_type = 'VIEW';

   l_ddl       CLOB;
   l_owner     VARCHAR2(30) := 'OPS$SCHEMAOWNER';
   l_new_owner VARCHAR2(30) := USER;
   l_seq_no    PLS_INTEGER := 1;

   -- *************************************************************************
   -- Local procedure to execute the DDL stored in a LOB variable
   PROCEDURE run_sql (p_lob IN CLOB)
   IS
      l_buffer_len CONSTANT BINARY_INTEGER := 256;
      l_sql_table  DBMS_SQL.VARCHAR2S;
      l_cursor     PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
      l_lob_length PLS_INTEGER;
      l_rows       PLS_INTEGER := 0;
      l_accum      INTEGER := 0;
      l_begin      INTEGER := 1;
      l_end        INTEGER := 256;
   BEGIN
      l_lob_length := DBMS_LOB.GETLENGTH(p_lob);

      LOOP
         -- Set the length to the remaining size if there are < l_buffer_len
         -- characters remaining.
         IF l_accum + l_buffer_len > l_lob_length
         THEN
            l_end := l_lob_length - l_accum;
         END IF;

         l_sql_table(NVL(l_sql_table.LAST, 0) + 1) := DBMS_LOB.SUBSTR(p_lob, l_end, l_begin);

         l_begin := l_begin + l_buffer_len;
         l_accum := l_accum + l_end;

         IF l_accum >= l_lob_length
         THEN
            EXIT;
         END IF;
      END LOOP;

      DBMS_SQL.PARSE(c             => l_cursor,
                     statement     => l_sql_table,
                     lb            => l_sql_table.FIRST,
                     ub            => l_sql_table.LAST,
                     lfflg         => FALSE,
                     language_flag => DBMS_SQL.NATIVE);

      l_rows := DBMS_SQL.EXECUTE(l_cursor);

      DBMS_SQL.CLOSE_CURSOR(l_cursor);

   END run_sql;
   -- *************************************************************************
 
BEGIN

   DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',TRUE);
   DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',FALSE);
   DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS',TRUE);
   DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS_AS_ALTER',FALSE);

   FOR table_rec IN c_table (l_owner)
   LOOP
      --dbms_output.put_line('Table name '||table_rec.table_name);
      SELECT DBMS_METADATA.GET_DDL ('TABLE', table_rec.table_name, l_owner)
      INTO   l_ddl
      FROM   DUAL;

      l_ddl := REPLACE(l_ddl,l_owner,l_new_owner);

      run_sql (l_ddl);

      FOR index_rec IN c_index (l_owner,
                                l_new_owner,
                                table_rec.table_name)
      LOOP
         SELECT DBMS_METADATA.GET_DDL ('INDEX', index_rec.index_name, l_owner)
         INTO   l_ddl
         FROM   DUAL;

         l_ddl := REPLACE(l_ddl,l_owner,l_new_owner);

         run_sql (l_ddl);
      END LOOP;

      FOR trigger_rec IN c_trigger (l_owner,
                                    l_new_owner,
                                    table_rec.table_name)
      LOOP
         SELECT REGEXP_REPLACE(DBMS_METADATA.GET_DDL('TRIGGER', trigger_rec.trigger_name, l_owner),'ALTER TRIGGER.*','')
         INTO   l_ddl
         FROM   DUAL;

         l_ddl := REPLACE(l_ddl,l_owner,l_new_owner);

         run_sql (l_ddl);
      END LOOP;

   END LOOP;

   FOR seq_rec IN c_seq (l_owner)
   LOOP
      SELECT DBMS_METADATA.GET_DDL ('SEQUENCE', seq_rec.sequence_name, l_owner)
      INTO   l_ddl
      FROM   DUAL;

      l_ddl := REPLACE(l_ddl,l_owner,l_new_owner);

      run_sql (l_ddl);
   END LOOP;

   FOR view_rec IN c_view (l_owner)
   LOOP
      SELECT DBMS_METADATA.GET_DDL ('VIEW', view_rec.view_name, l_owner)
      INTO   l_ddl
      FROM   DUAL;

      l_ddl := REPLACE(l_ddl,l_owner,l_new_owner);

      run_sql (l_ddl);
   END LOOP;

END;
/