( invoice_no NUMBER,
sale_year INT NOT NULL,
sale_month INT NOT NULL,
sale_day INT NOT NULL ) PARTITION BY RANGE (sale_year, sale_month, sale_day)
( PARTITION sales_q1 VALUES LESS THAN (1999, 04, 01)
TABLESPACE tsa,
PARTITION sales_q2 VALUES LESS THAN (1999, 07, 01)
TABLESPACE tsb,
PARTITION sales_q3 VALUES LESS THAN (1999, 10, 01)
TABLESPACE tsc,
PARTITION sales_q4 VALUES LESS THAN (2000, 01, 01)
TABLESPACE tsd );
(
deptname varchar2(20),
quarterly_sales number(10, 2),
state varchar2(2))
PARTITION BY LIST (state)
(PARTITION q1_northwest VALUES ('OR', 'WA')tablespace t1,
PARTITION q1_southwest VALUES ('AZ', 'UT', 'NM')tablespace t2,
PARTITION q1_northeast VALUES ('NY', 'VM', 'NJ')tablespace t3,
PARTITION q1_southeast VALUES ('FL', 'GA')tablespace t4,
PARTITION q1_northcentral VALUES ('SD', 'WI')tablespace t5,
PARTITION q1_southcentral VALUES ('OK', 'TX')tablespace t6);
state='OR'或者是state='WA'时,它就位于q1_northwest 分区中,
t.transaction_id=1;
create table BILL_MONTHFEE_ZERO
(
SERV_ID NUMBER(20) not null,
BILLING_CYCLE_MONTH NUMBER(6) not null,
DATE_TYPE NUMBER(1),
ACC_NBR VARCHAR2(80)
)
partition by range (BILLING_CYCLE_MONTH)
(partition p_200407 values less than (200407)
tablespace TS_ZIKEN
storage(initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0),
partition p_200408 values less than (200408)
tablespace TS_ZIKEN
storage(initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0))
;
create index idx_bill_monthfee_zero_idx01 on bill_monthfee_zero(billing_cycle_month)
tablespace TS_ZIKEN_idx
storage(initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0) nologging;
grant all on bill_monthfee_zero to dxsq_dev;
--增加分区表
alter table BILL_MONTHFEE_ZERO add Partition p_200409
values less than (200409) tablespace ts_ziken;
--删除一分区
alter table part_tbl drop Partition part_tbl_08;
--将一个分区分为两个分区
alter table bill_monthfee_zero split Partition p_200409 at (200409)
into (Partition p_200409_1 tablespace ts_ziken,
Partition p_200409_2 tablespace ts_ziken_idx);
--合并分区
ALTER TABLE bill_monthfee_zero
MERGE PARTITIONS p_200408, p_200409 INTO PARTITION p_all
--将分区改名
alter table bill_monthfee_zero rename Partition p_200408 to p_fee_200408
--将分区改表空间
alter table bill_monthfee_zero move Partition p_200409
tablespace ts_ziken_01 nologging
--查询特定分区
select count(*) from BILL_MONTHFEE_ZERO partition (p_200407);
--添加数据
insert into bill_monthfee_zero select * from bill_monthfee_zero partition (p_200407)
--分区表的导出
userid=dxsq/teledoone@jndxsq154
buffer=102400
tables=bill_monthfee:P_200401,
file=E:exp_paraexp_dxsq_tables.dmp
log=E:exp_paraexp_dxsq_tables.log
技巧:
删除表中一个字段:
alter table bill_monthfee_zero set unused column date_type;
添加一个字段:alter table bill_monthfee_zero add date_type number(1);
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9274353/viewspace-915978/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9274353/viewspace-915978/