3 Replies Latest reply: Jan 25, 2013 12:07 PM by rp0428 RSS

    dbms_redefinition

    user650888
      I have two questions related to dbms_redefinition

      1.

      begin
      dbms_redefinition.can_redef_table( schema1', 'mytable');
      end;


      ORA-12090: cannot online redefine table

      mytable is a normal non partitioned table, i am not sure how to check and make it redefinable, goal is to transfer data from non partitioned mytable into newly partitioned mytable2

      2.

      test_t1 is nonpartitioned table, test_t2 is newly partitioned table

      begin
      dbms_redefinition.can_redef_table( 'myschema', 'TEST_T1' );
      end;

      -- copies data
      BEGIN
      dbms_redefinition.start_redef_table( 'myschema' 'TEST_T1','TEST_T2' );
      END;


      It works until here, But when I try to copy indexes, constraints, triggers

      DECLARE
      nerr number;
      begin
      dbms_redefinition.copy_table_dependents
      ( 'myschema', 'TEST_T1', 'TEST_T2',
      copy_indexes => dbms_redefinition.cons_orig_params,
      copy_triggers => TRUE,
      copy_constraints => TRUE,
      copy_privileges => TRUE,
      copy_statistics => TRUE,
      num_errors => :nerr );
      END;

      why do I get index , triggers and constraint names as TMP$$_ORIGINALNAME for test_t2 ? Is there a way I can get the original names ?

      Eg: if test_t1 has index defined on one of its columns as idx_n1, then I need the same name for test_t2 also, not TMP$$_IDX_N1
        • 1. Re: dbms_redefinition
          JustinCave
          The indexes and constraints can't have the same name in the new table that they had in the old table because they're using the same namespace. You can't have two indexes with the same name in the same schema even if they are on two different tables.

          If you didn't use the dbms_redefinition package, you could copy the data from the old table to the new table, drop the old table, and then create constraints and indexes on the new table that match the names of the constraints and indexes on the old table. Since online redefinition presupposes that both tables are going to exist at the same time, the constraint and index names would need to be different.

          Justin
          • 2. Re: dbms_redefinition
            user650888
            For the first question, redefinition is only possible for heap organized tables ?

            From user_tables I found out my table is not temporary or nested. The ORA-12090 says it should not be clustered, AQ table
            • 3. Re: dbms_redefinition
              rp0428
              >
              For the first question, redefinition is only possible for heap organized tables ?
              >
              Not true - this doc lists the restrictions on the package
              http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables007.htm#ADMIN01510
              >
              Restrictions for Online Redefinition of Tables
              >
              See this Oracle-base example using DBMS_REDEFINITION
              http://www.oracle-base.com/articles/11g/online-table-redefinition-enhancements-11gr1.php