oracle怎么创建缺省分区,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')

);