Create and exchange partitions in a hybrid paritioned table on ADB

oracle_hybrid_partitions_ADB.sql

begin
    dbms_cloud.create_credential(
            credential_name => 'obj_store_cred2',
            username => 'domgiles',
            password => 'areallysecretpassword'
        );
end;

BEGIN
    DBMS_CLOUD.EXPORT_DATA(
            credential_name =>'obj_store_cred2',
            file_uri_list =>'https://objectstorage.uk-london-1.oraclecloud.com/n/oradbclouducm/b/ext-table/o/sales1995.dmp',
            format => json_object('type' value 'datapump'),
            query => 'SELECT * FROM sales partition(sales_1995)'
        );
END;

BEGIN
    DBMS_CLOUD.EXPORT_DATA(
            credential_name =>'obj_store_cred2',
            file_uri_list =>'https://objectstorage.uk-london-1.oraclecloud.com/n/oradbclouducm/b/ext-table/o/sales1996.dmp',
            format => json_object('type' value 'datapump'),
            query => 'SELECT * FROM sales partition(sales_1996)'
        );
END;

BEGIN
    DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
            table_name =>'SALES_1995_EXT',
            credential_name =>'obj_store_cred2',
            file_uri_list =>'https://objectstorage.uk-london-1.oraclecloud.com/n/oradbclouducm/b/ext-table/o/sales1995.dmp',
            format => json_object('type' value 'datapump'),
            column_list => 'PROD_ID NUMBER NOT NULL ENABLE, CUST_ID NUMBER NOT NULL ENABLE, TIME_ID DATE NOT NULL ENABLE, CHANNEL_ID NUMBER NOT NULL ENABLE, PROMO_ID NUMBER NOT NULL ENABLE, QUANTITY_SOLD NUMBER(10,2) NOT NULL ENABLE, SELLER NUMBER(6,0) NOT NULL ENABLE, FULFILLMENT_CENTER NUMBER(6,0) NOT NULL ENABLE, COURIER_ORG NUMBER(6,0) NOT NULL ENABLE, TAX_COUNTRY VARCHAR2(3) COLLATE USING_NLS_COMP NOT NULL ENABLE, TAX_REGION VARCHAR2(3) COLLATE USING_NLS_COMP, AMOUNT_SOLD NUMBER(10,2) NOT NULL ENABLE'
        );
END;

BEGIN
    DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
            table_name =>'SALES_1996_EXT',
            credential_name =>'obj_store_cred2',
            file_uri_list =>'https://objectstorage.uk-london-1.oraclecloud.com/n/oradbclouducm/b/ext-table/o/sales1996.dmp',
            format => json_object('type' value 'datapump'),
            column_list => 'PROD_ID NUMBER NOT NULL ENABLE, CUST_ID NUMBER NOT NULL ENABLE, TIME_ID DATE NOT NULL ENABLE, CHANNEL_ID NUMBER NOT NULL ENABLE, PROMO_ID NUMBER NOT NULL ENABLE, QUANTITY_SOLD NUMBER(10,2) NOT NULL ENABLE, SELLER NUMBER(6,0) NOT NULL ENABLE, FULFILLMENT_CENTER NUMBER(6,0) NOT NULL ENABLE, COURIER_ORG NUMBER(6,0) NOT NULL ENABLE, TAX_COUNTRY VARCHAR2(3) COLLATE USING_NLS_COMP NOT NULL ENABLE, TAX_REGION VARCHAR2(3) COLLATE USING_NLS_COMP, AMOUNT_SOLD NUMBER(10,2) NOT NULL ENABLE'
        );
END;

select * from SALES_1996_EXT;

select * from USER_TAB_PARTITIONS where TABLE_NAME = 'SALES';

BEGIN
  DBMS_CLOUD.CREATE_HYBRID_PART_TABLE(
      table_name =>'SALES_HYBRID',
      credential_name =>'obj_store_cred2',
      format => json_object('type' value 'datapump'),
      column_list => 'PROD_ID NUMBER NOT NULL ENABLE, CUST_ID NUMBER NOT NULL ENABLE, TIME_ID DATE NOT NULL ENABLE, CHANNEL_ID NUMBER NOT NULL ENABLE, PROMO_ID NUMBER NOT NULL ENABLE, QUANTITY_SOLD NUMBER(10,2) NOT NULL ENABLE, SELLER NUMBER(6,0) NOT NULL ENABLE, FULFILLMENT_CENTER NUMBER(6,0) NOT NULL ENABLE, COURIER_ORG NUMBER(6,0) NOT NULL ENABLE, TAX_COUNTRY VARCHAR2(3) COLLATE USING_NLS_COMP NOT NULL ENABLE, TAX_REGION VARCHAR2(3) COLLATE USING_NLS_COMP, AMOUNT_SOLD NUMBER(10,2) NOT NULL ENABLE',
      partitioning_clause => 'PARTITION BY RANGE (TIME_ID)
                             (PARTITION SALES_1995 VALUES LESS THAN (TO_DATE(''1996-01-01 00:00:00'', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')) external location (''https://objectstorage.uk-london-1.oraclecloud.com/n/oradbclouducm/b/ext-table/o/sales1995.dmp''),
                              PARTITION SALES_1996  VALUES LESS THAN (TO_DATE(''1997-01-01 00:00:00'', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')),
                              PARTITION SALES_H1_1997  VALUES LESS THAN (TO_DATE('' 1997-07-01 00:00:00'', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')),
                              PARTITION SALES_H2_1997  VALUES LESS THAN (TO_DATE('' 1998-01-01 00:00:00'', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')),
                              PARTITION SALES_Q1_1998  VALUES LESS THAN (TO_DATE('' 1998-04-01 00:00:00'', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')),
                              PARTITION SALES_Q2_1998  VALUES LESS THAN (TO_DATE('' 1998-07-01 00:00:00'', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')),
                              PARTITION SALES_Q3_1998  VALUES LESS THAN (TO_DATE('' 1998-10-01 00:00:00'', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')),
                              PARTITION SALES_Q4_1998  VALUES LESS THAN (TO_DATE('' 1999-01-01 00:00:00'', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')),
                              PARTITION SALES_Q1_1999  VALUES LESS THAN (TO_DATE('' 1999-04-01 00:00:00'', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')),
                              PARTITION SALES_Q2_1999  VALUES LESS THAN (TO_DATE('' 1999-07-01 00:00:00'', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')),
                              PARTITION SALES_Q3_1999  VALUES LESS THAN (TO_DATE('' 1999-10-01 00:00:00'', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')),
                              PARTITION SALES_Q4_1999  VALUES LESS THAN (TO_DATE('' 2000-01-01 00:00:00'', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')),
                              PARTITION SALES_Q1_2000  VALUES LESS THAN (TO_DATE('' 2000-04-01 00:00:00'', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')),
                              PARTITION SALES_Q2_2000  VALUES LESS THAN (TO_DATE('' 2000-07-01 00:00:00'', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')),
                              PARTITION SALES_Q3_2000  VALUES LESS THAN (TO_DATE('' 2000-10-01 00:00:00'', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')),
                              PARTITION SALES_Q4_2000  VALUES LESS THAN (TO_DATE('' 2001-01-01 00:00:00'', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')),
                              PARTITION SALES_Q1_2001  VALUES LESS THAN (TO_DATE('' 2001-04-01 00:00:00'', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')),
                              PARTITION SALES_Q2_2001  VALUES LESS THAN (TO_DATE('' 2001-07-01 00:00:00'', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')),
                              PARTITION SALES_Q3_2001  VALUES LESS THAN (TO_DATE('' 2001-10-01 00:00:00'', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')),
                              PARTITION SALES_Q4_2001  VALUES LESS THAN (TO_DATE('' 2002-01-01 00:00:00'', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN''))
                             )'
     );
END;

alter table sales_hybrid exchange partition SALES_1996 with table SALES_1996_EXT;

select * from SALES_HYBRID where TIME_ID > TO_DATE('1995-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN');

select * from USER_TAB_PARTITIONS where table_name = 'SALES_HYBRID';

drop table SALES_HYBRID purge;