-- Create table
create table PORTAL_REQUESTWS_HISTORY_01
(
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)
)
tablespace TBS_ZXKF
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create/Recreate indexes
create index P_REQUESTWS_HIS_01_REMARK13 on PORTAL_REQUESTWS_HISTORY_01 (REMARK13)
tablespace TBS_WT_IDX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
create index P_REQUESTWS_HIS_01_REQTIME on PORTAL_REQUESTWS_HISTORY_01 (REQTIME)
tablespace TBS_WT_IDX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
create index P_REQUESTWS_HIS_01_SVER on PORTAL_REQUESTWS_HISTORY_01 (SERVICECONTRACTVER)
tablespace TBS_WT_IDX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
create index P_REQUESTWS_HIS_01_TID on PORTAL_REQUESTWS_HISTORY_01 (TRANSACTIONID)
tablespace TBS_WT_IDX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table PORTAL_REQUESTWS_HISTORY_01
add constraint PK_PORTAL_REQUESTWS_HISTORY_01 primary key (ID)
using index
tablespace TBS_ZXKF
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Grant/Revoke object privileges
grant select on PORTAL_REQUESTWS_HISTORY_01 to APP_ZUOQY;
grant select on PORTAL_REQUESTWS_HISTORY_01 to WT_APP;
---1,分区测试
SQL> show user
USER 为 "SCOTT"
----------创始时间分区表
SQL> create table t_test_partition_1(id int,reqtime timestamp(6),call_ip varchar
2(200))
2 partition by range(reqtime)
3 (
4 partition p1 values less than (to_timestamp('2013-01-07 00:00:00.000000','
yyyy-mm-dd hh24:mi:ss.ff')),
5 partition p2 values less than (to_timestamp('2013-01-14 00:00:00.000000','
yyyy-mm-dd hh24:mi:ss.ff')),
6 partition p3 values less than (to_timestamp('2013-01-21 00:00:00.000000','
yyyy-mm-dd hh24:mi:ss.ff')),
7 partition p4 values less than (to_timestamp('2013-01-28 00:00:00.000000','
yyyy-mm-dd hh24:mi:ss.ff')),
8 partition p5 values less than (to_timestamp('2013-01-31 00:00:00.000000','
yyyy-mm-dd hh24:mi:ss.ff'))
9 );
表已创建。
SQL>
-------确认是否分区成功
SQL> select table_name,partitioned from user_tables where table_name='T_TES
TITION_1';
TABLE_NAME PAR
------------------------------ ---
T_TEST_PARTITION_1 YES
SQL> desc dba_tab_partitions;
名称 是否为空? 类型
----------------------------------------- -------- -----------------------
TABLE_OWNER VARCHAR2(30)
TABLE_NAME VARCHAR2(30)
COMPOSITE VARCHAR2(3)
PARTITION_NAME VARCHAR2(30)
SUBPARTITION_COUNT NUMBER
HIGH_VALUE LONG
HIGH_VALUE_LENGTH NUMBER
PARTITION_POSITION NUMBER
TABLESPACE_NAME VARCHAR2(30)
PCT_FREE NUMBER
PCT_USED NUMBER
INI_TRANS NUMBER
MAX_TRANS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENT NUMBER
MAX_EXTENT NUMBER
MAX_SIZE NUMBER
PCT_INCREASE NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
LOGGING VARCHAR2(7)
COMPRESSION VARCHAR2(8)
COMPRESS_FOR VARCHAR2(12)
NUM_ROWS NUMBER
BLOCKS NUMBER
EMPTY_BLOCKS NUMBER
AVG_SPACE NUMBER
CHAIN_CNT NUMBER
AVG_ROW_LEN NUMBER
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
BUFFER_POOL VARCHAR2(7)
FLASH_CACHE VARCHAR2(7)
CELL_FLASH_CACHE VARCHAR2(7)
GLOBAL_STATS VARCHAR2(3)
USER_STATS VARCHAR2(3)
IS_NESTED VARCHAR2(3)
PARENT_TABLE_PARTITION VARCHAR2(30)
INTERVAL VARCHAR2(3)
SEGMENT_CREATED VARCHAR2(3)
----------确认分区表的各个分表名称及分表键的值分布情况
SQL> select table_name,partition_name,tablespace_name,num_rows,high_value f
ba_tab_partitions where table_name='T_TEST_PARTITION_1';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
TABLESPACE_NAME NUM_ROWS
------------------------------ ----------
HIGH_VALUE
---------------------------------------------------------------------------
T_TEST_PARTITION_1 P1
USERS
TIMESTAMP' 2013-01-07 00:00:00'
T_TEST_PARTITION_1 P2
USERS
TIMESTAMP' 2013-01-14 00:00:00'
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
TABLESPACE_NAME NUM_ROWS
------------------------------ ----------
HIGH_VALUE
---------------------------------------------------------------------------
T_TEST_PARTITION_1 P3
USERS
TIMESTAMP' 2013-01-21 00:00:00'
T_TEST_PARTITION_1 P4
USERS
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
TABLESPACE_NAME NUM_ROWS
------------------------------ ----------
HIGH_VALUE
---------------------------------------------------------------------------
TIMESTAMP' 2013-01-28 00:00:00'
T_TEST_PARTITION_1 P5
USERS
TIMESTAMP' 2013-01-31 00:00:00'
-----------创建本地分区索引
SQL> create index idx_t_test_partition_1_id on t_test_partition_1(reqtime)
2 local
3 (partition p1_idx1,
4 partition p2_idx2,
5 partition p3_idx3,
6 partition p4_idx4,
7 partition p5_idx5
8 )
9 /
索引已创建。
--授权分区表给其它用户
SQL> grant select on t_test_partition_1 to sys;
授权成功。
SQL> desc user_ind_partitions;
名称 是否为空? 类型
----------------------------------------- -------- ------------------------
INDEX_NAME VARCHAR2(30)
COMPOSITE VARCHAR2(3)
PARTITION_NAME VARCHAR2(30)
SUBPARTITION_COUNT NUMBER
HIGH_VALUE LONG
HIGH_VALUE_LENGTH NUMBER
PARTITION_POSITION NUMBER
STATUS VARCHAR2(8)
TABLESPACE_NAME VARCHAR2(30)
PCT_FREE NUMBER
INI_TRANS NUMBER
MAX_TRANS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENT NUMBER
MAX_EXTENT NUMBER
MAX_SIZE NUMBER
PCT_INCREASE NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
LOGGING VARCHAR2(7)
COMPRESSION VARCHAR2(8)
BLEVEL NUMBER
LEAF_BLOCKS NUMBER
DISTINCT_KEYS NUMBER
AVG_LEAF_BLOCKS_PER_KEY NUMBER
AVG_DATA_BLOCKS_PER_KEY NUMBER
CLUSTERING_FACTOR NUMBER
NUM_ROWS NUMBER
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
BUFFER_POOL VARCHAR2(7)
FLASH_CACHE VARCHAR2(7)
CELL_FLASH_CACHE VARCHAR2(7)
USER_STATS VARCHAR2(3)
PCT_DIRECT_ACCESS NUMBER
GLOBAL_STATS VARCHAR2(3)
DOMIDX_OPSTATUS VARCHAR2(6)
PARAMETERS VARCHAR2(1000)
INTERVAL VARCHAR2(3)
SEGMENT_CREATED VARCHAR2(3)
-----查本地分区索引的相关数据
SQL> select index_name,partition_name,high_value,num_rows,tablespace_name,st
from user_ind_partitions;
INDEX_NAME PARTITION_NAME
------------------------------ ------------------------------
HIGH_VALUE
----------------------------------------------------------------------------
NUM_ROWS TABLESPACE_NAME STATUS
---------- ------------------------------ --------
IDX_T_PARTITION P1_IDX1
10
0 USERS USABLE
IDX_T_PARTITION P1_IDX2
20
0 USERS USABLE
INDEX_NAME PARTITION_NAME
------------------------------ ------------------------------
HIGH_VALUE
----------------------------------------------------------------------------
NUM_ROWS TABLESPACE_NAME STATUS
---------- ------------------------------ --------
IDX_T_PARTITION P1_IDX3
MAXVALUE
0 USERS USABLE
IDX_T_TEST_PARTITION_1_ID P1_IDX1
TIMESTAMP' 2013-01-07 00:00:00'
INDEX_NAME PARTITION_NAME
------------------------------ ------------------------------
HIGH_VALUE
----------------------------------------------------------------------------
NUM_ROWS TABLESPACE_NAME STATUS
---------- ------------------------------ --------
0 USERS USABLE
IDX_T_TEST_PARTITION_1_ID P2_IDX2
TIMESTAMP' 2013-01-14 00:00:00'
0 USERS USABLE
IDX_T_TEST_PARTITION_1_ID P3_IDX3
INDEX_NAME PARTITION_NAME
------------------------------ ------------------------------
HIGH_VALUE
----------------------------------------------------------------------------
NUM_ROWS TABLESPACE_NAME STATUS
---------- ------------------------------ --------
TIMESTAMP' 2013-01-21 00:00:00'
0 USERS USABLE
IDX_T_TEST_PARTITION_1_ID P4_IDX4
TIMESTAMP' 2013-01-28 00:00:00'
0 USERS USABLE
INDEX_NAME PARTITION_NAME
------------------------------ ------------------------------
HIGH_VALUE
----------------------------------------------------------------------------
NUM_ROWS TABLESPACE_NAME STATUS
---------- ------------------------------ --------
IDX_T_TEST_PARTITION_1_ID P5_IDX5
TIMESTAMP' 2013-01-31 00:00:00'
0 USERS USABLE
IDX_T_PARALLEL P1_IDX1
10
9 USERS USABLE
INDEX_NAME PARTITION_NAME
------------------------------ ------------------------------
HIGH_VALUE
----------------------------------------------------------------------------
NUM_ROWS TABLESPACE_NAME STATUS
---------- ------------------------------ --------
IDX_T_PARALLEL P2_IDX2
20
10 USERS USABLE
IDX_T_PARALLEL P3_IDX3
MAXVALUE
INDEX_NAME PARTITION_NAME
------------------------------ ------------------------------
HIGH_VALUE
----------------------------------------------------------------------------
NUM_ROWS TABLESPACE_NAME STATUS
---------- ------------------------------ --------
999981 USERS USABLE
已选择11行。
--为分区表添加主键唯一索引
SQL> alter table t_test_partition_1 add constraint pk_t_test_partition_1 primary
key(id);
表已更改。
--唯一主键索引没有进行分区
SQL> select index_name,partition_name,high_value,num_rows,tablespace_name,status
from user_ind_partitions where index_name='PK_T_TEST_PARTITION_1';
未选定行
--自索引表查说明主键没有进行分区
SQL> select table_name,index_name,partitioned from user_indexes where table_name
='T_TEST_PARTITION_1';
TABLE_NAME INDEX_NAME PAR
------------------------------ ------------------------------ ---
T_TEST_PARTITION_1 IDX_T_TEST_PARTITION_1_ID YES
T_TEST_PARTITION_1 PK_T_TEST_PARTITION_1 NO
--添加分表
SQL> alter table t_test_partition_1 add partition p6 values less than (to_timest
amp('2013-02-07 00:00:00.000000','yyyy-mm-dd hh24:mi:ss.ff'));
表已更改。
---删除分表
SQL> alter table t_test_partition_1 drop partition p6;
表已更改。