oracle - 对于触发器的 Oracle IMPDP REMAP_SCHEMA 问题(ORA-39083、ORA-00942)是否有好的解决方法？

Question

可以使用 Oracle 数据泵导入工具 (IMPDP.EXE) 通过 REMAP_SCHEMA 选项将一个架构导入到另一个架构中。然而，存在一个问题，即触发器未正确重新映射。这会导致触发器根本没有被创建，并出现如下错误:

ORA-39083: Object type TRIGGER failed to create with error: ORA-00942: table or view does not exist Failing sql is: CREATE TRIGGER "**NEW_SCHEMA**"."METER_ALARMS_BI"   BEFORE INSERT ON
**OLD_SCHEMA**.METER_ALARMS ...

这是因为创建SQL仍然引用OLD_SCHEMA。 Oracle 文档中确实提到:

The mapping may not be 100 percent complete, because there are certain schema references that Import is not capable of finding. For example, Import will not find schema references embedded within the body of definitions of types, views, procedures, and packages.

恕我直言，这有点是 Oracle 的逃避，但那是另一个讨论了!

根据 Oracle Metalink 说明 750783.1，解决方法是:

1. Create a SQLFILE to include the relevant DDL command(s):

      impdp system/****** directory=test_dp
      DUMPFILE=export_schemas.dmp
     remap_schema=u1:u2 sqlfile=script.sql

2. Extract the affected DDL from the written SQLFILE and correct the schema reference. Then execute the command manually.

这不是一个好方法，特别是如果您有许多失败的对象并且希望自动执行组合多个架构以进行数据库现场升级的过程。

有没有人找到更好的方法来做到这一点？如果要在现场使用，我需要一个必须 100% 可靠的解决方案。我可以解析生成的 SQL 文件，但是可以 100% 正确吗？是否没有某种方法可以拦截 IMPDP 执行的 CREATE SQL 语句并在导入时即时纠正它？可以直接给DMP文件打补丁吗？

Answer 1

我认为这取决于模式名称是否可以作为非模式名称的一部分出现在代码中。例如，您的变量名称是否包含与架构名称相同的字符。如果没有，那么我认为编写一个过程来编辑生成的触发器创建脚本并用新模式替换旧模式并不那么困难。也许您可以使用 datapump 导出/导入没有文本代码的对象类型(不是触发器、包、过程、函数等)，然后转储代码对象的 SQL，然后用新模式替换旧模式.

如果旧架构名称出现在您不想替换的位置，则替换起来会比较困难。您可以提取代码对象并尝试创建它们并收集所有错误。然后获取失败的对象的名称，并尝试根据错误将 oldschema.objectname 替换为 newschema.objectname 并重新运行。

假设没有像 oldschema 这样的字符串，如何在触发器文本中编辑架构的示例。您不想替换的:

Example

SQL> 
SQL> set define off
SQL> 
SQL> drop table test1.tab1;

Table dropped.

SQL> drop table test1.tab2;

Table dropped.

SQL> 
SQL> create table test1.tab1
  2  (
  3     col1 number,
  4     col2 number
  5  );

Table created.

SQL> 
SQL> create table test1.tab2
  2  (
  3     col1 number,
  4     col2 number
  5  );

Table created.

SQL> 
SQL> create or replace trigger test1.trg1
  2  before insert or update on test1.tab1
  3  for each row
  4  begin
  5    :new.col2 := :new.col1*2;
  6  end;
  7  /

Trigger created.

SQL> 
SQL> create or replace trigger test1.trg2
  2  before insert or update on test1.tab2
  3  for each row
  4  begin
  5    :new.col2 := :new.col1*2;
  6  end;
  7  /

Trigger created.

SQL> 
SQL> drop table clobout;

Table dropped.

SQL> 
SQL> create table clobout (doc clob);

Table created.

SQL> 
SQL> declare
  2  h NUMBER; --handle returned by OPEN
  3  th NUMBER; -- handle returned by ADD_TRANSFORM
  4  doc CLOB;
  5  BEGIN
  6  
  7  -- Specify the object type.
  8  h := DBMS_METADATA.OPEN('TRIGGER');
  9  
 10  -- Use filters to specify the particular object desired.
 11  DBMS_METADATA.SET_FILTER(h,'SCHEMA','TEST1');
 12  
 13  -- Request that the schema name be modified.
 14  th := DBMS_METADATA.ADD_TRANSFORM(h,'MODIFY');
 15  DBMS_METADATA.SET_REMAP_PARAM(th,'REMAP_SCHEMA','TEST1','TEST2');
 16  
 17  -- Request that the metadata be transformed into creation DDL.
 18  th := DBMS_METADATA.ADD_TRANSFORM(h,'DDL');
 19  
 20  dbms_metadata.set_transform_param(th,'SQLTERMINATOR',true);
 21  
 22  -- Fetch the triggers.
 23  
 24  LOOP
 25    doc := DBMS_METADATA.FETCH_CLOB(h);
 26    EXIT WHEN (doc is null);
 27    insert into clobout values (doc);
 28    commit;
 29  END LOOP;
 30  
 31  -- Release resources.
 32  DBMS_METADATA.CLOSE(h);
 33  END;
 34  /

PL/SQL procedure successfully completed.

SQL> 
SQL> -- update schema name in triggers
SQL> 
SQL> update clobout set doc=replace(doc,'test1.','test2.');

2 rows updated.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> select doc from clobout;

  CREATE OR REPLACE EDITIONABLE TRIGGER "TEST2"."TRG1"
before insert or update on test2.tab1
for each row
begin
  :new.col2 := :new.col1*2;
end;
/
ALTER TRIGGER "TEST2"."TRG1" ENABLE;


  CREATE OR REPLACE EDITIONABLE TRIGGER "TEST2"."TRG2"
before insert or update on test2.tab2
for each row
begin
  :new.col2 := :new.col1*2;
end;
/
ALTER TRIGGER "TEST2"."TRG2" ENABLE;


SQL> 
SQL> spool off