How the Oracle DB schema is obtained


This article aims to explain how Enterprise Recon queries for your Oracle DB schemas.



The first query statement ER makes is;
select owner, table_name from dba_tables order by owner asc

..if this query fails due to permissions or any other reason, it falls back to this statement;
select owner, table_name from all_tables order by owner asc

..then if that fails as well;
select table_name from user_tables order by owner asc

We then query the following tables to get columns, keys, indicies:
`dba_tab_cols`, `dba_cons_columns`,`dba_constraints`, `dba_ind_columns`, `dba_indexes` (OR the `all_` versions OR the `user_` versions)

Full query:
select a.column_name, a.data_type, nvl(e.index_type, 0),
  a.data_length, a.NULLABLE, nvl(c.CONSTRAINT_TYPE, 0)
    from dba_tab_cols a
      left join dba_cons_columns b on (b.owner='%s' and b.TABLE_NAME='%s'
      and a.column_name=b.column_name)
      left join dba_constraints c on (b.CONSTRAINT_NAME=c.CONSTRAINT_NAME
      and c.owner='%s' and c.TABLE_NAME='%s')
      left join dba_ind_columns d on ( d.TABLE_OWNER='%s' and
      d.TABLE_NAME='%s' and a.column_name=d.column_name)
      left join dba_indexes e on (d.index_name=e.index_name and
      e.owner='%s' and e.TABLE_NAME='%s')
      where a.owner='%s' and a.table_name='%s' order by a.column_id

Similar to the first query statement above, ER will fall back to the "all_" and "user_" variants if it lacks privileges.

The locking behavior on Oracle is such that 'NOLOCK' or 'READ UNCOMMITTED' are unnecessary, as read queries are guaranteed to never block a write query therefore we don't need to worry about this affecting Oracle scans.


All information in this article is accurate and true as of the last edited date.

Have more questions? Submit a request


Please sign in to leave a comment.