Exchange Subpartition in Oracle

We can exchange subpartition in oracle the same way we exchange partition.Let’s see few examples.

1.Exchange subpartition of *-Hash partitioned table :

Here I took example of List-Hash Partitioning method.

SQL> CREATE TABLE LIST_HASH
 (CITY_CD CHAR(2),
 CITY_NAME VARCHAR2(50),
 STATE_CD CHAR(2),
 ZONE_CD CHAR(2))
 PARTITION BY LIST(STATE_CD) SUBPARTITION BY HASH(ZONE_CD)
 (PARTITION P1 VALUES('GJ')
 (SUBPARTITION P1_H1,
  SUBPARTITION P1_H2) ,
  PARTITION P2 VALUES('RJ')
  (SUBPARTITION P2_H1,
   SUBPARTITION P2_H2))  2    3    4    5    6    7    8    9   10   11    (more...)

Exchange Partition of *-List and *-Range Partition

1.Exchange Partition of *-List Partition

Here I took example of Range-List partitioning method.

SQL> CREATE TABLE LIST_COMP_PART
  2      (EMP_ID NUMBER,
EMP_NAME VARCHAR2(30),
AGE VARCHAR2(50),
DEPT_ID CHAR(3))
PARTITION BY RANGE(AGE) SUBPARTITION BY LIST(DEPT_ID) 
(PARTITION P_R1 VALUES LESS THAN(25)
(SUBPARTITION P_R1_L1 VALUES('SLS','ACC'),
 SUBPARTITION P_R1_L2 VALUES('HRM','MAR')
),
PARTITION P_R2 VALUES LESS THAN(35)
(SUBPARTITION P_R2_L1 VALUES('SLS','ACC'),
 SUBPARTITION P_R2_L2 VALUES('HRM','MAR')
),
PARTITION P_R3 VALUES LESS THAN(MAXVALUE)
(SUBPARTITION P_R3_L1 VALUES('SLS','ACC'),
 SUBPARTITION P_R3_L2 VALUES('HRM','MAR')
));  3    4    5    6    7    8     (more...)

Exchanging a Partition of an Interval Partitioned Table

Let’s create Interval-Partitioned table and exchange partitions.

Step 1 : Create Interval Partitioned Table

SQL> CREATE TABLE TRAN_ALL
  2  (TRAN_ID NUMBER,
  DATE_OF_TRANSACTION DATE,
  AMOUNT VARCHAR2(100),
  ACCOUNT_NUMBER NUMBER
  )
PARTITION BY RANGE(DATE_OF_TRANSACTION) INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))
(PARTITION P1 VALUES LESS THAN(TO_DATE('01-01-2019','DD-MM-YYYY')),
PARTITION P2 VALUES LESS THAN(TO_DATE('01-02-2019','DD-MM-YYYY')),
PARTITION P3 VALUES LESS THAN(TO_DATE('01-03-2019','DD-MM-YYYY')),
PARTITION P4 VALUES LESS THAN(TO_DATE('01-04-2019','DD-MM-YYYY')),
PARTITION P5 VALUES LESS THAN(TO_DATE('01-05-2019','DD-MM-YYYY')),
PARTITION P6 VALUES LESS THAN(TO_DATE('01-06-2019','DD-MM-YYYY')))
;

Let’s insert some records in TRAN_ALL table. I have staging table which (more...)

Virtual Column Partitioning in Oracle

Oracle 11g came up with concept of Virtual Column which is created with expression. Which does not stores data physically rather it calculates at run time and give us output when we fetch using query.

We saw different types of partitioning in previous posts, today we will see how we can partition the table using virtual column.

To see Types of Partitioning :

Let’s create a table using virtual (more...)

Read-Only Partitions in Oracle

Read-Only partitions are of benefits

1.When you are working in Data-Warehouse environment there must be many partitions which are not updated frequently you can add those partitions to read-only state and then add those partitions to read-only tablespace. These read-only tablespace can help you take your RMAN backup faster as read-only tablespace can be avoided to be backed up in incremental backups.

2.By putting partitions to read-only state you can perform maintenance activity.

(more...)

Interval-* Composite Partitioned Tables

In my previous composite partitioning in oracle article I saw example of various types of composite partitioning methods. In addition to that post today we will see composite partitioning with INTERVAL partitioning type.

To read about previous composite partitioning :

In Interval-* composite partitioning method we will see Interval-Range , Interval-List , Interval-Hash.

1.Create a Interval-range composite partitioned table

SQL> CREATE TABLE TRAN_INTERVAL_range
(TRAN_ID NUMBER,
DATE_OF_TRAN DATE,
AMOUNT NUMBER(10,2))
PARTITION  (more...)

Modify Partitioned Table to Non-partitioned Table

In previous post we saw modifying non-partitioned table into partitioned one.Today we will see how we can convert partitioned table into non-partitioned table.

To read non-partitioned table to partitioned table:

Step 1 : Lets check first table is partitioned one.

SQL> SELECT TABLE_NAME,PARTITIONED FROM USER_TABLES WHERE TABLE_NAME='TRANSACTIONS';

TABLE_NAME															 PAR
-------------------------------------------------------------------------------------------------------------------------------- ---
TRANSACTIONS															 YES

Step 2 : To convert partitioned table to non-partitioned one we need to take backup and (more...)

Adding Table Partition in Oracle

Here we will see examples to add partition in all different types of partitioning methods.

1.Add Partition in Range-Partitioned tableĀ 

SQL> ALTER TABLE TRANSACTIONS ADD PARTITION Y_2020 VALUES LESS THAN (TO_DATE('01-JAN-2021','DD-MON-YYYY'));

Table altered.

2.Add Partition in Hash-Partitioned table

SQL> SELECT TABLE_NAME,PARTITION_NAME FROM ALL_TAB_PARTITIONS WHERE TABLE_NAME='TRAN_HASH';

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
TRAN_HASH                      SYS_P121
TRAN_HASH                      SYS_P122
TRAN_HASH                      SYS_P123
TRAN_HASH                      SYS_P124
TRAN_HASH                      SYS_P125

5 rows selected.

SQL> ALTER TABLE TRAN_HASH ADD PARTITION SYS_P126;

Table altered.

SQL>  SELECT  (more...)