{"id":233,"date":"2011-12-12T00:26:00","date_gmt":"2011-12-11T21:26:00","guid":{"rendered":"http:\/\/fatihacar.com\/blog\/?p=233"},"modified":"2011-12-12T00:26:00","modified_gmt":"2011-12-11T21:26:00","slug":"export-and-import-data-in-oracle","status":"publish","type":"post","link":"http:\/\/www.fatihacar.com\/blog\/export-and-import-data-in-oracle\/","title":{"rendered":"Export and Import Data in Oracle"},"content":{"rendered":"<p>Oracle&#8217;s export (exp) and import (imp) utilities are used to perform logical database backup and recovery. When exporting, database objects are dumped to a binary file which can then be imported into another Oracle database. These utilities can be used to move data between different machines, databases or schema. However, as they use a proprietary binary file format, they can only be used between Oracle databases. One cannot export data and expect to import it into a non-Oracle database.<\/p>\n<p><strong>The export\/import utilities are commonly used to perform the following tasks:<\/strong><\/p>\n<ul>\n<li>Backup and recovery (small databases only, say < +50GB, if bigger, use RMAN instead)<\/li>\n<li>Move data between Oracle databases on different platforms (for example from Solaris to Windows)<\/li>\n<li>Reorganization of data\/ eliminate database fragmentation (export, drop and re-import tables)<\/li>\n<li>Upgrade databases from extremely old versions of Oracle (when in-place upgrades are not supported by the Database Upgrade Assistant any more)<\/li>\n<li>Detect database corruption. Ensure that all the data can be read<\/li>\n<li>Transporting tablespaces between databases<\/li>\n<\/ul>\n<p><strong>Export Examples<\/strong><\/p>\n<blockquote><p>\n$> exp scott\/tiger file=emp.dmp log=emp.log tables=emp rows=yes indexes=no<br \/>\n$> exp scott\/tiger file=emp.dmp tables=(emp,dept)<br \/>\n$> exp scott\/tiger tables=emp query=&#8221;where deptno=10&#8243;<br \/>\n$> exp scott\/tiger file=abc.dmp tables=abc query=\\&#8221;where sex=\\&#8217;f\\&#8217;\\&#8221; rows=yes\n<\/p><\/blockquote>\n<p><strong>Import Examples<\/strong><\/p>\n<blockquote><p>\n$> imp scott\/tiger file=emp.dmp full=yes<br \/>\n$> imp scott\/tiger file=emp.dmp fromuser=scott touser=scott tables=dept\n<\/p><\/blockquote>\n<p><strong>Note :<\/strong> It is generally advised not to use exports as the only means of backing-up a database. Physical backup methods (for example, when you use RMAN) are normally much quicker and supports point in time based recovery (apply archivelogs after recovering a database). Also, exp\/imp is not practical for large database environments.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Oracle&#8217;s export (exp) and import (imp) utilities are used to perform logical database backup and recovery. When exporting, database objects are dumped to a binary file which can then be imported into another Oracle database. These utilities can be used to move data between different machines, databases or schema. However, as they use a proprietary&#8230;<\/p>\n","protected":false},"author":37,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[9,20],"tags":[23,14,90],"class_list":["post-233","post","type-post","status-publish","format-standard","hentry","category-administration","category-oracle-backup-and-recovery","tag-backup-and-recovery-2","tag-database-administration","tag-oracle"],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p39NFI-3L","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts\/233","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/users\/37"}],"replies":[{"embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/comments?post=233"}],"version-history":[{"count":1,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts\/233\/revisions"}],"predecessor-version":[{"id":234,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts\/233\/revisions\/234"}],"wp:attachment":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/media?parent=233"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/categories?post=233"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/tags?post=233"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}