It’s time to a new blog about partitioning improvement in 12cR2.
After auto-list partitioning and multi-column list partitioning & read-only partitions, I’ll demonstrate how we can easily convert a non-partitioned table to a partitioned table…online !

As a reminder, the way to convert a table to a partitioned table were to use the DBMS_REDEFINITION package (since 9i to 12cR1) which require few steps :

  1. Verify that the table is a candidate for redefinition :
    DBMS_REDEFINITION.CAN_REDEF_TABLE
  2. Create an interim partitioned table
  3. Start the redefinition :
    DBMS_REDEFINITION.START_REDEF_TABLE
  4. Copy dependent objects to the new table (grants, constraints, …) :
    DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS
  5. Synchronize the interim table (optional) :
    DBMS_REDEFINITION.SYNC_INTERIM_TABLE
  6. Complete the redefinition :
    DBMS_REDEFINITION.FINISH_REDEF_TABLE

With 12cR2, the conversion can be done in one shot :

Non-partitioned table creation :
SQL> CREATE TABLE sales_non_part
(
sales_id NUMBER(5),
sales_name VARCHAR2(30),
sales_city VARCHAR2(20),
sales_amount NUMBER(10)
);
Table created.

Data insertion :
SQL> INSERT INTO sales_non_part VALUES (1, 'JOHN', 'DELEMONT', 10);
1 row created.


SQL> INSERT INTO sales_non_part VALUES (2, 'JANE', 'BERN', 150);
1 row created.


SQL> INSERT INTO sales_non_part VALUES (3, 'JACK', 'NYON', 20);
1 row created.

Before starting the conversion, I’ll enable the lock tracing to prove that the operation is made online.
You can take note of the new statement in 12cR2 to enable it :
SQL> ALTER SESSION SET events='trace[ksq] disk medium';


Session altered.
(The “old” one was : alter session set events=’10704 trace name context forever, level 3′;)

Let’s start the conversion of the sales_non_part table :
SQL> ALTER TABLE sales_non_part MODIFY PARTITION BY LIST (sales_city) AUTOMATIC (PARTITION p_delemont VALUES ('DELEMONT')) ONLINE;


Table altered.

Looks good. Quick check of the partitions :
SQL> SELECT partition_name, high_value FROM dba_tab_partitions WHERE table_name = 'SALES_NON_PART';


PARTITION_NAME HIGH_VALUE
-------------------- ------------------------------
P_DELEMONT 'DELEMONT'
SYS_P5537 'BERN'
SYS_P5538 'NYON'

The table is now partitioned by list and with the AUTOMATIC option.
Now, we can check the locks which were applied on the table during the conversion, firstly by finding the objects ID of the table and of the partitions :
SQL> SELECT TO_CHAR(object_id,'0XXXXXXX'), object_name, object_type FROM all_objects WHERE owner='SYS' and object_name='SALES_NON_PART' ORDER BY 2;


TO_CHAR(O OBJECT_NAME OBJECT_TYPE
--------- --------------- ------------------------------
00011CD1 SALES_NON_PART TABLE
00011CE0 SALES_NON_PART TABLE PARTITION
00011CE1 SALES_NON_PART TABLE PARTITION
00011CE2 SALES_NON_PART TABLE PARTITION
Then by looking for the generated trace file :
SQL> SELECT tracefile FROM v$process WHERE addr=(SELECT paddr FROM v$session WHERE sid=sys_context('USERENV','SID'));


TRACEFILE
----------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_8223.trc

And finally by checking inside the file which kind of locks were hold on the objects :
SQL> host cat /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_8223.trc | grep -e 00011CD1 -e 00011CE0 -e 00011CE1 -e 00011CE2;


2016-12-16 11:22:09.821*:ksq.c@9006:ksqgtlctx(): *** TM-00011CD1-00000000-00000000-00000000 mode=3 flags=0x401 why=165 timeout=0 ***
2016-12-16 11:22:09.871*:ksq.c@9006:ksqgtlctx(): *** OD-00011CD1-00000000-00000000-00000000 mode=6 flags=0x10401 why=264 timeout=0 ***
2016-12-16 11:22:10.283*:ksq.c@9006:ksqgtlctx(): *** DL-00011CE0-00000000-00000000-00000000 mode=3 flags=0x10001 why=207 timeout=0 ***
2016-12-16 11:22:10.283*:ksq.c@9006:ksqgtlctx(): *** DL-00011CE1-00000000-00000000-00000000 mode=3 flags=0x10001 why=207 timeout=0 ***
2016-12-16 11:22:10.283*:ksq.c@9006:ksqgtlctx(): *** DL-00011CE2-00000000-00000000-00000000 mode=3 flags=0x10001 why=207 timeout=0 ***

One TM-lock mode=3 (Row Exclusive lock) were holds on the table. So the DML operations stay possible during the conversion !
Conclusion : the conversion is really done online 🙂

Information concerning the others locks :
OD (Online DDL) lock : used internally to allow truly online DDL operations
DL : lock to prevent index DDL during direct load
TM : synchronizes accesses to an object

Post Views: 675
Thumbnail [60x60]
by
Joël Cattin