Hi Tom,
I have a scenario where I need to refresh a partitioned table from another database. The existing process copies the data over a db link and creates a temporary partitioned table. Then we exchange the partitions with the target table and at the end we just rebuild the indexes on the target table.

Problem with this approach is during refresh of the table data is inconsistent as some partitions have old data and some have new data. Now using DBMS_REDEFINITION I am able to copy the data but problem is my source table does not have the indexes I need on the target table. Could you please suggest the best approach in this case.

Thank you in advance.

Regards,
Abhishek.

1. Why is your table data inconsistent? I don't fully understand the issue - could you clarify please?
A: Hi Chris, Thanks for your reply. I am sorry, I should have provided the information in the first place.

Any way, this table I am working with is a big partitioned table(T1). It is a dimension table that is loaded from customer information. It also has a bunch of indexes(I1-23). The file is primarily loaded in stage schema(Table St1). Then is there is any difference in existing table data and new data in stage we refresh the whole table from stage. Refresh is done using partition exchange and after the refresh all the indexes are rebuilt. The whole process takes around an hour and half from start to finish.

Now in between this time if any one queries T1 he can see some old partition data and some new partition data. This is what I meant as inconsistent data. I don't want to stop the users from querying T1. So I am going for online redefinition of the table.

Please check the following code snippet. I will use generic existing table to simulate the scenarios.

SQL> Set serverout on size unlimited timing on
SQL>
SQL> Create table T1 as select object_id, Object_name, Object_type from dba_objects where object_id is not null and rownum < 10;
Table created
SQL> Create table ST1 as select object_id, Object_name, Object_type from dba_objects where object_id is not null;
Table created

SQL>
SQL> Alter table T1 add constraint I_PK Primary key (object_id);
Table altered

SQL> Create index I2 on T1(object_type);
Index created

SQL> select 'S1' as Sample,'T1' as table_name,count(*) from t1
2 union all
3 select 'S1' as Sample,'ST1' as table_name,count(*) from St1;

SAMPLE TABLE_NAME COUNT(*)
------ ---------- ----------
S1 T1 9
S1 ST1 188452

SQL> select 'S1' as Sample,'T1' as table_name,count(*) from user_indexes where table_name = 'T1'
2 union all
3 select 'S1' as Sample,'ST1' as table_name,count(*) from user_indexes where table_name = 'ST1';

SAMPLE TABLE_NAME COUNT(*)
------ ---------- ----------
S1 T1 2
S1 ST1 0

SQL> exec DBMS_REDEFINITION.can_redef_table('AKAHALI','ST1',DBMS_REDEFINITION.CONS_USE_ROWID);
PL/SQL procedure successfully completed

SQL> exec DBMS_REDEFINITION.START_REDEF_TABLE( uname => 'AKAHALI', orig_table => 'ST1', int_table => 'T1', options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
PL/SQL procedure successfully completed

SQL> select 'S2' as Sample,'T1' as table_name,count(*) from t1
2 union all
3 select 'S2' as Sample,'ST1' as table_name,count(*) from St1;
SAMPLE TABLE_NAME COUNT(*)
------ ---------- ----------
S2 T1 188452
S2 ST1 188452
SQL> select 'S2' as Sample,'T1' as table_name,count(*) from user_indexes where table_name = 'T1'
2 union all
3 select 'S2' as Sample,'ST1' as table_name,count(*) from user_indexes where table_name = 'ST1';
SAMPLE TABLE_NAME COUNT(*)
------ ---------- ----------
S2 T1 3
S2 ST1 0

SQL>
SQL> Declare
2 lvn_errs number;
3 begin
4 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
5 uname => 'AKAHALI',
6 orig_table => 'ST1',
7 int_table => 'T1',
8 num_errors => lvn_errs
9 );
10 dbms_output.put_line('Errors: '||to_char(lvn_errs));
11 end;
12 /
SQL>
Errors: 0
PL/SQL procedure successfully completed

SQL> select 'S3' as Sample,'T1' as table_name,count(*) from t1
2 union all
3 select 'S3' as Sample,'ST1' as table_name,count(*) from St1;
SAMPLE TABLE_NAME COUNT(*)
------ ---------- ----------
S3 T1 188452
S3 ST1 188452

SQL> select 'S3' as Sample,'T1' as table_name,count(*) from user_indexes where table_name = 'T1'
2 union all
3 select 'S3' as Sample,'ST1' as table_name,count(*) from user_indexes where table_name = 'ST1';
SAMPLE TABLE_NAME COUNT(*)
------ ---------- ----------
S3 T1 3
S3 ST1 0

SQL>
SQL> begin
2 dbms_redefinition.finish_redef_table(
3 uname => 'AKAHALI',
4 orig_table => 'ST1',
5 int_table => 'T1'
6 );
7 end;
8 /
PL/SQL procedure successfully completed

SQL> select 'S4' as Sample,'T1' as table_name,count(*) from t1
2 union all
3 select 'S4' as Sample,'ST1' as table_name,count(*) from St1;
SAMPLE TABLE_NAME COUNT(*)
------ ---------- ----------
S4 T1 188452
S4 ST1 188452
SQL> select 'S4' as Sample,'T1' as table_name,count(*) from user_indexes where table_name = 'T1'
2 union all
3 select 'S4' as Sample,'ST1' as table_name,count(*) from user_indexes where table_name = 'ST1';
SAMPLE TABLE_NAME COUNT(*)
------ ---------- ----------
S4 T1 0
S4 ST1 2

Now the indexes on T1 are moved to ST1. I can recreate them but before that I want to know if there is a way to keep them. Also please advise if there is a better way to achieve this.

Thanks,
Abhishek.

SQL> create table T1 as select object_id, Object_name, Object_type from dba_objects where object_id is not null and rownum < 10;

Table created.

SQL> create table ST1 as select object_id, Object_name, Object_type from dba_objects where object_id is not null;

Table created.

SQL>
SQL> alter table T1 add constraint I_PK Primary key (object_id);

Table altered.

SQL> alter table ST1 add constraint SI_PK Primary key (object_id);

Table altered.

SQL>
SQL> Create index I2 on T1(object_type);

Index created.

SQL> Create index SI2 on ST1(object_type);

Index created.

SQL>
SQL> exec DBMS_REDEFINITION.can_redef_table('CHRIS','ST1',DBMS_REDEFINITION.CONS_USE_ROWID);

PL/SQL procedure successfully completed.

SQL> exec DBMS_REDEFINITION.START_REDEF_TABLE( uname => 'CHRIS', orig_table => 'ST1', int_table => 'T1', options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);

PL/SQL procedure successfully completed.

SQL>
SQL> select 'S2' as Sample,'T1' as table_name,count(*) from user_indexes where table_name = 'T1'
  2  union all
  3  select 'S2' as Sample,'ST1' as table_name,count(*) from user_indexes where table_name = 'ST1';

SA TABLE_NAME                       COUNT(*)
-- ------------------------------ ----------
S2 T1                                      3
S2 ST1                                     2

SQL>
SQL> declare
  2  lvn_errs number;
  3  begin
  4  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
  5  uname => 'CHRIS',
  6  orig_table => 'ST1',
  7  int_table => 'T1',
  8  num_errors => lvn_errs
  9  );
 10   dbms_output.put_line('Errors: '||to_char(lvn_errs));
 11  end;
 12  /
declare
*
ERROR at line 1:
ORA-01408: such column list already indexed
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1961
ORA-06512: at "SYS.DBMS_REDEFINITION", line 2486
ORA-06512: at "SYS.DBMS_REDEFINITION", line 3803
ORA-06512: at line 4


SQL>
SQL> drop index I2;

Index dropped.

SQL> alter table t1 drop primary key;

Table altered.

SQL>
SQL> declare
  2  lvn_errs number;
  3  begin
  4  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
  5  uname => 'CHRIS',
  6  orig_table => 'ST1',
  7  int_table => 'T1',
  8  num_errors => lvn_errs
  9  );
 10   dbms_output.put_line('Errors: '||to_char(lvn_errs));
 11  end;
 12  /
Errors: 0

PL/SQL procedure successfully completed.

SQL>
SQL> begin
  2  dbms_redefinition.finish_redef_table(
  3  uname => 'CHRIS',
  4  orig_table => 'ST1',
  5  int_table => 'T1'
  6  );
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL>
SQL> select 'S4' as Sample,'T1' as table_name,count(*) from user_indexes where table_name = 'T1'
  2  union all
  3  select 'S4' as Sample,'ST1' as table_name,count(*) from user_indexes where table_name = 'ST1';

SA TABLE_NAME                       COUNT(*)
-- ------------------------------ ----------
S4 T1                                      2
S4 ST1                                     2