Oracle exp imp按用户导出导入实例
<span class="Apple-style-span" style="font-family: song, Verdana; font-size: 12px; line-height: normal; background-color: rgb(243, 243, 243); ">按用户导出再导入实例，任务要求：<blockquote dir="ltr" style="margin-right: 0px; "><p style="font: normal normal normal 12px/normal song, Verdana; ">1.从一台windows服务器 A 上导出 Test1 这个用户的所有对象，然后导入到linux服务器 B 上的 Test2用户。（已知Test1密码为Test1passwd 或者用system用户导出也行）</p><p style="font: normal normal normal 12px/normal song, Verdana; ">2.B机器上Test2用户不存在，或Test2用户已经存在 两种情况（用户存在相对比较复杂）</p><p style="font: normal normal normal 12px/normal song, Verdana; ">---------如果Test2用户已经存在（数据没用，可以删除），级联删除用户及所有对象（有可能遇到有人正在连接，删除不掉的情况 方法参照下文），重新创建账号并赋权。</p><p style="font: normal normal normal 12px/normal song, Verdana; ">3.赋予适当的权限</p></blockquote><p style="font: normal normal normal 12px/normal song, Verdana; "> </p>操作步骤：<p style="font: normal normal normal 12px/normal song, Verdana; "><strong>1.从 A 上导出数据文件到指定目录（目录名称自己定义，只要自己能找到就行，和用户名没有关系）</strong></p><blockquote dir="ltr" style="margin-right: 0px; "><p style="font: normal normal normal 12px/normal song, Verdana; ">sqlplus /nolog</p><p style="font: normal normal normal 12px/normal song, Verdana; ">conn / as sysdba</p><p style="font: normal normal normal 12px/normal song, Verdana; ">exp Test1/Test1passwd owner=Test1 file=D:\files\Test1.dmp</p></blockquote><p style="font: normal normal normal 12px/normal song, Verdana; "><strong>2.在A机器上查看用户默认表空间，以便导入时创建一样的表空间</strong> </p><blockquote dir="ltr" style="margin-right: 0px; "><div style="border-top-width: 0.5pt; border-right-width: 0.5pt; border-bottom-width: 0.5pt; border-left-width: 0.5pt; border-top-style: solid; border-right-style: solid; border-bottom-style: solid; border-left-style: solid; border-top-color: windowtext; border-right-color: windowtext; border-bottom-color: windowtext; border-left-color: windowtext; padding-top: 4px; padding-right: 5.4pt; padding-bottom: 4px; padding-left: 5.4pt; background-image: none; background-attachment: scroll; background-origin: initial; background-clip: initial; background-color: rgb(230, 230, 230); width: 629px; background-position: 0% 0%; background-repeat: repeat repeat; "><div><img alt="" src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif" align="top"><span style="color: rgb(0, 0, 0); ">SQL</span><span style="color: rgb(0, 0, 0); ">></span><span style="color: rgb(0, 0, 0); "> select username,default_tablespace from dba_users </span><span style="color: rgb(0, 0, 255); ">where</span><span style="color: rgb(0, 0, 0); "> username </span><span style="color: rgb(0, 0, 0); ">=</span><span style="color: rgb(0, 0, 0); ">'</span><span style="color: rgb(0, 0, 0); ">TEST1</span><span style="color: rgb(0, 0, 0); ">'</span><span style="color: rgb(0, 0, 0); ">;<br style="font: normal normal normal 12px/normal song, Verdana; "><img alt="" src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif" align="top"><br style="font: normal normal normal 12px/normal song, Verdana; "><img alt="" src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif" align="top">USERNAME DEFAULT_TABLESPACE<br style="font: normal normal normal 12px/normal song, Verdana; "><img alt="" src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif" align="top"></span><span style="color: rgb(0, 0, 0); ">------------------------------</span><span style="color: rgb(0, 0, 0); "> </span><span style="color: rgb(0, 0, 0); ">------------------------------</span><span style="color: rgb(0, 0, 0); "><br style="font: normal normal normal 12px/normal song, Verdana; "><img alt="" src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif" align="top">TEST1 CMIS</span></div></div></blockquote><p dir="ltr" style="font: normal normal normal 12px/normal song, Verdana; "><strong>3.查看用户使用的表空间</strong> </p><blockquote dir="ltr" style="margin-right: 0px; "><div style="border-top-width: 0.5pt; border-right-width: 0.5pt; border-bottom-width: 0.5pt; border-left-width: 0.5pt; border-top-style: solid; border-right-style: solid; border-bottom-style: solid; border-left-style: solid; border-top-color: windowtext; border-right-color: windowtext; border-bottom-color: windowtext; border-left-color: windowtext; padding-top: 4px; padding-right: 5.4pt; padding-bottom: 4px; padding-left: 5.4pt; background-image: none; background-attachment: scroll; background-origin: initial; background-clip: initial; background-color: rgb(230, 230, 230); width: 629px; background-position: 0% 0%; background-repeat: repeat repeat; "><div><img alt="" src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif" align="top"><span style="color: rgb(0, 0, 0); ">SQL</span><span style="color: rgb(0, 0, 0); ">></span><span style="color: rgb(0, 0, 0); "> select DISTINCT owner ,tablespace_name from dba_extents </span><span style="color: rgb(0, 0, 255); ">where</span><span style="color: rgb(0, 0, 0); "> owner like </span><span style="color: rgb(0, 0, 0); ">'</span><span style="color: rgb(0, 0, 0); ">TEST1</span><span style="color: rgb(0, 0, 0); ">'</span><span style="color: rgb(0, 0, 0); ">;<br style="font: normal normal normal 12px/normal song, Verdana; "><img alt="" src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif" align="top"><br style="font: normal normal normal 12px/normal song, Verdana; "><img alt="" src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif" align="top">OWNER TABLESPACE_NAME<br style="font: normal normal normal 12px/normal song, Verdana; "><img alt="" src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif" align="top"></span><span style="color: rgb(0, 0, 0); ">------------------------------</span><span style="color: rgb(0, 0, 0); "> </span><span style="color: rgb(0, 0, 0); ">------------------------------</span><span style="color: rgb(0, 0, 0); "><br style="font: normal normal normal 12px/normal song, Verdana; "><img alt="" src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif" align="top">TEST1 XSL<br style="font: normal normal normal 12px/normal song, Verdana; "><img alt="" src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif" align="top">TEST1 CMIS</span></div></div></blockquote><p dir="ltr" style="font: normal normal normal 12px/normal song, Verdana; "><strong>4.查看表空间对应的数据文件，以便在B上创建大小合适的数据文件。</strong> </p><blockquote dir="ltr" style="margin-right: 0px; "><div style="border-top-width: 0.5pt; border-right-width: 0.5pt; border-bottom-width: 0.5pt; border-left-width: 0.5pt; border-top-style: solid; border-right-style: solid; border-bottom-style: solid; border-left-style: solid; border-top-color: windowtext; border-right-color: windowtext; border-bottom-color: windowtext; border-left-color: windowtext; padding-top: 4px; padding-right: 5.4pt; padding-bottom: 4px; padding-left: 5.4pt; background-image: none; background-attachment: scroll; background-origin: initial; background-clip: initial; background-color: rgb(230, 230, 230); width: 629px; background-position: 0% 0%; background-repeat: repeat repeat; "><div><img alt="" src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif" align="top"><span style="color: rgb(0, 0, 0); ">SQL</span><span style="color: rgb(0, 0, 0); ">></span><span style="color: rgb(0, 0, 0); "> select file_name,tablespace_name from dba_data_files </span><span style="color: rgb(0, 0, 255); ">where</span><span style="color: rgb(0, 0, 0); "> tablespace_name </span><span style="color: rgb(0, 0, 255); ">in</span><span style="color: rgb(0, 0, 0); "> (</span><span style="color: rgb(0, 0, 0); ">'</span><span style="color: rgb(0, 0, 0); ">CMIS</span><span style="color: rgb(0, 0, 0); ">'</span><span style="color: rgb(0, 0, 0); ">,</span><span style="color: rgb(0, 0, 0); ">'</span><span style="color: rgb(0, 0, 0); ">XSL</span><span style="color: rgb(0, 0, 0); ">'</span><span style="color: rgb(0, 0, 0); ">);<br style="font: normal normal normal 12px/normal song, Verdana; "><img alt="" src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif" align="top"><br style="font: normal normal normal 12px/normal song, Verdana; "><img alt="" src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif" align="top">FILE_NAME BYTES TABLESPACE<br style="font: normal normal normal 12px/normal song, Verdana; "><img alt="" src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif" align="top"></span><span style="color: rgb(0, 0, 0); ">------------------------------------------------------------</span><span style="color: rgb(0, 0, 0); "> </span><span style="color: rgb(0, 0, 0); ">----------</span><span style="color: rgb(0, 0, 0); "> </span><span style="color: rgb(0, 0, 0); ">----------</span><span style="color: rgb(0, 0, 0); "><br style="font: normal normal normal 12px/normal song, Verdana; "><img alt="" src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif" align="top">D:ORACLEPRODUCT10.</span><span style="color: rgb(0, 0, 0); ">2</span><span style="color: rgb(0, 0, 0); ">.0ORADATACMISDBCMIS </span><span style="color: rgb(0, 0, 0); ">8728346624</span><span style="color: rgb(0, 0, 0); "> CMIS<br style="font: normal normal normal 12px/normal song, Verdana; "><img alt="" src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif" align="top">D:ORACLEPRODUCT10.</span><span style="color: rgb(0, 0, 0); ">2</span><span style="color: rgb(0, 0, 0); ">.0ORADATACMISDBCMIS01.ORA </span><span style="color: rgb(0, 0, 0); ">8204058624</span><span style="color: rgb(0, 0, 0); "> CMIS<br style="font: normal normal normal 12px/normal song, Verdana; "><img alt="" src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif" align="top">D:ORACLEPRODUCT10.</span><span style="color: rgb(0, 0, 0); ">2</span><span style="color: rgb(0, 0, 0); ">.0ORADATACMISDBCMIS02.ORA </span><span style="color: rgb(0, 0, 0); ">4194304000</span><span style="color: rgb(0, 0, 0); "> CMIS<br style="font: normal normal normal 12px/normal song, Verdana; "><img alt="" src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif" align="top">D:ORACLEPRODUCT10.</span><span style="color: rgb(0, 0, 0); ">2</span><span style="color: rgb(0, 0, 0); ">.0ORADATACMISDBCMIS03.ORA </span><span style="color: rgb(0, 0, 0); ">4194304000</span><span style="color: rgb(0, 0, 0); "> CMIS<br style="font: normal normal normal 12px/normal song, Verdana; "><img alt="" src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif" align="top">D:ORACLEPRODUCT10.</span><span style="color: rgb(0, 0, 0); ">2</span><span style="color: rgb(0, 0, 0); ">.0ORADATACMISDBCMIS04.ORA </span><span style="color: rgb(0, 0, 0); ">4194304000</span><span style="color: rgb(0, 0, 0); "> CMIS<br style="font: normal normal normal 12px/normal song, Verdana; "><img alt="" src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif" align="top">D:ORACLEPRODUCT10.</span><span style="color: rgb(0, 0, 0); ">2</span><span style="color: rgb(0, 0, 0); ">.0ORADATACMISDBCMIS05.ORA </span><span style="color: rgb(0, 0, 0); ">4194304000</span><span style="color: rgb(0, 0, 0); "> CMIS<br style="font: normal normal normal 12px/normal song, Verdana; "><img alt="" src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif" align="top">D:ORACLEPRODUCT10.</span><span style="color: rgb(0, 0, 0); ">2</span><span style="color: rgb(0, 0, 0); ">.0ORADATACMISDBCMIS06.ORA </span><span style="color: rgb(0, 0, 0); ">4194304000</span><span style="color: rgb(0, 0, 0); "> CMIS<br style="font: normal normal normal 12px/normal song, Verdana; "><img alt="" src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif" align="top">D:ORACLEPRODUCT10.</span><span style="color: rgb(0, 0, 0); ">2</span><span style="color: rgb(0, 0, 0); ">.0ORADATACMISDBXSL.ORA </span><span style="color: rgb(0, 0, 0); ">4194304000</span><span style="color: rgb(0, 0, 0); "> XSL<br style="font: normal normal normal 12px/normal song, Verdana; "><img alt="" src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif" align="top">D:ORACLEPRODUCT10.</span><span style="color: rgb(0, 0, 0); ">2</span><span style="color: rgb(0, 0, 0); ">.0ORADATACMISDBXSL01.ORA </span><span style="color: rgb(0, 0, 0); ">4194304000</span><span style="color: rgb(0, 0, 0); "> XSL<br style="font: normal normal normal 12px/normal song, Verdana; "><img alt="" src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif" align="top">D:ORACLEPRODUCT10.</span><span style="color: rgb(0, 0, 0); ">2</span><span style="color: rgb(0, 0, 0); ">.0ORADATACMISDBXSL02.ORA </span><span style="color: rgb(0, 0, 0); ">4194304000</span><span style="color: rgb(0, 0, 0); "> XSL</span></div></div></blockquote><p dir="ltr" style="font: normal normal normal 12px/normal song, Verdana; "><strong>5.检查B机器的表空间，看是否存在CMIS，XSL</strong></p><blockquote dir="ltr" style="margin-right: 0px; "><p style="font: normal normal normal 12px/normal song, Verdana; ">select name from v$tablespace where name in ('XSL','CMIS');<br style="font: normal normal normal 12px/normal song, Verdana; ">查找不到，说明没有这个两个表空间，需要创建。</p></blockquote><p style="font: normal normal normal 12px/normal song, Verdana; "><strong>6.要导入数据的server没有xsl，cmis表空间。创建</strong></p><blockquote dir="ltr" style="margin-right: 0px; "><p style="font: normal normal normal 12px/normal song, Verdana; ">create tablespace xsl logging datafile '/opt/oracle/product/10.2.0/oradata/xsl.dbf' size 15000M extent management local;<br style="font: normal normal normal 12px/normal song, Verdana; ">create tablespace cmis logging datafile '/opt/oracle/product/10.2.0/oradata/cmis.dbf' size 37000M extent management local;</p></blockquote><p style="font: normal normal normal 12px/normal song, Verdana; "><strong>7.在服务器B上查找用户是否已经存在</strong></p><blockquote dir="ltr" style="margin-right: 0px; "><p style="font: normal normal normal 12px/normal song, Verdana; ">SQL> select username from dba_users where username='TEST2';<br style="font: normal normal normal 12px/normal song, Verdana; ">接下来分为两种情况，如果不存在那么按照 [一] 方法，如果存在按照 [二]</p></blockquote><p style="font: normal normal normal 12px/normal song, Verdana; ">-----【一】创建用户</p><blockquote dir="ltr" style="margin-right: 0px; "><p style="font: normal normal normal 12px/normal song, Verdana; ">create user Test2 identified by Test2passwd default tablespace cmis temporary tablespace temp profile default;</p></blockquote><p style="font: normal normal normal 12px/normal song, Verdana; ">-----【二】如果用户存在</p><blockquote dir="ltr" style="margin-right: 0px; "><p style="font: normal normal normal 12px/normal song, Verdana; ">drop user Test2 cascade; （删除用户及其拥有的所有对象）</p><p style="font: normal normal normal 12px/normal song, Verdana; ">#此时如果这个用户在连接，drop会出错，必须先杀掉用户的session，然后再drop user</p><p style="font: normal normal normal 12px/normal song, Verdana; ">SELECT 'alter system kill session '''||SID||','||SERIAL#||''' immediate;' FROM V$SESSION WHERE USERNAME='TEST2';</p><p style="font: normal normal normal 12px/normal song, Verdana; ">#（如果用户正在连接，构建命令并杀掉）</p><p style="font: normal normal normal 12px/normal song, Verdana; ">#（上面的语句是构建出杀掉Test2用户session的语句）比如：</p><div style="border-top-width: 0.5pt; border-right-width: 0.5pt; border-bottom-width: 0.5pt; border-left-width: 0.5pt; border-top-style: solid; border-right-style: solid; border-bottom-style: solid; border-left-style: solid; border-top-color: windowtext; border-right-color: windowtext; border-bottom-color: windowtext; border-left-color: windowtext; padding-top: 4px; padding-right: 5.4pt; padding-bottom: 4px; padding-left: 5.4pt; background-image: none; background-attachment: scroll; background-origin: initial; background-clip: initial; background-color: rgb(230, 230, 230); width: 629px; background-position: 0% 0%; background-repeat: repeat repeat; "><div><img alt="" src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif" align="top"><span style="color: rgb(0, 0, 0); ">'</span><span style="color: rgb(0, 0, 0); ">ALTERSYSTEMKILLSESSION</span><span style="color: rgb(0, 0, 0); ">'''</span><span style="color: rgb(0, 0, 0); ">||</span><span style="color: rgb(0, 0, 0); ">SID</span><span style="color: rgb(0, 0, 0); ">||</span><span style="color: rgb(0, 0, 0); ">'</span><span style="color: rgb(0, 0, 0); ">,</span><span style="color: rgb(0, 0, 0); ">'</span><span style="color: rgb(0, 0, 0); ">||</span><span style="color: rgb(0, 0, 0); ">SERIAL#</span><span style="color: rgb(0, 0, 0); ">||</span><span style="color: rgb(0, 0, 0); ">'''</span><span style="color: rgb(0, 0, 0); ">IMMEDIATE;</span><span style="color: rgb(0, 0, 0); ">'</span><span style="color: rgb(0, 0, 0); "><br style="font: normal normal normal 12px/normal song, Verdana; "><img alt="" src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif" align="top"></span><span style="color: rgb(0, 0, 0); ">--------------------------------------------------------------------------------</span><span style="color: rgb(0, 0, 0); "><br style="font: normal normal normal 12px/normal song, Verdana; "><img alt="" src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif" align="top">alter system kill session </span><span style="color: rgb(0, 0, 0); ">'</span><span style="color: rgb(0, 0, 0); ">129,3570</span><span style="color: rgb(0, 0, 0); ">'</span><span style="color: rgb(0, 0, 0); "> immediate;<br style="font: normal normal normal 12px/normal song, Verdana; "><img alt="" src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif" align="top">alter system kill session </span><span style="color: rgb(0, 0, 0); ">'</span><span style="color: rgb(0, 0, 0); ">131,2</span><span style="color: rgb(0, 0, 0); ">'</span><span style="color: rgb(0, 0, 0); "> immediate;<br style="font: normal normal normal 12px/normal song, Verdana; "><img alt="" src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif" align="top">alter system kill session </span><span style="color: rgb(0, 0, 0); ">'</span><span style="color: rgb(0, 0, 0); ">133,572</span><span style="color: rgb(0, 0, 0); ">'</span><span style="color: rgb(0, 0, 0); "> immediate;<br style="font: normal normal normal 12px/normal song, Verdana; "><img alt="" src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif" align="top">alter system kill session </span><span style="color: rgb(0, 0, 0); ">'</span><span style="color: rgb(0, 0, 0); ">135,1456</span><span style="color: rgb(0, 0, 0); ">'</span><span style="color: rgb(0, 0, 0); "> immediate;<br style="font: normal normal normal 12px/normal song, Verdana; "><img alt="" src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif" align="top">alter system kill session </span><span style="color: rgb(0, 0, 0); ">'</span><span style="color: rgb(0, 0, 0); ">136,487</span><span style="color: rgb(0, 0, 0); ">'</span><span style="color: rgb(0, 0, 0); "> immediate;<br style="font: normal normal normal 12px/normal song, Verdana; "><img alt="" src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif" align="top">alter system kill session </span><span style="color: rgb(0, 0, 0); ">'</span><span style="color: rgb(0, 0, 0); ">138,302</span><span style="color: rgb(0, 0, 0); ">'</span><span style="color: rgb(0, 0, 0); "> immediate;<br style="font: normal normal normal 12px/normal song, Verdana; "><img alt="" src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif" align="top">alter system kill session </span><span style="color: rgb(0, 0, 0); ">'</span><span style="color: rgb(0, 0, 0); ">139,366</span><span style="color: rgb(0, 0, 0); ">'</span><span style="color: rgb(0, 0, 0); "> immediate;</span></div></div><p style="font: normal normal normal 12px/normal song, Verdana; ">再复制这些语句，粘贴到sqlplus中执行，来杀掉Test2的session。</p></blockquote><p style="font: normal normal normal 12px/normal song, Verdana; ">-----create user Test2 identified by Test2passwd default tablespace cmis temporary tablespace temp profile default;（创建用户）<br style="font: normal normal normal 12px/normal song, Verdana; "> <br style="font: normal normal normal 12px/normal song, Verdana; ">-----grant connect,resource to Test2; （授权）</p><p style="font: normal normal normal 12px/normal song, Verdana; "><strong>8.把文件从A机器上拷贝到B机器上。假如拷贝过来放到tmp目录下/tmp/Test1.dmp</strong></p><p style="font: normal normal normal 12px/normal song, Verdana; "><strong>9.最后在A机器上按用户导入数据</strong></p><blockquote dir="ltr" style="margin-right: 0px; "><p style="font: normal normal normal 12px/normal song, Verdana; ">#一定注意执行imp时要退出sqlplus，在linux的shell下执行imp</p><p style="font: normal normal normal 12px/normal song, Verdana; ">$ imp Test2/Test2passwd fromuser =Test1 touser =test2 file=D:\xsldb.DMP log =app/oracle/file/log/DEV_PMODOC.log;</p><p style="font: normal normal normal 12px/normal song, Verdana; ">文章来自互联网,感谢作者!</p></blockquote></span>
页:
[1]