CODEWITHSUNDEEP
sqloracle-database
Pha3drus
Pha3drus

Reputation: 169

Check for the existence of a column in v$database in Oracle

I am familiar how to check for a column name in a table or view by using the following query 

select count(*) 
from user_tab_columns 
where table_name = [name of view] 
and column_name = [name of column]

But, this doesn't work for a column that I am trying to check for in v$database.

Is there a workaround for this?

Upvotes: 0

Views: 661

Answers (2)

sstan
sstan

Reputation: 36523

v$database is actually a public synonym for the v_$database view owned by sys (checked in Oracle 12).

So to get column information:

  1. You can't use user_tab_columns, because that only considers tables/views owned by the current user. But the view you're interested in is owned by sys.
  2. You can't use the synonym name v$database. You have to use the actual view name: v_$database.

So you can use either dba_tab_columns or all_tab_columns like so:

select count(*) 
from dba_tab_columns 
where table_name = 'V_$DATABASE' 
and column_name = [name of column]

EDIT:

Another way that is less dependant on the actual view name in sys, which I guess could change between Oracle database versions, would be to join with dba_synonyms:

select count(*)
  from dba_synonyms s
  join dba_tab_columns c
    on c.owner = s.table_owner
   and c.table_name = s.table_name
   and c.column_name = [column name]
 where s.owner = 'PUBLIC'
   and s.synonym_name = 'V$DATABASE'

Upvotes: 2

Franck Piochaud
Franck Piochaud

Reputation: 96

Try this

select *
from dba_tab_columns 
where table_name = 'V_%DATABASE%'

Upvotes: 0

Related Questions