Toad Usage and Oracle Autonomous Database

Toad for Oracle
1

Hey all,

We are looking at doing some work in the Oracle autonomous Database Cloud area, and after setting up a trial run at it, and connecting to it with TOAD, we are running into some issues...

Haven't found anything else like what we are seeing, so Im guessing it's something screwy with our setup...

Basically, 90% of TOAD doesn't work...Cant look at Procedures/Functions/Triggers, Can't look at data in Tables, or scripts for anything...

Everything that fails always returns with the dreaded "ORA-00942: table or view does not exist" error...

I'm assuming that this is because the online database is "hiding" all the SYS level tables that TOAD uses to do it's magic...

Is there some setting or something I'm missing, or is TOAD just not usable with the online Oracle Autonomous Database?

Thanks for any info...
RonO

2

On the main menu, click Database -> Spool SQL -> Spool to Screen.

Then make your connection and do whatever you do to get the error.

You'll see every SQL that Toad is executing, and which ones are throwing errors. Post the output here if you aren't sure what to do about it.

And also let me know which version of Toad you are on.

3

Hey John,

First, thanks for the reply...Much appreciated....I'm currently using TOAD 13.1.0.78...

The Spool shows that my user doesn't have access some of the DBA_xxxx views and sys$ table...things like sys.DBA_TRIGGERS, sys.DBA_IND_COLUMNS, sys.procedureinfo$

Is there some script or something I can run as the ADMIN user (SYS isn't available to the Autonomous world) to setup all these missing things?

Actually, I just tried to do a grant on the sys.procedureinfo$ table to my test user/schema owner, and got table or view doesnt exist.../sigh...

Here is the query that came from the log when I tried to look at functions...

select o.*, NVL(d.debuginfo, 'F') DEBUGINFO,
NVL(p.PIPELINED, 'NO') pipelined,
NVL(p.PARALLEL, 'NO') parallel,
NVL(p.DETERMINISTIC, 'NO') deterministic,
NVL(p.AUTHID, 'DEFINER') authid
from
(Select :own owner, object_name, decode(status, 'VALID', 'V', 'I') status, last_ddl_time, object_id, created
FROM sys.user_objects
WHERE 1=1
AND object_type = 'FUNCTION') o
,(SELECT distinct obj# object_id, 'T' debuginfo
FROM sys.idl_char$
WHERE part = 1) d
,(SELECT obj# object_id,
DECODE (BITAND (properties, 16), 16, 'YES', 'NO') pipelined,
DECODE (BITAND (properties, 32), 32, 'YES', 'NO') parallel,
DECODE (BITAND (properties, 256), 256, 'YES', 'NO') deterministic,
DECODE (BITAND (properties, 1024), 1024, 'CURRENT_USER', 'DEFINER') authid
FROM sys.procedureinfo$
WHERE PROCEDURE# = 1
AND overload# = 0) p
where p.object_id (+) = o.object_id
and o.OBJECT_ID = d.object_id (+)
order by 2

4

What roles and/or system privileges does your user have?

It sounds like Toad is detecting that you have something like SELECT_ANY_DICTIONARY and in this version of Oracle, that system privilege doesn't give you what it does in the regular database.

Does Toad work OK if you log in as ADMIN? If so, log in as that user and try revoking SELECT_ANY_DICTIONARY or anything similar to your user. You may have to create your own role with these grants and grant that instead.

If you are logged in as your own user in Toad, log out and then back in after making these changes. Toad does its check for this kind of thing when the connection is first made.

5

That's interesting...Revoking the SELECT_ANY_DICTIONARY priv from the test user/schema owner cleared up not being able to see the PL/SQL code objects....I'll have to go thru our User Create script to see why that's there...doesn't seem like something normally needed...

So Toad dynamically changes how it accesses objects based on the current owners granted sys level privs...That's so cool...Toad never ceases to amaze me...:slight_smile:

Thank you for the answer, and your time...
RonO

1 Like
6

Yes, and Oracle version too. The SYS-owned tables are generally the fastest way to access the data dictionary, so sometimes I rely on those instead of DBA_, ALL_, and USER_, if I think the user has access to them. In this case, I was wrong, hence your error. USER_ views are generally the next fastest, followed by DBA_, and ALL_ is the slowest.

If I can detect connection to an Oracle Autonomous Cloud database vs an on-prem db, I can probably make a few tweaks to the logic and fix this.

7

Hey John,

As a follow up to this, any reason why certain options in the Schema Browser wont show up in the tab list? Things like Policies and Contexts? These two, for example, don't even show up on the list to select when in the Configure window....

I can see the Policies exist by selecting a table in the Schema Browser, and clicking the Policies tab on the Table data...

8

Those object types require the DB Admin module. Do you have that in your Toad license? Check in Help -> About.

9

Indeed, I couldn't live without the DBA Admin module... :slight_smile:

Looking at the my "local" Oracle instances, those tabs show up, but when looking at the Autonomous Cloud instance, not so much...

10

Are those the only LHS tabs that are missing?

What do you get from this query in the Editor?

SELECT version
FROM SYS.PRODUCT_COMPONENT_VERSION
WHERE UPPER(PRODUCT) LIKE '%ORACLE%'

11

The comes back with:

21.0.0.0.0

From what I can tell, those are the only 2 that are missing from the tabbed view....

12

There was a bug in 13.1 where Contexts, Policies, Dimensions, and Java doesn't appear in the schema browser if the package DBMS_JAVA does not exist in the database (or you don't have execute privs on it). Obviously, only Java shouldn't appear in SB if that package doesn't exist.

I am guessing that's what is happening here. Log in as ADMIN to see if the package exists. If it does, run:
GRANT EXECUTE on DBMS_JAVA to YOUR_USER;

If that package does not exist in the Autonomous DB, then you'll have to update your Toad to at least version 13.3 to get around this.

13

Hey John,

That must be it....The Autonomous Cloud instances do not provide User Java availability, and that DBMS_JAVA package is not available to users, or even the ADMIN user...

I can't do any software upgrades till July at the earliest, so I can't test if later versions work with that Autonomous Cloud issue...

Once again, thanks for the info and your time....
RonO