Oracle Remp_data多个表-无法在expdp中应用？

嗨，汤姆，

试图将remp_data应用于多个表它总是失败。

列名在我应用函数的两个表中都是相同的，
它从源架构到目标架构导出和导入没有任何错误，但是它从不应用旨在实现相同功能的功能。

导出
=======
开始 “XXXX”。“系统 _ 导出 _ 表 _ 01”: xxxx/******** 目录 = expdp_dir dumpfile = test0409.dmp日志文件 = test0409.log
remap_data = xxxx.Tablename1.column_name: xxxx.pkg_test2.mask，xxxx.Tablename2.column_name: xxxx.pkg_test2.mask
用分块法估算在...
处理对象类型TABLE_EXPORT/TABLE/TABLE_DATA
使用块法的总估计: 128 KB
处理对象类型TABLE_EXPORT/TABLE
处理对象类型TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
。。导出 “XXXX”。“TABLENAME1” 22.24 KB 99行
。。导出 “XXXX”。“TABLENAME2” 22.68 KB 99行
主表 “XXXX”。“SYS_EXPORT_TABLE_01” 已成功加载/卸载
*
XXXX.SYS_EXPORT_TABLE_01的转储文件集是:
/备份/Oracle/导出/XXXX/test0409.dmp
作业 “XXXX”。“系统导出表 _ 01” 在11:30:55成功完成



导入
=

连接到: Oracle数据库11g企业版11.2.0.3.0-64位生产
分区、自动存储管理、OLAP、数据挖掘
和实际应用测试选项
主表 “XXXX”。“SYS_IMPORT_FULL_01” 成功加载/卸载
从 “XXXX” 开始。“SYS_IMPORT_FULL_01”: xxxx/******** 目录 = expdp_dir dumpfile = test0409.dmp日志文件 = testimp0409.log rempap_schema = xxxx:xxxx_d
处理对象类型TABLE_EXPORT/TABLE
处理对象类型TABLE_EXPORT/TABLE/TABLE_DATA
。。导入的 "XXXX_D"."TABLENAME1" 22.24 KB 99行
。。导入的 "XXXX_D"."TABLENAME2" 22.68 KB 99行
处理对象类型TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
作业 “XXXX”。“系统输入 _ 完整 _ 01” 在11:31:51成功完成


函数
=======

创建或替换包主体pkg_test2为

功能掩码 (
nrr_number_low IN VARCHAR2
) 将VARCHAR2返回为
m_len数: = 0;
m_no_masked VARCHAR2(10) := NULL;
开始
dbms_output.put_line('原始手机号码:' | | nrr_number_low);
m_no_masked := substr(nrr_number_low，1,4)
| | regexp_replace(substr(nrr_number_low，5,6)，'1 | 2 | 3 | 4 | 5 | 6 | 7 | 8'，'*');

dbms_output.put_line('屏蔽手机号码:' | | m_no_masked);
返回m_no_masked;
结束面具;

结束;

这是我完整的测试用例

SQL> drop user demo cascade;

User dropped.

SQL> create user demo identified by demo;

User created.

SQL> grant connect, resource to demo;

Grant succeeded.

SQL> alter user demo quota 100m on users;

User altered.

SQL> create table demo.t1 ( m varchar2(20));

Table created.

SQL>
SQL> insert into demo.t1 values ('123123123');

1 row created.

SQL> insert into demo.t1 values ('763134123');

1 row created.

SQL> insert into demo.t1 values ('233145123');

1 row created.

SQL> insert into demo.t1 values ('433123123');

1 row created.

SQL>
SQL> create table demo.t2 as select * from demo.t1;

Table created.

SQL> commit;

Commit complete.

SQL>
SQL> grant read, write on directory temp to demo;

Grant succeeded.

SQL>
SQL> drop user demo1 cascade;

User dropped.

SQL> create user demo1 identified by demo1;

User created.

SQL> grant connect, resource to demo1;

Grant succeeded.

SQL> alter user demo1 quota 100m on users;

User altered.

SQL> grant read, write on directory temp to demo1;

Grant succeeded.

C:\temp>expdp demo/demo directory=TEMP dumpfile=demo.dmp

Export: Release 12.2.0.1.0 - Production on Wed Sep 12 12:33:36 2018

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "DEMO"."SYS_EXPORT_SCHEMA_01":  demo/******** directory=TEMP dumpfile=demo.dmp
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "DEMO"."T2"                                 5.101 KB       4 rows
. . exported "DEMO"."T1"                                 5.101 KB       4 rows
Master table "DEMO"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DEMO.SYS_EXPORT_SCHEMA_01 is:
  C:\TEMP\DEMO.DMP
Job "DEMO"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Sep 12 12:33:53 2018 elapsed 0 00:00:17

SQL> create or replace
  2  PACKAGE demo1.pkg_test2 AS
  3
  4  FUNCTION mask (
  5  nrr_number_low IN VARCHAR2
  6  ) RETURN VARCHAR2 ;
  7
  8  END;
  9  /

Package created.

SQL>
SQL> create or replace
  2  PACKAGE BODY demo1.pkg_test2 AS
  3
  4  FUNCTION mask (
  5  nrr_number_low IN VARCHAR2
  6  ) RETURN VARCHAR2 AS
  7  m_len NUMBER := 0;
  8  m_no_masked VARCHAR2(10) := NULL;
  9  BEGIN
 10  --dbms_output.put_line('ORIGINAL MOBILE NUMBER: ' || nrr_number_low);
 11  m_no_masked := substr(nrr_number_low,1,4)
 12  || regexp_replace(substr(nrr_number_low,5,6),'1|2|3|4|5|6|7|8','*');
 13
 14  --dbms_output.put_line('MASKED MOBILE NUMBER: ' || m_no_masked);
 15  RETURN m_no_masked;
 16  END mask;
 17
 18  END;
 19  /

Package body created.

C:\temp>impdp demo1/demo1 directory=TEMP remap_schema=demo:demo1 dumpfile=demo.dmp remap_data=demo.t1.m:demo1.pkg_test2.mask,demo.t2.m:demo1.pkg_test2.mask

Import: Release 12.2.0.1.0 - Production on Wed Sep 12 12:34:29 2018

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "DEMO1"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "DEMO1"."SYS_IMPORT_FULL_01":  demo1/******** directory=TEMP remap_schema=demo:demo1 dumpfile=demo.dmp remap_data=demo.t1.m:demo1.pkg_test2.mask,demo.t2.m:demo
1.pkg_test2.mask
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "DEMO1"."T2"                                5.101 KB       4 rows
. . imported "DEMO1"."T1"                                5.101 KB       4 rows
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "DEMO1"."SYS_IMPORT_FULL_01" successfully completed at Wed Sep 12 12:34:36 2018 elapsed 0 00:00:07

SQL> select * from demo1.t1;

M
----------------------------------------------
1231*****
7631*****
2331*****
4331*****

4 rows selected.

SQL> select * from demo1.t2;

M
----------------------------------------------
1231*****
7631*****
2331*****
4331*****

4 rows selected.
复制

对我来说很好