Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2003
    Posts
    35

    Unanswered: Why my function is always marked invalid?

    Hi Anyone experience this?

    I have function,procedure and package which is always get back to invalid again.
    When i re-compile the objects is successful and the object is valid however after few minutes, it is go back again to invalid.

    Our server is 8.1.7 proffesional edition in NT O/S.


    Any help is appreciated.

    Mike.

  2. #2
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Cool My problem is simular

    Hey Gang, I created a test database on my machine that had some invalid packages on it. After confirming that sysdba own them and running the necessary scripts to re-compile. I get 3 packages that are still invalid. Is this because the other database reside on servers and this test database reside on my machine.
    Schema
    Package Body
    Created
    Last Modified
    Status

    WKSYS
    WK_CRW
    04-Sep-2001
    09-Sep-2003
    Invalid

    RMAN
    DBMS_RCVCAT
    04-Sep-2001
    03-Sep-2003
    Invalid

    RMAN
    DBMS_RCVMAN
    04-Sep-2001
    03-Sep-2003
    Invalid


    These are the packages that are invalid. What affect will this have on Recovery?

  3. #3
    Join Date
    Sep 2003
    Posts
    6
    Hi

    To get a quick count of the number of existing invalids (if any), use the
    following select statement:

    SELECT COUNT(*)
    FROM DBA_OBJECTS
    WHERE STATUS = 'INVALID';

    For a more detailed query, use the following script:

    SELECT OWNER, OBJECT_TYPE, COUNT(*)
    FROM DBA_OBJECTS
    WHERE STATUS = 'INVALID'
    GROUP BY OWNER, OBJECT_TYPE;

    To recompile an individual object, connect to SQL*PLUS as the owner of the
    object (generally apps). Use one of the following depending on the object
    type:

    SQL> alter package <package_name> compile; (package
    specification)
    SQL> alter package <package_name> compile body; (package
    body)
    SQL> alter view <view_name> compile; (view)

    If the object compiles with warnings, use either of the following to see
    the errors that caused the warnings:

    SQL> show errors
    or
    SQL> select * from user_errors where name = '<OBJECT_NAME>';

    Another way to correct invalid objects is to run the adadmin utility as
    follows:

    UNIX OPERATING PLATFORM
    -----------------------
    a. Log in as applmgr: <applmgr username>/<applmgr password>
    b. Start the utility from the Unix prompt with this command:

    $ adadmin

    The utility will then ask you a series of questions.

    c. Under the Maintain Applications Database Objects Menu,
    select Compile APPS schema(s)

    This task spawns parallel workers to compile invalid database objects in
    your APPS schema(s). It uses the same parallel phases as AutoInstall.

    WINDOWS NT OPERATING PLATFORM
    -----------------------------
    a. Log in as applmgr: <applmgr username>/<applmgr password>
    b. Start the utility from the Dos prompt with this command:

    > adadmin

    c. Under the Maintain Applications Database Objects Menu,
    select Compile APPS schema(s)

    Within Applications, there is a script to compile INVALID objects - called
    ADCOMPSC.pls

    Arguments for ADCOMPSC.pls:
    1 - Schema to run in
    2 - Password for schema
    3 - Check errors for objects starting with #3

    NOTE: The order in which to compile Invalid Objects in schemas is
    SYS, SYSTEM, APPS and then all others. APPS_DDL and APPS_ARRAY_DDL
    should exist in all schema's. In case of an ORA-1555 error
    while running adcompsc.pls, restart the script.

    The script can be run as follows:

    cd $AD_TOP/sql
    sqlplus @adcompsc.pls SCHEMA_NAME SCHEMA_PASSWORD %

    Example: SQL> @adcompsc.pls apps apps %

    After the script completes, check for invalid objects again. If the number
    has decreased, but invalid objects still exist, run adcompsc.pls again.
    Keep running adcompsc.pls until number of invalid objects stops decreasing.

    If there are any objects still left INVALID, verify them by using derrchk.
    sql
    to record the remaining INVALID objects. Aderrchk.sql use the same syntax
    as
    adcompsc.pls. This script is also supplied with the Applications.
    Send the aderrchk.sql to a file using the spool <file> command in
    sqlplus.

    e.g. sqlplus x/y @aderrchk.sql SCHEMA_NAME SCHEMA_PASSWORD %

    For objects will not compile, try the following:

    select text
    from user_source
    where name = 'OBJECTNAME'
    and text like '%Header%';

    This script will provide the sql that creates the packages > recreate
    the
    packages. SQL>@packageheader SQL>@packagebody
    If recreating the package does not make the package valid, analyze the
    user_errors table to determine the cause of the invalid package.

    select text
    from user_errors
    where name = 'PACKAGENAME';

    hope this will help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •