Custom Script to Identify Invalid Packages

Identifying Invalid Objects

SELECT owner,
       object_type,
       object_name,
       status
FROM   dba_objects
WHERE  status = 'INVALID'
ORDER BY owner, object_type, object_name;

Manual Approach

ALTER PACKAGE my_package COMPILE;
ALTER PACKAGE my_package COMPILE BODY;
ALTER PROCEDURE my_procedure COMPILE;
ALTER FUNCTION my_function COMPILE;
ALTER TRIGGER my_trigger COMPILE;
ALTER VIEW my_view COMPILE;

DBMS_DDL

EXEC DBMS_DDL.alter_compile(‘PACKAGE’, ‘MY_SCHEMA’, ‘MY_PACKAGE’);

EXEC DBMS_DDL.alter_compile(‘PACKAGE BODY’,’MY_SCHEMA’,’MY_PACKAGE’);

EXEC DBMS_DDL.alter_compile(‘PROCEDURE’,’MY_SCHEMA’,’MY_PROCEDURE’);

EXEC DBMS_DDL.alter_compile(‘FUNCTION’, ‘MY_SCHEMA’, ‘MY_FUNCTION’);

EXEC DBMS_DDL.alter_compile(‘TRIGGER’, ‘MY_SCHEMA’, ‘MY_TRIGGER’);

DBMS_UTILITY

EXEC DBMS_UTILITY.compile_schema(schema => 'SCOTT');

UTL_RECOMP

The UTL_RECOMP package contains two procedures used to recompile invalid objects.

 

Parallel execution is perfomed using the job queue. All existing jobs are marked as disabled until the operation is complete.

The package must be run from SQL*Plus as the SYS user, or another user with SYSDBA.

The package expects the STANDARD, DBMS_STANDARD, DBMS_JOB and  DBMS_RANDOM to be present and valid.

— Schema level.

EXEC UTL_RECOMP.recomp_serial(‘SCOTT’);

EXEC UTL_RECOMP.recomp_parallel(4, ‘SCOTT’);

— Database level.

EXEC UTL_RECOMP.recomp_parallel(4);

— Using job_queue_processes value.

EXEC UTL_RECOMP.recomp_parallel();

EXEC UTL_RECOMP.recomp_parallel(NULL, ‘SCOTT’);

 

Custom Script for Compiling Invalid Packages in File

CREATE OR REPLACE PROCEDURE COMPILE_PACKAGES

IS

outfile   UTL_FILE.file_type;

stmt      VARCHAR2 (150);

BEGIN

  outfile := UTL_FILE.fopen (‘PLSQL_FILE’, ‘INVALID_OBJECTS.txt’, ‘w’);

  FOR cur_rec IN (SELECT owner,

                         object_name,

                         object_type,

                         DECODE(object_type, ‘PACKAGE’, 1,

                                             ‘PACKAGE BODY’, 2, 2) AS                    recompile_order

                  FROM   dba_objects

                  WHERE  object_type IN (‘PACKAGE’, ‘PACKAGE BODY’)

                  AND    status != ‘VALID’

                  ORDER BY 4)

  LOOP

    BEGIN

      IF cur_rec.object_type = ‘PACKAGE’ THEN

        stmt := ‘ALTER ‘ || cur_rec.object_type || ‘ "’ || cur_rec.owner || ‘"."’ || cur_rec.object_name || ‘" COMPILE’;

        dbms_output.PUT_LINE ( ‘Package statement: ‘||stmt);

        –EXECUTE IMMEDIATE stmt;

      ElSE

        stmt:= ‘ALTER PACKAGE "’ || cur_rec.owner ||

            ‘"."’ || cur_rec.object_name || ‘" COMPILE BODY’;

        –EXECUTE IMMEDIATE stmt;

        dbms_output.PUT_LINE ( ‘Package statement: ‘||stmt);

      END IF;

      UTL_FILE.PUT_LINE(outfile, stmt);

    EXCEPTION

      WHEN OTHERS THEN

        DBMS_OUTPUT.put_line(cur_rec.object_type || ‘ : ‘ || cur_rec.owner ||

                             ‘ : ‘ || cur_rec.object_name);

    END;

  END LOOP;

  UTL_FILE.FCLOSE(outfile);

END COMPILE_PACKAGES;

Main Source of this article: Oracle Base link on Recompiling Objects

• DBMS_UTILITY.compile_schema
• UTL_RECOMP

Oracle Supplied Packages

DBMS_SQL

Using DBMS_SQL, one can write stored procedures that use dynamic SQL. It can issue data definition language statements in PLSQL

 

CREATE OR REPLACE PROCEDURE delete_all_rows
  (tab_name IN VARCHAR2, rows_del OUT NUMBER)
  IS
  cursor_name : = DBMS_SQL.OPEN_CURSOR;

  DBMS_SQL.PARSE ( cursor_name, ‘DELETE FROM ‘ ||tab_name,    DBMS_SQL.NATIVE);
  rows_del := DBMS_SQL.EXECUTE(cursor_name);
  DBMS_SQL.CLOSE_CURSOR(cursor_name);
END;

 

DBMS_DDL

DBMS_DDL provides access to some SQL DDL statements from stored procedures. It includes ALTER_COMPILE and ANALYZE_OBJECT procedures. 

DBMS_DDL.ALERT_COMPILE  (‘PROCEDURE’ ,’USER’, ‘OBJECT_NAME’ );   #DDL

DBMS_DDL.ANALYZE_OBJECT(‘ OBJECT_TYPE’, ‘USER’, ‘NAME’ , ‘COMPUTE’);    

DBMS_JOBS and DBMS_SCHEDULER

It enables scheduling and execution of PL/SQL programs:

Submitting jobs
Executing jobs
Removing and Suspending jobs

begin
--job:=101;   --job created to execute for every minute.
sys.dbms_job.submit(job => job,
what => 'del_test_info_p;',
next_date => TRUNC(SYSDATE, 'HH')+ 1/24,
interval => 'TRUNC(SYSDATE,''MI'')+1/(24/60));

commit;
end;

BEGIN

  DBMS_SCHEDULER.create_job (

    job_name        => ‘dummy_job’,

    job_type        => ‘PLSQL_BLOCK’,

    job_action      => ‘BEGIN NULL; /* Do Nothing */ END;’,

    start_date      => SYSTIMESTAMP,

    repeat_interval => ‘SYSTIMESTAMP + 1 /* 1 Day */’);

END;

DBMS_OUTPUTLINE

DBMS_OUTPUT.PUT_LINE(‘I got here:’||:new.col||’ is the new value’);

UTL_FILE

The UTL_FILE package provides text file I/O  from within PL/SQL. In the past, accessible directories for the UTL_FILE functions were specified in the initialization file using the UTL_FILE_DIR parameter. However, UTL_FILE_DIR access is not recommended. It is recommended that you use the CREATE DIRECTORY feature, which replaces UTL_FILE_DIR.  CREATE DIRECTORY privilege is granted only to SYS and SYSTEM by default.

    

declare      
       outfile         UTL_FILE.file_type;

      locdataline   VARCHAR2 (150);

BEGIN

     outfile := UTL_FILE.fopen (‘/u99/sas_data’, ‘VehData.dat’, ‘w’);

      FOR v1 IN veh_data

      LOOP

         BEGIN

            locdataline :=    v1.a  || ‘|’   || v1.b || ‘|’   || v1.c || ‘|’   || v1.d   || ‘|’;

            UTL_FILE.put_line (outfile, locdataline);

         END;

      END LOOP;

      UTL_FILE.fclose (outfile);

END

UTL_HTTP

The UTL_HTTP package makes Hypertext Transfer Protocol (HTTP) callouts from SQL and PL/SQL. You can use it to access data on the Internet over HTTP.

UTL_TCP

With the UTL_TCP package and its procedures and functions, PL/SQL applications can communicate with external TCP/IP-based servers using TCP/IP. Because many Internet application protocols are based on TCP/IP, this package is useful to PL/SQL applications that use Internet protocols and e-mail.

DBMS_STATS

Below is a sample execution of dbms_stats with the options clause.

exec dbms_stats.gather_schema_stats( –

ownname          => ‘SCOTT’, –

options          => ‘GATHER AUTO’, –

estimate_percent => dbms_stats.auto_sample_size, –

method_opt       => ‘for all columns size repeat’, –

cascade          => true, –

degree           => 15 –

)

Here is a representative example of invoking dbms_stats in 10g:

DBMS_STATS.gather_schema_stats(

ownname=>’<schema>’,

estimate_percent=>dbms_stats.auto_sample_size

cascade=>TRUE,

method_opt=>’FOR ALL COLUMNS SIZE AUTO’)

 

OTHER USEFUL PACKAGES

DBMS_ALERT

DBMS_DESCRIBE

DBMS_LOCK

DBMS_SESSION

DBMS_APPLICATION_INFO

DBMS_TRANSACTION

DBMS_SHARED_POOL

DBMS_UTILITY

DBMS_LOGMNR

DBMS_PROFILER

DBMS_REPAIR

DBMS_SPACE

DBMS_STATS