Toad 13.0 does not show dependencies in schema browser

[Maaher]

On our 12c development database it is impossible to get the dependencies, tab Deps (Used By) in the right hand side of the schema browser of Toad.

The query executed (according to SQL Tracker) is:

Select to_char(object_id) object_id, to_char(referenced_object_id) referenced_object_id, to_char(level) "LEVEL" from (select object_id, referenced_object_id from public_dependency where referenced_object_id <> object_id) pd connect by nocycle prior pd.object_id = pd.referenced_object_id start with pd.referenced_object_id = 76882

The object id in this query is incorrect. Where does Toad get this from? Is this a setting in Toad? This behavior can be reproduced on every object of our application schemas.

Our 12c database should be identical to the 11g version we have and on that 11g database we haven’t got this problem.

Our Toad version is 13.0.0.80.

[JohnDorlon]

Spool SQL (Off Toad’s main menu) is easier to use than SQL Tracker. You should try that instead.

If you look a little ways up in the spool SQL, you’ll see the query where we get the object ID.

what type of object is this?

If you spool SQL on 11g, do you see the same query?

[Maaher]

It is a function. Here’s the full spool from Toad when I select the function from the list on the left (tab used by is activated):

-- Session: USER@DB12C
-- Timestamp: 16:48:59.278
Select  TEXT 
from SYS.USER_SOURCE 
where NAME=:name 
and TYPE=:type 
order by LINE;
:name(VARCHAR[4],IN/OUT)='MYFUNCTION' 
:TYPE(VARCHAR[8],IN/OUT)='FUNCTION'

-- Session: USER@DB12C
-- Timestamp: 16:48:59.282
Select last_ddl_time
  FROM sys.USER_OBJECTS
 WHERE object_type = :type AND object_name = :name;
:TYPE(VARCHAR[8])='FUNCTION' 
:name(VARCHAR[4])='MYFUNCTION'

-- Session: USER@DB12C
-- Timestamp: 16:48:59.328
Select to_char(object_id) object_id, to_char(referenced_object_id) referenced_object_id, to_char(level) "LEVEL"
from   (select object_id, referenced_object_id
        from  public_dependency
        where referenced_object_id <> object_id) pd
connect by nocycle prior pd.object_id = pd.referenced_object_id
start with pd.referenced_object_id = :ObjID;
:ObjID(INTEGER,IN/OUT)=76882

And on our 11g it looks like this:

-- Session: USER@DB11G
-- Timestamp: 16:54:53.909
Select  TEXT 
from SYS.USER_SOURCE 
where NAME=:name 
and TYPE=:type 
order by LINE;
:name(VARCHAR[4],IN/OUT)='MYFUNCTION' 
:TYPE(VARCHAR[8],IN/OUT)='FUNCTION'

-- Session: USER@DB11G
-- Timestamp: 16:54:53.959
Select created, last_ddl_time, object_id, status
from sys.user_objects
where object_name = :nm
and object_type = :t;
:nm(VARCHAR[4],IN/OUT)='MYFUNCTION' 
:t(VARCHAR[8],IN/OUT)='FUNCTION'

-- Session: USER@DB11G
-- Timestamp: 16:54:53.984
Select to_char(object_id) object_id, to_char(referenced_object_id) referenced_object_id, to_char(level) "LEVEL"
from   (select object_id, referenced_object_id
        from  public_dependency
        where referenced_object_id <> object_id) pd
connect by nocycle prior pd.object_id = pd.referenced_object_id
start with pd.referenced_object_id = :ObjID;
:ObjID(INTEGER,IN/OUT)=97907

-- Session: USER@DB11G
-- Timestamp: 16:54:54.015
with Objs as
  (Select object_id
   from   (select object_id, referenced_object_id
           from public_dependency
           where referenced_object_id <> object_id) pd
   connect by nocycle prior object_id = referenced_object_id
   start with referenced_object_id = :ObjID)
Select o.owner, o.object_type, o.object_name, o.object_id, o.status
from   sys.DBA_OBJECTS o, Objs
where  o.Object_id = Objs.Object_id;
:ObjID(INTEGER,IN/OUT)=97907

[JohnDorlon]

we should be getting the object ID when we load the list of functions in the left hand side. That query can vary a bit depending on your options and privileges, but for me, it looks like this:

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;

Does the Object ID in shown in this query correspond with what you see in the query when you go to the Deps (Used By) tab?

[Maaher]

I cannot use that particular query, as my user lacks the necessary privileges on those dictionary tables, but the query my Toad uses for the left hand side list shows the correct identifiers…

…but here’s something else.

I was composing an answer with the extra information that some schemas did show up the dependencies and that older versions of Toad did show the dependencies (of the schemas I had issues with on Toad 13) when I realized something. We switched to Toad 13 so that we had team coding support with Microsoft TFS 2017.

Here’s what happened:

1. the database was created as a copy from an other database on 11g.
2. in TFS a project and workspace were created.
3. in TOAD we ran the Team Coding upgrade.
4. I added the necessary schemas to team coding.

Now, someone had made some substantial changes to some schemas on the source database and asked the DBA to recreate those schemas on the new database as that was easier than changing everything by hand, forgetting about Team Coding.

In the mean time, I was unaware of this schema change, and so was Toad Team Coding. The object id’s used by toad for the dependencies in the schema browser seem to come from Toad.tcx_objects as they correspond with the id’s found in that table.

The way I see it, I have two options:

1. Quick (and maybe dirty) - Update the TOAD tables (objects + object_history) with the correct object Ids (from SYS.DBA_OBJECTS). That would involve disabling a foreign key I suppose.
2. Slow (but maybe as it should be done): Check in all objects in Team coding, remove the failing schemas from Team Coding, add the failing schemas to Team coding.

Do you think that I’m right about what might have happened? If so, would option 1 (update toad tables manually) break team coding or do you think we can risk it? There have been changes made on the database and I’m not very keen on loosing the history.

I am sorry that I did not mention Team Coding earlier.

Edit: thank you for looking into this.

[jbowman]

Hi Maaher,

Reading through this, it does sound like the Object IDs in the Team Coding tables (which are supposed to match the Data Dictionary where available), got out of sync as a result of a manual copy.

To fix this issue in your environment for the immediate situation, you can do one of two options. You can try the first option you suggested:

• disable the foreign key between TCX_OBJECTS and TCX_OBJECT_HISTORY
• update the Object ID’s for the objects based on what’s currently in the data dictionary
• re-enable the foreign key between TCX_OBJECTS and TCX_OBJECT_HISTORY

Alternatively, if you don’t mind the Team Coding object history being blown away and recreated, an easier way would simply be to open up the Team Coding Configuration window and clicking the “Clear Objects from Team Coding” toolbutton (Recycle bin) in the toolbar. That will delete all records from TCX_OBJECTS and TCX_OBJECT_HISTORY tables, and those records will be recreated based on your current database once you click the “OK” button.

In the meanwhile, I’ll see if there’s a way for Team Coding to do some additional validation in Toad 13.2 without adversely affecting performance.

-John

[Maaher]

Thanks for the quick reply, John. If I use the “clear objects” option in Toad, the objects are recreated in Team Coding, correct? And what are the consequences for TFS? Will the history remain there or will the objects be recreated over there as well?

-Maarten

[jbowman]

Hi Maarten,

That simply rebuilds the control records sitting in Team Coding’s TCX tables. All of your history in TFS will still be in TFS. Team Coding doesn’t delete anything from the VCS.

-John

[Maaher]

Right. I will confirm Monday whether that solved the problem or not. Thanks again for the advise!

[Maaher]

This is what I tried:
Team coding -> Configuration… -> Clear objects from Team Coding. That did not work. I got a deadlock issue:
Error occurred: [60] (ORA-00060: deadlock detected while waiting for resource when trying to insert in TOAD.TCX_OBJECTS. I’ve seen that TOAD uses an autonomous transaction for that. Maybe that’s the issue.

It took ages to get to 30% of the rebuild before I noticed the error. I ran the rebuild from the TOAD schema, that is not part of the Team Coding configuration.

I’ve also tried removing a small schema from my TC config and the objects remained in the TCX tables. Is that expected behavior? When I added the schema again, the incorrect object id’s were still there.

After consulting a couple of colleagues we decided that the best and fastest option was to use a script, like suggested here before, as an alternative approach.

After an update on the TCX tables, the error seems to be resolved. I can now see the dependencies of the function I’ve started this thread with. So, right now it is solved.

But I was curious and I’ve tried clearing the objects afterwards and I saw this deadlock coming back. I tried this using both the TOAD user (owner) and another user that has the TC_ADMIN_ROLE.

Either we have done something terrible with our setup or there’s something not right with the team coding in TOAD 13.

TL;DR: Problem solved by updating by hand. Clearing objects in team coding didn’t work.