ITPub博客

首页 > 数据库 > Oracle > Oracle分区操作相关语句

Oracle分区操作相关语句

原创 Oracle 作者:OracleNotebook 时间:2007-01-15 19:27:26 0 删除 编辑

  -- Create table(创建分区表)

  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))

  ;

[@more@]

  -- Create table(创建分区表)

  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/8351093/viewspace-890571/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
  • 博文量
    6
  • 访问量
    13040