oracle10g 创建分区表,oracle10g_10.2.0.5_构建生产某表为分区及分区索引的日志

前言:

--数据库版本:

select * from v$version where rownum=1;

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit

--1,构建基于月份的4分区表

--报错:ORA-30078: 分区边界必须是 TIME/TIMESTAMP

create table PORTAL_REQUESTWS_HISTORY_01_new

(

id                 VARCHAR2(32) not null,

call_time          TIMESTAMP(6),

call_ip            VARCHAR2(200),

xml_contents       CLOB,

response_result    VARCHAR2(200),

response_content   CLOB,

response_time      TIMESTAMP(6),

transactionid      VARCHAR2(200),

actioncode         VARCHAR2(200),

buscode            VARCHAR2(200),

servicecode        VARCHAR2(200),

servicecontractver VARCHAR2(200),

servicelevel       VARCHAR2(200),

srcorgid           VARCHAR2(200),

srcsysid           VARCHAR2(200),

srcsyssign         VARCHAR2(200),

dstorgid           VARCHAR2(200),

dstsysid           VARCHAR2(200),

reqtime            TIMESTAMP(6),

createdateppm      VARCHAR2(200),

disable_opid       VARCHAR2(200),

disable_date       TIMESTAMP(6),

create_opid        VARCHAR2(200),

create_date        TIMESTAMP(6),

rec_status         NUMBER(1) not null,

remark1            VARCHAR2(200),

remark2            VARCHAR2(200),

remark3            VARCHAR2(200),

remark4            VARCHAR2(200),

remark5            VARCHAR2(200),

remark6            VARCHAR2(200),

remark7            VARCHAR2(200),

remark8            VARCHAR2(200),

remark9            VARCHAR2(200),

remark10           VARCHAR2(200),

remark11           VARCHAR2(200),

remark12           VARCHAR2(200),

remark13           VARCHAR2(200),

remark14           VARCHAR2(200),

remark15           VARCHAR2(200)

)

partition by range(reqtime)

(

partition p1 values less than (to_timestamp('2013-01-07 00:00:00.000000','yyyy-mm-dd hh24:mi:ss.ff')) tablespace TBS_ZXKF pctfree 10

initrans 1

maxtrans 255

storage

(

initial 64K

next 1M

minextents 1

maxextents unlimited

),

partition p2 values less than (to_timestamp('2013-01-14 00:00:00.000000','yyyy-mm-dd hh24:mi:ss.ff')) tablespace TBS_ZXKF pctfree 10

initrans 1

maxtrans 255

storage

(

initial 64K

next 1M

minextents 1

maxextents unlimited

),

partition p3 values less than (to_timestamp('2013-01-21 00:00:00.000000','yyyy-mm-dd hh24:mi:ss.ff')) tablespace TBS_ZXKF pctfree 10

initrans 1

maxtrans 255

storage

(

initial 64K

next 1M

minextents 1

maxextents unlimited

),

partition p4 values less than (to_timestamp('2013-01-31 24:00:00.000000','yyyy-mm-dd hh24:mi:ss.ff')) tablespace TBS_ZXKF pctfree 10

initrans 1

maxtrans 255

storage

(

initial 64K

next 1M

minextents 1

maxextents unlimited

)

);

------再次修改语句后如下

--1,构建基于月份的4分区表

--依旧报错:ORA-14019: partition bound element must be one of: string, datetime or interval literal, number, or MAXVALUE

create table PORTAL_REQUESTWS_HISTORY_01new

(

id                 VARCHAR2(32) not null,

call_time          TIMESTAMP(6),

call_ip            VARCHAR2(200),

xml_contents       CLOB,

response_result    VARCHAR2(200),

response_content   CLOB,

response_time      TIMESTAMP(6),

transactionid      VARCHAR2(200),

actioncode         VARCHAR2(200),

buscode            VARCHAR2(200),

servicecode        VARCHAR2(200),

servicecontractver VARCHAR2(200),

servicelevel       VARCHAR2(200),

srcorgid           VARCHAR2(200),

srcsysid           VARCHAR2(200),

srcsyssign         VARCHAR2(200),

dstorgid           VARCHAR2(200),

dstsysid           VARCHAR2(200),

reqtime            TIMESTAMP(6),

createdateppm      VARCHAR2(200),

disable_opid       VARCHAR2(200),

disable_date       TIMESTAMP(6),

create_opid        VARCHAR2(200),

create_date        TIMESTAMP(6),

rec_status         NUMBER(1) not null,

remark1            VARCHAR2(200),

remark2            VARCHAR2(200),

remark3            VARCHAR2(200),

remark4            VARCHAR2(200),

remark5            VARCHAR2(200),

remark6            VARCHAR2(200),

remark7            VARCHAR2(200),

remark8            VARCHAR2(200),

remark9            VARCHAR2(200),

remark10           VARCHAR2(200),

remark11           VARCHAR2(200),

remark12           VARCHAR2(200),

remark13           VARCHAR2(200),

remark14           VARCHAR2(200),

remark15           VARCHAR2(200)

)

partition by range(reqtime)

(

partition p1 values less than (TIMESTAMP('2013-01-07 00:00:00.000000','yyyy-mm-dd hh24:mi:ss.ff') tablespace TBS_ZXKF,

partition p2 values less than (TIMESTAMP('2013-01-14 00:00:00.000000','yyyy-mm-dd hh24:mi:ss.ff') tablespace TBS_ZXKF,

partition p3 values less than (TIMESTAMP('2013-01-21 00:00:00.000000','yyyy-mm-dd hh24:mi:ss.ff') tablespace TBS_ZXKF,

partition p4 values less than (TIMESTAMP('2013-01-31 23:59:59.000000','yyyy-mm-dd hh24:mi:ss.ff') tablespace TBS_ZXKF

);

----第三次修改如下:成功执行

--1,构建基于月份的4分区表

create table PORTAL_REQUESTWS_HISTORY_01new

(

id                 VARCHAR2(32) not null,

call_time          TIMESTAMP(6),

call_ip            VARCHAR2(200),

xml_contents       CLOB,

response_result    VARCHAR2(200),

response_content   CLOB,

response_time      TIMESTAMP(6),

transactionid      VARCHAR2(200),

actioncode         VARCHAR2(200),

buscode            VARCHAR2(200),

servicecode        VARCHAR2(200),

servicecontractver VARCHAR2(200),

servicelevel       VARCHAR2(200),

srcorgid           VARCHAR2(200),

srcsysid           VARCHAR2(200),

srcsyssign         VARCHAR2(200),

dstorgid           VARCHAR2(200),

dstsysid           VARCHAR2(200),

reqtime            TIMESTAMP(6),

createdateppm      VARCHAR2(200),

disable_opid       VARCHAR2(200),

disable_date       TIMESTAMP(6),

create_opid        VARCHAR2(200),

create_date        TIMESTAMP(6),

rec_status         NUMBER(1) not null,

remark1            VARCHAR2(200),

remark2            VARCHAR2(200),

remark3            VARCHAR2(200),

remark4            VARCHAR2(200),

remark5            VARCHAR2(200),

remark6            VARCHAR2(200),

remark7            VARCHAR2(200),

remark8            VARCHAR2(200),

remark9            VARCHAR2(200),

remark10           VARCHAR2(200),

remark11           VARCHAR2(200),

remark12           VARCHAR2(200),

remark13           VARCHAR2(200),

remark14           VARCHAR2(200),

remark15           VARCHAR2(200)

)

partition by range(reqtime)

(

partition p1 values less than (TIMESTAMP'2013-01-07 00:00:00.000000') tablespace TBS_ZXKF,

partition p2 values less than (TIMESTAMP'2013-01-14 00:00:00.000000') tablespace TBS_ZXKF,

partition p3 values less than (TIMESTAMP'2013-01-21 00:00:00.000000') tablespace TBS_ZXKF,

partition p4 values less than (TIMESTAMP'2013-01-31 23:59:59.000000') tablespace TBS_ZXKF

);

----2,添加基于分区键的本地分区索引

create index idx_REQUESTWS_HIS_01_REQTIME on PORTAL_REQUESTWS_HISTORY_01new(reqtime) local tablespace TBS_WT_IDX;

----3,查询上述创建的本地分区索引

select index_name,partition_name,tablespace_name from user_ind_partitions where index_name in (select index_name from user_indexes where table_name='PORTAL_REQUESTWS_HISTORY_01NEW');

----4,查询索引的信息

select * from user_ind_columns uc where uc.TABLE_NAME='PORTAL_REQUESTWS_HISTORY_01NEW';

select index_name,tablespace_name from user_indexes where TABLE_NAME='PORTAL_REQUESTWS_HISTORY_01NEW';

---5,创建基于id列的本地唯一分区索引,即起到主键的作用

create unique index idx_REQUESTWS_HIS_01_id on PORTAL_REQUESTWS_HISTORY_01new(reqtime,id) local tablespace TBS_WT_IDX;

--6,创建基于REMARK13列的本地分区索引

create  index idx_REQUESTWS_HIS_01_REMARK13 on PORTAL_REQUESTWS_HISTORY_01new(reqtime,remark13) local tablespace TBS_WT_IDX;

---7,创建基于SERVICECONTRACTVER列的本地分区索引

create  index idx_REQUESTWS_HIS_01_SR on PORTAL_REQUESTWS_HISTORY_01new(reqtime,SERVICECONTRACTVER) local tablespace TBS_WT_IDX;

----8,创建基于TRANSACTIONID列的本地分区索引

create  index idx_REQUESTWS_HIS_01_TRANSID on PORTAL_REQUESTWS_HISTORY_01new(reqtime,TRANSACTIONID) local tablespace TBS_WT_IDX;

---9,授权

-- Grant/Revoke object privileges

grant select on PORTAL_REQUESTWS_HISTORY_01new to APP_ZUOQY;

grant select on PORTAL_REQUESTWS_HISTORY_01new to WT_APP;

---10,重命名生产表PORTAL_REQUESTWS_HISTORY_01为PORTAL_REQUESTWS_HISTORY_01_product

alter table PORTAL_REQUESTWS_HISTORY_01 rename to PORTAL_REQUESTWS_HIS_01product;

--11,重命名PORTAL_REQUESTWS_HISTORY_01new为生产表

alter table PORTAL_REQUESTWS_HISTORY_01new rename to PORTAL_REQUESTWS_HISTORY_01;

--12,查询转化过来的生产表是否为分区及分区索引信息是否完整

select * from user_tab_partitions where table_name='PORTAL_REQUESTWS_HISTORY_01';

select * from user_part_tables where table_name='PORTAL_REQUESTWS_HISTORY_01';

select index_name,partition_name,tablespace_name from user_ind_partitions where index_name in (select index_name from user_indexes where table_name='PORTAL_REQUESTWS_HISTORY_01');

--13,查询授权是否完整 grantor授权者 grantee被授权者

select * from user_tab_privs where table_name='PORTAL_REQUESTWS_HISTORY_01'

select * from user_tab_privs_made where table_name='PORTAL_REQUESTWS_HISTORY_01'