List views columns in Oracle database

Query below lists:

(A) all columns in views accessible to the current user in Oracle database

(B) all columns in a specific view accessible to the current user in Oracle database

(C) all columns in views in Oracle database

Query was executed under the Oracle12c Database version.

Query

A. All views accessible to the current user

select col.column_id, 
       col.owner as schema_name,
       col.table_name, 
       col.column_name, 
       col.data_type, 
       col.data_length, 
       col.data_precision, 
       col.data_scale, 
       col.nullable
from sys.all_tab_columns col
inner join sys.all_views v on col.owner = v.owner 
                              and col.table_name = v.view_name
order by col.owner, col.table_name, col.column_id;

B. Specific view accessible to the current user

select column_id, 
       owner as schema_name,
       table_name, 
       column_name, 
       data_type, 
       data_length, 
       data_precision, 
       data_scale, 
       nullable
from sys.all_tab_columns
where table_name = 'CTX_INDEX_SECTIONS' 
and owner = 'CTXSYS'
order by column_id;

C. If you have privilege on dba_tab_columns and dba_views

select col.column_id, 
       col.owner as schema_name,
       col.table_name, 
       col.column_name, 
       col.data_type, 
       col.data_length, 
       col.data_precision, 
       col.data_scale, 
       col.nullable
from sys.dba_tab_columns col
inner join sys.dba_views v on col.owner = v.owner 
                              and col.table_name = v.view_name
order by col.owner, col.table_name, col.column_id;

Columns

  • column_id - sequence number of the column as created
  • schema_name - view owner, schema name
  • table_name - view name
  • column_name - column name
  • data_type - column datatype
  • data_length - column length in bytes
  • data_precision - length - for NUMBER - decimal digits; for FLOAT - binary digits
  • data_scale - digits to right of decimal point in a number
  • nullable - flag indicating if column allows nulls values

Rows

  • One row represents one column in a specific view in a database
  • Scope of rows: (A) all columns in views accessible to the current user in Oracle database, (B) all columns in a specific view accessible to the current user in Oracle database, (C) all columns in views in Oracle database

  • Ordered by schema name, table name, column sequence number

Sample results

Here is a view of view columns in Oracle SQL Developer:

0
There are no comments. Click here to write the first comment.