Ended with zero rows by using DBMS_REDIFINITION
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-lReceived on Thu Oct 06 2016 - 22:35:58 CEST