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:
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
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
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
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
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 .
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:
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
Oracle 11g Related Interview Questions
|Oracle 10g Interview Questions||Oracle 9i Interview Questions|
|Oracle 8i Interview Questions||Informatica Interview Questions|
|PL/SQL Interview Questions||Oracle 11g Interview Questions|
|SQL Interview Questions||Oracle apps Interview Questions|
|Sybase Interview Questions||Oracle Apps ERP Interview Questions|
|Oracle 7.3 Interview Questions||Oracle Access Manager Interview Questions|
|Oracle Application Framework Interview Questions||Oracle Apps DBA Interview Questions|
Oracle 11g Related Practice Tests
|Oracle 10g Practice Tests||Oracle 9i Practice Tests|
|Oracle 8i Practice Tests||Informatica Practice Tests|
|PL/SQL Practice Tests||Oracle 11g Practice Tests|
|SQL Practice Tests||Oracle apps Practice Tests|
|Sybase Practice Tests||Oracle Apps ERP Practice Tests|
|Oracle 7.3 Practice Tests|
Oracle 11g Tutorial
Installing, Upgrading, And Managing Change
Database Diagnosability And Failure Repair
Backup And Recovery
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.