Thursday, March 28, 2013

DBMS_SESSION.SET_SQL_TRACE

This was tested on Oracle 11.1. In previous posts, I have looked at enabling tracing. You can start and stop a trace of your current session like this:

SQL> alter session set sql_trace = true;
 
Session altered.
 
SQL> alter session set sql_trace = false;
 
Session altered.
 
SQL>

You can start and stop tracing an entire instance as follows:

SQL> alter system set sql_trace = true;
 
System altered.
 
SQL> alter system set sql_trace = false;
 
System altered.
 
SQL>

You can also start and stop tracing an instance by setting the sql_trace initialization parameter.
 
Going through some old notes, I saw a different way of tracing your current session and decided to try it out (the first statement is just to make it easy to see the trace file):

SQL> l
  1* alter session set tracefile_identifier = 'ANDREW'
SQL> /
 
Session altered.
 
SQL> exec dbms_session.set_sql_trace(true);
 
PL/SQL procedure successfully completed.
 
SQL> select * from dual
  2  /
 
D
-
X
 
SQL> exec dbms_session.set_sql_trace(false);
 
PL/SQL procedure successfully completed.
 
SQL>

Then I looked in the directory specified by the user_dump_dest parameter and saw the associated trace file below:

Solaris > ls *ANDREW.trc
PQEDPT1_ora_27267_ANDREW.trc
Solaris >

Wednesday, March 27, 2013

V$SQL_BIND_CAPTURE

This simple example, tested on Oracle 11.2, shows how to use V$SQL_BIND_CAPTURE to see the value of bind variables used in a WHERE clause. First I created a table called TAB1 with 1 row of data: 

SQL> create table tab1 (col1 varchar2(10))
  2  /
 
Table created.
 
SQL> insert into tab1 values ('ANDREW')
  2  /
 
1 row created.
 
SQL> select * from tab1
  2  /
 
COL1
----------
ANDREW
 
SQL>

Then I created a bind variable called VAR1. I used this to select the row of data with COL1 set to ANDREW then change it to BRIAN:

SQL> variable var1 varchar2(10)
SQL> begin
  2  select 'ANDREW' into :var1 from dual;
  3  update tab1 set col1 = 'BRIAN' where col1 = :var1;
  4  end;
  5  /
 
PL/SQL procedure successfully completed.
 
SQL>

I checked that the update had worked:

SQL> select * from tab1
  2  /
 
COL1
----------
BRIAN
 
SQL> 

Then I looked in V$SQL_BIND_CAPTURE to see the value of the bind variable like this: 

SQL> select
  2  a.sql_fulltext, b.name, b.value_string
  3  from v$sql a, v$sql_bind_capture b
  4  where a.sql_id = b.sql_id
  5  and a.child_address = b.child_address
  6  and sql_text like 'UPDATE TAB1%'
  7  /
 
SQL_FULLTEXT
-------------------------------------------------------
NAME
------------------------------
VALUE_STRING
-------------------------------------------------------
UPDATE TAB1 SET COL1 = 'BRIAN' WHERE COL1 = :B1
:B1
ANDREW
 
SQL> 

N.B. This only seems to work for bind variables in WHERE clauses. I also tried to use it to look at the bind variable in the SQL below: 

update tab1 set col1 = :var1 

In this case, the value of the bind variable was not shown.

Tuesday, March 26, 2013

V$RESERVED_WORDS

This was run on an Oracle 11.1 database. The v$reserved_words view lists Oracle's reserved words:

SQL> desc v$reserved_words
Name                       Null?    Type
-------------------------- -------- ------------------
KEYWORD                             VARCHAR2(30)
LENGTH                              NUMBER
RESERVED                            VARCHAR2(1)
RES_TYPE                            VARCHAR2(1)
RES_ATTR                            VARCHAR2(1)
RES_SEMI                            VARCHAR2(1)
DUPLICATE                           VARCHAR2(1)
 
SQL>

It has 1733 rows:

SQL> select count(*) from v$reserved_words
  2  /
 
  COUNT(*)
----------
      1733
 
SQL>

Of these, 2 contain null keywords:

SQL> select * from v$reserved_words
  2  where keyword is null
  3  /
 
KEYWORD                            LENGTH R R R R D
------------------------------ ---------- - - - - -
                                        0 Y N N N N
                                        0 N N N N N
 
SQL>

The remaining 1731 contain unique reserved words:

SQL> select count(distinct keyword)
  2  from v$reserved_words
  3  /
 
COUNT(DISTINCTKEYWORD)
----------------------
                  1731
 
SQL>

The length column records the length of the keyword. I'm not sure why you need this as you can get it yourself using the length function if you so desire:

SQL> select count(*) from v$reserved_words
  2  where keyword is not null
  3  and length = length(keyword)
  4  /
 
  COUNT(*)
----------
      1731
 
SQL>

The reserved column has values of Y and N:

SQL> select reserved, count(*)
  2  from v$reserved_words
  3  group by reserved
  4  /
 
R   COUNT(*)
- ----------
Y        100
N       1633
 
SQL>

If reserved is N, you can use the keyword as an identifier:

SQL> select keyword
  2  from v$reserved_words
  3  where keyword is not null
  4  and rownum < 11
  5  and reserved = 'N'
  6  /
 
KEYWORD
------------------------------
}
NOMONITORING
SYS_OP_VECXOR
GLOBAL
INFORMATIONAL
SEQUENCED
TIMES
SYS_OP_XPTHATG
PENDING
EXPLOSION
 
10 rows selected.
 
SQL> create table global (col1 number)
  2  /
 
Table created.
 
SQL>

Conversely, if reserved is Y, you cannot use the keyword as an identifier:

SQL> select keyword
  2  from v$reserved_words
  3  where keyword is not null
  4  and rownum < 11
  5  and reserved = 'Y'
  6  /
 
KEYWORD
------------------------------
FLOAT
TRIGGER
START
CONNECT
TABLE
DROP
ELSE
SYNONYM
DECIMAL
SIZE
 
10 rows selected.
 
SQL> create table decimal (col1 number)
  2  /
create table decimal (col1 number)
             *
ERROR at line 1:
ORA-00903: invalid table name
 
SQL>

Wednesday, March 13, 2013

ORA-04021

A colleague had another problem with a package compilation hanging in an Oracle 11.1.0.6.0 test database. I was able to reproduce it as follows: 

SQL> alter package srce.pk_pricing compile
  2  /
alter package srce.pk_pricing compile
*
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object
 
SQL>
 
There were locks on this package according to V$DB_OBJECT_CACHE but this time, flushing the shared pool made no difference and I found that I still could not compile the package:
 
SQL> l
  1  select type, locks
  2  from v$db_object_cache
  3  where owner = 'SRCE'
  4* and name = 'PK_PRICING'
SQL> /
 
TYPE                              LOCKS
---------------------------- ----------
PACKAGE BODY                          3
PACKAGE                               3
 
SQL> alter system flush shared_pool
  2  /
 
System altered.
 
SQL> select type, locks
  2  from v$db_object_cache
  3  where owner = 'SRCE'
  4  and name = 'PK_PRICING'
  5  /
 
TYPE                              LOCKS
---------------------------- ----------
PACKAGE BODY                          3
PACKAGE                               3
 
SQL>
 
I read somewhere that you could not compile a package if somebody was using it and you could find who it was by looking in V$ACCESS. I joined it with V$SESSION to pick up the SERIAL# as follows:
 
SQL> l
  1  select a.sid, serial#
  2  from v$access a, v$session b
  3  where a.sid = b.sid
  4* and object = 'PK_PRICING'
SQL> /
 
       SID    SERIAL#
---------- ----------
       187       7623
       225       3111
       179       6987
 
SQL>
 
I killed the first session:
 
SQL> alter system kill session '187,7623'
  2  /
 
System altered.
 
SQL>
 
… and the number of locks in V$DB_OBJECT_CACHE went down:
 
SQL> l
  1  select type, locks
  2  from v$db_object_cache
  3  where owner = 'SRCE'
  4* and name = 'PK_PRICING'
SQL> /
 
TYPE                              LOCKS
---------------------------- ----------
PACKAGE BODY                          2
PACKAGE                               2
 
SQL>
 
I killed the other two sessions:
 
SQL> alter system kill session '225,3111'
  2  /
 
System altered.
 
SQL> alter system kill session '179,6987'
  2  /
 
System altered.
 
SQL>
 
The number of locks in V$DB_OBJECT_CACHE went to zero:
 
SQL> l
  1  select type, locks
  2  from v$db_object_cache
  3  where owner = 'SRCE'
  4* and name = 'PK_PRICING'
SQL> /
 
TYPE                              LOCKS
---------------------------- ----------
PACKAGE BODY                          0
PACKAGE                               0
 
SQL>
 
… and I was able to compile the package in a second or two:
 
SQL> alter package srce.pk_pricing compile
  2  /
 
Package altered.
 
SQL>

If this does not work for you, click on the Older Post link below to see what I did the first time this happened.