You can try to access the DBA_Objects table and check the type is TABLE or synonym. If it is a TABLE then it will be a internal table for you. If it is Synonym then access the DBA_SYNONYMS table , this will give the correct table name and the DB Link name to access the table. like select * from <table_name_in_fnd_synyms>@<DB_Link_In_DBA_Synonyms>
One option would be to query the ALL_EXTERNAL_TABLES data dictionary view. If there is a row in ALL_EXTERNAL_TABLES for the table you're querying (I'm assuming you know that you are querying a table, not a view or a synonym), it is an external table. Otherwise, it is a regular table.
Hi Justin cave,
Thanks for your reply.
As you assumed, i am querying a table, not a view or a synonym.
ALL_EXTERNAL_TABLES data dictionary view is fine.
And also i find another way.
In ALL_TABLES data dictionary view, MONITORING and GLOBAL_STATS column is useful.
For Regular table:
MONITORING - YES
GLOBAL_STATS - YES
For External table:
MONITORING - NO
GLOBAL_STATS - NO
One benefit by querying in this way is that, here we can get result for both regular table and external table?
Shall i go ahead with this?
Thanks for your replies.
Neither MONITORING nor GLOBAL_STATISTICS are reliable indicators that a table is an external table. It is quite possible for regular tables to have monitoring disabled or to have global_stats set to NO.