DBMS_DATAPUMP.start_job (l_dp_handle);
DBMS_DATAPUMP.detach (l_dp_handle);
END;
/
/**************************************************/
/* Author: Robinson Cheng */
/* Blog: http://blog.csdn.net/robinson_0612 */
/* MSN: robinson_0612@hotmail.com */
/* QQ: 645746311 */
/**************************************************/
[sql] view plaincopyprint
--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
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 => 'SUBQUER