Database Administration (MOSC)

MOSC Banner

how to prevent all_tab_columns/col from selecting external tables

edited May 25, 2015 3:42PM in Database Administration (MOSC) 5 commentsAnswered ✓

Oracle 11.2.0.4 (Standard Edition)

how to prevent all_tab_columns/col from selecting external tables? Documnetation does not mention this.

I'm assuming there is no way except for

SELECT distinct owner, table_name

FROM all_tab_columns

WHERE owner = 'EIS_REPORT' and table_name NOT IN (SELECT TABLE_NAME FROM ALL_EXTERNAL_TABLES)

order by table_name;

or

SELECT distinct owner, table_name

FROM all_tab_columns

WHERE owner = 'EIS_REPORT' and table_name NOT EXISTS (SELECT 42 FROM ALL_EXTERNAL_TABLES WHERE ALL_TAB_COLUMNS.TABLE_NAME = ALL_EXTERNAL.TABLE_NAME)

order by table_name;

for second query, thank you to a_horse_with_no_name from stack overflow for where I got it from.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center