Toad World® Forums

Expanding "Tables" Node hangs


#1

I just installed 6.2.4176 (in hopes that this would be fixed) but when I expand the Tables node, it hangs…forever. I’m connecting as my personal user and switching over to our app schema. I’ve never really waited long enough to see how long it takes ( I gave up after 15 mins) . The DBA says its trying to run the following SQL which I can run from a code editor just fine. I can’t figure out if this is a SQLNav issue or a permission issue. All I know is that it didn’t happen prior to v6.1

The funny thing is that this happens in our Q, U and prod envs not Dev. I’m having the dba take a look at the roles and grants etc. but that shouldn’t matter should it as long as I have select privs on sys.all_objects and sys.all_tables? Has anyone seen this problem?

Update: I installed Toad 9.7.2.5 and was able to retrieve the list of tables in seconds. SQL Navigator hangs forever. What is different about the way the two tools retrieve the list of tables?

SELECT ‘EDW_STG’ owner,
a.object_name,
a.object_id,
a.created,
a.last_ddl_time,
DECODE(a.status, ‘VALID’, 0, ‘INVALID’, 1, 2) status,
DECODE(b.partitioned, ‘YES’, ‘Y’, ‘NO’, ‘N’) partitioned,
‘N’ object_table,
‘N’ external_table,
DECODE(b.nested, ‘YES’, ‘Y’, ‘N’) NESTED,
DECODE(b.IOT_Type, ‘IOT’, 1, ‘IOT_OVERFLOW’, 2, 0) IOT_Type,
b.IOT_Name,
b.temporary
FROM sys.all_objects a, sys.all_tables b
WHERE b.owner = ‘EDW_STG’ --:schema
AND a.owner = ‘EDW_STG’ --:schema
AND a.object_name = b.table_name
AND a.object_type = ‘TABLE’
AND b.dropped = ‘NO’

Message was edited by: jstinnett


#2

Hi John,

How many tables do you have in that schema? Could you please provide us with extract ddl for that user? have you tried turning ON/OFF “Enable using DBA views” in the Oracle logon?

Gwen

Message was edited by: Gwen


#3

There are 65 tables, 50 are partitioned. “Enable using DBA views” is grayed out when I choose our Dev and Prod envs although it’s available for QA and UA. I checked it in QA and it still hangs. How does checking it change the query to the DB?

I’m working on getting the ddl.
John


#4

Gwen - how can I send you the ddl - the file is 40MB (1.4 million lines)


#5

I wonder how the extracted ddl of that user is so big. But anyway, please check your inbox for my email address. Thanks.


#6

Hi John,

I have received your email today with the extracted DDL. We will try building a schema from the file you sent us and see if we can reproduce the issue. I will keep you posted.

Gwen


#7

Hi John,

We manage to build the environment using your script and couldn’t get the issue. Did you have problem with DB Navigator tree or DB Explorer? Did you log in as EDW_STG and expand the my schema node or did you log in as a different user?

Gwen


#8

Also, it would be helpful if you can provide us the screenshots of the Oracle logon for each environment that you were using (QA, Prod, etc…). Please make sure the Options tab at the bottom is expanded. Thanks.