--创建用户Credit
CREATE USER credit IDENTIFIED BY pass
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;

--向用户Credit授权
GRANT CREATE SESSION TO credit;
GRANT CREATE TABLE TO credit;
GRANT CREATE SEQUENCE TO credit;
GRANT CREATE USER, ALTER USER, DROP USER TO credit;

--向用户credit授予表空间users的配额
ALTER USER credit QUOTA 100m ON users;

--首先为12个分区创建各自的表空间，同时创建对应的数据文件,在实际应用中应该将不同分区的表空间分配到不同的磁盘驱动器上。
CREATE TABLESPACE credittbs1
DATAFILE 'D:\app\Wesley\oradata\Wesley\CreditDataFile01.DBF' SIZE 50m;
CREATE TABLESPACE credittbs2
DATAFILE 'D:\app\Wesley\oradata\Wesley\CreditDataFile02.DBF' SIZE 50m;
CREATE TABLESPACE credittbs3
DATAFILE 'D:\app\Wesley\oradata\Wesley\CreditDataFile03.DBF' SIZE 50m;
CREATE TABLESPACE credittbs4
DATAFILE 'D:\app\Wesley\oradata\Wesley\CreditDataFile04.DBF' SIZE 50m;
CREATE TABLESPACE credittbs5
DATAFILE 'D:\app\Wesley\oradata\Wesley\CreditDataFile05.DBF' SIZE 50m;
CREATE TABLESPACE credittbs6
DATAFILE 'D:\app\Wesley\oradata\Wesley\CreditDataFile06.DBF' SIZE 50m;
CREATE TABLESPACE credittbs7
DATAFILE 'D:\app\Wesley\oradata\Wesley\CreditDataFile07.DBF' SIZE 50m;
CREATE TABLESPACE credittbs8
DATAFILE 'D:\app\Wesley\oradata\Wesley\CreditDataFile08.DBF' SIZE 50m;
CREATE TABLESPACE credittbs9
DATAFILE 'D:\app\Wesley\oradata\Wesley\CreditDataFile09.DBF' SIZE 50m;
CREATE TABLESPACE credittbs10
DATAFILE 'D:\app\Wesley\oradata\Wesley\CreditDataFile10.DBF' SIZE 50m;
CREATE TABLESPACE credittbs11
DATAFILE 'D:\app\Wesley\oradata\Wesley\CreditDataFile11.DBF' SIZE 50m;
CREATE TABLESPACE credittbs12
DATAFILE 'D:\app\Wesley\oradata\Wesley\CreditDataFile12.DBF' SIZE 50m;

--创建表空间后还需要向用户credit授予表空间的配额，这样才能在创建分区表时使用上面创建的表空间。
ALTER USER credit QUOTA 50m ON creditTbs1;
ALTER USER credit QUOTA 50m ON creditTbs2;
ALTER USER credit QUOTA 50m ON creditTbs3;
ALTER USER credit QUOTA 50m ON creditTbs4;
ALTER USER credit QUOTA 50m ON creditTbs5;
ALTER USER credit QUOTA 50m ON creditTbs6;
ALTER USER credit QUOTA 50m ON creditTbs7;
ALTER USER credit QUOTA 50m ON creditTbs8;
ALTER USER credit QUOTA 50m ON creditTbs9;
ALTER USER credit QUOTA 50m ON creditTbs10;
ALTER USER credit QUOTA 50m ON creditTbs11;
ALTER USER credit QUOTA 50m ON creditTbs12;

--创建分区表
CREATE TABLE credit2009
(
    consumeID   NUMBER,
    cardno  VARCHAR2(50),
    shop    VARCHAR2(50),
    goods VARCHAR2(50),
    amount FLOAT,
    consumeDate DATE
)
PARTITION BY RANGE(consumeDate)
(
    PARTITION partition1  VALUES LESS THAN (TO_DATE('2013-02-01', 'YYYY-MM-DD')) TABLESPACE creditTbs1,
    PARTITION partition2  VALUES LESS THAN (TO_DATE('2013-03-01', 'YYYY-MM-DD')) TABLESPACE creditTbs2,
    PARTITION partition3  VALUES LESS THAN (TO_DATE('2013-04-01', 'YYYY-MM-DD')) TABLESPACE creditTbs3,
    PARTITION partition4  VALUES LESS THAN (TO_DATE('2013-05-01', 'YYYY-MM-DD')) TABLESPACE creditTbs4,
    PARTITION partition5  VALUES LESS THAN (TO_DATE('2013-06-01', 'YYYY-MM-DD')) TABLESPACE creditTbs5,
    PARTITION partition6  VALUES LESS THAN (TO_DATE('2013-07-01', 'YYYY-MM-DD')) TABLESPACE creditTbs6,
    PARTITION partition7  VALUES LESS THAN (TO_DATE('2013-08-01', 'YYYY-MM-DD')) TABLESPACE creditTbs7,
    PARTITION partition8  VALUES LESS THAN (TO_DATE('2013-09-01', 'YYYY-MM-DD')) TABLESPACE creditTbs8,
    PARTITION partition9  VALUES LESS THAN (TO_DATE('2013-10-01', 'YYYY-MM-DD')) TABLESPACE creditTbs9,
    PARTITION partition10 VALUES LESS THAN (TO_DATE('2013-11-01', 'YYYY-MM-DD')) TABLESPACE creditTbs10,
    PARTITION partition11 VALUES LESS THAN (TO_DATE('2013-12-01', 'YYYY-MM-DD')) TABLESPACE creditTbs11,
    PARTITION partition12 VALUES LESS THAN (MAXVALUE) TABLESPACE creditTbs12
);

--创建全局范围分区索引

CREATE INDEX ix_consume2009 ON credit2009(consumeDate)
GLOBAL PARTITION BY RANGE(consumeDate)
(
    PARTITION partition1  VALUES LESS THAN (TO_DATE('2013-02-01', 'YYYY-MM-DD')) TABLESPACE creditTbs1,
    PARTITION partition2  VALUES LESS THAN (TO_DATE('2013-03-01', 'YYYY-MM-DD')) TABLESPACE creditTbs2,
    PARTITION partition3  VALUES LESS THAN (TO_DATE('2013-04-01', 'YYYY-MM-DD')) TABLESPACE creditTbs3,
    PARTITION partition4  VALUES LESS THAN (TO_DATE('2013-05-01', 'YYYY-MM-DD')) TABLESPACE creditTbs4,
    PARTITION partition5  VALUES LESS THAN (TO_DATE('2013-06-01', 'YYYY-MM-DD')) TABLESPACE creditTbs5,
    PARTITION partition6  VALUES LESS THAN (TO_DATE('2013-07-01', 'YYYY-MM-DD')) TABLESPACE creditTbs6,
    PARTITION partition7  VALUES LESS THAN (TO_DATE('2013-08-01', 'YYYY-MM-DD')) TABLESPACE creditTbs7,
    PARTITION partition8  VALUES LESS THAN (TO_DATE('2013-09-01', 'YYYY-MM-DD')) TABLESPACE creditTbs8,
    PARTITION partition9  VALUES LESS THAN (TO_DATE('2013-10-01', 'YYYY-MM-DD')) TABLESPACE creditTbs9,
    PARTITION partition10 VALUES LESS THAN (TO_DATE('2013-11-01', 'YYYY-MM-DD')) TABLESPACE creditTbs10,
    PARTITION partition11 VALUES LESS THAN (TO_DATE('2013-12-01', 'YYYY-MM-DD')) TABLESPACE creditTbs11,
    PARTITION partition12 VALUES LESS THAN (MAXVALUE) TABLESPACE creditTbs12
);

--创建本地分区索引
CREATE INDEX ix_consumedate_local ON credit2009(consumeDate) LOCAL ;


--创建散列(hash)分区
CREATE TABLESPACE tbsLog1
DATAFILE 'D:\app\Wesley\oradata\Wesley\LogDataFile01.DBF' SIZE 50m;
CREATE TABLESPACE tbsLog2
DATAFILE 'D:\app\Wesley\oradata\Wesley\LogDataFile02.DBF' SIZE 50m;
CREATE TABLESPACE tbsLog3
DATAFILE 'D:\app\Wesley\oradata\Wesley\LogDataFile04.DBF' SIZE 50m;

CREATE TABLE hashPartitionLog
(
    logId   NUMBER,
    logTest VARCHAR2(4000),
    logDate DATE
)
PARTITION BY HASH(logID)
(
    PARTITION partition1 TABLESPACE tbsLog1,
    PARTITION partition2 TABLESPACE tbsLog2,
    PARTITION partition3 TABLESPACE tbsLog3
);

--创建全局散列分区索引
CREATE INDEX ix_logid
ON hashPartitionLog(logID)
GLOBAL PARTITION BY HASH(logID)
(
    PARTITION partition1 TABLESPACE tbsLog1,
    PARTITION partition2 TABLESPACE tbsLog2,
    PARTITION partition3 TABLESPACE tbsLog3
);

--创建列表(list)分区表
CREATE TABLESPACE tbsMarket1
DATAFILE 'D:\app\Wesley\oradata\Wesley\MarketDataFile01.DBF' SIZE 50m;
CREATE TABLESPACE tbsMarket2
DATAFILE 'D:\app\Wesley\oradata\Wesley\MarketDataFile02.DBF' SIZE 50m;
CREATE TABLESPACE tbsMarket3
DATAFILE 'D:\app\Wesley\oradata\Wesley\MarketDataFile03.DBF' SIZE 50m;
CREATE TABLESPACE tbsMarket4
DATAFILE 'D:\app\Wesley\oradata\Wesley\MarketDataFile04.DBF' SIZE 50m;

CREATE TABLE saleMarket
(
    areaID      NUMBER,
    areaName    VARCHAR2(4000),
    description DATE
)
PARTITION BY LIST(areaName)
(
    PARTITION part_db VALUES('黑龙江', '吉林', '辽宁') TABLESPACE tbsMarket1,
    PARTITION part_hb VALUES('北京', '天津', '河北') TABLESPACE tbsMarket2,
    PARTITION part_hn VALUES('广东', '广西', '河南') TABLESPACE tbsMarket3,
    PARTITION part_qt VALUES(DEFAULT) TABLESPACE tbsMarket4
);


--创建范围-散列分区
CREATE TABLESPACE hashTbs1
DATAFILE 'D:\app\Wesley\oradata\Wesley\HashDataFile01.DBF' SIZE 50m;
CREATE TABLESPACE hashTbs2
DATAFILE 'D:\app\Wesley\oradata\Wesley\HashDataFile02.DBF' SIZE 50m;
CREATE TABLESPACE hashTbs3
DATAFILE 'D:\app\Wesley\oradata\Wesley\HashDataFile03.DBF' SIZE 50m;

ALTER USER credit QUOTA 50m ON hashTbs1;
ALTER USER credit QUOTA 50m ON hashTbs2;
ALTER USER credit QUOTA 50m ON hashTbs3;

CREATE TABLE credit2010
(
    consumeID   NUMBER,
    cardNO  VARCHAR2(50),
    shop    VARCHAR2(50),
    goods   VARCHAR2(50),
    amount  FLOAT,
    consumeDate DATE
)
PARTITION BY RANGE(consumeDate)
SUBPARTITION BY HASH(consumeID)
SUBPARTITIONS 3 STORE IN(hashTbs1, hashTbs2, hashTbs3)
(
    PARTITION partition1  VALUES LESS THAN (TO_DATE('2013-02-01', 'YYYY-MM-DD')) TABLESPACE creditTbs1,
    PARTITION partition2  VALUES LESS THAN (TO_DATE('2013-03-01', 'YYYY-MM-DD')) TABLESPACE creditTbs2,
    PARTITION partition3  VALUES LESS THAN (TO_DATE('2013-04-01', 'YYYY-MM-DD')) TABLESPACE creditTbs3,
    PARTITION partition4  VALUES LESS THAN (TO_DATE('2013-05-01', 'YYYY-MM-DD')) TABLESPACE creditTbs4,
    PARTITION partition5  VALUES LESS THAN (TO_DATE('2013-06-01', 'YYYY-MM-DD')) TABLESPACE creditTbs5,
    PARTITION partition6  VALUES LESS THAN (TO_DATE('2013-07-01', 'YYYY-MM-DD')) TABLESPACE creditTbs6,
    PARTITION partition7  VALUES LESS THAN (TO_DATE('2013-08-01', 'YYYY-MM-DD')) TABLESPACE creditTbs7,
    PARTITION partition8  VALUES LESS THAN (TO_DATE('2013-09-01', 'YYYY-MM-DD')) TABLESPACE creditTbs8,
    PARTITION partition9  VALUES LESS THAN (TO_DATE('2013-10-01', 'YYYY-MM-DD')) TABLESPACE creditTbs9,
    PARTITION partition10 VALUES LESS THAN (TO_DATE('2013-11-01', 'YYYY-MM-DD')) TABLESPACE creditTbs10,
    PARTITION partition11 VALUES LESS THAN (TO_DATE('2013-12-01', 'YYYY-MM-DD')) TABLESPACE creditTbs11,
    PARTITION partition12 VALUES LESS THAN (MAXVALUE) TABLESPACE creditTbs12
);

--在单个范围分区上创建散列分区
CREATE TABLE credit2011
(
    consumeID   NUMBER,
    cardNO  VARCHAR2(50),
    shop    VARCHAR2(50),
    goods   VARCHAR2(50),
    amount  FLOAT,
    consumeDate DATE
)
PARTITION BY RANGE(consumeDate)
SUBPARTITION BY HASH(consumeID)
(
    PARTITION partition1  VALUES LESS THAN (TO_DATE('2013-02-01', 'YYYY-MM-DD')) TABLESPACE creditTbs1,
    PARTITION partition2  VALUES LESS THAN (TO_DATE('2013-03-01', 'YYYY-MM-DD')) TABLESPACE creditTbs2,
    PARTITION partition3  VALUES LESS THAN (TO_DATE('2013-04-01', 'YYYY-MM-DD')) TABLESPACE creditTbs3
    (
        SUBPARTITION sub_part_1 TABLESPACE hashTbs1,
        SUBPARTITION sub_part_2 TABLESPACE hashTbs2,
        SUBPARTITION sub_part_3 TABLESPACE hashTbs3
    ),
    PARTITION partition4  VALUES LESS THAN (TO_DATE('2013-05-01', 'YYYY-MM-DD')) TABLESPACE creditTbs4,
    PARTITION partition5  VALUES LESS THAN (TO_DATE('2013-06-01', 'YYYY-MM-DD')) TABLESPACE creditTbs5,
    PARTITION partition6  VALUES LESS THAN (TO_DATE('2013-07-01', 'YYYY-MM-DD')) TABLESPACE creditTbs6,
    PARTITION partition7  VALUES LESS THAN (TO_DATE('2013-08-01', 'YYYY-MM-DD')) TABLESPACE creditTbs7,
    PARTITION partition8  VALUES LESS THAN (TO_DATE('2013-09-01', 'YYYY-MM-DD')) TABLESPACE creditTbs8,
    PARTITION partition9  VALUES LESS THAN (TO_DATE('2013-10-01', 'YYYY-MM-DD')) TABLESPACE creditTbs9,
    PARTITION partition10 VALUES LESS THAN (TO_DATE('2013-11-01', 'YYYY-MM-DD')) TABLESPACE creditTbs10,
    PARTITION partition11 VALUES LESS THAN (TO_DATE('2013-12-01', 'YYYY-MM-DD')) TABLESPACE creditTbs11,
    PARTITION partition12 VALUES LESS THAN (MAXVALUE) TABLESPACE creditTbs12
);

--创建组合范围-列表分区表
CREATE TABLESPACE listTbs1
DATAFILE 'D:\app\Wesley\oradata\Wesley\ListDataFile01.DBF' SIZE 50m;
CREATE TABLESPACE listTbs2
DATAFILE 'D:\app\Wesley\oradata\Wesley\ListDataFile02.DBF' SIZE 50m;
CREATE TABLESPACE listTbs3
DATAFILE 'D:\app\Wesley\oradata\Wesley\ListDataFile03.DBF' SIZE 50m;
CREATE TABLESPACE listTbs4
DATAFILE 'D:\app\Wesley\oradata\Wesley\ListDataFile04.DBF' SIZE 50m;

CREATE TABLE rangeListPartTable
(
    id  NUMBER,
    name    VARCHAR2(4000)
)
PARTITION BY RANGE(id)
SUBPARTITION BY LIST(name)
SUBPARTITION template
(
    SUBPARTITION part_a VALUES('a') TABLESPACE listTbs1,
    SUBPARTITION part_b VALUES('b') TABLESPACE listTbs2,
    SUBPARTITION part_c VALUES('c') TABLESPACE listTbs3,
    SUBPARTITION part_d VALUES(DEFAULT) TABLESPACE listTbs4
)
(
    PARTITION partition1 VALUES LESS THAN(100) TABLESPACE listTbs1,
    PARTITION partition2 VALUES LESS THAN(200) TABLESPACE listTbs2,
    PARTITION partition3 VALUES LESS THAN(300) TABLESPACE listTbs3,
    PARTITION partition4 VALUES LESS THAN(MAXVALUE) TABLESPACE listTbs4,
);


--查询分区表
SELECT * FROM hashPartitionLog PARTITION(partition 1);

--添加表分区
alter table hashPartitionLog
ADD PARTITION partition4 TABLESPACE tbSlog3;

--收缩表分区，只应用于散列分区或者组合范围-散列分区表
ALTER TABLE hashPartitionLog COALESCE PARTITION;

--合并表分区
ALTER TABLE salemarket MERGE PARTITIONS PART_HB, PART_HN
INTO PARTITION part_qt;

--拆分表分区，例如将范围分区表rangePartTable的partition3分成partition3_1和partition3_2
PARTITION partition3 VALUES LESS THAN(300) TABLESPACE listTbs3

ALTER TABLE rangePartTable SPLIT PARTITION partition3
AT(250) INTO (
PARTITION partition3_1 TABLESPACE listTbs3,
PARTITION partition3_2 TABLESPACE listTbs4
);

--拆分列表分区
CREATE TABLE listPartTable
(
    id  NUMBER,
    name VARCHAR2(4000)
)
PARTITION BY LIST(id)
(
    PARTITION partition1 VALUES(1, 2, 3, 4, 5) TABLESPACE listTbs1,
    PARTITION partition2 VALUES(6, 7, 8, 9, 10) TABLESPACE listTbs2,
    PARTITION partition3 VALUES(100, 200, 300, 400) TABLESPACE listTbs3
);

ALTER TABLE listPartTable SPLIT PARTITION partition3
VALUES(100, 200) INTO (
    PARTITION partition3_1 TABLESPACE listTbs3,
    PARTITION partition3_2 TABLESPACE listTbs4
);

--截断表空间
ALTER TABLE hashPartitionLog TRUNCATE PARTITION partition1;

--重命名表分区
ALTER TABLE hashPartitionLog RENAME PARTITION partititon1 TO partitionOne;

--删除表分区，只能对范围分区表，列表分区表和组合范围-列表分区表执行删除分区的操作

--删除分区
ALTER TABLE saleMarcket
DROP PARTITION part_qt;

--删除子分区
ALTER TABLE rangeListPartTable
DROP subpartition partition1_part_d;

--添加索引分区
ALTER INDEX ix_logid
ADD PARTITION partition4 TABLESPACE tbsLog3;

--重新编译索引分区，如果分区表的结构发生了变化，并且在修改分区表时没有使用UPDATE INDEX字句，则很有可能导致该分区表上定义的分区索引无效。此时，必须重新编译索引上的分区。如果全局分区索引，则可能对范围分区索引进行重新编译的操作，如果是本地分区索引则没有这种限制。
ALTER INDEX ix_consume2009
REBUILD PARTITION partition1;

--拆分索引分区，只有为全局范围分区索引执行拆分操作。
ALTER INDEX ix_rangePartTable_id SPLIT PARTITION partition3
AT(250) INTO (
PARTITION partition3_1 TABLESPACE listTbs3,
PARTITION partition3_2 TABLESPACE listTbs4
);

--修改索引分区
ALTER INDEX ix_rangePartTable_id
RENAME PARTITION partition1 TO partitionOne;

--删除索引分区
ALTER INDEX ix_rangePartTable_id
DROP PARTITION partition2


--读《ORACLE 学习笔记：日常应用，深入管理，性能优化》记录