在使用imp导入过程中，经常会遇到IMP-00041: Warning: object created with compilation warnings
这个错误是编译的问题，只要在导入结束之后，重新编译一下数据库对象即可。执行一下这个脚本就可以编译数据库对象$ORACLE_HOME/rdbms/admin/utlrp.sql，执行这个脚本要确保数据库中没有活动的事务或者DDL，否则容易出现死锁。
一般的，在完成数据库升级、应用完补丁和DDL操作之后，数据库内的相关对象会失效，这个时候就需要重新编译数据库对象。当然有的时候对象虽然失效了，但是在下次被调用的时候，它会被重新编译。
对象为什么会失败？一句话就是对象的结构发生了变化，对象与对象之间存在依赖关系，这也是在做imp的时候需要考虑的。什么叫依赖关系？比方说视图和表就是依赖关系，如果一个视图v_tab1是基于表tab1的，那么当tab1表结构发生变化，那么v_tab1这个对象就会失效。如果这个时候你去查v_tab1的状态，那么它肯定是invalid的，但是你select * from v_tab1，然后再去查看v_tab1的对象状态，这个时候它又变成valid状态，也就是说你刚刚查看视图的时候，oracle已经重新编译了这个视图。
在重新编译之前，查看一下数据库中的无效对象
SQL> select owner, count(owner) from dba_objects where status ='INVALID' group by owner;
OWNER COUNT(OWNER) ------------------------------ ------------ PUBLIC 170 OLAPSYS 257 SHFC 1 AUDITUSR 519 SYSMAN 17 CNCORA 22 SADEUSR3 138 SADEUSR2 95 SHWC 15 SADEUSR 172 BOMC 3 FMS 6 EPMS_SH 9 OE 9 BPM1 1 |
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP UTLRP_BGN 2019-05-22 07:52:24
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid DOC> objects in the database. Recompilation time is proportional to the DOC> number of invalid objects in the database, so this command may take DOC> a long time to execute on a database with a large number of invalid DOC> objects. DOC> DOC> Use the following queries to track recompilation progress: DOC> DOC> 1. Query returning the number of invalid objects remaining. This DOC> number should decrease with time. DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6); DOC> DOC> 2. Query returning the number of objects compiled so far. This number DOC> should increase with time. DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED; DOC> DOC> This script automatically chooses serial or parallel recompilation DOC> based on the number of CPUs available (parameter cpu_count) multiplied DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu). DOC> On RAC, this number is added across all RAC nodes. DOC> DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel DOC> recompilation. Jobs are created without instance affinity so that they DOC> can migrate across RAC nodes. Use the following queries to verify DOC> whether UTL_RECOMP jobs are being created and run correctly: DOC> DOC> 1. Query showing jobs created by UTL_RECOMP DOC> SELECT job_name FROM dba_scheduler_jobs DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%'; DOC> DOC> 2. Query showing UTL_RECOMP jobs that are running DOC> SELECT job_name FROM dba_scheduler_running_jobs DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%'; DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP UTLRP_END 2019-05-22 08:32:20
DOC> The following query reports the number of objects that have compiled DOC> with errors. DOC> DOC> If the number is higher than expected, please examine the error DOC> messages reported with each object (using SHOW ERRORS) to see if they DOC> point to system misconfiguration or resource constraints that must be DOC> fixed before attempting to recompile these objects. DOC>#
OBJECTS WITH ERRORS ------------------- 2
DOC> The following query reports the number of errors caught during DOC> recompilation. If this number is non-zero, please query the error DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors DOC> are due to misconfiguration or resource constraints that must be DOC> fixed before objects can compile successfully. DOC>#
ERRORS DURING RECOMPILATION --------------------------- 2
Function created. |
重新编译完之后，再查看数据库中的无效对象
SQL> select owner, count(owner) from dba_objects where status ='INVALID' group by owner;
OWNER COUNT(OWNER) ------------------------------ ------------ PUBLIC 170 OLAPSYS 257 SHFC 1 AUDITUSR 378 SYSMAN 17 CNCORA 22 SADEUSR3 138 SADEUSR2 95 SHWC 3 SADEUSR 95 BOMC 3 FMS 6 EPMS_SH 9 OE 9 BPM1 1
15 rows selected. |
