SYS@PROD4> create directory sales_dir as '/home/oracle/scripts';
Directory created.
SYS@PROD4> CREATE TABLE hybrid_partition_table
(
region varchar2(6) NOT NULL,
time_id DATE NOT NULL,
amount NUMBER(10,2)
)
EXTERNAL PARTITION ATTRIBUTES (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY sales_dir
ACCESS PARAMETERS(
FIELDS TERMINATED BY ','
(region,time_id DATE 'yyyy-mm-dd',amount)
)
REJECT LIMIT UNLIMITED
)
PARTITION BY RANGE (time_id)
(
PARTITION sales_2016 VALUES LESS THAN (TO_DATE('2017-01-01','yyyy-mm-dd')) EXTERNAL DEFAULT DIRECTORY sales_dir LOCATION ('sales_2016.txt'),
PARTITION sales_2017 VALUES LESS THAN (TO_DATE('2018-01-01','yyyy-mm-dd')) EXTERNAL DEFAULT DIRECTORY sales_dir LOCATION ('sales_2017.txt'),
PARTITION sales_2018 VALUES LESS THAN (TO_DATE('2019-01-01','yyyy-mm-dd')) EXTERNAL LOCATION ('sales_2018.txt'),
PARTITION sales_2019 VALUES LESS THAN (TO_DATE('2020-01-01','yyyy-mm-dd'))
);
Table created.
查看是否为混合分区表:
SYS@PROD4> r
1* select table_name,partitioned, hybrid from dba_tables where owner='SYS' and table_name='HYBRID_PARTITION_TABLE'
TABLE_NAME PAR HYB
------------------------------ --- ---
HYBRID_PARTITION_TABLE YES YES
混合分区表的DML 操作
SYS@PROD4> insert into hybrid_partition_table values('EAST', to_date('20190101','yyyy-mm-dd'),1032);
1 row created.
SYS@PROD4> insert into hybrid_partition_table values('EAST', to_date('20190102','yyyy-mm-dd'),2371);
1 row created.
SYS@PROD4> delete from HYBRID_PARTITION_TABLE partition (sales_2019);
2 rows deleted.
SYS@PROD4> insert into hybrid_partition_table values('EAST', to_date('20160102','yyyy-mm-dd'),2371);
insert into hybrid_partition_table values('EAST', to_date('20160102','yyyy-mm-dd'),2371)
*
ERROR at line 1:
ORA-14466: Data in a read-only partition or subpartition cannot be modified.
SYS@PROD4> delete from HYBRID_PARTITION_TABLE partition (sales_2016);
delete from HYBRID_PARTITION_TABLE partition (sales_2016)
*
ERROR at line 1:
ORA-14354: operation not supported for a hybrid-partitioned table