Ended with zero rows by using DBMS_REDIFINITION

From: Balwanth B <balwanthdba_at_gmail.com>
Date: Thu, 6 Oct 2016 16:35:58 -0400
Message-ID: <CAL72EnC1r1Sr4OXksJrXL13SepYeBZwTYmdXSzcO5dd4oxwWfg_at_mail.gmail.com>



Hello All,

Today I was testing DBMS_redefinition (for redefining table online) for changing the structure of table in our test environment. But,I am end up with zero rows in original table. Structure changed but I do not have any idea what happen to data. I created interim table with with desired partitions and added the primary key on the same column as of original table.
 There were around 50 million records in original table. I followed below method.

for checking redefinition is possible or not,I used below 1)
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE(' user','original_table',

      DBMS_REDEFINITION.CONS_USE_PK);
END;
/

PL/SQL procedure successfully completed.

2) here I used options_flag => 'DBMS_REDEFINITION.CONS_USE_PK' which was throwing up error ora-06502 pl/sqL. I removed it assuming by default it will use primary key then PL/SQL procedure successfully completed.

BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(

   uname       => 'user',
   orig_table  => ' xxxxx',
   int_table   =>  'yyyyyy'

   );
END;
/

3)

SYNC THE TABLE TILL CUT OFF TIME BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE(

   uname      => 'user',
   orig_table => ' xxxxxxx',
   int_table  => 'yyyyy'

   );
END;
/

PL/SQL procedure successfully completed.

4)

COPY DEPENDENT OBJECTS AUTOMATICALLY SET SERVEROUTPUT ON
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(

    uname               => 'user',
    orig_table          => 'xxxx',
    int_table           => 'yyyy',
    copy_indexes        => xxxxxx',
    copy_triggers       => xxx,
    copy_constraints    => xxx,
    copy_privileges     => xxx,
    ignore_errors       => xxxx,
    num_errors          => num_errors);

END;
/

 while performing step 4 I ORA-06502: PL/SQL: numeric or value error string
 .
5)

FINALIZE THE OBJECTS BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE(user, 'xxxxxx','yyyyyyy'); END;
/

PL/SQL procedure successfully completed.

select count(*) from original table returns zero.

I'm sure that I created the table with same columns and datatypes(to be clear I just copied and pasted and changed the names where required) Interim table is in same schema

Does anyone have idea what went wrong?

Thanks,

Bobilli

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Oct 06 2016 - 22:35:58 CEST

Original text of this message