[Oracle] Assess Migration workflow: Table Partitions will be COLOCATED by default in Target DB

[sanyamsinghal]

Jira Link: DB-13438
With assess migration command in migration workflow, the table partitions created on TargetDB will be colocated by default.
Although the assessment report might still suggest some table partitions to be colocated and for some to be sharded. If you want to follow those recommendations for partitions then you will have to modify the DDLs manually.

Workaround: Modify DDLs to create the table partitions as normal tables and later on attach them using ALTER TABLE ATTACH PARTITION

Exported Schema from Source Oracle:

./table.sql
CREATE TABLE orders_interval_partition (
        order_id bigint GENERATED BY DEFAULT AS IDENTITY (START WITH 106 INCREMENT BY 1 MAXVALUE 9223372036854775807 MINVALUE 1 NO CYCLE CACHE 20 ),
        customer_id integer NOT NULL,
        status varchar(20) NOT NULL,
        salesman_id integer,
        order_date timestamp NOT NULL,
        PRIMARY KEY (order_id,order_date)
) PARTITION BY RANGE (order_date) ;

./partition.sql
CREATE TABLE order_items_range_partitioned_p1 PARTITION OF order_items_range_partitioned
FOR VALUES FROM (MINVALUE,MINVALUE) TO (50, 8);
CREATE TABLE order_items_range_partitioned_p2 PARTITION OF order_items_range_partitioned
FOR VALUES FROM (50, 8) TO (70, 15);
CREATE TABLE order_items_range_partitioned_p3 PARTITION OF order_items_range_partitioned
FOR VALUES FROM (70, 15) TO (90, 15);

If you want to create the partitions some of the partitions as sharded, and some as colocated, modify the schema as below:

-- ./partition.sql
-- sharded table
CREATE TABLE order_items_range_partitioned_p1 (LIKE orders_interval_partition INCLUDING ALL) WITH COLOCATION=FALSE;
ALTER TABLE ATTACH PARTITION order_items_range_partitioned_p1 FOR VALUES FROM (MINVALUE,MINVALUE) TO (50, 8);

-- colocated table
CREATE TABLE order_items_range_partitioned_p2 PARTITION OF order_items_range_partitioned
FOR VALUES FROM (50, 8) TO (70, 15);

--sharded table
CREATE TABLE order_items_range_partitioned_p3 (LIKE orders_interval_partition INCLUDING ALL) WITH COLOCATION=FALSE;
ALTER TABLE ATTACH PARTITION order_items_range_partitioned_p3 FOR VALUES FROM (70, 15) TO (90, 15);

Assumption: the target db is a colocated db