Invalid Oracle compilation user object

Invalid Oracle compilation user object

When using the IMPDP/IMP tool to migrate data, a warning is often prompted for invalid objects, which must be handled as follows.

1. query invalid objects of the specified user

Su-Oracle

Sqlplus/as sysdba

SQL> select owner, object_name, replace (object_type, '','') object_type, to_char (created, 'yyyy-mm-dd') as created, to_char (last_ddl_time, 'yyyy-mm-dd') as last_ddl_time, status from dba_objects where status = 'invalid' and owner = 'hkjjw ';
OWNER OBJECT_NAME OBJECT_TYPE CREATED LAST_DDL_T STATUS
----------------------------------------------------------------------------------
HKJJW FUNC_RETURN_PY FUNCTION 2013-08-02 2013-08-02 INVALID
HKJJW MONITORING_FLOW_PARALLEL_VIEW VIEW 2013-08-02 2013-08-02 INVALID
Hkjjw test view 2013-08-02 2013-08-02 INVALID

Query invalid objects of a user.

SQL> select * from dba_objects where status <> 'valid' and owner = 'hkjjw ';

2. manually compile or

Alter function hkjjw. FUNC_RETURN_PY COMPILE;
Alter view hkjjw. MONITORING_FLOW_PARALLEL_VIEW COMPILE;
Alter view hkjjw. test compile;

If it is an index, it needs to be rebuilt.
Alter index index_name rebuild;

For package body
Alter package DBMS_CUBE compile body;

3. automatically generate an SQL statement for compiling invalid objects

1) count the number of invalid objects of the current user:

SQL> select owner, object_type, status, count (*) from dba_objects where status = 'invalid' group by owner, object_type, status order by owner, object_type;

OWNER OBJECT_TYPE status count (*)
------------------------------------------------------------------
HNADEV_GROUP function invalid 1
HNADEV_GROUP package body invalid 35
HNADEV_GROUP view invalid 7

2) generate an SQL statement for compiling invalid objects

SQL> select 'alter '| OBJECT_TYPE | ''| OWNER | '. '| OBJECT_NAME | 'compile;' from dba_objects where status = 'invalid' and object_type in ('package', 'package body', 'function', 'process ', 'trigger', 'view ');

'Alter '| OBJECT_TYPE | ''| OWNER |'. '| OBJECT_NAME | 'compile ;'
----------------------------------------------------------------------
Alter view HNADEV_GROUP.V_E_T_FEEMONTH_CAL COMPILE;
Alter view HNADEV_GROUP.V_E_T_FEEMONTH COMPILE;
Alter view HNADEV_GROUP.V_E_Z_BILL_STAY_GROUP COMPILE;
...
By copying the preceding SQL statements, you can directly execute compilation and execution.

4. You can also perform manual compilation under the oracle user using the following method:

# Su-oracle
$ Sqlplus/as sysdba
SQL> @ $ ORACLE_HOME/rdbms/admin/utlrp. SQL