分区表的建立，访问

范围分区表：

CREATE TABLE sales

(invoice_no NUMBER,

...

sale_date DATE NOT NULL )

PARTITION BY RANGE (sale_date)

(PARTITION sales1999_q1

VALUES LESS THAN (TO_DATE(‘1999-04-01’,’YYYY-MM-DD’)

TABLESPACE ts_sale1999q1,

PARTITION sales1999_q2

VALUES LESS THAN (TO_DATE(‘1999-07-01’,’YYYY-MM-DD’)

TABLESPACE ts_sale1999q2,

PARTITION sales1999_q3

VALUES LESS THAN (TO_DATE(‘1999-10-01’,’YYYY-MM-DD’)

TABLESPACE ts_sale1999q3,

PARTITION sales1999_q4

VALUES LESS THAN (TO_DATE(‘2000-01-01’,’YYYY-MM-DD’)

TABLESPACE ts_sale1999q4 );

索引分区：

CREATE INDEX month_ix ON sales(sales_month)
GLOBAL PARTITION BY RANGE(sales_month)
(PARTITION pm1_ix VALUES LESS THAN (2)
PARTITION pm12_ix VALUES LESS THAN (MAXVALUE));
删除分区：

ALTER TABLE sales DROP PARTION sales1999_q1;

截短分区:

alter table sales truncate partiton sales1999_q2;

合并分区：

alter table sales merge partitons sales1999_q2, sales1999_q3 into sales1999_q23;

alter index ind_t2 rebuild partition p123 parallel 2;

分裂分区：

ALTER TABLE sales

SPLIT PARTITON sales1999_q4

AT TO_DATE (‘1999-11-01’,’YYYY-MM-DD’)

INTO (partition sales1999_q4_p1, partition sales1999_q4_p2) ;

alter table t2 split partition p123 values (1,2) into (partition p12,partition p3);

交换分区:

alter table x exchange partition p0 with table bsvcbusrundatald ;

访问指定分区：

select * from sales partition(sales1999_q2);

具体应用的例子

create table CLIENT_INVOICE
(
CHARGE_NO VARCHAR2(12) not null,
CHARGE_SEQ NUMBER(5) default 0 not null,
COMP_NO VARCHAR2(8) not null,
CHARGE_CODE VARCHAR2(6) not null,
CHARGE_DATE DATE not null,
EQP_STA VARCHAR2(1) not null,
UNIT VARCHAR2(6) not null,
GOOD_TYPE VARCHAR2(6),
REEF_IND VARCHAR2(1) default 'N' not null,
OSZ_IND VARCHAR2(1) default 'N' not null,
DUTY_IND VARCHAR2(1) default 'N' not null,
VAL_IND VARCHAR2(1) default 'N' not null,
HAZ_IMCO VARCHAR2(4) default 'N' not null,
OTH_RMK VARCHAR2(40),
QUANTITY NUMBER(9,2) not null,
RATE NUMBER(9,3) not null,
AMOUNT NUMBER(14,4) not null,
LST_UPD_SEQ NUMBER(12) not null,
LST_TX_DATE DATE not null,
LST_TX_USER VARCHAR2(8) not null,
STATUS VARCHAR2(1) not null,
ACTIVITY VARCHAR2(3),
ARV_TYPE VARCHAR2(1),
REF_ID VARCHAR2(32),
UNIT_PER VARCHAR2(16),
QUANTITY_PER NUMBER(9,3),
INVOICE_NO VARCHAR2(12),
INVOICE_SEQ NUMBER(5),
INVOICE_TYPE VARCHAR2(6),
INVOICE_DATE DATE,
VOUCHER_NO VARCHAR2(10),
VOUCHER_DATE DATE,
REC_NO VARCHAR2(20),
REC_DATE DATE,
REC_AMOUNT NUMBER(12,2),
CNTR VARCHAR2(10),
CHK_DGT VARCHAR2(2),
BL_NO VARCHAR2(20),
VSL VARCHAR2(6),
VVD VARCHAR2(12),
OPR VARCHAR2(7),
ENTRY_DATE DATE,
FREE_TIME DATE,
GOOD_CHARGE VARCHAR2(6),
EXIT_DATE DATE,
EQP_TYPE VARCHAR2(6),
FROM_DATE DATE,
SPC_HDLG VARCHAR2(6),
TRK_CO VARCHAR2(30),
RATETON NUMBER(9,3),
CARGO_PKG NUMBER(9),
CARGO_CBM NUMBER(9,3),
CARGO_WGT NUMBER(9,3),
RATE_PERCENT NUMBER(7,3),
TO_PLC VARCHAR2(20),
MILEAGE NUMBER(6),
SVC VARCHAR2(6),
EQP_SVC VARCHAR2(6),
PREPARE_BY VARCHAR2(8),
GRS_WGT NUMBER(6),
HOLIDAY VARCHAR2(6),
FOREIGN_VVD NUMBER(1),
HAZ_CLASS VARCHAR2(12),
CATEGORY VARCHAR2(2),
CHARGE_SECTION VARCHAR2(10),
PER_METHOD VARCHAR2(1),
DISC_BY VARCHAR2(1),
CREDIT_BY VARCHAR2(1),
CREDIT_PERCENT NUMBER(7,3),
SPLIT_PERCENT NUMBER(7,3),
DISC_PERCENT NUMBER(7,3),
BKG VARCHAR2(20),
FINANCIAL_DATE DATE,
SHPR VARCHAR2(20),
CSGN VARCHAR2(20)
)
partition by range (invoice_date)
(
partition invoice_2001 values less than (TO_DATE('2002-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
tablespace DATA_2001
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 1m
next 1M
minextents 1
maxextents unlimited
),
partition invoice_2002 values less than (TO_DATE('2003-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
tablespace DATA_2002
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 1m
next 1M
minextents 1
maxextents unlimited
),
partition invoice_2003 values less than (TO_DATE('2004-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
tablespace DATA_2003
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 1m
next 1M
minextents 1
maxextents unlimited
),
partition invoice_2004 values less than (TO_DATE('2005-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
tablespace DATA_2004
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 1m
next 1M
minextents 1
maxextents unlimited
),
partition invoice_2005 values less than (TO_DATE('2006-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
tablespace DATA_2005
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 1m
next 1M
minextents 1
maxextents unlimited
),
partition invoice_2006 values less than (TO_DATE('2007-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
tablespace DATA_2006
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 1m
next 1M
minextents 1
maxextents unlimited
)
);

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8748334/viewspace-918597/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8748334/viewspace-918597/