Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Displaying and fixing compilation errors on a SQLTXPLAIN package

leave a comment »

If you are installing SQLTXPLAIN and for some reason you get a PL/SQL compilation error in a SQLT log like the one below, chances are the SHOW ERRORS command on SQL*Plus won’t show the actual error. This is because the installation connects as SYS and the packages are owned by SQLTXADMIN. In most cases the cause of the error is missing a GRANT or a SYNONYM in libraries called by SQLT like DBMS_METADATA or UTL_FILE.

... creating package body for SQLT$R
Warning: Package Body created with compilation errors.
No errors.
...
SELECT column_value libraries FROM TABLE(SQLTXADMIN.sqlt$r.libraries_versions)
ERROR at line 1:
ORA-04063: package body "SQLTXADMIN.SQLT$R" has errors

How to display PL/SQL package compilation error on SQLTXPLAIN

You may need to unlock the schema owner of SQLT packages and compile the invalid object connecting as this SQLTXADMIN account, then lock it back. By following steps below you will get to see the actual compile error. Then proceed to fix it and recompile the invalid package body. If it is a missing GRANT/SYNONYM you may want to create the GRANT EXECUTE of the SYS library to SQLTXADMIN then create a SYNONYM with same name (not a PUBLIC SYNONYM). Ex: GRANT EXECUTE ON SYS.DBMS_METADATA TO SQLTXADMIN; CREATE SYNONYM SQLTXADMIN.DBMS_METADATA FOR SYS.DBMS_METADATA;

$ cd sqlt/install
$ sqlplus / AS SYSDBA
--
SQL> SELECT object_name, object_type FROM dba_objects WHERE owner = 'SQLTXADMIN' AND object_type LIKE 'PACKAGE%' AND status = 'INVALID';
SQL> START sqcommon1.sql
SQL> GRANT CREATE SESSION TO sqltxadmin;
SQL> ALTER USER sqltxadmin IDENTIFIED BY &&password. ACCOUNT UNLOCK;
SQL> CONN sqltxadmin/&&password.;
--
-- compile invalid packages as per query output above
--
SQL> ALTER PACKAGE sqlt$m COMPILE;
SQL> ALTER PACKAGE sqlt$r COMPILE;
--
-- fix root cause of error
--
SQL> CONN / AS SYSDBA
SQL> REVOKE CREATE SESSION FROM sqltxadmin;
SQL> ALTER USER sqltxadmin PASSWORD EXPIRE ACCOUNT LOCK;

Written by Carlos Sierra

June 19, 2013 at 11:50 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,572 other followers

%d bloggers like this: