Some doubt abort v$sql.object_status

From: louis <ylouis83_at_gmail.com>
Date: Fri, 6 Jan 2012 13:43:29 +0800
Message-ID: <CAARjh8=OK=UrwAEGM6rwYz9zGD9ZiSWrpC6Nx+UOVX1TwZ+hAg_at_mail.gmail.com>

As we know 11g new feature -*finer dependency management*. ,In Oracle 11g  if we do DDL command on tables ,v$sql.object_staus will change,let's do a test:
first

 SQL cursor are dependent on table t1.

SQL> desc t1;
 Name Null? Type

• --------

  ---

  C1 NUMBER(38) C2 NUMBER(38)

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

and then I do a ddl:

alter table t1 add c3 int;

*

*

*

*

SQL> desc t1;
 Name Null? Type

• --------

  ---

  C1 NUMBER(38) C2 NUMBER(38) C3 NUMBER(38)

SQL> in 10g:

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

no rows selected

in 11g:

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

SQL_ID OBJECT_STATUS

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

87nk78p1sdwn9 INVALID_UNAUTH

SQL> Object_status changed to *'INVALID_UNAUTH'*, and then I do a 10053 trace and run this SQL again:

SQL> Alter session set events '10053 trace name context forever,level 2';

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

If event 10053 is set, this event will be triggered in a hard parse (not a soft parse).

the trace show that oracle do a hard parse like:

BASE STATISTICAL INFORMATION

---

Table Stats::
  Table: T1 Alias: T1 (NOT ANALYZED)

Access path analysis for T1

---

SINGLE TABLE ACCESS PATH
....

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

SQL_ID OBJECT_STATUS

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

c8f88bcmhcqgr VALID
87nk78p1sdwn9 INVALID_UNAUTH

My question is what is 'INVALID_UNAUTH' meaning ? and if I Execution a some SQL oracle will create a new cursor ,It seems that there are no difference
between 11g and 10g and how can oracle be benefited from the new feature in 11g?

*
*
*
*
*
*

*
*

*
*

*
*

*
*

Phone: +86 13918046970

Email & Gtalk: ylouis83_at_gmail.com

Personal Blog: http://www.vmcd.org

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jan 05 2012 - 23:43:29 CST