Oracle 12c: Managing Partitioning

July 2nd, 2013 | Posted in 12c, Blog | 2 Comments


In this post, we will see how to manage partitions in 12c.
We will see :

- Multi partition maintenance.
- Online partition operations.
- Interval Reference Partitioning.
- Asynchronous Global Index maintenance.

1- Before to start:

Let’s create few partitioned tables to use them for all the following examples.

Connected to Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 
Connected as wissem
 
SQL> 
SQL> 
SQL> CREATE TABLE Tab_tst1
  2      ( COL1_ID        NUMBER(6) PRIMARY KEY
  3      , COL2_NAME      VARCHAR2(4000)
  4      , P_DATE        DATE
  5      )
  6  PARTITION BY RANGE (P_DATE)
  7    (PARTITION Tab_tst1_PART1 VALUES LESS THAN (TO_DATE('01-JUL-2013','DD-MON-YYYY')),
  8     PARTITION Tab_tst1_PART2 VALUES LESS THAN (TO_DATE('01-AUG-2013','DD-MON-YYYY')),
  9     PARTITION Tab_tst1_PART3 VALUES LESS THAN (TO_DATE('01-SEP-2013','DD-MON-YYYY')),
 10     PARTITION Tab_tst1_PART4 VALUES LESS THAN (TO_DATE('01-OCT-2013','DD-MON-YYYY')),
 11     PARTITION Tab_tst1_PART5 VALUES LESS THAN (TO_DATE('01-NOV-2013','DD-MON-YYYY')),
 12     PARTITION Tab_tst1_PART6 VALUES LESS THAN (TO_DATE('01-DEC-2013','DD-MON-YYYY')),
 13      PARTITION Tab_tst1_PART7 VALUES LESS THAN (TO_DATE('01-JAN-2014','DD-MON-YYYY')),
 14     PARTITION Tab_tst1_PART_MAX VALUES LESS THAN (MAXVALUE))
 15  /
 
Table created

SQL> CREATE TABLE Tab_tst2
  2      ( COL1_ID        NUMBER(6) PRIMARY KEY
  3      , COL2_NAME      VARCHAR2(4000)
  4      , P_DATE        DATE
  5      )
  6  PARTITION BY RANGE (P_DATE)
  7    (PARTITION Tab_tst1_PART1 VALUES LESS THAN (TO_DATE('01-JUL-2013','DD-MON-YYYY')))
  8  /
 
Table created

SQL>   CREATE TABLE Tab_tst3
  2    ( COL1_ID        NUMBER(6) PRIMARY KEY
  3      , COL2_NAME      VARCHAR2(4000)
  4      , P_DATE        DATE
  5      )
  6  PARTITION BY SYSTEM
  7  ( PARTITION Tab_tst3_PART1,
  8    PARTITION Tab_tst3_PART2,
  9    PARTITION Tab_tst3_PART3,
 10    PARTITION Tab_tst3_PART_MAX);
 
Table created

2- Multi partition maintenance:

Multipartition maintenance enables adding, dropping, truncate, merge, split operations on multiple partitions.

- Drop Multiple Partitions:

The new “ALTER TABLE … DROP PARTITIONS ” help drop multiple partitions or subpartitions with a single statement.

Example:

SQL> ALTER TABLE Tab_tst1 DROP PARTITIONS 
Tab_tst1_PART5, Tab_tst1_PART6, Tab_tst1_PART7;
 
Table altered
 
SQL>

Restrictions :
- You can’t drop all partitions of the table.
- If the table has a single partition, you will get the error: ORA-14083: cannot drop the only partition of a partitioned table.

SQL> ALTER TABLE Tab_tst2 DROP PARTITIONS Tab_tst1_PART1;
 
ALTER TABLE Tab_tst2 DROP PARTITIONS Tab_tst1_PART1
 
ORA-14083: cannot drop the only partition of a partitioned table
 
SQL> 

- Global and local indexes must be rebuild after the operation unless you specify UPDATE INDEXES or UPDATE GLOBAL INDEXES clauses.
Example:

SQL> ALTER TABLE Tab_tst1 DROP PARTITIONS Tab_tst1_PART5, 
Tab_tst1_PART6, Tab_tst1_PART7 UPDATE GLOBAL INDEXES;
 
Table altered
 
SQL> ALTER TABLE Tab_tst1 DROP PARTITIONS Tab_tst1_PART5, 
Tab_tst1_PART6, Tab_tst1_PART7 UPDATE INDEXES;
 
Table altered
 
SQL> 

- Truncate Multiple Partitions:

The new “ALTER TABLE … TRUNCATE PARTITIONS ” help truncate multiple partitions or subpartitions with a single statement. The local indexes are truncated in the operation.

Example:

SQL> ALTER TABLE Tab_tst1 TRUNCATE PARTITIONS Tab_tst1_PART1, 
Tab_tst1_PART2, Tab_tst1_PART3;
 
Table truncated
 
SQL>

Restrictions:

- Global must be rebuild after the operation unless you specify UPDATE INDEXES or UPDATE GLOBAL INDEXES clauses.
Example:

SQL> ALTER TABLE Tab_tst1 TRUNCATE PARTITIONS Tab_tst1_PART1, 
Tab_tst1_PART2, Tab_tst1_PART3 UPDATE GLOBAL INDEXES;
 
Table truncated
 
SQL> ALTER TABLE Tab_tst1 TRUNCATE PARTITIONS Tab_tst1_PART1, 
Tab_tst1_PART2, Tab_tst1_PART3 UPDATE INDEXES;
 
Table truncated
 
SQL>

- ADD Multiple Partitions:

The command “ALTER TABLE … ADD PARTITION … PARTITION … PARTITION … BEFORE” help add multiple partitions or subpartitions with a single statement.

Example:

SQL> ALTER TABLE Tab_tst3 ADD
  2    PARTITION Tab_tst3_PART4,
  3    PARTITION Tab_tst3_PART5
  4    BEFORE PARTITION Tab_tst3_PART_MAX;
 
Table altered
 
SQL> 

Try the same command in 11.2.0.3 database;

SQL> ALTER TABLE Tab_tst3 ADD
  2    PARTITION Tab_tst3_PART4,
  3    PARTITION Tab_tst3_PART5
  4    BEFORE PARTITION Tab_tst3_PART_MAX;
 
ALTER TABLE Tab_tst3 ADD
  PARTITION Tab_tst3_PART4,
  PARTITION Tab_tst3_PART5
  BEFORE PARTITION Tab_tst3_PART_MAX
 
ORA-14043: only one partition may be added

Restrictions:
- The partitioned tables must be a system partitioned table. Trying to add multiple partitions on a non-system partitioned table result in “ORA-14703: The AFTER clause can be used to ADD PARTITION only to a System Partitioned table.”

ALTER TABLE Tab_tst1 ADD
  PARTITION Tab_tst1_PART4,
  PARTITION Tab_tst1_PART5
  BEFORE PARTITION Tab_tst1_PART_MAX
 
ORA-14703: The AFTER clause can be used to ADD PARTITION only to a System Partitioned table.
 
SQL> 

- Merge Multiple Partitions:

The new “ALTER TABLE … MERGE PARTITIONS ” help merge multiple partitions or subpartitions with a single statement. When merging multiple partitions, local and global index operations and semantics for inheritance of unspecified physical attributes are the same for merging two partitions.

Example:

SQL> ALTER TABLE Tab_tst1 MERGE PARTITIONS Tab_tst1_PART5, Tab_tst1_PART6, Tab_tst1_PART7;
 
Table altered
 
SQL> 

- Split into Multiple Partitions:
You can use the following syntax;

SQL> ALTER TABLE Tab_tst1 SPLIT PARTITION Tab_tst1_PART1 INTO
  2  ( PARTITION Tab_tst1_PART1_1 VALUES LESS THAN (TO_DATE('01-JUN-2013','dd-MON-yyyy')),
  3    PARTITION Tab_tst1_PART1_2 VALUES LESS THAN (TO_DATE('15-JUN-2013','dd-MON-yyyy')),
  4    PARTITION Tab_tst1_PART1_3 VALUES LESS THAN (TO_DATE('30-JUN-2013','dd-MON-yyyy')),
  5    PARTITION Tab_tst1_PART1_4 );
 
Table altered
 
SQL> 

3- Online partition operations:

- Online Move Partition:

DML are allowed during Online Move Partition. “ALTER TABLE … MOVE PARTITION” becomes non-blocking online DDL. Global indexes and local indexed are maintained during the move partition, so a manual index rebuild is no longer required. The online partitioning movement removes the read-only state for the actual MOVE PARTITION command.

Example:

SQL> ALTER TABLE Tab_tst1 MOVE PARTITION Tab_tst1_PART1 ONLINE;
 
Table altered
 
SQL> ALTER TABLE Tab_tst1 MOVE PARTITION 
Tab_tst1_PART1 ONLINE UPDATE INDEXES;
 
Table altered
 
SQL> ALTER TABLE Tab_tst1 MOVE PARTITION
Tab_tst1_PART1 ONLINE UPDATE GLOBAL INDEXES;
 
Table altered
 
SQL> 

Restrictions:
- Online on IOT is not supported.

- Online Compress Partition:
Global indexes and local indexed are maintained during the online compress partition, so a manual index rebuild is no longer required.
Example:

- Enable basic partition compression:

SQL> ALTER TABLE Tab_tst1 MOVE PARTITION Tab_tst1_PART1 
COMPRESS BASIC UPDATE INDEXES ONLINE;
 
Table altered

- ROW STORE COMPRESS ADVANCED / FOR OLTP: Oracle Database compresses data during all DML operations on the table. This form of compression is recommended for OLTP environments.
In earlier releases, Advanced Row Compression was enabled using COMPRESS FOR OLTP. This syntax has been deprecated (FOR OLTP still valid syntax for 12.1).

SQL> ALTER TABLE Tab_tst1 MOVE PARTITION Tab_tst1_PART1
 COMPRESS FOR OLTP UPDATE INDEXES ONLINE;
 
Table altered

SQL> ALTER TABLE Tab_tst1 MOVE PARTITION Tab_tst1_PART1 
ROW STORE COMPRESS ADVANCED  UPDATE INDEXES ONLINE;
 
Table altered
 
SQL> 

- COMPRESS FOR QUERY: This compression option is useful in data warehousing environments.

SQL> ALTER TABLE Tab_tst1 MOVE PARTITION Tab_tst1_PART1 
COMPRESS FOR QUERY UPDATE INDEXES ONLINE;
 
Table altered

- COMPRESS FOR ARCHIVE: This compression option is useful for compressing data that will be stored for long periods of time

SQL> ALTER TABLE Tab_tst1 MOVE PARTITION Tab_tst1_PART1
 COMPRESS FOR ARCHIVE UPDATE INDEXES ONLINE;
 
Table altered
 
SQL> 

4- Interval Reference Partitioning:

From 12c, you can use interval partitioned tables as parent tables for reference partitioning.
Example:

Connected to Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 
Connected as wissem
 
SQL> 
SQL> CREATE TABLE Tab_tst4(COL1_ID NUMBER PRIMARY KEY, COL2_NAME      INT)
  2   PARTITION BY RANGE(COL2_NAME) INTERVAL (10)
  3   (PARTITION PART1 VALUES LESS THAN (10));
 
Table created
 
SQL> 
SQL> CREATE TABLE Tab_tst4_child(COL1_ID_FK INT NOT NULL, COL2_NAME INT,
  2   CONSTRAINT COL1_ID_FK FOREIGN KEY(COL1_ID_FK) REFERENCES Tab_tst4(COL1_ID))
  3   PARTITION BY REFERENCE(COL1_ID_FK);
 
Table created
 
SQL> 
SQL> INSERT INTO Tab_tst4 VALUES(10, 10);
 
1 row inserted
SQL> INSERT INTO Tab_tst4 VALUES(20, 20);
 
1 row inserted
SQL> INSERT INTO Tab_tst4 VALUES(30, 30);
 
1 row inserted
 
SQL> commit;
 
Commit complete
 
SQL> 
SQL> SELECT table_name, partition_position, high_value, interval
  2     FROM DBA_TAB_PARTITIONS WHERE table_name IN ('TAB_TST4', 'TAB_TST4_CHILD')
  3     AND TABLE_OWNER = 'WISSEM'
  4     ORDER BY 1, 2;
 
TABLE_NAME                                                                       PARTITION_POSITION HIGH_VALUE                                                                       INTERVAL
-------------------------------------------------------------------------------- ------------------ -------------------------------------------------------------------------------- --------
TAB_TST4                                                                                          1 10                                                                               NO
TAB_TST4                                                                                          2 20                                                                               YES
TAB_TST4                                                                                          3 30                                                                               YES
TAB_TST4                                                                                          4 40                                                                               YES
TAB_TST4_CHILD                                                                                    1                                                                                  NO
 
SQL> 

5- Asynchronous Global Index maintenance:
Refer to my previous post Article

References ; Doc 12cR1



2 Comments to “Oracle 12c: Managing Partitioning”

  1. [...] Wissem EL KHLIFI : Oracle 12c: Partitioning Related Posts:Wissem EL KHLIFI : Oracle 12c: DBMS_PART Package…Wissem EL KHLIFI : Oracle 12c: ASM – New asmcmd commandsWissem EL KHLIFI : Oracle 12c: RMAN recover TableWissem EL KHLIFI : Oracle 12c, RMAN supports SELECT…Tim Hall : Oracle Database 12c Installation On Oracle Linux…Share this:LinkedInTwitterGoogle +1Like this:Like Loading… [...]


Leave a Comment





Polls

which oracle topic interests you most?

View Results

Loading ... Loading ...


Oracle Class Tweets


Recent Posts


Recent Comments

  • Mike: Very nice article!! Thanks for sharing!!
  • yathish: I really appreciate if you can send me, if you have tutorial for 11g (11.2.0.1) RAC & ASM (prefer to be...
  • Farrukh Kamal: Fahad, I’m interesting in pursuing remote employment with Pythian. What tips would you suggest...
  • pavankumar: for the error it need to set env correctly http://orcl11gdba.blogspot.in/ 2014/08/asmcmd-08102-no-con...
  • wissem: Post result off; crsctl check has crsctl check crs
  • ron: unable to connect to Cluster Synchronization Service
  • KevinB: Please post or attach the response file
  • nnarimanov: I’d like to clarify the reason why rman restores all datafiles into +DATA. It’s because of...
  • Sergio: Hi, Where is declared the function agent_clean() ? I dont see agent_clean() definition !! regards, Sergio
  • Sikandar: I do salute you brother, This is very………..nti mes helpful and correct documents. I...
  • Karthik Singh: Thanks for sharing a useful article. Looks like you have installed weblogic 10.3.4. Then shall I...
  • Joseph: Paul, Here target is from which database we are taking cloning. and auxilary is the cloning database. Wissem,...
  • sundar: In Doc ID 1472171.1 it says that the snapshot control file should be in a shared location for RAC databases....
  • Taoqir: Hello, This is a wonderfull and very helping material.now I am going to configure Data Guard. Thanks
  • Adrian Iriarte: Saludos, Que buen articulo, casi siempre al momento de querer implementar soluciones para el...
  • Patricia: Hi Wissem, is there a workaround for this? I mean I want to use the sqlnet.authentication_services =none,...
  • Brian: I created the cdb manually using the instruction, but then I tried to create pdb within in, it failed. have...
  • Farmer Johnson: Hi Wissem. How did you avoid the error you had previsouly encountered: “sudo opatch auto...
  • Héctor Moro: Querido Nelson; Después de entrevistar a mas de 50 profesionales de primer nivel en Uruguay, al fin te...
  • What is NO2 Maximus: I genuinely apрreciated your site! You shoulԁ update it wiϳth new info!