oracle分区表测试

先建三个表空间

create tablespace dinya_space01 datafile 'E:\dinya01.dnf' size 50M autoallocate;

create tablespace dinya_space02 datafile 'E:\dinya02.dnf' size 50M autoallocate;

create tablespace dinya_space03 datafile 'E:\dinya03.dnf' size 50M autoallocate;

建立分区表（按范围存储）

create table kkkk

(

transaction_id number primary key,

item_id number(8) not null,

item_description varchar2(300),

transaction_date date not null

)

partition by range (transaction_id)

(

partition part_01 values less than(30000000) tablespace dinya_space01,

partition part_02 values less than(60000000) tablespace dinya_space02,

partition part_03 values less than(maxvalue) tablespace dinya_space03 )

建立分区表（按日期存储）

create table PPP

(

transaction_id number primary key,

item_id number(8) not null,

item_description varchar2(300),

transaction_date date not null

)

partition by range (transaction_date)

(

partition part_01 values less than(to_date('2006-01-01','yyyy-mm-dd'))

tablespace dinya_space01,

partition part_02 values less than(to_date('2010-01-01','yyyy-mm-dd'))

tablespace dinya_space02,

partition part_03 values less than(maxvalue) tablespace dinya_space03

);

查询分区表

SELECT * FROM useR_TAB_PARTITIONS WHERE TABLE_NAME='KKKK'

select * from user_tables a where a.partitioned='YES'

select * from DBA_PART_TABLES

插入数据

insert into KKKK values(3,12, 'BOOKS',to_date('2006-05-30','yyyy-mm-dd'));

insert into KKKK values(4,12, 'BOOKS',to_date('2007-06-23','yyyy-mm-dd'));

insert into KKKK values(5,12, 'BOOKS',to_date('2007-06-23','yyyy-mm-dd'));

insert into KKKK values(13,12, 'BOOKS',to_date('2006-05-30','yyyy-mm-dd'));

insert into KKKK values(14,12, 'BOOKS',to_date('2007-06-23','yyyy-mm-dd'));

insert into KKKK values(15,12, 'BOOKS',to_date('2007-06-23','yyyy-mm-dd'));

insert into KKKK values(54000000,12, 'BOOKS',to_date('2011-02-26','yyyy-mm-dd'));

insert into KKKK values(54000100,12, 'BOOKS',to_date('2011-02-26','yyyy-mm-dd'));

insert into KKKK values(54001000,12, 'BOOKS',to_date('2011-02-26','yyyy-mm-dd'));

insert into KKKK values(54000010,12, 'BOOKS',to_date('2011-02-26','yyyy-mm-dd'));

insert into KKKK values(54001020,12, 'BOOKS',to_date('2011-02-26','yyyy-mm-dd'));

insert into KKKK values(9999999999999,12, 'BOOKS',to_date('2011-04-30','yyyy-mm-dd'));

insert into KKKK values(9999999199999,12, 'BOOKS',to_date('2011-04-30','yyyy-mm-dd'));

insert into KKKK values(9999999299999,12, 'BOOKS',to_date('2011-04-30','yyyy-mm-dd'));

查询分区数据

select * from KKKK

select * from KKKK partition(part_01);

select * from KKKK partition(part_02);

select * from KKKK partition(part_03);

查询分区是否存在

select * from user_segments where segment_type='TABLE PARTITION' and partition_name like 'PART%'

删除分区表：

此可以彻底删除分区表及分区

drop table KKKK purge;

此只删除分区表但不删除分区

truncate table KKKK;

整个表创建索引：

Create index dinya_idx_t on dinya_test(item_id);

查询所有索引

select *from all_indexes

 

 

注：在删除带有分区表的表空间时必须删除表空间下所有的分区表，否则报错ora-14404

若分区表跨不同表空间，做导出、导入时目标数据库必须预建这些表空间。分区表各区所在表空间在做导入时目标数据库一定要预建这些表空间！这些表空间不一定是用户的默认表空间，只要存在即可。如果有一个不存在，就会报错！

分区表导出脚本：

expdp cawy_cas/1 directory=DIR dumpfile=kkkk.dmp logfile=kkkk.log tables=kkkk parallel=3

在分区表中的所属表空间可以不属于任何用户，但分区表属于某个用户就行

在导入数据时只需预建好目标表空间和相关用户即可，这样分区表就可以顺利导入（不需要单独导入备份文件然后再导入分区表备份文件，只要分区表在整个备份文件某个用户中）