Re: Where is v$sqltext

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?

The answer is: NONE OF THE ABOVE! V$ 'objects' are not database objects per se, but are views on 'fixed tables' (the X$ tables). The ${ORACLE_HOME}/rdbms/admin/catalog.sql script, which gets run when you create your database, does not create a public synonym for V$SQLTEXT for some reason (maybe a security risk). So do this:

sqlplus sys/change_on_install
create view v_$sqltext as select * from v$sqltext; create public synonym v$sqltext for v_$sqltext; grant select on v_$sqltext to system with grant option;

--
Bob Poortinga (mailto:bobp_at_tsc.com, http://www.tsc.com/~bobp/)
Technology Service Corp. (http://www.tsc.com/)
Bloomington, Indiana  USA