This is one of the new feature of oracle 12.2 release .
Non-partitioned tables can be converted to partitioned table online without any downtime to the application , i.e no impact to the DML activities.
Till now for this activity, we were using dbms_redef methods. But in Oracle 12.2 release this has been simplified much.
See the below example:
1. Identify the non partitioned table.
SQL> desc BSSTDBA.ORDER_TAB
Name Null? Type
----------------------------------------- -------- ----------------------------
ROW_ID NOT NULL VARCHAR2(15 CHAR)
CREATED NOT NULL DATE
CREATED_BY NOT NULL VARCHAR2(15 CHAR)
LAST_UPD NOT NULL DATE
MODIFICATION_NUM NOT NULL NUMBER(10)
CONFLICT_ID NOT NULL VARCHAR2(15 CHAR)
ALW_PART_SHIP_FLG NOT NULL CHAR(1 CHAR)
SQL> col owner for a13
SQL> col table_name for a14
SQL> set lines 299
SQL> select owner,table_name,partitioned from dba_tables where table_name='ORDER_TAB';
OWNER TABLE_NAME PAR
------------- -------------- ---
BSSTDBA ORDER_TAB NO
SQL> select count(*) from BSSTDBA.ORDER_TAB;
COUNT(*)
----------
954598
SQL> SQL> create index BSSTDBA.ORDER_TAB_IND1 on BSSTDBA.ORDER_TAB(row_id);
Index created.
SQL> SQL> create index BSSTDBA.ORDER_TAB_IND2 on BSSTDBA.ORDER_TAB(created);
2. Alter table modify to partition the table.( partition key is column CREATED )
alter table BSSTDBA.ORDER_TAB modify
PARTITION BY RANGE (CREATED)
(partition created_2105_p8 VALUES LESS THAN (TO_DATE('01/09/2015', 'DD/MM/YYYY')),
partition created_2105_p9 VALUES LESS THAN (TO_DATE('01/10/2015', 'DD/MM/YYYY')),
partition created_2105_p10 VALUES LESS THAN (TO_DATE('01/11/2015', 'DD/MM/YYYY')),
partition created_2105_p11 VALUES LESS THAN (TO_DATE('01/12/2015', 'DD/MM/YYYY')),
partition created_2105_p12 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')),
PARTITION Created_MX VALUES LESS THAN (MAXVALUE)) ONLINE;
This activity will take some time depending upon amount of data table has.
While this alter statement is running, I have started running DML activities on the same table, To check whether it is impacting the DML activities.
SESSION 2:
insert into BSSTDBA.ORDER_TAB select * from BSSTDBA.ORDER_TAB;
Lets check for blocking session:
SID USERNAME MODULE STATUS EVENT BLOCKING_SESSION
---------- -------------------- ------------------------------ -------- ------------------------------ ----------------
490 SYS sqlplus@bttstdev64 (TNS V1-V3) ACTIVE enq: TX - row lock contention 7
SID > 490
SQL_TEXT > alter table BSSTDBA.ORDER_TAB modify PARTITION BY RANGE (CREATE
D) (partition created_2105_p8 VALUES LESS THAN (TO_DATE('01/09/2
015', 'DD/MM/YYYY')), partition created_2105_p9 VALUES LESS THAN
(TO_DATE('01/10/2015', 'DD/MM/YYYY')), partition created_2105_p
10 VALUES LESS THAN (TO_DATE('01/11/2015', 'DD/MM/YYYY')), parti
tion created_2105_p11 VALUES LESS THAN (TO_DATE('01/12/2015', 'D
D/MM/YYYY')), partition created_2105_p12 VALUES LESS THAN (TO_DA
TE('01/01/2016', 'DD/MM/YYYY')), partition created_2016_p1 VALUE
THAN (MAXVALUE)) ONLINE
SID > 7
SQL_TEXT> insert into BSSTDBA.ORDER_TAB select * from BSSTDBA.ORDER_TAB;
We can see that the insert statement(SID 7), is blocking the alter table command(SID 490), not the other way around. It means during this partition conversion activity, if any DML requests are coming, then it will allow them to complete their request. This may slow down the partition conversion time, But it won’t impact the application. Once ALTER TABLE MODIFY is completed. Check the whether table was partitioned properly or not.
SQL> select partition_name,high_value from dba_tab_partitions where table_name='ORDER_TAB';
PARTITION_NAME HIGH_VALUE
----------------------- --------------------------------------------------------------------------------
CREATED_2105_P10 TO_DATE(' 2015-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
CREATED_2105_P11 TO_DATE(' 2015-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
CREATED_2105_P12 TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
CREATED_2105_P8 TO_DATE(' 2015-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
CREATED_2105_P9 TO_DATE(' 2015-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
CREATED_MX MAXVALUE
20 rows selected.
But what happened to the INDEXES:
select index_name,PARTITIONED from dba_indexes where table_name='ORDER_TAB'; INDEX_NAME PARTITIONED ------------------- ------------ ORDER_TAB_IND1 NO ORDER_TAB_IND2 YES
We can see ORDER_TAB_IND1 was NON partitioned, But ORDER_TAB_IND2 was partitioned.
Oracle document Says:
If no index clause is mentioned in the alter table statement, then
nonprefixed indexes(i.e index column is not a partitioned key) will be become global non-partitioned Index.
prefixed indexes(i.e index column is a partitioned key) will become local partitioned Index.
ORDER_TAB_IND1 - ------------------- INDEX_SQL - > create index BSSTDBA.ORDER_TAB_IND1 on BSSTDBA.ORDER_TAB(row_id); It is an nonprefixed Index i.e index column is not a partitioned key. So it became global non partitioned Index ORDER_TAB_IND2 - -------------------- create index BSSTDBA.ORDER_TAB_IND2 on BSSTDBA.ORDER_TAB(created); It is an prefixed Index. i.e index column in a partitione key . So this indexes became local partitioned Index.
SQL> select index_name,PARTITION_NAME,HIGH_VALUE from dba_ind_partitions where index_name='ORDER_TAB_IND2';
INDEX_NAME PARTITION_NAME HIGH_VALUE
------------------- ----------------------- --------------------------------------------------------------------------------
ORDER_TAB_IND2 CREATED_2016_P9 TO_DATE(' 2016-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDER_TAB_IND2 CREATED_2105_P10 TO_DATE(' 2015-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDER_TAB_IND2 CREATED_2105_P11 TO_DATE(' 2015-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDER_TAB_IND2 CREATED_2105_P12 TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDER_TAB_IND2 CREATED_2105_P8 TO_DATE(' 2015-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDER_TAB_IND2 CREATED_2105_P9 TO_DATE(' 2015-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDER_TAB_IND2 CREATED_MX MAXVALUE
20 rows selected.
There are lot of 12.2 New features like moving a table online, Spliting a partitioned table online etc. which we will discuss very soon.
SEE:
