oracle创建分区表

这里的第一种方式创建分区表是最近更改程序时学会的，哈哈

-- 通过create table tablename as select * from tablename (指定分区)

 create table scott.tp_comp_user_day_20100601
       partition by list (area_cd)
      (
        partition P0451 values (0451) tablespace TBs_DATA,
        partition P0452 values (0452) tablespace TBS_DATA,
        partition P0453 values (0453) tablespace TBS_DATA,
        partition P0454 values (0454) tablespace TBS_DATA,
        partition P0455 values (0455) tablespace TBS_DATA,
        partition P0456 values (0456) tablespace TBS_DATA,
        partition P0457 values (0457) tablespace TBS_DATA,
        partition P0458 values (0458) tablespace TBS_DATA,
        partition P0459 values (0459) tablespace TBS_DATA,
        partition P0464 values (0464) tablespace TBS_DATA,
        partition P0467 values (0467) tablespace TBS_DATA,
        partition P0468 values (0468) tablespace TBS_DATA,
        partition P0469 values (0469) tablespace TBS_DATA,
        partition P0000 values (-1)tablespace TBS_DATA
        )
as  select
      accs_nbr,
      201006,
      bill_month,
      comp_id,
      area_cd,
      prov_id,
      long_type,
      brand_cd,
      std_comp_prd_type_cd,
      last_call_date,
      this_call_date,
      latest_in_date,
      latest_out_date,
      is_arrive,
      is_new
   from scott.tp_comp_user_p_day_cur  t
  where t.stat_date = 20100601
;
commit;

-- 正常方式

-- create table
create table tp_serv_day
(
  date_cd                varchar2(8),
  serv_id                number(12),
  acc_nbr                varchar2(30),
  physical_number        varchar2(32),
  sim_id                 varchar2(20),
  cdma_nbr_head_type     varchar2(5),
  os_sts                 varchar2(50),
  cde_serv_state_cd      varchar2(15),
  std_serv_state_cd      number(15),
  state_date             date,
  state_month            number(9),
  cde_corp_user_cd       varchar2(14),
  std_corp_user_cd       number(15),
  cde_user_type_cd       varchar2(15),
  std_user_type_cd       number(15),
  cde_prd_id             varchar2(20),
  std_prd_id             number(15),
  cde_ofr_id             varchar2(20),
  std_ofr_id             number(15),
  cde_urban_flag_cd      varchar2(10),
  std_urban_flag_cd      number(15),
  cde_pay_meth_cd        varchar2(18),
  std_pay_meth_cd        number(15),
  if_convergent_prod     number(1),
  group_no               number(12),
  group_ofr_id           number(15),
  std_group_ofr_id       number(15),
  tele_emp_flag          number(1),
  acct_id                number(12),
  user_name              varchar2(250),
  prd_address            varchar2(100),
  user_unit              varchar2(100),
  user_contact_nbr       varchar2(20),
  age                    integer,
  std_gender_code        number(15),
  exch_id                varchar2(30),
  connect_box_id         varchar2(50),
  res_zone_id            varchar2(50),
  emp_id                 varchar2(64),
  cde_channel_type_cd    varchar2(10),
  std_channel_type_cd    number(15),
  cde_so_channel_type_cd varchar2(10),
  std_so_channel_type_cd number(15),
  cust_id                number(12),
  cde_cust_type_id       varchar2(10),
  std_cust_type_id       number(15),
  cde_sale_organize_cd   varchar2(10),
  std_sale_organize_cd   number(15),
  cde_cert_type_cd       varchar2(10),
  std_cert_type_cd       number(15),
  cert_nbr               varchar2(40),
  cde_credit_grade_cd    varchar2(10),
  std_credit_grade_cd    number(15),
  complete_date          date,
  innet_date             date,
  outnet_date            date,
  stop_date              date,
  join_month             integer,
  std_join_level_cd      varchar2(2),
  owe_months             number(9),
  stop_month             integer,
  std_new_old_cd         varchar2(2),
  std_stop_level_cd      number(15),
  std_brand_cd           varchar2(10),
  std_s_cust_brand_cd    number(15),
  std_e_cust_brand_cd    number(15),
  high_user              number(8),
  product_family_id      number(10),
  is_arrive              number(1),
  is_innet_arrive        number(1),
  is_bil_arrive          number(1),
  prd_complete_in        number(1),
  prd_complete_out       number(1)
)
  tablespace tbs_02
partition by list (date_cd)
(
  partition tp_serv_day_20100501 values ('20100501')
);