Questions on Oracle Data Pump Export and Import

Reference: http://www.iselfschooling.com/mcd12_ora10g/Oracle10g_Answers.htm

Q: Why do you use Data Pump Export and Import?
A: The expdp and impdp tools support all the original exp and imp functionalities plus many new features. With previous release, you could only move the transportable tablespace across Oracle databases that were running on the same architecture and operating system. With Data Pump, you are able to transport data files from one plateform to another. Only you have to make sure that both source and target databases set their COMPATIBLE initialization parameter to 10.0.0 or greater.

Q: Export the DEPT and EMP records that deptno is 10 or 30 from the ISELF schema.
A:
# expdp
FILE=/u02/oradata/ora10g/EXPDAT02.DMP
FILESIZE=2048M
LOG=/u02/oradata/ora10g/EXPDAT.LOG
TABLES=ISELF.CUSTOMER,ISELF.DEPT,ISELF.EMP
GRANTS=y
INDEXES=y
ROWS=y
CONSTRAINTS=y
CONSISTENT=n
RECORD=n
QUERY='WHERE deptno IN (10, 30)'

Q: Export the iself, outln and system schemas.
A:
# expdp
FILE=/u02/oradata/ora10g/EXPDAT05.DMP
FILESIZE=2048M
LOG=/u02/oradata/ora10g/EXPDAT.LOG
OWNER=ISELF,OUTLN,SYSTEM
GRANTS=y
INDEXES=y
ROWS=y
CONSTRAINTS=y
CONSISTENT=n
RECORD=n

Q: How do you import the DEPT and EMP tables with recalculating statistics and committing after each array insert?
A:
# impdp
FILE=/u02/oradata/ora10g/EXPDAT.DMP
LOG=/u02/oradata/ora10g/IMPORT.LOG
FROMUSER=iself
TABLES=emp,dept
GRANTS=y
INDEXES=y
ROWS=y
CONSTRAINTS=y
IGNORE=y
COMMIT=y
RECALCULATE_STATISTICS=y
DATAFILES=n

Q: Perform a Parallel Full Export on the DIR1, DIR2 directory objects and make sure that each file be 2 GB in size.
A:
$ expdp
FULL=y
PARALLEL=2
DUMPFILE=DIR1:exp1%U.dmp, DIR2:exp2%U.dmp
FILESIZE=2G
The %u implies that multiple files may be generated and start at 01 with a two-digital number.

Q: Export only all functions, tables, procedures (proc1 and proc2 only), and all views that starts with the ‘EMP’ characters from the iself and SCOTT schemas.
A:
$ expdp
SCHEMAS=iself,scott
DIRECTORY=private_exp_space
DUMPFILE=expdat01.dmp
INCLUDE=function
INCLUDE=table
INCLUDE=procedure:”in (‘proc1’,’proc2’)”
INCLUDE=view:”like ‘EMP%’”
Either you should use INCLUDE or EXCLUDE.

Q: Generate a SQL script from an existing export dump file.
A:
$ impdp
DIRECTORY=private_exp_space
DUMPFILE=expdat01.dmp
SQLFILE=MyScript.sql

Q: Move objects from one tablespace to another by using the REMAP_TABLESPACE option.
A:
$ impdp
SCHEMAS=iself
REMAP_TABLESPACE=iself_tablespace:urself_tablespace

Q: How can you read from your exported file directly without importing them into your database?
A:
SQL> CREATE TABLE external_emp
(ename, sal, comm)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY private_exp_space
LOCATION ( ‘expdat01.dmp’)
)
PARALLEL AS
SELECT ename, sal, comm.
FROM emp WHERE deptno IN (10, 30);

Q: What is an endian format?
A: The endian format or Byte ordering is a format that will affect the results when data is written and read. For example, the 2-bytes integer value 1 is written as 0x0001 on a big-endian system and as 0x0100 on a little-endian system. To determine the endian format of a platform do the following query:
SQL> SELECT p.endian_format
FROM v$transportable_platform p, v$database d
WHERE p.platform_name = d.platform_name
/
The v$transportable_platform view contains all supported platforms. In order to convert form one platform to another platform uses the rman utility. The following is an example of how to convert from one platform to another.
$ rman TARGET=/
RMAN> CONVERT DATAFILE ‘/local/oradata/school/*’
FROM PLATFORM = ‘Solari [tm] OE (32-bit)’
DB_FILE_NAME_CONVERT =
‘/local/oradata/school/data’ , ‘/remote/oradata/data’;