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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s