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
