1

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)

1 Answer 1

1

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
4
  • That works well for some of the views, however V$SEGSTAT returns 0 cols, any idea why?
    – Robert H
    Sep 11, 2013 at 20:05
  • use V_$SEGSTAT v$segstat is only a synonym for V_$SEGSTAT
    – user953
    Sep 11, 2013 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, 2013 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, 2013 at 20:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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