Tag Archives: expdp backup

Expdp backup for partition table

Expdp backup for partition table

Oracle having feature for partition of the tables to divide the data into partitions wise and increase the performance o f the tables.

Note: Partition in oracle require separate licensing.

Create a table invoices with 4 partition name invoice_q1,invoice_q2,invoice_q3,invoice_q4.

Partition is created as range partitions on the basis of dates like column invoice_date.

Following is syntax of creating range partition table:

CREATE TABLE invoices
(invoice_no    NUMBER NOT NULL,
invoice_date  DATE   NOT NULL )
PARTITION BY RANGE (invoice_date)
(PARTITION invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2011', 'DD/MM/YYYY')) TABLESPACE users,
PARTITION invoices_q2 VALUES LESS THAN (TO_DATE('01/07/2012', 'DD/MM/YYYY')) TABLESPACE users,
PARTITION invoices_q3 VALUES LESS THAN (TO_DATE('01/09/2013', 'DD/MM/YYYY')) TABLESPACE users,
PARTITION invoices_q4 VALUES LESS THAN (TO_DATE('01/01/2014', 'DD/MM/YYYY')) TABLESPACE users);

Use EXPDP utility for taking the logical backup of the partition tables.
Following is syntax:

expdp tables=SCHEMA_NAME.TABLE_NAME directory=directoryname dumpfile=datafile logfile=logfile

Example:
expdp tables=sales.invoices directory=dbbackup dumpfile=invoice.dmp logfile=invoice.log

WARNING: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.

Starting "SYS"."SYS_EXPORT_TABLE_01":  /******** AS SYSDBA tables=sales.invoices
directory=dbbackup dumpfile=invoice.dmp logfile=invoice.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 8 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "SALES"."INVOICES":"INVOICES_Q2"             5.898 KB       1 rows
. . exported "SALES"."INVOICES":"INVOICES_Q1"                 0 KB       0 rows
. . exported "SALES"."INVOICES":"INVOICES_Q3"                 0 KB       0 rows
. . exported "SALES"."INVOICES":"INVOICES_Q4"                 0 KB       0 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is: C:\invoice.dmp