Query below lists:

(A) all columns in a specific table accessible to the current user in Oracle database

(B) all columns in a specific table in Oracle database

Query was executed under the Oracle9i Database version.

Query

A. Table 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_tables t on col.owner = t.owner 
                              and col.table_name = t.table_name
where col.owner = 'AP'
and col.table_name = 'AP_INVOICES_ALL'
order by col.column_id;

B. If you have privilege on dba_tab_columns and dba_tables

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_tables t on col.owner = t.owner 
                              and col.table_name = t.table_name
where col.owner = 'AP'
and col.table_name = 'AP_INVOICES_ALL'
order by col.column_id;

Columns

• column_id - sequence number of the column as created
• schema_name - table owner, schema name
• table_name - table 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 table in a database
• Scope of rows: (A) all columns of a specific table accessible to the current user in Oracle database, (B) all columns of a specific table in Oracle database
• Ordered by column sequence number

Sample results

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

You could also get this

Get this interactive HTML data dictionary in minutes with Dataedo.

See live HTML data dictionary sample

Try for free