Datapump-2
This post is in continuation of my previous post on Oracle Datapump Part I here
Directory:
In Datapump syntax we found Directory, What does this directory means ? Does it talk about the OS level directory ???
well you can say that here directory means object.
we need to create database level directory as well as os level directory
How and Why?? ==>> Datapump is server based and notclient based. Server based means, datapump creates all its dump files on the server not on client machine.
BG processes of the oracle they are responsible for all the I/O operations for the dump files.
While doing export/import earlier (in old export/import) We were able to give dump file location where ever we find space but in 10g we have specific location which is pointed by this Directory Object.
Now you cant let any user be able to specify an absolute file path on the server. so over writing the server file if you have power to write dump file anywhere will be stopped.
Datapump maps to a specific OS directory with this Directory object.
Example
SQL> desc dba_directories;
Name Null? Type
—————————————– ——– —————————-
OWNER NOT NULL VARCHAR2(30)
DIRECTORY_NAME NOT NULL VARCHAR2(30)
DIRECTORY_PATH VARCHAR2(4000)
SQL> select * from dba_directories where DIRECTORY_NAME like ‘%DATA_PUMP%’;
OWNER DIRECTORY_NAME DIRECTORY_PAT
—————————— ————————————————————————
SYS DATA_PUMP_DIR /u01/oracle/product/TEST/10.2.0/rdbms/log/
===>> Want to create the directory right now and then do datapump export
SQL> create directory test_dir1 as ‘/u01/oracle/product/TEST/10.2.0/export’;
Directory created.
Then where the security is if all the users who has db privilege can create the directory????===> To create the directory we need to have DBA role privilege OR “CREATE ANY DIRECTORY” privilege.
Now if you want to grant user priya privileges on the newly created directory test_dir1,
SQL> GRANT READ,WRITE ON DIRECTORY test_dir1 to priya;
Grant succeeded.
To do the datapump export you should have ” Write” privilege on all the files (means write privilege on Directory) and for import; need to have Read access to that export dump files.
Is only read privilege for import is sufficient ??? Nope as you need to write log filetoo so you shuld have write privilege as well on the Directory for import 😉
Once the Directory is created, all the DataPump export and import jobs can use the DIRECTORY parameter to specify the name of the directory object
(DIRECTORY=test_dir1), thus DIRECTORY is pointing to the OS level directories and files.
$expdp priya/passwd dumpfile=test_dir_export.dmp
IF you have already created the default directory with the name DATA_PUMP_DIR, then no need to specify DIRECTORY parameter in export/import commands.
Oracle will automatically look for the directory which is specified by the value DATA_PUMP_DIR.
SQL> CREATE DIRECTORY TEST_DIR2 AS ‘/U01/oracle/product/10.2.0/export’;
Directory created.
$export DATA_PUMP_DIR=TEST_DIR2
$expdp priya/passwd tables=emp dumpfile=emp.dmp.
$expdp priya/passwd tables=emp logfile=test_dir2:priya.log
test_dir2is directory and DataPump file name is priya.log , (:) is separation for both the value (Data Pump file contains dump file,logfile or sqlfile).
Some practicle examples:
1) Import of tables from scott’s account to priya’s account :
If you do the same using the old import job:
$ imp username/password FILE=scott.dmp FROMUSER=scott TOUSER=priya TABLES=(*)
Data Pump Import:
$ impdp username/password DIRECTORY=test_dir1 DUMPFILE=scott.dmp
TABLES=scott.emp REMAP_SCHEMA=scott:priya
Here fromuser/touser is replaced with remap_schema, so EMP table of scott ownership will be imported to the priya schema.
2) Export of an entire database to a dump file with all GRANTS, INDEXES,
and data
$ exp username/password FULL=y FILE=dba.dmp GRANTS=y INDEXES=y ROWS=y $ expdp username/password FULL=y INCLUDE=GRANT INCLUDE= INDEX
DIRECTORY=test_dir1 DUMPFILE=dba.dmp CONTENT=ALL
The INCLUDE parameter allows you to specify which object , you want to keep in the export job. The EXCLUDE parameter allows you to specify which object , you want to keep out of the export job.
You cannot mix the two parameters in one job. Both parameters work with Data Pump Import as well.
REMAP
• REMAP_TABLESPACE – This allows you to easily import a table into a different
tablespace from which it was originally exported. The databases have to be 10.1
or later.
Example:> impdp username/password REMAP_TABLESPACE=tbs_1:tbs_6
DIRECTORY=dpumpdir1 DUMPFILE=employees.dmp
• REMAP_DATAFILES– This is a very useful feature when you move databases between platforms that have different file naming conventions. This parameter changes the source datafile name to the target datafile name in all SQL statements where the source datafile is referenced. Because the REMAP_DATAFILE value uses quotation marks, it’s best to specify the parameter within a parameter file.