Scribd
Upload
11 views

Oracle Partitioning - Enhance Performance & Data Management-1

Uploaded by

retrogradeview
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
11 views

Oracle Partitioning - Enhance Performance & Data Management-1

Uploaded by

retrogradeview
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 6

www.linkedin.com/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')),

www.linkedin.com/daniyaldba/ +923408801269 danishjee05@gmail.com


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

www.linkedin.com/daniyaldba/ +923408801269 danishjee05@gmail.com


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/user01.dbf
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

www.linkedin.com/daniyaldba/ +923408801269 danishjee05@gmail.com


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

www.linkedin.com/daniyaldba/ +923408801269 danishjee05@gmail.com


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=================================

www.linkedin.com/daniyaldba/ +923408801269 danishjee05@gmail.com

You might also like