Oracle 12.2 New features----convert non-partitioned tables to partitioned tables online

Before the Oracle12.2 version, if you want to convert a non-partitioned table to a partitioned table, there are several ways to do this: 1. Build the partition table and insert the data into the partition table with the INSERT into SELECT, 2, use the method of online redefinition (dbms_redefinition). Their currency is: the first method, if the table has frequent DML operations, especially the update operation, you need to stop the business to do the conversion. The second method can be operated online without the need to stop the business, but the steps are complex and may be error-prone.

A new feature is provided in the ORACLE12CR2 version, where a statement can convert a non-partitioned table to a partitioned table with the following syntax:

ALTER TABLE table_name MODIFY table_partitioning_clauses [filter_condition] [ONLINE] [UPDATE INDEXES [(Index {L Ocal_partitioned_index | Global_partitioned_index | GLOBAL} [, index {local_partitioned_index | global_partitioned_index |                   GLOBAL}] ...) ]  ]

Let's test the new feature here.

1. Create test tables and related indexes, and view status

[email protected]>create table emp as select * from scott.emp; Table created. [Email protected]>create index idx_emp_no on emp (Empno); Index created. [Email protected]>create index idx_emp_job on emp (Job); Index created. [Email protected]>col table_name for a30[email protected]>col index_name  for a30[email protected]>select table_name,partitioned from user_tables  where table_name= ' EMP '; table_name                      par------------------------------ ---emp                              NO[email protected]>select index_name,partitioned,status From user_indexes where table_name= ' EMP ';index_name                      PAR  STATUS------------------------------ --- --------idx_emp_no                      NO   valididx_emp_job                     no  valid

2. Use the ALTER TABLE statement to perform partition table conversion operations

[Email protected]>alter table EMP Modify 2 partition by range (DEPTNO) interval (TEN) 3 (partition P1 values Le SS Than (Ten), 4 partition P2 values less than (5) online 6; Table altered.

3. View the status of tables and indexes now

[Email protected]>select table_name,partitioned from user_tables where table_ Name= ' EMP '; table_name                      par------------------------------ ---emp                              yes[email protected]>select index_name,partitioned,status from  user_indexes where table_name= ' EMP ';index_name                      PAR  STATUS------------------------------ --- --------idx_emp_no                      NO   Valididx_emp_job                    no   valid[email protected]>select table_name,partition_name from user_tab_partitions  Where table_name= ' EMP '; table_name                      partition_name------------------------------ ------------------------------ emp                             P1EMP                              P2EMP                             SYS_P405EMP                              sys_p406

Now that the table EMP has been converted to a partitioned table, the index is converted to a partitioned index, but the index state is normal.

4. If you want to convert the index at the same time when converting the table, you can use the Update indexes clause

[Email protected]>alter table emp modify  2    partition  by range  (Deptno)  interval  (Ten)   3     ( partition  p1 values less than  (  4      partition),  p2 values less than  (  5    )  online   6    update indexes  7     (idx_emp_no local)   8  ; Table altered. [Email protected]>col table_name for a30[email protected]>col index_name  for a30[email protected]>select table_name,partitioned from user_tables  where table_name= ' EMP '; table_name                      par------------------------------ ---emp                             YES[email protected]> Select index_name,partitioned,status from user_indexes where table_name= ' EMP '; INDEX_ name                      par status------------------------------ --- --------idx_emp_no                       YES N/AIDX_EMP_JOB                     no  valid[email protected]>select table _name,partition_name from user_tab_partitions where table_name= ' EMP '; Table_name      &nbSp;              partition_ NAME------------------------------ ------------------------------emp                              P1EMP                             P2EMP                              SYS_P403EMP                              sys_p404[email protected]>select index_name,partition_name,status from user _ind_partitions where index_nAme= ' Idx_emp_no ';index_name                      PARTITION_NAME                  status------------------------------ -------------- ---------------- --------idx_emp_no                      P1                               USABLEIDX_EMP_NO                      P2                               usableidx_emp_no                      SYS_P403                        USABLEIDX_EMP_NO                      SYS_P404                         usable

From the above results, not only the table EMP is converted to a partitioned table, but also the index idx_emp_no is converted to the partition index, all index states are normal.

Here are some caveats in the official documentation:

When using UPDATE INDEXES the clause, note the following.

• This clause can is used to change the partitioning state of indexes and storage properties of the indexes being converted.

• The specification of the UPDATE INDEXES clause is optional.

  Indexes is maintained both for the online and offline conversion to a partitioned table.

• This clause cannot, the columns on which the original list of indexes is defined.

• This clause cannot, the uniqueness property of the index, or any, of the Index property.

• If you don't specify the tablespace for any of the indexes and then the following tablespace defaults apply.

  • Local indexes after the conversion collocate with the table partition.

  • Global indexes after the conversion reside in the same tablespace of the original global index on the non-partitioned tabl E.

• If you don't specify the INDEXES clause or the clause does not specify all the indexes on the INDEXES original Non-partitione d table, then the following default behavior applies for all unspecified indexes.

  • Global partitioned indexes remain the same and retain the original partitioning shape.

  • non-prefixed indexes become global nonpartitioned indexes.

  • Prefixed indexes is converted to local partitioned indexes.

    Prefixed means that the partition key columns be included in the "index definition, but the" index definition is not limite D to including the partitioning keys only.

  • Bitmap indexes become local partitioned indexes, regardless whether they is prefixed or not.

    Bitmap indexes must always be local partitioned indexes.

• The conversion operation cannot be performed if there is domain indexes.

Reference: Http://docs.oracle.com/database/122/VLDBG/evolve-nopartition-table.htm#VLDBG-GUID-5FDB7D59-DD05-40E4-8AB4-AF82EA0D0FE5

This article is from the "DBA fighting!" blog, so be sure to keep this source http://hbxztc.blog.51cto.com/1587495/1949739

Oracle 12.2 New features----convert non-partitioned tables to partitioned tables online