Posted by Andy Hassall on 07/06/07 12:20

On Thu, 05 Jul 2007 23:45:10 GMT, Mladen Gogala
<mgogala.SPAM_ME.NOT@verizon.net> wrote:

>On Mon, 02 Jul 2007 19:31:52 +0100, Andy Hassall wrote:
>
>>>It does, but it will not work for views or V$ tables.
>>
>> Um, yes it will. Views' columns appear in USER_TAB_COLUMNS.
>
>It does work for views, but not for V$ tables:
>SQL> select column_name from dba_tab_columns
> 2 where table_name='V$PROCESS';
>
>no rows selected

That's because V$PROCESS is a synonym, not a table or view. Dereference it
first.

SQL> select table_owner, table_name
2 from dba_synonyms
3 where synonym_name = 'V$PROCESS'
4 and owner = 'PUBLIC';

TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------
SYS V_$PROCESS

SQL> select column_name from dba_tab_columns
2 where owner = 'SYS'
3 and table_name = 'V_$PROCESS';

COLUMN_NAME
------------------------------
PGA_USED_MEM
PGA_ALLOC_MEM
PGA_FREEABLE_MEM
PGA_MAX_MEM
ADDR
PID
SPID
USERNAME
SERIAL#
TERMINAL
PROGRAM
TRACEID
BACKGROUND
LATCHWAIT
LATCHSPIN

15 rows selected


--
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

[Back to original message]