Re: How does SQL*Plus's DESCRIBE command work internally?

I used to think (since version 4 :-), that there is no SQL involved in the DESCRIBE statement (which belongs to SQLPLUS and not to SQL). IMHO it simply translates to the OCI-call ODESCR(). But maybe this is all different now that ODESCR() has been replaced by OCIDescribeAny(0.

rob van lopik

"Thomas Kyte" <tkyte_at_oracle.com> wrote in message news:aa2ecr01uas_at_drn.newsguy.com...
> In article <3CC48036.D9A40B4E_at_peasland.com>, Brian says...
> >
> >> Take a look at the data dictionary views xxx_arguments.
> >>
> >> I'm not sure if that is where DESCRIBE is going ... but it certainly
could.
> >
> >What's interesting is that if you set SQL_TRACE=true, then do a describe
> >on any object, you won't see any recursive SQL being generated on your
> >behalf.......
> >
> >
> >Cheers,
> >Brian
>
> ahh, but you will sometimes. Depends on whether the object is in the
dictionary
> cache already or not (might help to answer the question "where does it
go...")
>
> Consider:
>
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> alter system flush shared_pool;
> System altered.
>
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> alter session set sql_trace=true;
> Session altered.
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> desc scott.emp;
> ....
>
>
> from the tkprof then:
>
> alter session set sql_trace=true
>
>
> select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1
> from
> obj$ where owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or
> remoteowner is null and :4 is null)and(linkname=:5 or linkname is null
and
> :5 is null)and(subname=:6 or subname is null and :6 is null)
>
>
> select ts#,file#,block#,nvl(bobj#,0),nvl(tab#,0),intcols,nvl(clucols,0),
> audit$,flags,pctfree$,pctused$,initrans,maxtrans,rowcnt,blkcnt,empcnt,
> avgspc,chncnt,avgrln,analyzetime,

samplesize,cols,property,nvl(degree,1),
> nvl(instances,1),avgspc_flb,flbcnt,kernelcols,nvl(trigflag,
0),nvl(spare1,0)
> ,nvl(spare2,0),spare4,nvl(spare3,0)
> from
> tab$ where obj#=:1
>
> select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,
> NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0)
> from
> seg$ where ts#=:1 and file#=:2 and block#=:3
>
> select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,
i.property,
> i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,
i.lblkkey,
> i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,
> nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.pctthres$,256),
> i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0),
> nvl(i.spare1,i.intcols),i.spare4,spare2,decode(i.pctthres$,null,null,
> mod(trunc(i.pctthres$/256),256))
> from
> ind$ i, (select enabled, min(cols) unicols,
min(to_number(bitand(defer,1)))
> deferrable#, min(to_number(bitand(defer,4))) valid# from cdef$ where
obj#=
> :1 and enabled > 1 group by enabled) c where i.obj#=c.enabled(+) and
i.bo#=
> :1
>
>
> select pos#,intcol#,col#,spare1
> from
> icol$ where obj#=:1
>
>
> select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,
>

nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,
>

scale,183,scale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,
> rowid,col#,property, charsetid,charsetform,spare1,spare2
> from
> col$ where obj#=:1 order by intcol#
>
> ....
>
> 16 statements in there. Do this:
>
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> alter session set sql_trace=true;
> Session altered.
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> desc scott.emp;
> ....
>
>
> again and nothing in the tkprof (until you flush it again)
>
> --
> Thomas Kyte (tkyte@oracle.com) http://asktom.oracle.com/
> Expert one on one Oracle, programming techniques and solutions for Oracle.
> http://www.amazon.com/exec/obidos/ASIN/1861004826/
> Opinions are mine and do not necessarily reflect those of Oracle Corp
>
Received on Tue Apr 23 2002 - 09:31:03 CDT