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
),
--資料庫版本:
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
),
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
),
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
)
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,
--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,
--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;
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;
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;
-- 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;
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;
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');
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 where table_name='PORTAL_REQUESTWS_HISTORY_01'
select * from user_tab_privs_made where table_name='PORTAL_REQUESTWS_HISTORY_01'
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-751897/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 續_oracle10g_10.2.0.5_構建生產某表為分割槽及分割槽索引的日誌(二)_parallel_nologgingOracle索引Parallel
- Oracle分割槽表及分割槽索引Oracle索引
- 分割槽表及分割槽索引建立示例索引
- 全面學習分割槽表及分割槽索引(13)--分隔表分割槽索引
- 全面學習分割槽表及分割槽索引(10)--交換分割槽索引
- 全面學習分割槽表及分割槽索引(9)--刪除表分割槽索引
- 全面學習分割槽表及分割槽索引(11)--合併表分割槽索引
- 全面學習分割槽表及分割槽索引(12)--修改list表分割槽索引
- 全面學習分割槽表及分割槽索引(17)--其它索引分割槽管理操作索引
- 深入學習Oracle分割槽表及分割槽索引Oracle索引
- 全面學習分割槽表及分割槽索引(1)索引
- 全面認識oracle分割槽表及分割槽索引Oracle索引
- 深入學習分割槽表及分割槽索引(1)索引
- 全面學習分割槽表及分割槽索引(16)--增加和刪除索引分割槽索引
- rebuild分割槽表分割槽索引的方法Rebuild索引
- 全面學習分割槽表及分割槽索引(8)--增加和收縮表分割槽索引
- 全面學習分割槽表及分割槽索引(15)--修改表分割槽屬性和模板索引
- 簡單ORACLE分割槽表、分割槽索引Oracle索引
- 【學習筆記】分割槽表和分割槽索引——管理索引分割槽（四）筆記索引
- 如何查詢分割槽表的分割槽及子分割槽
- 學習筆記】分割槽表和分割槽索引——新增表分割槽（二）筆記索引
- 分割槽表分割槽索引查詢效率探究索引
- 全面學習分割槽表及分割槽索引(6)--建立range-list組合分割槽索引
- 【學習筆記】分割槽表和分割槽索引——分割槽表的其他管理（三）筆記索引
- MySQL 分割槽建索引MySql索引
- 全面學習分割槽表及分割槽索引(7)--怎樣管理索引
- Oracle帶區域性分割槽索引的分割槽表刪除舊分割槽新增新分割槽Oracle索引
- 深入學習分割槽表及分割槽索引(5)--建立range-hash組合分割槽(續)索引
- oracle 針對普通表的索引分割槽及10g新增hash 索引分割槽Oracle索引
- 分割槽表、分割槽索引和全域性索引部分總結索引
- oracle分割槽partition及分割槽索引partition index(一)Oracle索引Index
- 全面學習分割槽表及分割槽索引(7)--怎樣管理(續)索引
- Oracle分割槽表基礎運維-06分割槽表索引Oracle運維索引
- 【三思筆記】 全面學習Oracle分割槽表及分割槽索引筆記Oracle索引
- 分割槽表中的區域性分割槽索引及全域性索引與執行計劃索引
- 關於分割槽表和分割槽索引(About Partitioned Tables and Indexes)索引Index
- 將非分割槽錶轉換為分割槽表
- PLSQL根據分割槽表的分割槽名批次truncate分割槽SQL