Some doubt abort v$sql.object_status
- From: louis <ylouis83@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 6 Jan 2012 13:43:29 +0800
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@xxxxxxxxx
Personal Blog: http://www.vmcd.org
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » Some doubt abort v$sql.object_status - louis
