Using �copy table� or �append table� for Oracle partitioned tables. (4286960)

When using the copy command for Oracle partitioned tables, only certain options may work. This solution delves on the dos and donts when running copy on Oracle partitioned tables.

N.A.

The following scenarios cover all combination when trying to copy an Oracle partitioned table:

A. If the source table and target table are partitioned with the source partitioned table replicating to the target partitioned table, and if just the table names are specified in the copy without specifying a particular partition, then the command succeeds as follows:

sp_ctrl (server_name:port_num)> copy owner.table_name to owner.table_name

copy started; job id 64

sp_ctrl (server_name:port_num)> copy status

Job ID : 64
Host : server_name
Started : 08-DEC-08 17:27:20
Job Type : Copy
Status : Done

ID Tablename Total Rows %Comp Status Status T
ime Total Time
------ ------------------------------------ ---------- ----- ---------- --------
--- ----------
1 OWNER.TABLE_NAME.P1 1 100 Done
N/A 1:04
2 OWNER.TABLE_NAME.P2 0 100 Done
N/A 1:04
3 OWNER.TABLE_NAME.P3 0 100 Done
N/A 1:04

B. If an attempt is made to copy a specific partition from source table, it will result in the following error Specification of partition is invalid regardless of specifying a specific partition on target table or just the target table:

sp_ctrl (server_name:port_num)> copy owner.table_name.p1

Cannot copy OWNER.TABLE_NAME.P1
Specification of partition is invalid

sp_ctrl (server_name:port_num)> copy owner.table_name.p1 to owner.table_name

Cannot copy OWNER.TABLE_NAME.P1
Specification of partition is invalid

sp_ctrl (server_name:port_num)> copy owner.table_name.p1 to owner.table_name.p1

Cannot copy OWNER.TABLE_NAME.P1
Specification of partition is invalid

C. If an attempt is made to copy an ordinary source table to a specific partition on the target table (where the ordinary table from source is replicating to the partitioned table on target), it results in the following error:

sp_ctrl (server_name:port_num)> copy owner.source_table to owner.target_table.P1

Cannot copy OWNER.SOURCE_TABLE
Specification of target partition is invalid

D. If a non-partitioned source table is replicating to the partitioned target table, and if the copy table is issued without any reference to the partitions of the target table, then the copy will succeed as below:

sp_ctrl (server_name:port_num)> copy owner.source_table to owner.target_table

copy started; job id 69

sp_ctrl (server_name:port_num)> copy status

Job ID : 69
Host : server_name
Started : 09-DEC-08 10:47:57
Job Type : Copy
Status : Done

ID Tablename Total Rows %Comp Status Status T
ime Total Time
------ ------------------------------------ ---------- ----- ---------- --------
--- ----------
1 OWNER.SOURCE_TABLE 0 100 Done
N/A 1:00

E. If the partitioned table is replicating to an ordinary table, and if a copy table is issued to copy from that source partitioned table to the ordinary target table without specifying any partition of the source table, the command will succeed as below:

sp_ctrl (server_name:port_num)> copy owner.parttable to owner.target_table

copy started; job id 67

sp_ctrl (server_name:port_num)> copy status

Job ID : 67
Host : server_name
Started : 09-DEC-08 10:33:20
Job Type : Copy
Status : Syncing

ID Tablename Total Rows %Comp Status Status e
------ ------------------------------------ ---------- ----- ---------- --------
1 OWNER.SOURCE_TABLE.P1 1 100 Done 4
2 OWNER.SOURCE_TABLE.P2 0 100 Done 4
3 OWNER.SOURCE_TABLE.P3 0 100 Done 4

In brief, it is possible to copy a replicating partitioned table from source to the partitioned table on the target. It is also possible to copy a replicating partitioned table to an ordinary target table or an ordinary source table to a partitioned target table.

It is not possible to copy a specific partition from source to a specific partition on target, nor is it possible to copy that specific partition from source to the target table (even when not specifying the particular target partition and just specifying the complete partitioned table on target).