ITPub博客

首页 > 数据库 > Oracle > IMP Takes More Time To Import The Constraints (Doc ID 166887.1)

IMP Takes More Time To Import The Constraints (Doc ID 166887.1)

Oracle 作者:rongshiyuan 时间:2014-02-18 17:20:27 0 删除 编辑
IMP Takes More Time To Import The Constraints (Doc ID 166887.1)

Applies to:

Oracle Server - Enterprise Edition - Version 8.1.6.0 to 11.2.0.4 [Release 8.1.6 to 11.2]
Information in this document applies to any platform.

Symptoms

Exporting from Oracle database (older versions 8i/9i) and importing into Oracle 8i/9i/10g/11g. You observed a slow perfomance when running import job. The TKPROF output shows the time is spent on view SYS.IMP8CDT. Your application has many constraints.

Cause

The view IMP8CDT was investigated in bugs:

Bug 1779169 IMPORT IS VERY SLOW TO WRAP UP WHEN THERE ARE LARGE NUMBERS OF CONSTRAINTS
Bug 1936535 IMPORT TAKES MORE TIME AFTER UPGRADE
Bug 12341094 IMPORT INTO ORACLE 11.2 IS TOO SLOW USING ORACLE 9.2 DUMP FILE

which are closed (not a bug, not reproducible)

Solution

1. Import with CONSTRAINTS=N. Or:

2. Change the following export data dictionary views and restart the import:

CREATE OR REPLACE view imp8cdt (bad) AS
SELECT decode(bitand(c$.defer,16),16,1,0)
FROM   sys.cdef$ c$
WHERE  c$.defer is NOT NULL
  AND bitand(c$.defer,16) = 16
/

CREATE OR REPLACE view imp8cdt2 (ownerid, bad) AS
SELECT co$.owner#, decode(bitand(c$.defer,16),16,1,0)
FROM   sys.cdef$ c$, sys.con$ co$
WHERE  c$.defer is NOT NULL
   AND bitand(c$.defer,16) = 16
   AND c$.con# = co$.con#
/

grant select on imp8cdt to select_catalog_role;
grant select on imp8cdt2 to select_catalog_role;
CREATE OR REPLACE view imp8cdtu  AS
SELECT * from imp8cdt2
     WHERE ownerid = UID
/

References

BUG:1936535 - IMPORT TAKES MORE TIME AFTER UPGRADE
BUG:12341094 - IMPORT INTO ORACLE 11.2 IS TOO SLOW USING ORACLE 9.2 DUMP FILE.
BUG:1779169 - IMPORT IS VERY SLOW TO WRAP UP WHEN THERE ARE LARGE NUMBERS OF CONSTRAINTS.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17252115/viewspace-1083336/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-11-24

  • 博文量
    798
  • 访问量
    3252614