CODEWITHSUNDEEP
sqldatabaseoracle-databaseoracle11gsqlplus
user3488855
user3488855

Reputation: 61

How to get the list of external tables in oracle schema 11g

I want to get the list of only external tables in oracle. i tried to get the list using Select * from tab . But it return list of all the table including actual and external . But i only want list of external tables

Upvotes: 4

Views: 18773

Answers (1)

user330315
user330315

Reputation:

Use 

select *
from all_external_tables;

to see all external tables your user as access to. To see them for a specific schema/user:

select *
from all_external_tables
where owner = 'ARTHUR';

If you only want to see the ones owned by your current user, use 

select *
from user_external_tables;

To see all table that are not external tables use this:

select ut.table_name
from user_tables ut
where not exists (select 42
                  from user_external_tables uet
                  where uet.table_Name = ut.table_name);

More details in the manual:

Upvotes: 10

Related Questions