--//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 

郑州同济医院：http://jbk.39.net/yiyuanzaixian/zztjyy/

------------------------------------------------------------------------------------------------------- 
| 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个命令实现了整个过程罢了。