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 […]

Read 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. Step 2 : To convert partitioned table to non-partitioned one we need to take backup […]

Read 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  2.Add Partition in Hash-Partitioned table 3.Add Partition in List-partitioned table 4.Add Partition in Interval-Partitioned table We can’t simply add partition in interval-partitioned table. But to add partition we can LOCK the last partition that will […]

Read More

Merging Table Partitions in Oracle

In previous post I wrote about splitting single partition into two or more partitions. In this post we will see how we can merge two or more partitions into single partition and can reduce the overhead of managing multiple partitions. To read split partitions Here we will see example of merging different types of partiitons. […]

Read More

Split Partition in Oracle

Oracle split partition feature allows you to split partition which are already created. We can take benefit of this feature when in specific partition lots of records are inserted and its tough to maintain it individually. In this post we will see how can we partition different types of partition. 1.Splitting a partition of range-partitioned […]

Read More

Export and Import of Partition

In this post we will see export and import operation of a table partition. Let’s see step by step. Step 1 : Create a directory  Step 2 : Run expdp command to take partition backup We can see in above output I have defined tables=TRANSACTIONS_MAIN:Y_2015 clause which will export Y_2015 partition of TRANSACTIONS_MAIN table. We […]

Read More

Load a Partition with SQL*Loader

In this post we will see how we can insert records in specific partition using sql*loader. Step 1 : Create a .dat file which contains records Step 2 : Create .ctl file In above output we can see I am trying to insert records in L1 partition of TEST_LOADER table. Step 3 : Prepare a […]

Read More

Renaming Partitions and Subpartitions

Rename operations in partitions and subpartitions are as easy as renaming tables  we just need to take care of syntax. Rename Partitions : We can see in above steps I have renamed Y_2013  partition of transaction table to Y_1_2013. Rename Subpartition : Same way we can rename a subpartition too. Let us see how. We […]

Read More

Exchange Partition in Oracle

Exchanging Partitions is something new. And it provides a huge benefits. By exchanging partition you can actually get data quickly in or out of partitioned table. The data that resides in the partition is exchanged with given non partitioned or partitioned table without deletion and insertion operation.  Things to be taken care of while performing […]

Read More

Modify Non-Partitioned Table to Partitioned

After seeing many articles about types or partitions , benefits of partitions , different operation on partitions , the one question which will surely arise in DBA’s brain is what about my existing table. I am not going to drop my existing partition and create new partitioned table and insert records to those partitioned table. […]

Read More