Oracle compiled user Invalid object

Source: Internet
Author: User
Tags sqlplus

When migrating data using the Impdp/imp tool, warnings for invalid objects are often prompted and need to be handled as follows.

1. Querying invalid objects for a 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

Specifies that an invalid object is queried for a user.

Sql> SELECT * from dba_objects where status<> ' VALID ' and owner= ' HKJJW ';

2. Manually perform the compilation or

ALTER FUNCTION HKJJW. Func_return_py COMPILE;
ALTER VIEW HKJJW. Monitoring_flow_parallel_view COMPILE;
ALTER VIEW HKJJW. TEST COMPILE;

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

If the package body
Alter package dbms_cube compile body;

3. Automatically generate compiled invalid object SQL1) count the number of invalid objects for 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 compiled invalid object sql

sql> select ' ALTER ' | | object_type | |  "| | OWNER | | '. ' | | object_name | | ' COMPILE; ' from dba_objects where status = ' INVALID ' and object_type in (' Package ', ' Package BODY ', ' FUNCTION ', ' PROCEDURE ', ' 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;
Slightly...
Manually perform the compilation execution directly by copying the above SQL statement.

4. You can also manually compile under Oracle users in the following ways

# su-oracle
$ sqlplus/as SYSDBA
sql> @ $ORACLE _home/rdbms/admin/utlrp.sql

This article is from the "Koumm Linux Technology blog" blog, be sure to keep this source http://koumm.blog.51cto.com/703525/1582548

Oracle compiled user Invalid object

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.