Oracle IMPDP REMAP_SCHEMA 触发器问题（ORA-39083、ORA-00942）是否有很好的解决方法？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2138348/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is there a good workaround to the Oracle IMPDP REMAP_SCHEMA issue with triggers (ORA-39083, ORA-00942)?
提问by VinceJS
One can use the Oracle data pump import tool (IMPDP.EXE) to import one schema into another using the REMAP_SCHEMA option. However there is an issue in that triggers are not properly remapped. This leads to the trigger not being created at all with an error as follows:
可以使用 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 ...
The reason for this is because the create SQL still refers to OLD_SCHEMA. It does say in the Oracle documentation that:
这样做的原因是因为创建 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.
映射可能不是 100% 完整，因为导入无法找到某些架构引用。例如，Import 不会找到嵌入在类型、视图、过程和包的定义主体中的模式引用。
IMHO this is a bit of a cop out by Oracle but that's another discussion!
恕我直言，这是甲骨文的一个警察，但这是另一个讨论！
According to Oracle Metalink note 750783.1, the workaround is to:
根据 Oracle Metalink 说明 750783.1，解决方法是：
- Create a SQLFILE to include the relevant DDL command(s):
- 创建一个 SQLFILE 以包含相关的 DDL 命令：
impdp system/****** directory=test_dp
DUMPFILE=export_schemas.dmp
remap_schema=u1:u2 sqlfile=script.sql
- Extract the affected DDL from the written SQLFILE and correct the schema reference. Then execute the command manually.
- 从写入的 SQLFILE 中提取受影响的 DDL 并更正架构引用。然后手动执行命令。
This is not a good way to do it especially if you have many failed objects and want to automate the process of combining multiple schema for in field upgrading of databases.
这不是一个好的方法，特别是如果您有许多失败的对象并且想要自动化组合多个模式的过程以进行数据库的现场升级。
Has anyone found a better way to do this? I need a solution that must be 100% reliable if its to be used in the field. I could parse the generated SQL file but can one get this 100% correct? Is there not some way to intercept the CREATE SQL statements execute by IMPDP and correct it on the fly while importing? Could one patch the DMP file directly?
有没有人找到更好的方法来做到这一点？如果要在现场使用，我需要一个必须 100% 可靠的解决方案。我可以解析生成的 SQL 文件，但是可以 100% 正确吗？有没有办法拦截 IMPDP 执行的 CREATE SQL 语句并在导入时即时更正它？可以直接修补DMP文件吗？
回答by Bobby Durrett
I think it depends on whether the schema names can appear in your code as part of something that is not a schema name. For example, do you have variable names that include the same characters as the schema name. If not then I don't think it would be that hard to script up a process that edits the generated trigger create scripts replacing the old schema with the new one. Maybe you can use datapump to export/import the object types that don't have text code (not triggers, packages, procedures, functions, etc.) and then dump out the SQL for the code objects and just replace old schema with new one.
我认为这取决于架构名称是否可以作为非架构名称的一部分出现在您的代码中。例如，您是否有包含与架构名称相同的字符的变量名称。如果不是，那么我认为编写一个编辑生成的触发器创建脚本的过程并用新的模式替换旧模式的过程不会那么难。也许您可以使用 datapump 导出/导入没有文本代码的对象类型（不是触发器、包、过程、函数等），然后转储代码对象的 SQL，只需将旧模式替换为新模式.
If the old schema name appears in places that you don't want to replace it would be harder to do. You might extract the code objects and try to create them and collect all of the errors. Then get the names of the objects that it failed on and try replacing the oldschema.objectname with newschema.objectname based on the errors and rerun.
如果旧模式名称出现在您不想替换的位置，则很难做到。您可以提取代码对象并尝试创建它们并收集所有错误。然后获取它失败的对象的名称，并根据错误尝试用 newschema.objectname 替换 oldschema.objectname 并重新运行。
Example of how you might edit the schema in the trigger text assuming there were no strings like oldschema. that you don't want to replace:
假设没有像 oldschema 这样的字符串，您可以如何编辑触发器文本中的架构的示例。您不想替换的：
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
回答by Gary Myers
You could look a DBMS_METADATA
你可以看看 DBMS_METADATA
There's a REMAP_SCHEMAoption for that. Not sure whether it will work any better than DATAPUMP (and I'd suspect that DATAPUMP would use DBMS_METADATA under the covers). But it would be easier to 'post-process' the output.
有一个REMAP_SCHEMA选项。不确定它是否会比 DATAPUMP 更好地工作（我怀疑 DATAPUMP 会在幕后使用 DBMS_METADATA）。但是对输出进行“后处理”会更容易。
