Partitioning in oracle database

What is partitioning in theory:

Oracle partitioning is a licensing option of oracle database only available with Oracle EE.

Partitioning allows a table, index, IOT to be subdivided into small pieces. Each piece of these objects is called a partition. Each partition has its own name. From DBA Perspective, a partitioned objects has several partitions and can be managed individually or collectively. However, From Application perspective partitioning of a DB object is transparent and partitioned object is similar to non-partition object and not necessary to modify the application queries when accessing to the partitioned object.

From DBA point of view, we can achieve several performance benefits with partitioning. Here I mentioned few.

•   Let’s say a company is running for over 10 years and their application is having 10 years of employee data consists of the employee joining date, salary and other details and this table is partitioned by a year and has 10 partitions in the table. The company HR is trying to access the data of the employees for a particular year then this query would access one partition instead of 10 partitions. Like this we can achieve 10 times faster data retrieval to the customer.
•  The I/O contention will be reduced. Partitioned tables will be physically stored in the datafiles of the tablespace in different drives or filesystems. Hash partitioning can be used to evenly distributes the rows among partitions, giving partitions approximately the same size.
•  Let say one partition of the table is unavailable and all remaining partitions are available and online. The application can access the table and get the results until and unless the query required the unavailable partition likewise High availability can achieved.

Enable partition option as a DBA

Partition enabling in 10g

Please follow the below steps to enable the partition in the oracle database using chopt tool

·         Check whether the partitioning is enable or not with the below query

Select * from v$option;

·         If disable then shut down the database using sqlplus or srvctl command and its services

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk part_on

$ORACLE_HOME/bin/relink all

·         Bring up the database.

·         Select * from v$option;

Partition enabling in 11g

Cd $ORACLE_HOME/bin

chopt enable partitioning

Disable partitioning

cd $ORACLE_HOME/bin
chopt disable partitioning