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 >

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.

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>

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.