Columns with JSON datatype can be stored in data types like VARCHAR, BLOB, CLOB but it is recommended to define constraint on that column, which ensures that inserted data is well-formated JSON document.

The query below lists:

(A) all columns with JSON constraint accessible to the current user in Oracle database

(B) all columns with JSON constraint in Oracle database

Query

A. All columns with JSON constraint

select col.owner,
       col.table_name,
       col.column_id,
       col.column_name,
       col.data_type,
       jscol.format
from sys.all_tab_cols col
join sys.all_tables tab on col.owner = tab.owner
                        and col.table_name = tab.table_name
join sys.all_json_columns jscol 
     on jscol.owner = col.owner
     and jscol.table_name = col.table_name
     and jscol.column_name = col.column_name 
where col.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 col.owner,
         col.table_name,
         column_id;

B. All columns with JSON constraint

select col.owner,
       col.table_name,
       col.column_id,
       col.column_name,
       col.data_type,
       jscol.format
from sys.all_tab_cols col
join sys.all_tables tab on col.owner = tab.owner
                        and col.table_name = tab.table_name
join sys.all_json_columns jscol 
     on jscol.owner = col.owner
     and jscol.table_name = col.table_name
     and jscol.column_name = col.column_name 
where col.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 col.owner,
         col.table_name,
         column_id;

Columns

• owner - name of the schema
• table_name - name of the table
• column_id - column position in table
• column_name - name of the column
• data_type - type of data
• format -
  • TEXT
  • BINARY

Rows

• One row represents one column with a JSON data type (constraint)
• Scope of rows: all columns containing a JSON data type (constraint) in the database
• Ordered by schema name, table name and column position in table

Sample results