DBA Hub

📋Steps in this guide1/7

Convert non partitioned table to partitioned online DBACLASS

Convert non partitioned table to partitioned online Oracle 12.2 New feature. In one line we can covert non partitioned table partitioned table online ,without any downtime.

oracle clusteringintermediate
by OracleDba
14 views
1

Overview

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.
2

Section 2

2. Alter table modify to partition the table.( partition key is column CREATED ) This activity will take some time depending upon amount of data table has.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
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);

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;
3

Section 3

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: Lets check for blocking session:

Code/Command (click line numbers to comment):

1
insert into BSSTDBA.ORDER_TAB select * from BSSTDBA.ORDER_TAB;
4

Section 4

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. But what happened to the INDEXES:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
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;

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.

select  index_name,PARTITIONED from dba_indexes where table_name='ORDER_TAB';
 
INDEX_NAME          PARTITIONED     
------------------- ------------ 
ORDER_TAB_IND1      NO         
ORDER_TAB_IND2      YES
5

Section 5

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. 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.
6

Section 6

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

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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.
7

Section 7

SEE: Partitioning using exchange method: Interval Partitioning :

Comments (0)

Please to add comments

No comments yet. Be the first to comment!