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.

0 out of 0 found this helpful



Please sign in to leave a comment.