[Link]/daniyaldba/ +923408801269 danishjee05@gmail.
com
PARTITIONING IN ORACLE
Partitioning in Oracle is a technique used to divide large tables and indexes into smaller, more
manageable pieces called partitions. This can improve performance, manageability, and
availability. Here’s a step-by-step explanation:
Step 1: Understand Partitioning Types
Oracle supports several types of partitioning:
Range Partitioning: Partitions are defined by a range of values, such as dates or numbers.
List Partitioning: Partitions are defined by a list of values, such as regions or categories.
Hash Partitioning: Partitions are created by hashing a column’s values, distributing rows evenly
across partitions.
Composite Partitioning: Combines range, list, and hash partitioning methods.
Interval Partitioning: Automatically creates partitions based on a specified interval, such as
monthly or yearly.
Step 2: Create a Partitioned Table
Here’s an example of creating a range-partitioned table based on a date column:
SQL> CREATE TABLE sales (
sale_id NUMBER PRIMARY KEY,
product_id NUMBER,
sale_date DATE,
amount NUMBER
)
PARTITION BY RANGE (sale_date) (
PARTITION p2023 VALUES LESS THAN (TO_DATE('01-JAN-2024', 'DD-MON-YYYY')),
PARTITION p2024 VALUES LESS THAN (TO_DATE('01-JAN-2025', 'DD-MON-YYYY')),
[Link]/daniyaldba/ +923408801269 danishjee05@[Link]
PARTITION p2025 VALUES LESS THAN (TO_DATE('01-JAN-2026', 'DD-MON-YYYY'))
);
Table created.
Step 3: Insert Data into Partitions
Insert data into the partitioned table:
SQL>
INSERT INTO sales (sale_id, product_id, sale_date, amount) VALUES (1, 100, TO_DATE('15-
DEC-2023', 'DD-MON-YYYY'), 500);
INSERT INTO sales (sale_id, product_id, sale_date, amount) VALUES (2, 101, TO_DATE('05-
JAN-2024', 'DD-MON-YYYY'), 300);
2 row created.
Step 4: Query Data from Partitions
Query data from specific partitions:
SQL> SELECT * FROM sales PARTITION (p2023);
SALE_ID PRODUCT_ID SALE_DATE AMOUNT
---------- ---------- --------- ----------
1 100 15-DEC-23 500
Step 5: Manage Partitions
[Link]/daniyaldba/ +923408801269 danishjee05@[Link]
Manage partitions by adding, dropping, or merging them as needed:
Add a new partition
SQL> ALTER TABLE sales ADD PARTITION p2026 VALUES LESS THAN
(TO_DATE('01-JAN-2027', 'DD-MON-YYYY'));
Table altered.
Drop a partition
SQL> ALTER TABLE sales DROP PARTITION p2023;
Table altered.
Merge partitions
SQL> ALTER TABLE sales MERGE PARTITIONS p2024, p2025 INTO PARTITION
p2024_2025;
Table altered.
Step 6: Backup and Recovery
Backup and recovery individual partitions:
Backup a partition
Using RMAN:
You will typically backup the entire tablespace or datafile that contains the partition
RMAN>BACKUP DATAFILE ‘/u01/app/oracle/oradata/orcl/[Link]
Or Tablespace
RMAN > BACKUP TABLESPACE TESTTBLSPC;
Use Oracle Data Pump for Partition-Level Export
expdp username/password DIRECTORY=dump_directory
DUMPFILE=partition_sales_p2023.dmp TABLES=sales:p2023
[Link]/daniyaldba/ +923408801269 danishjee05@[Link]
Recover a partition
Since RMAN doesn’t support direct partition-level backups or restores,
you will typically restore the entire tablespace or datafile that contains the partition. Here are the
steps for each case.
a) Restore a Tablespace in RMAN
If you backed up the tablespace containing the partition, you can restore it like this:
RMAN> CONNECT TARGET /;
Take the tablespace offline
RMAN> SQL 'ALTER TABLESPACE TESTTBLSPC OFFLINE IMMEDIATE';
Restore the tablespace
RMAN> RESTORE TABLESPACE TESTTBLSPC;
Recover the tablespace to apply any archived logs or redo entries
RMAN> RECOVER TABLESPACE TESTTBLSPC;
Bring the tablespace online
RMAN> SQL 'ALTER TABLESPACE TESTTBLSPC ONLINE';
To restore (import) this partition data back into the database
impdp username/password DIRECTORY=dump_directory
DUMPFILE=partition_sales_p2023.dmp TABLES=sales:p2023
[Link]/daniyaldba/ +923408801269 danishjee05@[Link]
IMPORTANCE OF PARTITIONING
Partitioning in Oracle is critical for a DBA because it provides powerful tools to manage large
datasets effectively, optimizing performance, data maintenance, and availability. Here’s why
these capabilities are essential:
1. Enhanced Query Performance and Scalability: Partitioning improves query speed by
scanning relevant partitions and utilizing partitioned indexes, reducing I/O and boosting
performance.
2. Simplified Data Maintenance and Management: Partitioning enables efficient loading,
purging, and lifecycle management, streamlining maintenance tasks and aligning with
business rules.
3. High Availability and Minimized Downtime: Online partition operations ensure
availability, and partition-specific recovery options minimize downtime.
4. Improved Backup and Recovery Efficiency: Partitioned backups with Data Pump and
tablespace-level recovery enhance backup efficiency and recovery speed.
5. Adaptable to Business Requirements: Data partitioning aligns with business logic,
allowing optimized storage and handling of data growth based on business needs.
6. Compliance and Data Retention: Partitioning supports data retention policies and
security measures, aiding compliance and safeguarding sensitive data.
=========================GOOD LUCK=================================
[Link]/daniyaldba/ +923408801269 danishjee05@[Link]
