Getting above error when trying to access list of tables from schema browser.
Toad 13.3.0.181
Database : 11.2.0.3
oracle client : 19.3
Below query is fired from toad.
Select t.table_name, t.owner, t.cluster_name,
t.partitioned, t.iot_type, t.tablespace_name, t.last_analyzed, round(t.num_rows) num_rows,
t.initial_extent, t.degree, t.logging, t.cache, t.buffer_pool
, t.monitoring, t.temporary, t.table_type, t.table_type_owner, t.nested
, t.Compression, t.dropped
, t.compress_for, t.flash_cache, t.cell_flash_cache, t.segment_created
, external
from sys.ALL_ALL_TABLES t
where 1=1
and t.owner = :own
order by table_name
11.2.0.3 doesn't have external column in that view, how can I avoid TOAD doing that or any workaround?
I just looked at the source code for Toad version 13.3.
It looks like the only way that the ", external" line will appear in your query is if Toad thinks that you are connected to Oracle Version 12.2 or newer.
Is this maybe a 12.2 database version with the COMPATIBLE parameter set to 11.2.0.3?
It is 11.2.0.3 database and compatible parameter set to 11.2.0.0.0
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SQL> sho parameter compatible
NAME TYPE VALUE
compatible string 11.2.0.0.0
I can verify that I also get "Invalid Identifier" on my 11gR2, if it's worth confirming...
SQL> SELECT version
2 FROM SYS.PRODUCT_COMPONENT_VERSION
3 WHERE UPPER(PRODUCT) LIKE '%ORACLE%';
VERSION
11.2.0.3.0
To clarify for other users following this thread, I initially tested the user's SQL statement directly in my Editor and received the error message.
However, my Toad 14.1 on my 11gR2 (11.2.0.3) database does NOT generate the error message when I access the Tables list off the Schema Browser.
This is what Toad generates on my 11gR2...
Select t.table_name, user as owner, t.cluster_name,
t.partitioned, t.iot_type, t.tablespace_name, t.last_analyzed, round(t.num_rows) num_rows,
t.initial_extent, t.degree, t.logging, t.cache, t.buffer_pool
, t.monitoring, t.temporary, t.table_type, t.table_type_owner, t.nested
, t.Compression, t.dropped
, t.compress_for, t.flash_cache, t.cell_flash_cache, t.segment_created
, decode(nvl(tablespace_name, 'x') || upper(partitioned) || nvl(iot_type, 'x') || to_char(pct_free), 'xNOx0', 'YES', 'NO') as External
from sys.user_all_tables t
where 1=1
order by table_name;
It looks like you ran that version check SQL in SQL*Plus. Will you please run it in Toad so we can be 100% certain that you are connected to the same database that gives the schema browser error?
The only other thing that I can think of is that maybe you have the query set by a filter. If this button does not look grey like it does in my screen shot, then click the dropdown next to it and then "clear filter for tables"
Thanks Gary. I get the same SQL as you, both in Toad 14.1 and 13.3.
I get the same version 11.2.0.3 from TOAD too..
What about the filter option that I suggested?
If that's not it, do you also get SQL errors if you try to see profiles and sequences in the schema browser? If so, then somehow Toad thinks you are running Oracle version 12R2, in which case you might want to open a support case. I don't know how that could be happening given your query results but maybe they can figure it out.
Thanks So much John, Clearing the filter worked like a charm!! Sorry for the delay as I was helping other user with this issue and couldn't get hold of her until now to try the solution.
Thanks again!!
I'm glad that was it, because I was out of ideas!