Introduction
This article aims to explain how Enterprise Recon queries for your Oracle DB schemas.
Procedure
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
It will then issue the following queries to get the columns:
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 Comments