v$sql.object_status – what the hell is this?

select count(*) from v$sql where sql_text like '<part_of_sql>%';

This is the query I often use to verify if the cursor is invaliated by dependency chain.

Suddenly I realized that I should use more elegant way on 11g. Simple test case will show what I mean.

1. Create objects. View v1, Procedure proc1 and one SQL cursor are dependent on table t1.

create table t1(c1 int, c2 int);

create or replace view v1 as
select c1, c2 from t1
;

create or replace procedure proc1
is
begin
  update t1 set c1 = c1 + 1;
end;
/

select /* sql */ c1, c2 from t1 where c1 = 1;

2. Load them onto the shared pool.

select /* sql */ c1, c2 from t1 where c1 = 1;

select * from v1;

exec proc1;

3. Now I compare the status of each object on 10g and 11g.

select object_name, status

from user_objects where object_name in ('V1', 'PROC1');

-- 10g

OBJECT_NAME          STATUS
-------------------- --------------------
PROC1                VALID
V1                   VALID

-- 11g

OBJECT_NAME          STATUS             
-------------------- --------------------
PROC1                VALID              
V1                   VALID

select count(*)
from v$sql where sql_text like 'select /* sql */ c1, c2 from t1 where c1 = 1%';

-- 10g
  COUNT(*)
----------
         1

-- 11g
  COUNT(*)
----------
         1

select sql_id, object_status
from v$sql where sql_text like 'select /* sql */ c1, c2 from t1 where c1 = 1%';

-- 10g
SQL_ID                                  OBJECT_STATUS
--------------------------------------- ---------------
87nk78p1sdwn9                           VALID

-- 11g

SQL_ID                                  OBJECT_STATUS

--------------------------------------- ------------------

87nk78p1sdwn9                           VALID

4. What happens if I alter the base table?

alter table t1 add c3 int;

select object_name, status

from user_objects where object_name in ('V1', 'PROC1');

-- 10g

OBJECT_NAME          STATUS
-------------------- ---------
PROC1                INVALID
V1                   INVALID

-- 11g
OBJECT_NAME          STATUS             
-------------------- --------------------
PROC1                VALID    <-- Note here!       
V1                   VALID    <-- Note here!

On 11g, View v1 and Procedure proc is NOT invalidated! This is a new feature of Oracle 11g which is sometimes called finer dependency management. Very cool.

But how about the cursor?  Is SQL cursor also finely managed?

select count(*)
from v$sql where sql_text like 'select /* sql */ c1, c2 from t1 where c1 = 1%';

-- 10g
  COUNT(*)
----------
         0

-- 11g
  COUNT(*)
----------
         1    <-- Note here!

At first, It really looks so. Oracle didn’t invalidate the cursor on 11g. Really? See following result.

select sql_id, object_status
from v$sql where sql_text like 'select /* sql */ c1, c2 from t1 where c1 = 1%';

-- 10g
no rows selected

-- 11g

SQL_ID                                  OBJECT_STATUS    
--------------------------------------- ------------------
87nk78p1sdwn9                           INVALID_UNAUTH    <-- Note here!

Oops. There is a big difference between 11g and lower versions.

• On lower versions, the v$sql view does not show the invalidated cursor. So my old verifier(select count(*) ..) worked with no problem.
• But on 11g, the v$sql does show the invalidated cursor with the object_status column value “INVALID_UNAUTH”.

I looked up the exact meaning of v$sql.object_status but with no satisfaction. But one thing is clear.

Always check v$sql.object_status on 11g!