oracle分區(qū)表學(xué)習(xí)及應(yīng)用
2024-08-29 13:48:45
供稿：網(wǎng)友
-- Create table(創(chuàng)建分區(qū)表)
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; --增加分區(qū)表 alter table BILL_MONTHFEE_ZERO add Partition p_200409
values less than (200409) tablespace ts_ziken;
--刪除一分區(qū)
alter table part_tbl drop Partition part_tbl_08; --將一個分區(qū)分為兩個分區(qū)
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); --合并分區(qū)
ALTER TABLE bill_monthfee_zero
MERGE PARTITIONS p_200408, p_200409 INTO PARTITION p_all --將分區(qū)改名alter table bill_monthfee_zero rename Partition p_200408 to p_fee_200408 --將分區(qū)改表空間alter table bill_monthfee_zero move Partition p_200409
tablespace ts_ziken_01 nologging --查詢特定分區(qū)
select count(*) from BILL_MONTHFEE_ZERO partition (p_200407); --添加數(shù)據(jù)
insert into bill_monthfee_zero select * from bill_monthfee_zero partition (p_200407) --分區(qū)表的導(dǎo)出userid=dxsq/teledoone@jndxsq154
buffer=102400
tables=bill_monthfee:P_200401,
file=E:/eXP_para/exp_dxsq_tables.dmp
log=E:/exp_para/exp_dxsq_tables.log 技巧：刪除表中一個字段：alter table bill_monthfee_zero set unused column date_type;添加一個字段：alter table bill_monthfee_zero add date_type number(1);
