, 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
*
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#