--6、过滤特定表上的特定行 --现在表tb_emp上HIREDATE为日期型，需要按日期进行过滤 scott@CNMMBO> desc tb_emp Name Null? Type ----------------------------------------- -------- ---------------------------- EMPNO NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE VARCHAR2(10) SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) scott@CNMMBO> select empno,ename,hiredate from tb_emp; EMPNO ENAME HIREDATE ---------- ---------- ---------- 9999 Ro.Ch 7369 SMITH 19801217 7499 ALLEN 19810220 7521 WARD 19810222 7566 JONES 19810402 7654 MARTIN 19810928 7698 BLAKE 19810501 7782 CLARK 19810609 7788 SCOTT 19870419 7839 KING 19811117 7844 TURNER 19810908 7876 ADAMS 19870523 7900 JAMES 19811203 7902 FORD 19811203 7934 MILLER 19820123 15 rows selected. scott@CNMMBO> select count(*) from tb_emp where hiredate>='19810311'; COUNT(*) ---------- 11 DECLARE l_dp_handle NUMBER; BEGIN l_dp_handle := DBMS_DATAPUMP.open (operation => 'EXPORT', job_mode => 'TABLE'); dbms_datapump. add_file (handle => l_dp_handle , filename => 'scott_tb_emp.dmp' , directory => 'DB_DUMP_DIR' , filetype => DBMS_DATAPUMP.KU$file_type_dump_file); dbms_datapump. add_file (handle => l_dp_handle , filename => 'scott_tb_emp.log' , directory => 'DB_DUMP_DIR' , filetype => DBMS_DATAPUMP.KU$file_type_log_file); DBMS_DATAPUMP.metadata_filter (handle => l_dp_handle , name => 'NAME_EXPR' , VALUE => ' =''TB_EMP'' ' , object_type => 'TABLE'); DBMS_DATAPUMP.data_filter( handle => l_dp_handle , name => 'SUBQUERY' , VALUE => 'WHERE HIREDATE >=''19810311''' , table_name => 'TB_EMP' ); DBMS_DATAPUMP.start_job (l_dp_handle); DBMS_DATAPUMP.detach (l_dp_handle); END; / /* oracle@SZDB:/u02/database/CNMMBO/BNR/dump> more scott_tb_emp.log Starting "SCOTT"."SYS_EXPORT_TABLE_01": Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 64 KB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "SCOTT"."TB_EMP" 7.695 KB 11 rows Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is: /u02/database/CNMMBO/BNR/dump/scott_tb_emp.dmp Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 17:33:23 */ --7、批量过滤特定表上的特定行 --将下面的代码包含在PL/SQL块中，使用游标循环来传递需要过滤的表的名字从而生成多个过滤条件 --下面的PL/SQL块中所有包含ARC字符的表上的特定日期v_split_date的记录才能被导出 FOR tab_cur IN (SELECT table_name, num_rows FROM dba_tables WHERE table_name LIKE '%ARC%' AND owner='GOEX_ADMIN') LOOP dbms_datapump. data_filter ( handle => hand, name => 'SUBQUERY', VALUE => 'WHERE BUSINESS_DATE >= ''' || v_split_date || '''', table_name => '' || tab_cur.table_name || ''); END LOOP; --8、错误处理 --如果定义了job_name则经常会碰到下列错误，如果未指定job_name则有系统自动生成job_name，并由系统自动管理job_name DECLARE * ERROR at line 1: ORA-31634: job already exists ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79 ORA-06512: at "SYS.DBMS_DATAPUMP", line 911 ORA-06512: at "SYS.DBMS_DATAPUMP", line 4354 ORA-06512: at line 7 scott@CNMMBO> ho oerr ora 31634 /* 31634, 00000, "job already exists" // *Cause: Job creation or restart failed because a job having the selected // name is currently executing. This also generally indicates that // a Master Table with that job name exists in the user schema. Refer // to any following error messages for clarification. // *Action: Select a different job name, or stop the currently executing job // and re-try the operation (may require a DROP on the Master Table). */ scott@CNMMBO> select table_name from user_tables where table_name like 'JOB%'; TABLE_NAME ------------------------------ JOB_EXP scott@CNMMBO> drop table job_exp; drop table job_exp * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified scott@CNMMBO> SELECT DISTINCT object_name 2 || ' ' 3 || locked_mode 4 || ' ' 5 || ctime 6 || ' ' 7 || c.SID 8 || ' ' 9 || serial# 10 FROM v$locked_object a, dba_objects b, v$lock c, v$session d 11 WHERE a.object_id = b.object_id 12 AND c.SID = a.session_id 13 AND c.SID = d.SID; OBJECT_NAME||''||LOCKED_MODE||''||CTIME||''||C.SID||''||SERIAL# ----------------------------------------------------------------------- JOB_EXP 3 552 1075 799 scott@CNMMBO> alter system kill session '1075,799'; System altered. scott@CNMMBO> drop table job_exp purge; -->删除表之后再次进行导出 Table dropped. 9、使用视图监控datapump状态 scott@CNMMBO> col owner_name format a15 scott@CNMMBO> col operation format a15 scott@CNMMBO> col state format a20 scott@CNMMBO> select owner_name,job_name,operation,job_mode,state,degree from dba_datapump_jobs; OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE DEGREE --------------- --------------- --------------- ---------- -------------------- ---------- SCOTT JOB_EXP1 EXPORT SCHEMA EXECUTING 1 10、使用下面的过程设定并行度 DBMS_DATAPUMP.set_parallel (hand, 1); 11、上述操作所在的演示环境 scott@CNMMBO> select * from v$version where rownum<2; BANNER ---------------------------------------------------------------- Oracle Database 10g Release 10.2.0.3.0 - 64bit Production</td></tr> <p> </p></table> </div><!-- /content --> <div class="dede_pages"> <ul class="pagelist"> <li><a>共16页: </a></li><li><a href='20120118754_14.html'>上一页</a></li><li><a href='20120118754.html'>1</a></li><li><a href='20120118754_2.html'>2</a></li><li><a href='20120118754_3.html'>3</a></li><li><a href='20120118754_4.html'>4</a></li><li><a href='20120118754_5.html'>5</a></li><li><a href='20120118754_6.html'>6</a></li><li><a href='20120118754_7.html'>7</a></li><li><a href='20120118754_8.html'>8</a></li><li><a href='20120118754_9.html'>9</a></li><li><a href='20120118754_10.html'>10</a></li><li><a href='20120118754_11.html'>11</a></li><li><a href='20120118754_12.html'>12</a></li><li><a href='20120118754_13.html'>13</a></li><li><a href='20120118754_14.html'>14</a></li><li class="thisclass"><a href='#'>15</a></li><li><a href='20120118754_16.html'>16</a></li><li><a href='20120118754_16.html'>下一页</a></li> </ul> <p>本页地址 http://www.jybase.net/wangzhanbiancheng/20120118754.html</p> <p> <p> 【<a href="http://www.baidu.com/s?wd=使用 Oracle Datapump API 实现数据导出 .(15)" target="_blank"><font color=red>百度搜索更多</font></a>】 </p><p>【<a href="http://www.google.com.hk/search?q=使用 Oracle Datapump API 实现数据导出 .(15)" target="_blank"><font color=red>谷歌搜索更多</font></a>】</p> </div><!-- /pages --> <!-- //顶踩 --> <div class="newdigg" id="newdigg"> <div class="diggbox digg_good" onmousemove="this.style.backgroundPosition='left bottom';" onmouseout="this.style.backgroundPosition='left top';" onclick="javascript:postDigg('good',754)"> <div class="digg_act">顶一下</div> <div class="digg_num">(0)</div> <div class="digg_percent"> <div class="digg_percent_bar"><span style="width:0%"></span></div> <div class="digg_percent_num">0%</div> </div> </div> <div class="diggbox digg_bad" onmousemove="this.style.backgroundPosition='right bottom';" onmouseout="this.style.backgroundPosition='right top';" onclick="javascript:postDigg('bad',754)"> <div class="digg_act">踩一下</div> <div class="digg_num">(0)</div> <div class="digg_percent"> <div class="digg_percent_bar"><span style="width:0%"></span></div> <div class="digg_percent_num">0%</div> </div> </div> </div> <script language="javascript" type="text/javascript">getDigg(754);</script> <!-- //顶踩部份的源码结束 --> <div class="boxoff"> <strong>------分隔线----------------------------</strong> </div> <div class="handle"> <div class="context"> <ul> <li>上一篇：<a href='http://www.jybase.net/wangzhanbiancheng/20111113614.html'>js的字符串加密/javascript字符编码 escape,urlencode等</a> </li> <li>下一篇：<a href='http://www.jybase.net/wangzhanbiancheng/20120120756.html'>关于js中call和apply函数的应用</a> </li> </ul> </div><!-- /context --> <div class="actbox"> <ul> <li id="act-fav"><a href="http://www.jybase.net/plus/stow.php?aid=754" target="_blank">收藏</a></li> <li id="act-err"><a href="http://www.jybase.net/plus/erraddsave.php?aid=754&title=使用 Oracle Datapump API 实现数据导出 .(15)" target="_blank">挑错</a></li> <li id="act-pus"><a href="http://www.jybase.net/plus/recommend.php?aid=754" target="_blank">推荐</a></li> <li id="act-pnt"><a href="#" onClick="window.print();">打印</a></li> </ul> </div><!-- /actbox --> </div><!-- /handle --> </div><!-- /viewbox --> <!-- Baidu Button BEGIN --> <div id="bdshare" class="bdshare_t bds_tools get-codes-bdshare"> <a class="bds_qzone">QQ空间</a> <a class="bds_tsina">新浪微博</a> <a class="bds_tqq">腾讯微博</a> <a class="bds_renren">人人网</a> <span class="bds_more">更多</span> </div> <!-- //AJAX评论区 --> <!-- //主模板必须要引入http://www.jybase.net/include/dedeajax2.js --> <a name='postform'></a> <div class="mt1"> <dl> <dd> <div class="dede_comment_post"> <form action="#" method="post" name="feedback"> <input type="hidden" name="dopost" value="send" /> <input type="hidden" name="comtype" value="comments"> <input type="hidden" name="aid" value="754" /> <input type="hidden" name="fid" id='feedbackfid' value="0"/> <a name='postform'></a> <div class='leftinput'> <div class="icon_ctn"> <div class="dcmp-stand"> <strong>评价:</strong> <input type="radio" name="feedbacktype" checked="1" value="feedback" id="dcmp-stand-neu" /><label for="dcmp-stand-neu">中立</label> <input type="radio" name="feedbacktype" value="good" id="dcmp-stand-good" /><label for="dcmp-stand-good">好评</label> <input type="radio" name="feedbacktype" value="bad" id="dcmp-stand-bad" /><label for="dcmp-stand-bad">差评</label> </div><!-- /dcmp-stand --> <div class="icon_ctn" > 昵称: <input name="username" type="text" id="name" value="" onfocus="javascript:if(this.value=='') this.value='';" /> 验证码:<input type='text' name='validate' size='4' class='ipt-txt' /><img src="/include/vdimgck.php" id="validateimg" style="cursor:pointer" onclick="this.src=this.src+'?'" title="点击我更换图片" alt="点击我更换图片" /> <input type="hidden" type="checkbox" name="notuser" id="dcmp-submit-guest" /><label for="dcmp-submit-guest" /><!–匿名? –></label> </div> </div><!-- /dcmp-title --> <div class="dcmp-content"> <textarea cols="60" name="msg" rows="5" class="text_area" id="textarea">

• 电脑相关
• 语言编程
• 美工图像
• 网站建设
• 网络赚钱

• windows
• 各类编程
• linux
• qq相关
• 软件破解
• 系统安全

• 建站技巧|seo
• 在线工具
• 网页特效
• 网站编程
• 美工设计
• 网站安全

• 系统教程
• 编程教程
• 建站教程
• 美工教程
• 破解教程
• 网络安全教程

• 海量网赚项目
• 网赚教程
• seo|竞价|营销

• 系统软件下载
• 网络软件下载
• 电子书籍