create_table.sql

一本介绍oracle的书籍

drop table user_log;
create table user_log
(
    log_id                      number(12) not null,
    create_ts 			date not null,
    log_dt			char(8) not null,
    loginname	                char(12) not null,
    content                     varchar2(300) not null,
    constraint user_log_x00 primary key (log_id)
) pctused 70 pctfree 10 tablespace ts_data enable primary key using index tablespace ts_index; 
create index user_log_x01 on user_log(loginname) tablespace ts_index;

drop table user_log_partition;
create table user_log_partition
(
    log_id                      number(12) not null,
    create_ts 			date not null,
    log_dt			char(8) not null,
    loginname	                char(12) not null,
    content                     varchar2(300) not null,
    constraint user_log_partition_x00 primary key (log_id)
)
partition by range(log_dt)
(
  partition par1 values less than ('20010104') tablespace ts_data1,
  partition par2 values less than ('20010107') tablespace ts_data2,
  partition par3 values less than ('20010110') tablespace ts_data3,
  partition par4 values less than ('20010113') tablespace ts_data4,
  partition par5 values less than ('20010116') tablespace ts_data5
) enable row movement
pctused 70 pctfree 10 enable primary key using index tablespace ts_index;
create index user_log_partition_x01 on user_log_partition(loginname)
global partition by range(loginname)
(
  partition par1 values less than ('test2001') tablespace ts_index1,
  partition par2 values less than ('test4001') tablespace ts_index2,
  partition par3 values less than ('test6001') tablespace ts_index3,
  partition par4 values less than ('test8001') tablespace ts_index4,
  partition par5 values less than (MAXVALUE) tablespace ts_index5
)
pctfree 10;

drop table user_log_20010101;
create table user_log_20010101
(
    log_id                      number(12) not null,
    create_ts                   date not null,
    loginname                   char(12) not null,
    content                     varchar2(300) not null,
    constraint user_log_20010101_x00 primary key (log_id)
) pctused 70 pctfree 10 tablespace ts_data enable primary key using index tablespace ts_index;
create index user_log_20010101_x01 on user_log_20010101(loginname) tablespace ts_index;

drop table user_log_20010102;
create table user_log_20010102
(
    log_id                      number(12) not null,
    create_ts                   date not null,
    loginname                   char(12) not null,
    content                     varchar2(300) not null,
    constraint user_log_20010102_x00 primary key (log_id)
) pctused 70 pctfree 10 tablespace ts_data enable primary key using index tablespace ts_index;
create index user_log_20010102_x01 on user_log_20010102(loginname) tablespace ts_index;

drop table user_log_20010103;
create table user_log_20010103
(
    log_id                      number(12) not null,
    create_ts                   date not null,
    loginname                   char(12) not null,
    content                     varchar2(300) not null,
    constraint user_log_20010103_x00 primary key (log_id)
) pctused 70 pctfree 10 tablespace ts_data enable primary key using index tablespace ts_index;
create index user_log_20010103_x01 on user_log_20010103(loginname) tablespace ts_index;

drop table user_log_20010104;
create table user_log_20010104
(
    log_id                      number(12) not null,
    create_ts                   date not null,
    loginname                   char(12) not null,
    content                     varchar2(300) not null,
    constraint user_log_20010104_x00 primary key (log_id)
) pctused 70 pctfree 10 tablespace ts_data enable primary key using index tablespace ts_index;
create index user_log_20010104_x01 on user_log_20010104(loginname) tablespace ts_index;

drop table user_log_20010105;
create table user_log_20010105
(
    log_id                      number(12) not null,
    create_ts                   date not null,
    loginname                   char(12) not null,
    content                     varchar2(300) not null,
    constraint user_log_20010105_x00 primary key (log_id)
) pctused 70 pctfree 10 tablespace ts_data enable primary key using index tablespace ts_index;
create index user_log_20010105_x01 on user_log_20010105(loginname) tablespace ts_index;

drop table user_log_20010106;
create table user_log_20010106
(
    log_id                      number(12) not null,
    create_ts                   date not null,
    loginname                   char(12) not null,
    content                     varchar2(300) not null,
    constraint user_log_20010106_x00 primary key (log_id)
) pctused 70 pctfree 10 tablespace ts_data enable primary key using index tablespace ts_index;
create index user_log_20010106_x01 on user_log_20010106(loginname) tablespace ts_index;

drop table user_log_20010107;
create table user_log_20010107
(
    log_id                      number(12) not null,
    create_ts                   date not null,
    loginname                   char(12) not null,
    content                     varchar2(300) not null,
    constraint user_log_20010107_x00 primary key (log_id)
) pctused 70 pctfree 10 tablespace ts_data enable primary key using index tablespace ts_index;
create index user_log_20010107_x01 on user_log_20010107(loginname) tablespace ts_index;

drop table user_log_20010108;
create table user_log_20010108
(
    log_id                      number(12) not null,
    create_ts                   date not null,
    loginname                   char(12) not null,
    content                     varchar2(300) not null,
    constraint user_log_20010108_x00 primary key (log_id)
) pctused 70 pctfree 10 tablespace ts_data enable primary key using index tablespace ts_index;
create index user_log_20010101_x08 on user_log_20010108(loginname) tablespace ts_index;

drop table user_log_20010109;
create table user_log_20010109
(
    log_id                      number(12) not null,
    create_ts                   date not null,
    loginname                   char(12) not null,
    content                     varchar2(300) not null,
    constraint user_log_20010109_x00 primary key (log_id)
) pctused 70 pctfree 10 tablespace ts_data enable primary key using index tablespace ts_index;
create index user_log_20010101_x09 on user_log_20010109(loginname) tablespace ts_index;

drop table user_log_20010110;
create table user_log_20010110
(
    log_id                      number(12) not null,
    create_ts                   date not null,
    loginname                   char(12) not null,
    content                     varchar2(300) not null,
    constraint user_log_20010110_x00 primary key (log_id)
) pctused 70 pctfree 10 tablespace ts_data enable primary key using index tablespace ts_index;
create index user_log_20010110_x01 on user_log_20010110(loginname) tablespace ts_index;

drop table user_log_20010111;
create table user_log_20010111
(
    log_id                      number(12) not null,
    create_ts                   date not null,
    loginname                   char(12) not null,
    content                     varchar2(300) not null,
    constraint user_log_20010111_x00 primary key (log_id)
) pctused 70 pctfree 10 tablespace ts_data enable primary key using index tablespace ts_index;
create index user_log_20010111_x01 on user_log_20010111(loginname) tablespace ts_index;

drop table user_log_20010112;
create table user_log_20010112
(
    log_id                      number(12) not null,
    create_ts                   date not null,
    loginname                   char(12) not null,
    content                     varchar2(300) not null,
    constraint user_log_20010112_x00 primary key (log_id)
) pctused 70 pctfree 10 tablespace ts_data enable primary key using index tablespace ts_index;
create index user_log_20010112_x01 on user_log_20010112(loginname) tablespace ts_index;

drop table user_log_20010113;
create table user_log_20010113
(
    log_id                      number(12) not null,
    create_ts                   date not null,
    loginname                   char(12) not null,
    content                     varchar2(300) not null,
    constraint user_log_20010113_x00 primary key (log_id)
) pctused 70 pctfree 10 tablespace ts_data enable primary key using index tablespace ts_index;
create index user_log_20010113_x01 on user_log_20010113(loginname) tablespace ts_index;

drop table user_log_20010114;
create table user_log_20010114
(
    log_id                      number(12) not null,
    create_ts                   date not null,
    loginname                   char(12) not null,
    content                     varchar2(300) not null,
    constraint user_log_20010114_x00 primary key (log_id)
) pctused 70 pctfree 10 tablespace ts_data enable primary key using index tablespace ts_index;
create index user_log_20010114_x01 on user_log_20010114(loginname) tablespace ts_index;

drop table user_log_20010115;
create table user_log_20010115
(
    log_id                      number(12) not null,
    create_ts                   date not null,
    loginname                   char(12) not null,
    content                     varchar2(300) not null,
    constraint user_log_20010115_x00 primary key (log_id)
) pctused 70 pctfree 10 tablespace ts_data enable primary key using index tablespace ts_index;
create index user_log_20010115_x01 on user_log_20010115(loginname) tablespace ts_index;

drop table user_log_partition;
create table user_log_partition
(
    log_id                      number(12) not null,
    create_ts 			date not null,
    log_dt			char(8) not null,
    loginname	                char(12) not null,
    content                     varchar2(300) not null,
    constraint user_log_partition_x00 primary key (log_id)
)
partition by range(log_dt)
(
  partition par1 values less than ('20010104') tablespace ts_data1,
  partition par2 values less than ('20010107') tablespace ts_data2,
  partition par3 values less than ('20010110') tablespace ts_data3,
  partition par4 values less than ('20010113') tablespace ts_data4,
  partition par5 values less than ('20010116') tablespace ts_data5
) enable row movement
pctused 70 pctfree 10 enable primary key using index tablespace ts_index;
rem create index user_log_partition_x01 on user_log_partition(loginname)
rem global partition by range(loginname)
rem (
rem   partition par1 values less than ('test2001') tablespace ts_index1,
rem   partition par2 values less than ('test4001') tablespace ts_index2,
rem   partition par3 values less than ('test6001') tablespace ts_index3,
rem   partition par4 values less than ('test8001') tablespace ts_index4,
rem   partition par5 values less than (MAXVALUE) tablespace ts_index5
rem )
rem pctfree 10;
create index user_log_partition_x01 on user_log_partition(loginname) tablespace ts_index1 pctfree 10;