根据日期来进行oracle partition table 测试

测试说明：

   hr.employees为oracle安装时的example表，默认在examples表空间中。

 

一、测试环境：

数据库版本：Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod

       使用工具：plsql 7.1

二、步骤

    测试采用了范围分区的方法，表使用了在线重建的方式

1. 创建备份表hr.employees_test

选择hr.employees测试，创建测试表hr.employees_test

 

create table HR.EMPLOYEES_TEST as select * from hr.EMPLOYEES;

 

-- Create/Recreate check constraints

alter table HR.EMPLOYEES_TEST

  add constraint EMP_TEST_EMAIL_NN

  check ("EMAIL" IS NOT NULL);

alter table HR.EMPLOYEES_TEST

  add constraint EMP_TEST_HIRE_DATE_NN

  check ("HIRE_DATE" IS NOT NULL);

alter table HR.EMPLOYEES_TEST

  add constraint EMP_TEST_JOB_NN

  check ("JOB_ID" IS NOT NULL);

alter table HR.EMPLOYEES_TEST

  add constraint EMP_TEST_LAST_NAME_NN

  check ("LAST_NAME" IS NOT NULL);

alter table HR.EMPLOYEES_TEST

  add constraint EMP_TEST_SALARY_MIN

  check (salary > 0);

 

 

-- Create/Recreate primary, unique and foreign key constraints

alter table EMPLOYEES_TEST

  add constraint EMP_TEST_EMP_ID_PK primary key (EMPLOYEE_ID)

  using index

  tablespace USERS

  pctfree 10

  initrans 2

  maxtrans 255

  storage

  (

    initial 64K

    minextents 1

    maxextents unlimited

  );

alter table EMPLOYEES_TEST

  add constraint EMP_TEST_EMAIL_UK unique (EMAIL)

  using index

  tablespace USERS

  pctfree 10

  initrans 2

  maxtrans 255

  storage

  (

    initial 64K

    minextents 1

    maxextents unlimited

  );

 

-- Create/Recreate indexes

create index HR.EMP_TEST_DEPARTMENT_IX on HR.EMPLOYEES_TEST (DEPARTMENT_ID)

  tablespace USERS

  pctfree 10

  initrans 2

  maxtrans 255

  storage

  (

    initial 64K

    minextents 1

    maxextents unlimited

  );

create index HR.EMP_TEST_JOB_IX on HR.EMPLOYEES_TEST (JOB_ID)

  tablespace USERS

  pctfree 10

  initrans 2

  maxtrans 255

  storage

  (

    initial 64K

    minextents 1

    maxextents unlimited

  );

create index HR.EMP_TEST_MANAGER_IX on HR.EMPLOYEES_TEST (MANAGER_ID)

  tablespace USERS

  pctfree 10

  initrans 2

  maxtrans 255

  storage

  (

    initial 64K

    minextents 1

    maxextents unlimited

  );

create index HR.EMP_TEST_NAME_IX on HR.EMPLOYEES_TEST (LAST_NAME, FIRST_NAME)

  tablespace USERS

  pctfree 10

  initrans 2

  maxtrans 255

  storage

  (

    initial 64K

    minextents 1

    maxextents unlimited

  );

 

2. 准备测试数据

以字段HIRE_DATE为PARTITION KEY，解除表中对该字段的约束，方便测试null值

 

-- Add/modify columns

alter table HR.EMPLOYEES_TEST modify HIRE_DATE null;

 

alter table HR.EMPLOYEES_TEST

  disable constraint EMP_TEST_HIRE_DATE_NN;

 

插入测试数据：

insert into hr.employees_test (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)

values (999, 'guo', 'zongqing', 'ggggg', '110', null, 'SH_CLERK', 3000.00, null, 124, 50);

 

创建TS_NULL表空间，用来存放HIRE_DATE为空的数据，（下面的datafile中的/u02/oradata/ORCL/datafile/是你当前系统Oracle的数据文件存放地址）

 

CREATE SMALLFILE TABLESPACE "TS_NULL"

DATAFILE '/u02/oradata/ORCL/datafile/null01' SIZE 100M

AUTOEXTEND ON NEXT 1M

MAXSIZE UNLIMITED

LOGGING EXTENT MANAGEMENT

LOCAL SEGMENT

SPACE MANAGEMENT AUTO

 

创建TS_21TH表空间，用来HIRE_DATE在2000以后的数据

CREATE SMALLFILE TABLESPACE "TS_21TH"

DATAFILE '/u02/oradata/ORCL/datafile/21th' SIZE 100M

AUTOEXTEND ON NEXT 1M

MAXSIZE UNLIMITED

LOGGING EXTENT MANAGEMENT

LOCAL SEGMENT

SPACE MANAGEMENT AUTO

 

3. 创建分区表  hr.employees_p

 

CREATE TABLE hr.EMPLOYEES_P

( 

  EMPLOYEE_ID    NUMBER(6) not null,

  FIRST_NAME     VARCHAR2(20),

  LAST_NAME      VARCHAR2(25),

  EMAIL          VARCHAR2(25),

  PHONE_NUMBER   VARCHAR2(20),

  HIRE_DATE      DATE,

  JOB_ID         VARCHAR2(10),

  SALARY         NUMBER(8,2),

  COMMISSION_PCT NUMBER(2,2),

  MANAGER_ID     NUMBER(6),

  DEPARTMENT_ID  NUMBER(4)

)

PARTITION BY RANGE(HIRE_DATE)

       (PARTITION HIRE_DATE_P1 VALUES LESS THAN

(TO_DATE('1990-1-1','YYYY-MM-DD')) TABLESPACE USERS,

        PARTITION HIRE_DATE_P2 VALUES LESS THAN

(TO_DATE('1995-1-1','YYYY-MM-DD')) TABLESPACE USERS,

        PARTITION HIRE_DATE_P3 VALUES LESS THAN

(TO_DATE('2000-1-1','YYYY-MM-DD')) TABLESPACE USERS,

        PARTITION HIRE_DATE_21TH VALUES LESS THAN

(TO_DATE('2005-1-1','YYYY-MM-DD')) TABLESPACE TS_21TH,

        PARTITION HIRE_DATE_NULL VALUES LESS THAN (MAXVALUE)

TABLESPACE TS_NULL);

 

4. 检测是否能够在线分区 

exec DBMS_REDEFINITION.CAN_REDEF_TABLE

('hr','EMPLOYEES_TEST',DBMS_REDEFINITION.CONS_USE_PK);

 

5. 开始数据重组和重定义过程

 EXEC DBMS_REDEFINITION.START_REDEF_TABLE('hr',

'EMPLOYEES_TEST','EMPLOYEES_P');

 

6. 给分区表建立PK、INDEX、CHECK、TIGER等并赋予权限  这步有点问题 

   但不影响测试

 

7. 结束数据重组和重定义

EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('hr',

'EMPLOYEES_TEST','EMPLOYEES_P');

 

8. 删除临时表

drop table hr.employees_p ;

 

三、测试结果

 

    select a.partition_name,a.partition_position,a.tablespace_name from dba_tab_partitions a WHERE a.table_owner = 'HR';

结果：

 

SELECT * FROM hr.employees_test partition(HIRE_DATE_P1);

结果：

 

SELECT * FROM hr.employees_test partition(HIRE_DATE_P2);

SELECT * FROM hr.employees_test partition(HIRE_DATE_P3);

SELECT * FROM hr.employees_test partition(HIRE_DATE_21TH);

 

 

SELECT * FROM hr.employees_test partition(HIRE_DATE_NULL);

结果：

 

插入新数据：

insert into hr.employees_test (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)

values (888, 'guo1', 'zongqing1', 'ggggg1', '1101', null, 'SH_CLERK', 30001.00, null, 124, 50);

 

插入到分区表中。

 

 

 

最后需要注意的是：

如果要更新数据，则需开启row movement属性，oracle是如下描述的：

When you create (or alter) a partitioned table, a row movement clause, either ENABLE ROW MOVEMENT or DISABLE ROW MOVEMENT can be specified. This clause either enables or disables the migration of a row to a new partition if its key is updated. The default is DISABLE ROW MOVEMENT.

如果还想新增分区，最好分区没有MAXVALUE则用普通的alter ... add partition

如果最后分区有MAXVALUE，要用SPLIT PARTITION，语句如下：

ALTER TABLE hr.EMPLOYEES_P SPLIT PARTITION HIRE_DATE_NULL AT (TO_DATE('2006-1-1','YYYY-MM-DD')) INTO (PARTITION HIRE_DATE_NEW TABLESPACE TS_21TH, PARTITION HIRE_DATE_NULL);

把HIRE_DATE_NULL分区SPLIT出一个分区HIRE_DATE_NEW，并指定表空间为TS_21TH