Skip to content

Instantly share code, notes, and snippets.

@domgiles

domgiles/oracle_hybrid_partitions_ADB.sql

Last active August 5, 2022 09:18
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save domgiles/f3f7e3815cb7ba0db87fa249ef26c169 to your computer and use it in GitHub Desktop.
Save domgiles/f3f7e3815cb7ba0db87fa249ef26c169 to your computer and use it in GitHub Desktop.
Download ZIP
Create and exchange partitions in a hybrid paritioned table on ADB
Raw
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment