[20190805]Oracle 12c New Feature – Online Partitioning.txt

[20190805]Oracle 12c New Feature – Online Partitioning.txt --//oracle 12c 支持建立分区表新特性，就是在线修改普通表为分区表。 1.环境： SCOTT@test01p> @ ver1 PORT_STRING          VERSION    BANNER                                                                       CON_ID -------------------- ---------- ---------------------------------------------------------------------------- ------ IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production      0 SCOTT@test01p> create table tx as select rownum id ,'test' name from dual connect by level<=2e4; Table created. SCOTT@test01p> create unique index i_t_id on tx (id); Index created. SCOTT@test01p> create index i_t_name on tx (name); Index created. SCOTT@test01p> create index i_t_id_name on tx (id,name); Index created. --//分析略。 2.测试： --//session 1,插入不提交。 SCOTT@test01p> insert into tx values (2e4+1,'aaaa'); 1 row created. --//session 2,执行如下： alter table tx modify partition by range (id) (PARTITION p1 VALUES LESS THAN (10000), PARTITION p2 VALUES LESS THAN (20000), PARTITION px VALUES LESS THAN (MAXVALUE)) ONLINE ; --//挂起!! --//session 1: SCOTT@test01p> @ spid        SID    SERIAL# PROCESS                  SERVER    SPID                     PID  P_SERIAL# C50 ---------- ---------- ------------------------ --------- -------------------- ------- ---------- --------------------------------------------------        251      41873 7720:4852                DEDICATED 6316                      27          4 alter system kill session '251,41873' immediate; SCOTT@test01p> @ viewlock    SID    SERIAL# USERNAME   OSUSER     MACHINE    MODULE       LOCK_TYPE       MODE_HELD  MODE_REQUE LOCK_ID1   LOCK_ID2   OWNER  OBJECT_TYP OBJECT_NAME          BLOCK LOCKWAIT ------ ---------- ---------- ---------- ---------- ------------ --------------- ---------- ---------- ---------- ---------- ------ ---------- -------------------- ----- --------------------     24      45764 SCOTT      ZWS\Admini WORKGROUP\ SQL*Plus     TM DML(TM)      Row-X (SX) None       27765      0          SCOTT  TABLE      TX                   No    000007FF091426B8     24      45764 SCOTT      ZWS\Admini WORKGROUP\ SQL*Plus     TM DML(TM)      Exclusive  None       27775      0          SCOTT  TABLE      SYS_RMTAB$$_H27765   No    000007FF091426B8     24      45764 SCOTT      ZWS\Admini WORKGROUP\ SQL*Plus     TX Transaction  None       Share      589839     1387                                              No    000007FF091426B8     24      45764 SCOTT      ZWS\Admini WORKGROUP\ SQL*Plus     TM DML(TM)      Exclusive  None       27773      0          SCOTT  TABLE      SYS_JOURNAL_27765    No    000007FF091426B8     24      45764 SCOTT      ZWS\Admini WORKGROUP\ SQL*Plus     TX Transaction  Exclusive  None       262173     1470                                              No    000007FF091426B8    251      41873 SCOTT      ZWS\Admini WORKGROUP\ SQL*Plus     TX Transaction  Exclusive  None       589839     1387                                              Yes    251      41873 SCOTT      ZWS\Admini WORKGROUP\ SQL*Plus     TM DML(TM)      Row-X (SX) None       27765      0          SCOTT  TABLE      TX                   No 7 rows selected.        --//因为session 有事务没有提交。 SCOTT@test01p> commit ; Commit complete. --//session 2,很快建立完成。 SCOTT@test01p> SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='TX'; PARTITION_NAME -------------------- P1 P2 PX SCOTT@test01p> select * from tx where id=1;         ID NAME ---------- --------------------          1 test SCOTT@test01p> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  6sj645u5hrrbv, child number 0 ------------------------------------- An uncaught error happened in prepare_sql_statement : ORA-01403: no data found Plan hash value: 419152296 ------------------------------------------------------------------------------------------------------- | Id  | Operation              | Name        | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | ------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT       |             |        |       |     2 (100)|          |       |       | |   1 |  PARTITION RANGE SINGLE|             |      1 |    10 |     2   (0)| 00:00:01 |     1 |     1 | |*  2 |   INDEX RANGE SCAN     | I_T_ID_NAME |      1 |    10 |     2   (0)| 00:00:01 |     1 |     1 | ------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1    2 - SEL$1 / TX@SEL$1 Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("ID"=1) Note -----    - Warning: basic plan statistics not available. These are only collected when:        * hint 'gather_plan_statistics' is used for the statement or        * parameter 'statistics_level' is set to 'ALL', at session or system level --//可以发现索引重新建立，变成了local分区索引。 --//你可以发现i_t_id_name也是local分区索引。i_t_name是global索引，大家可以自行验证结果不再贴出。 --//也就是如果建立分区表，其原来索引包含分区的键值并且在前面，建立的索引是前缀分区索引。而原来索引没有包含分区的键值， --//建立的就是global索引。 --//我的理解这个相当于以前的在线重定义，只不过oracle封装为1个命令实现了整个过程罢了。