Thinking Out Loud

February 6, 2013

DBA_ERRORS

Filed under: oracle — mdinh @ 12:17 pm

Learn something new every day, from the twitter feed, DBA – Default Blame Acceptor.

Another is DBA_ERRORS

Here is an example:

SQL> alter procedure bad_proc compile;
Warning: Procedure altered with compilation errors.
SQL> show error;
Errors for PROCEDURE BAD_PROC:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/1      PL/SQL: SQL Statement ignored
4/8      PL/SQL: ORA-00904: "E": invalid identifier
SQL>
SQL> SELECT DISTINCT owner||'.'||name||'|'||type,text
  2  FROM dba_errors
  3  WHERE owner='SCOTT'
  4  AND text LIKE '%ORA-%'
  5  AND name='BAD_PROC'
  6  ORDER BY 1
  7  ;
OWNER||'.'||NAME||'|'||TYPE
--------------------------------------------------------------------------
TEXT
--------------------------------------------------------------------------------
SCOTT.BAD_PROC|PROCEDURE
PL/SQL: ORA-00904: "E": invalid identifier
SQL>

Thanks to Luke from Pythian for raising my curiosity.

It’s amazing the number of information you can find when you go digging.

For example: DBMS_UTILITY.COMPILE_SCHEMA

10.2 – procedure compiles all procedures, functions, packages, and triggers in the specified schema
11.2 – procedure compiles all procedures, functions, packages, views and triggers in the specified schema

Let’s test this for 11.2.

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> create or replace force view tv as select * from t;
Warning: View created with compilation errors.

SQL> SELECT DISTINCT owner||'.'||name||'|'||type,text
2 FROM dba_errors
3 WHERE owner='SCOTT'
4 AND text LIKE '%ORA-%'
5 AND name='TV'
6 ORDER BY 1
7 ;

OWNER||'.'||NAME||'|'||TYPE
--------------------------------------------------------------------------
TEXT
--------------------------------------------------------------------------------
SCOTT.TV|VIEW
ORA-00942: table or view does not exist

SQL> create table t as select * from emp;
Table created.

SQL> SELECT DISTINCT owner||'.'||name||'|'||type,text
2 FROM dba_errors
3 WHERE owner='SCOTT'
4 AND text LIKE '%ORA-%'
5 AND name='TV'
6 ORDER BY 1
7 ;

OWNER||'.'||NAME||'|'||TYPE
--------------------------------------------------------------------------
TEXT
--------------------------------------------------------------------------------
SCOTT.TV|VIEW
ORA-00942: table or view does not exist

SQL> execute DBMS_UTILITY.COMPILE_SCHEMA (schema=>user,compile_all=>false);
PL/SQL procedure successfully completed.

SQL> SELECT DISTINCT owner||'.'||name||'|'||type,text
2 FROM dba_errors
3 WHERE owner='SCOTT'
4 AND text LIKE '%ORA-%'
5 AND name='TV'
6 ORDER BY 1
7 ;

no rows selected
SQL>

Lastly, be aware of the BUG:
DBMS_UTILITY.COMPILE_SCHEMA Always Turns The DEBUG Flag to TRUE [ID 1208971.1]
PL/SQL – Version: 10.2.0.4 to 11.1.0.7 – Release: 10.2 to 11.1

About these ads

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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

The Rubric Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 314 other followers

%d bloggers like this: