oracle创建分区表

来源：互联网发布：恢复windows删除数据编辑：程序博客网时间：2024/04/30 19:09

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

-- 通过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')
);