In Oracle database character columns are with the following data types: CHAR, VARCHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, NCLOB, LONG (only for backward compatibility).

The query below lists:

(A) all columns with character datatypes accessible to the current user in Oracle database

(B) all columns with character datatypes in Oracle database

Query

A. All columns with character datatypes accessible to the current user

select owner as schema_name,
       table_name,
       column_id,
       column_name,
       data_type,
       data_length
from sys.all_tab_cols
where data_type in ('CHAR', 'VARCHAR', 'VARCHAR2', 'NCHAR',
                    'NVARCHAR2', 'CLOB', 'NCLOB')
      and owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
      'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS',
      'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
      'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 
      'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
      'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC',
      'OUTLN', 'WKSYS', 'APEX_040200')
order by owner,
         table_name,
         column_id;

B. All columns with character datatypes

select owner as schema_name,
       table_name,
       column_id,
       column_name,
       data_type,
       data_length
from sys.dba_tab_cols
where data_type in ('CHAR', 'VARCHAR', 'VARCHAR2', 'NCHAR',
                    'NVARCHAR2', 'CLOB', 'NCLOB')
      and owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
      'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS',
      'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
      'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 
      'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
      'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC',
      'OUTLN', 'WKSYS', 'APEX_040200')
order by owner,
         table_name,
         column_id;

Columns

• schema_name - name of the schema (owner)
• table - name of the table
• column_id - column position in table
• column_name - name of the column
• data_type - type of data
• data_length - maximum length in bytes

Rows

• One row represents one column with a character datatype
• Scope of rows: all columns containing character datatypes in the schema
• Ordered by schema name, table name and position in table

Sample results