Export and Import of Partitions - Oracle 11g

Oracle Database 11g adds the ability to allow partitions to be exported and imported with Data Pump using the TRANSPORTABLE=ALWAYS option.In previous releases,the transportable tablespace mechanism of Data Pump could be speci fied only at the tablespace level.

Now,Oracle Database 11g provides the mechanism to export/import partitions/ sub partitions without having to move an entire table or exchange partitions by introducing the PARTITION_OPTIONS parameter.The default behavior is to departition a partitioned table export to a new table at import time.If the TRANSPORTABLE option is not set during export or the partition name is not specified as a filter, the expected behavior would be none.

Allowable values for PARTITION_OPTIONS are as follows:

  • none: Creates the table as it was on the source database.This option cannot be used if the transportable option was set with the partition/subpartition filter during the export.
  • departition: Creates each partition/subpartition as a new table.The new table name will be a derived name from the table and partition name.If the export was performed with the TRANSPORTABLE option and partition/subpartition was specified,the import must be done using the departition option.
  • merge: Combines all partitions/subpartitions into a single table.This option cannot be used if the TRANSPORTABLE option was set with the partition/subpartition filter during the export.

Let’s take a look at an example of how to use the departition option to import a single partition of a table.Here we have a list-partitioned table called KB:

SQL> create table kb
2 (doc_id number,
3 category varchar2(55),
4 note varchar2(4000))
5 partition by list (category)
6 (
7 partition p_dg values ('DG') tablespace kb1,
8 partition p_plsql values ('PLSQL') tablespace kb2,
9 partition p_default values (default) tablespace kb3
10* )
/
Table created.

By querying the DBA_TAB_PARTITIONS view, you can confirm that the P_PLSQL partition resides on the KB2 tablespace.We will perform a single row insert into the P_PLSQL partition with the DOC_ID of 1001:

SQL> insert into kb
2 values
3 (1001, 'PLSQL', 'PL/SQL in Oracle Database 11g is faster and better!')
4 /
1 row created.

Document identifier 1001 will be established as the baseline data for this demonstration.Behind the scenes,the KB2 tablespace is converted into a read-only tablespace.Next,we unload the P_PLSQL partition of the KB table using Data Pump export with the TRANSPORTABLE=ALWAYS option, as shown here:

DBA11g > expdp tables=sh.kb:p_plsql userid=sh/sh
directory=dbadir dumpfile=p_plsql.dmp
logfile=logdir_p_plsql.log
reuse_dumpfiles=Y
transportable=always
Export: Release 11.1.0.6.0 - Production on Saturday, 01 September, 2007 23:46:03
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SH"."SYS_EXPORT_TABLE_01": tables=sh.kb:p_plsql userid=sh/********
directory=dbadir dumpfile=p_plsql. dmp logfile=logdir_p_plsql.log
transportable=always
Processing object type TABLE_EXPORT/TABLE/PLUGTS_BLK
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/END_PLUGTS_BLK
Master table "SH"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
*******************************************
Dumpfile set for SH.SYS_EXPORT_TABLE_01 is:
/home/oracle/p_plsql.dmp
********************************************
Datafiles required for transportable tablespace KB2:
+DATA/dba11g/datafile/kb2.294.632183817 Job "SH"."SYS_EXPORT_TABLE_01" successfully completed at 23:46:14

The Data Pump export log indicates that the KB2 tablespace datafile resides in the DATA diskgroup in the ASM instance.The datafile, +DATA /dba11g /datafile /kb2.294.632183817, is required to transport the KB2 tablespace.

We will now take the Data Pump export file p_plsql.dmp and the+DATA /dba11g/datafile/kb2. 294.632183817 datafile and perform a copy of the datafile followed by an import into another database.The easiest way to take a datafile out of ASM is to copy the datafile out of ASM to the file system using the cp command .Here the +DATA/dba11g/datafile/kb2.294.632183817 file is copied to the /tmp directory on the operating system:

ASMCMD [+] > cp -irf +DATA/dba11g/datafile/kb2.294.632183817 /tmp/kb2.dbf
source +DATA/dba11g/datafile/kb2.294.632183817
target /tmp/kb2.dbf
copying file(s)...
file, /tmp/kb2.dbf, copy committed.

Next,we will copy the kb2.dbf file into the ASM instance on another server.Using any kind of secure copy or file transfer utility,transfer the /tmp/kb2.dbf and p_plsql.dmp files to the target database server.Once the files are successfully copied to the target server, using the same ASM copy command (cp) syntax used earlier in the asmcmd utility,copy the kb2.dbf file into the ASM diskgroup, as shown here:

ASMCMD [+DATA/DBA11G1/DATAFILE] > cp /home/oracle/kb2.dbf .
source /home/oracle/kb2.dbf
target +DATA/DBA11G1/DATAFILE/kb2.dbf
copying file(s)...
file, +DATA/dba11g1/datafile/kb2.dbf, copy committed

Now,we can import the P_PLSQL partition of the KB table using thePARTITION_OPTIONS=DEPARTITION option in conjunction with the
TRANSPORT_ DATAFILES option of the Data Pump import utility, as shown here:

DBA11g1 > impdp userid="'/ as sysdba'"
partition_options=departition
dumpfile=dbadir:p_plsql.dmp
logfile=logdir:p_plsql.log
transport_datafiles='+FRA/dba11g1/kb2.dbf'
Import: Release 11.1.0.6.0 - Production on Sunday, 02 September, 2007 1:46:32
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": userid='/******** AS SYSDBA'
partition_options=departition dumpfile=dbadir: p_plsql.dmp logfile=logdir:p_plsql.log
transport_datafiles=+FRA/dba11g1/kb2.dbf
Processing object type TABLE_EXPORT/TABLE/PLUGTS_BLK
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/END_PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 01:46:37

Logging in as the SH user, you can verify the partition KB:P_PLSQL is created as [table_name]_[partition_name].In the following example, you notice a table called KB_P_PLSQL created from the earlier example and that the row we inserted exists in the departitioned table:

SQL> desc kb_p_plsql
Name Null? Type
----------------- -------- ------------
DOC_ID NUMBER
CATEGORY VARCHAR2(55)
NOTE VARCHAR2(4000)
SQL> select doc_id, category, note
from kb_p_plsql
where doc_id=1001
SQL> /
DOC_ID CATEGORY NOTE
---------- ------------ ------------------------------
1001 PLSQL PL/SQL in Oracle Database 11g
is faster and better!

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Oracle 11g Topics