列********************************************************************************

创建分区表

CREATE TABLE ccc1

            ( id number(8),name varchar2(30),sales_date date)

   PARTITION BY RANGE (sales_date)

  (PARTITION p1 VALUES LESS THAN ( TO_DATE(‘01-JAN-1999‘,‘DD-MON-YYYY‘)) tablespace zh,

   PARTITION p2 VALUES LESS THAN ( TO_DATE(‘01-JAN-2000‘,‘DD-MON-YYYY‘)) tablespace zh,

   PARTITION p3 VALUES LESS THAN ( TO_DATE(‘01-JAN-2001‘,‘DD-MON-YYYY‘)) tablespace zh,

   PARTITION p4 VALUES LESS THAN ( TO_DATE(‘01-JAN-2002‘,‘DD-MON-YYYY‘)) tablespace zh,

   PARTITION p5 VALUES LESS THAN (MAXVALUE) tablespace zh

  );

插入数据

 insert into ccc1 values(1,‘PC1‘,to_date(‘17-FEB-2000‘,‘DD-MON-YYYY‘));

 insert into ccc1 values(2,‘PC2‘,to_date(‘17-FEB-2001‘,‘DD-MON-YYYY‘));

 insert into ccc1 values(3,‘PC3‘,‘17-FEB-1999‘);

 insert into ccc1 values(4,‘PC4‘,‘15-FEB-2000‘);

 insert into ccc1 values(5,‘PC5‘,‘17-FEB-2002‘);

 insert into ccc1 values(6,‘PC6‘,‘12-FEB-2001‘);

insert into ccc1 values(7,‘PC7‘,‘11-FEB-1998‘);

 insert into ccc1 values(8,‘PC8‘,‘13-FEB-2002‘);

SQL> insert into ccc1 values(8,‘PC8‘,‘13-FEB-2013‘);

创建唯一索引

CREATE unique INDEX i_ccc1_id ON ccc1(id) GLOBAL

PARTITION BY HASH (id)

(PARTITION pi1  TABLESPACE zh,

 PARTITION pi2  TABLESPACE zh,

 PARTITION pi3  TABLESPACE zh,

 PARTITION pi4  TABLESPACE zh) parallel 4;

创建失败有重复值 --去重查询id相同的为重复但rowid都是不同的。以此去重。

SQL> select a.* from ccc1 a where a.rowid in(select min(b.rowid) from ccc1 b where b.id=a.id);

查询重复数据

SQL> select a.* from ccc1 a where a.rowid not in(select min(b.rowid) from ccc1 b where b.id=a.id); 

删除重复数据

SQL> delete from ccc1 a where a.rowid not in(select min(b.rowid) from ccc1 b where b.id=a.id);   

再创建索引 全局唯一

CREATE unique INDEX i_ccc1_id ON ccc1(id) GLOBAL

PARTITION BY HASH (id)

(PARTITION pi1  TABLESPACE zh,

 PARTITION pi2  TABLESPACE zh,

 PARTITION pi3  TABLESPACE zh,

 PARTITION pi4  TABLESPACE zh) parallel 4;       

创建局部分区索引

SQL> create index i_ccc1_date on ccc1(sales_date) local;              

创建不分区索引

SQL> create index i_ccc1_name on ccc1(name);

查询索引信息

SQL> select index_name,index_type,table_name,tablespace_name,partitioned from dba_indexes where table_name =‘CCC1‘;

添加分区

SQL> alter table ccc1 add partition p6 values less than (to_date(‘2019-01-01‘,‘yyyy-mm-dd‘)) tablespace zh;

alter table ccc1 add partition p6values less than (to_date(‘2019-01-01‘,‘yyyy-mm-dd‘)) tablespace zh

                               *

ERROR at line 1:

ORA-14074: partition bound must collate higher than that of the last partition

报错因为前面用过PARTITION p5 VALUES LESS THAN (MAXVALUE)所以有俩种办法解决

第一删除p5重新添加

Alter table ccc1 droppartition p5;

第二拆分分区 :split  合并分区gerge

查询分区表

SQL> SELECT TABLESPACE_NAME,TABLE_NAME, PARTITION_NAME

FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME=‘CCC1‘

ORDER BY TABLESPACE_NAME;

本文出自 “君落尘的博客” 博客，请务必保留此出处http://lchen.blog.51cto.com/9200824/1538162