Find all date and time columns in Oracle database

Date and time in Oracle database are represented by following data types: date, timestamp, timestamp with time zone, timestamp with local time zone, interval.

The query below lists:

(A) all columns with date/time data types accessible to the current user in Oracle database

(B) all columns with date/time data types in Oracle database

Query

A. All columns with date/time data types accessible to the current user

select col.owner as schema_name,
       col.table_name,
       column_id,
       column_name,
       data_type,
       data_scale
from sys.all_tab_cols col
join sys.all_tables tab on col.owner = tab.owner
                        and col.table_name = tab.table_name
where (data_type in ('DATE')
      or col.data_type like 'TIMESTAMP%'
      or col.data_type like 'INTERVAL%')
      and col.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 
      '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', 'LBACSYS')
order by col.owner,
         col.table_name,
         column_id;

B. All columns with date/time data types

select col.owner as schema_name,
       col.table_name,
       column_id,
       column_name,
       data_type,
       data_scale as second_scale
from sys.dba_tab_cols col
join sys.dba_tables tab on col.owner = tab.owner
                        and col.table_name = tab.table_name
where (data_type in ('DATE')
      or col.data_type like 'TIMESTAMP%' 
      or col.data_type like 'INTERVAL%')
      and col.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 
      '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','LBACSYS')
order by col.owner,
         col.table_name,
         column_id;

Columns

  • schema_name - 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
  • second_scale - fractional seconds precision

Rows

  • One row represents one column with a date/time data type
  • Scope of rows: all columns containing date/time data types in the database
  • Ordered by schema name, table name and column position in table

Sample results

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
0
There are no comments. Click here to write the first comment.