Re: Where is v$sqltext

Allen Hubenak wrote:
>
> In article <4m8r9e$460_at_cougar.vut.edu.au>,
> Leng Kaing <leng_at_cougar.vut.edu.au> wrote:
> >Env: Oracle 7.1.3, VMS 6.1
> >
> >Found something interesting: I can see v$sqltext in sql*dba when
> >connected as SYS or SYSTEM. But there is no such object in sql*plus
> >when connected as SYS or SYSTEM. I've searched the dictionary,
> >dba_segments, dba_objects, all_segments, all_objects but can't find
> >the thing. So have you got it in you 7.1.3 database? Where did it come
> >from?
> >
> >TIA,
> >Leng.
>
> Try this:
>
> Select * from sys.v_$sqltext;
>
> I don't know why they use the v_$ syntax for these table names, like
> v_$process and v_$session.
>
> Later...
>
> Allen Hubenak

Apparently this is a minor slip-up they've made in building the data dictionary. Start looking at the SQL scripts provided in $ORACLE_HOME/rdbms/admin. Look particularly at catalog.sql (right near the top) and utlmontr.sql script. The catalog.sql (followed by catproc.sql) are the main proponents in building your database. The catalog.sql misses the v_$sqltext view when they build the FAMILY "FIXED" VIRTUAL VIEWS. Originally came the dynamic performance/runtime v$views (without the underscores). Then they added v_$views pointing to them. And v_$sqltext got missed out !!!

We run the following the Korn shell after every database build to set things right: Then anybody having role monitorer granted to them will have access to v_$sqltext, even from SQL*Plus.

#!/bin/ksh
sqldba mode=line <<EOF
connect internal;
create or replace view v_\$sqltext as select * from v\$sqltext; drop public synonym v\$sqltext;
create public synonym v\$sqltext for v_\$sqltext; grant select on v_\$sqltext to monitorer; exit
EOF Hope this helped.

Rajiv Tandon Received on Thu May 02 1996 - 00:00:00 CEST