Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

As the title says, is it possible to retrieve the number of columns in a view dynamically?

I'm learning Oracle 11gR2 and I'm checking out the V$ views. One of the things I want to do is output them to file (for various reasons I am unable to spool the document) so I was going to output using java, however I need to know how many columns are in each view - a daunting task with 536 views, and now I'm curious to if I can do this dynamically.

I tried using user_tab_columns but it returns 0 for views.

Note: performance is not key, this is a learning exercise for me, so doing it correctly is more important than doing it quickly (if its even possible)

share|improve this question
up vote 1 down vote accepted

To spool the columns of the V$ views in Oracle you can use:

select owner, table_name, column_name, data_type, column_id
from dba_tab_columns c
where table_name = 'V_$PARAMETER'
order by owner, table_name, column_id
share|improve this answer
That works well for some of the views, however V$SEGSTAT returns 0 cols, any idea why? – Robert H Sep 11 '13 at 20:05
use V_$SEGSTAT v$segstat is only a synonym for V_$SEGSTAT – ik_zelf Sep 11 '13 at 20:17
Interesting, I was under the impression that the V_ views should be avoided in favor of V$. Does this convention hold true for the naming of all the views? (i.e. I want to run the code once, not run, correct any errors and run again etc) – Robert H Sep 11 '13 at 20:31
Answered my own question - yes. Thanks for the help, the final query I used is select count(column_name) from dba_tab_columns c WHERE table_name LIKE 'V_$HANG_SESSION_INFO', of course the view is replaced as required.... – Robert H Sep 11 '13 at 20:47

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.