{"id":516,"date":"2008-06-18T15:04:49","date_gmt":"2008-06-18T19:04:49","guid":{"rendered":"http:\/\/onlineappsdba.com\/index.php\/2008\/06\/18\/datapump-2\/"},"modified":"2008-06-18T15:04:49","modified_gmt":"2008-06-18T19:04:49","slug":"datapump-2","status":"publish","type":"post","link":"https:\/\/onlineappsdba.com\/index.php\/2008\/06\/18\/datapump-2\/","title":{"rendered":"Datapump-2"},"content":{"rendered":"

\u00a0This post is in continuation of my previous post on\u00a0 Oracle Datapump Part I here<\/strong>
\n<\/a>\u00a0<\/p>\n

Directory:
\n<\/u><\/strong><\/p>\n

In Datapump syntax we found Directory, What does this directory means <\/strong>?\u00a0Does it talk about the OS level directory <\/strong>???<\/p>\n

well you can say that here directory means object.
\nwe need to create database level directory as well as os level directory<\/p>\n

How and Why??\u00a0 <\/strong>==>>\u00a0 Datapump is server based and\u00a0not<\/strong>client based. Server based means, datapump creates all its dump files on the server not on client machine.\u00a0<\/p>\n

BG<\/strong>\u00a0processes of the oracle they\u00a0 are responsible for all the I\/O operations for the dump files.<\/p>\n

\u00a0While doing export\/import<\/strong> 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<\/strong>.<\/p>\n

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.
\nDatapump maps to a specific\u00a0OS directory with this Directory object.<\/p>\n

<\/u><\/strong><\/p>\n

Example <\/u><\/strong><\/p>\n

SQL> desc dba_directories;<\/strong>
\n\u00a0Name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Null?\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Type
\n\u00a0—————————————– ——– —————————-
\n\u00a0OWNER\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL VARCHAR2(30)
\n\u00a0DIRECTORY_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL VARCHAR2(30)
\n\u00a0DIRECTORY_PATH\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 VARCHAR2(4000)<\/p>\n

SQL> select * from dba_directories where DIRECTORY_NAME like ‘%DATA_PUMP%’;<\/strong><\/p>\n

OWNER\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DIRECTORY_NAME\u00a0\u00a0\u00a0DIRECTORY_PAT
\n—————————— ————————————————————————
\nSYS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DATA_PUMP_DIR\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/u01\/oracle\/product\/TEST\/10.2.0\/rdbms\/log\/<\/p>\n

===>> Want to create the directory\u00a0right now and then do datapump export<\/strong><\/p>\n

SQL> create directory test_dir1 as ‘\/u01\/oracle\/product\/TEST\/10.2.0\/export’;<\/p>\n

Directory created.<\/p>\n

Then where the security is if all the users who has db privilege can create the directory<\/strong>????===> To create the directory we need to have DBA role privilege OR “CREATE ANY DIRECTORY<\/strong>” privilege.<\/p>\n

Now if you want to grant user priya privileges on the newly created directory test_dir1,<\/p>\n

SQL> GRANT READ,WRITE ON DIRECTORY test_dir1 to priya<\/strong>;
\nGrant succeeded.<\/p>\n

To do the datapump export you should have ” Write<\/strong>” privilege on all the files (means write privilege on Directory) and for import<\/strong>; need to have Read<\/strong> access to that export dump files.<\/p>\n

Is\u00a0only read privilege for import is sufficient<\/strong> ??? Nope as you need to write log file<\/strong>too so you shuld have write privilege<\/strong> as well on the Directory for import \ud83d\ude09<\/p>\n

Once the Directory is created, all the DataPump export and import jobs can use the DIRECTORY<\/strong> parameter to specify the name of the directory object
\n(DIRECTORY=test_dir1), thus DIRECTORY is pointing to the\u00a0OS level directories and files<\/strong>.<\/p>\n

$expdp priya\/passwd dumpfile=test_dir_export.dmp<\/strong><\/p>\n

IF you have already created the default directory with the name DATA_PUMP_DIR, then no need to specify<\/strong> DIRECTORY parameter in export\/import commands.<\/p>\n

Oracle will automatically look for the directory which is specified by the value DATA_PUMP_DIR.<\/p>\n

SQL> CREATE DIRECTORY TEST_DIR2 AS ‘\/U01\/oracle\/product\/10.2.0\/export’<\/strong>;
\nDirectory created.<\/p>\n

$export DATA_PUMP_DIR=TEST_DIR2
\n$expdp priya\/passwd tables=emp dumpfile=emp.dmp.
\n$expdp priya\/passwd tables=emp logfile=test_dir2:<\/strong>priya.log<\/p>\n

test_dir2<\/strong>is directory and\u00a0DataPump file\u00a0name is priya.log , (:<\/strong>) is separation for both the value (Data Pump file contains dump file,logfile or sqlfile).<\/p>\n

\u00a0Some practicle examples<\/u><\/strong>:<\/p>\n

1)\u00a0 Import of tables from scott\u2019s account to\u00a0priya’s \u00a0account :<\/strong><\/p>\n

If you do the same using the old import<\/strong> job:<\/p>\n

$\u00a0imp username\/password FILE=scott.dmp FROMUSER=scott TOUSER=priya TABLES=(*)<\/p>\n

Data Pump Import<\/strong>:\u00a0
\n$ impdp username\/password DIRECTORY=test_dir1 DUMPFILE=scott.dmp
\nTABLES=scott.emp REMAP_SCHEMA=scott:priya<\/p>\n

Here fromuser\/touser is replaced with <\/strong>remap_schema, so EMP table of scott ownership will be imported to the priya schema.<\/p>\n

2) Export of an entire database to a dump file with all GRANTS, INDEXES,
\nand data <\/strong><\/p>\n

$\u00a0exp username\/password FULL=y FILE=dba.dmp GRANTS=y INDEXES=y ROWS=y $\u00a0expdp username\/password FULL=y INCLUDE=GRANT INCLUDE= INDEX
\nDIRECTORY=test_dir1 DUMPFILE=dba.dmp CONTENT=ALL<\/p>\n

The INCLUDE parameter<\/strong> allows you to specify which object\u00a0, you want to keep in the export job. The EXCLUDE parameter<\/strong> allows you to specify which object\u00a0, you want to keep out of the export job.
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 You cannot mix the two parameters<\/strong> in one job. Both parameters work with Data Pump Import as well.<\/p>\n

REMAP\u00a0
\n<\/u><\/strong>\u2022 REMAP_TABLESPACE<\/strong> \u2013 This allows you to easily import a table into a different
\ntablespace from which it was originally exported. The databases have to be 10.1
\nor later.
\nExample:> impdp username\/password REMAP_TABLESPACE=tbs_1:tbs_6
\nDIRECTORY=dpumpdir1 DUMPFILE=employees.dmp<\/strong><\/p>\n

\u2022 REMAP_DATAFILES<\/strong>\u2013 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\u2019s best to specify the parameter within a parameter file.<\/p>\n","protected":false},"excerpt":{"rendered":"

\u00a0This post is in continuation of my previous post on\u00a0 Oracle Datapump Part I here \u00a0 Directory: In Datapump syntax […]<\/p>\n","protected":false},"author":115,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[424],"tags":[],"class_list":["post-516","post","type-post","status-publish","format-standard","hentry","category-database"],"yoast_head":"\nDatapump-2 -<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/onlineappsdba.com\/index.php\/2008\/06\/18\/datapump-2\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Datapump-2 -\" \/>\n<meta property=\"og:description\" content=\"\u00a0This post is in continuation of my previous post on\u00a0 Oracle Datapump Part I here \u00a0 Directory: In Datapump syntax […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/onlineappsdba.com\/index.php\/2008\/06\/18\/datapump-2\/\" \/>\n<meta property=\"article:published_time\" content=\"2008-06-18T19:04:49+00:00\" \/>\n<meta name=\"author\" content=\"Masroof Ahmad\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Masroof Ahmad\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/onlineappsdba.com\/index.php\/2008\/06\/18\/datapump-2\/\",\"url\":\"https:\/\/onlineappsdba.com\/index.php\/2008\/06\/18\/datapump-2\/\",\"name\":\"Datapump-2 -\",\"isPartOf\":{\"@id\":\"https:\/\/onlineappsdba.com\/#website\"},\"datePublished\":\"2008-06-18T19:04:49+00:00\",\"author\":{\"@id\":\"https:\/\/onlineappsdba.com\/#\/schema\/person\/909a876ed58d400faf82caf81d61bfdb\"},\"breadcrumb\":{\"@id\":\"https:\/\/onlineappsdba.com\/index.php\/2008\/06\/18\/datapump-2\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/onlineappsdba.com\/index.php\/2008\/06\/18\/datapump-2\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/onlineappsdba.com\/index.php\/2008\/06\/18\/datapump-2\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/onlineappsdba.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Datapump-2\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/onlineappsdba.com\/#website\",\"url\":\"https:\/\/onlineappsdba.com\/\",\"name\":\"\",\"description\":\"Oracle Implementation & Training Experts\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/onlineappsdba.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/onlineappsdba.com\/#\/schema\/person\/909a876ed58d400faf82caf81d61bfdb\",\"name\":\"Masroof Ahmad\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/onlineappsdba.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/10f9db7bdbbd7f9ccfbe9b2d208e5978fc28315e9c704383e639a926ea0fce5f?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/10f9db7bdbbd7f9ccfbe9b2d208e5978fc28315e9c704383e639a926ea0fce5f?s=96&d=mm&r=g\",\"caption\":\"Masroof Ahmad\"},\"url\":\"https:\/\/onlineappsdba.com\/index.php\/author\/masroof\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Datapump-2 -","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/onlineappsdba.com\/index.php\/2008\/06\/18\/datapump-2\/","og_locale":"en_US","og_type":"article","og_title":"Datapump-2 -","og_description":"\u00a0This post is in continuation of my previous post on\u00a0 Oracle Datapump Part I here \u00a0 Directory: In Datapump syntax […]","og_url":"https:\/\/onlineappsdba.com\/index.php\/2008\/06\/18\/datapump-2\/","article_published_time":"2008-06-18T19:04:49+00:00","author":"Masroof Ahmad","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Masroof Ahmad","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/onlineappsdba.com\/index.php\/2008\/06\/18\/datapump-2\/","url":"https:\/\/onlineappsdba.com\/index.php\/2008\/06\/18\/datapump-2\/","name":"Datapump-2 -","isPartOf":{"@id":"https:\/\/onlineappsdba.com\/#website"},"datePublished":"2008-06-18T19:04:49+00:00","author":{"@id":"https:\/\/onlineappsdba.com\/#\/schema\/person\/909a876ed58d400faf82caf81d61bfdb"},"breadcrumb":{"@id":"https:\/\/onlineappsdba.com\/index.php\/2008\/06\/18\/datapump-2\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/onlineappsdba.com\/index.php\/2008\/06\/18\/datapump-2\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/onlineappsdba.com\/index.php\/2008\/06\/18\/datapump-2\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/onlineappsdba.com\/"},{"@type":"ListItem","position":2,"name":"Datapump-2"}]},{"@type":"WebSite","@id":"https:\/\/onlineappsdba.com\/#website","url":"https:\/\/onlineappsdba.com\/","name":"","description":"Oracle Implementation & Training Experts","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/onlineappsdba.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/onlineappsdba.com\/#\/schema\/person\/909a876ed58d400faf82caf81d61bfdb","name":"Masroof Ahmad","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/onlineappsdba.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/10f9db7bdbbd7f9ccfbe9b2d208e5978fc28315e9c704383e639a926ea0fce5f?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/10f9db7bdbbd7f9ccfbe9b2d208e5978fc28315e9c704383e639a926ea0fce5f?s=96&d=mm&r=g","caption":"Masroof Ahmad"},"url":"https:\/\/onlineappsdba.com\/index.php\/author\/masroof\/"}]}},"_links":{"self":[{"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/posts\/516","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/users\/115"}],"replies":[{"embeddable":true,"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/comments?post=516"}],"version-history":[{"count":0,"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/posts\/516\/revisions"}],"wp:attachment":[{"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/media?parent=516"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/categories?post=516"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/tags?post=516"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}