# Toad 13.0 does not show dependencies in schema browser

**URL:** <https://forums.toadworld.com/t/toad-13-0-does-not-show-dependencies-in-schema-browser/42935>\
**Category:** Toad for Oracle & DevOps Toolkit\
**Created:** [October 26, 2018, 1:41pm UTC](https://forums.toadworld.com/t/toad-13-0-does-not-show-dependencies-in-schema-browser/42935 "2018-10-26T13:41:56Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![Maaher](https://avatars.discourse-cdn.com/v4/letter/m/a587f6/32.png) [@Maaher](https://forums.toadworld.com/u/Maaher)\
**Post date:** [October 26, 2018, 1:41pm UTC](https://forums.toadworld.com/t/toad-13-0-does-not-show-dependencies-in-schema-browser/42935/1 "2018-10-26T13:41:56Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\
**Post date:** [October 26, 2018, 2:08pm UTC](https://forums.toadworld.com/t/toad-13-0-does-not-show-dependencies-in-schema-browser/42935/2 "2018-10-26T14:08:02Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![Maaher](https://avatars.discourse-cdn.com/v4/letter/m/a587f6/32.png) [@Maaher](https://forums.toadworld.com/u/Maaher)\
**Post date:** [October 26, 2018, 2:58pm UTC](https://forums.toadworld.com/t/toad-13-0-does-not-show-dependencies-in-schema-browser/42935/3 "2018-10-26T14:58:00Z")

</div>

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):

```auto
-- 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'
```

```auto
-- 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'
```

```auto
-- 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:

```auto
-- 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'
```

```auto
-- 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'
```

```auto
-- 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
```

```auto
-- 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
```

---

<div class="post-metadata">

**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\
**Post date:** [October 26, 2018, 3:52pm UTC](https://forums.toadworld.com/t/toad-13-0-does-not-show-dependencies-in-schema-browser/42935/4 "2018-10-26T15:52:50Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![Maaher](https://avatars.discourse-cdn.com/v4/letter/m/a587f6/32.png) [@Maaher](https://forums.toadworld.com/u/Maaher)\
**Post date:** [October 26, 2018, 8:53pm UTC](https://forums.toadworld.com/t/toad-13-0-does-not-show-dependencies-in-schema-browser/42935/5 "2018-10-26T20:53:00Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![jbowman](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/jbowman/32/22_2.png) [@jbowman](https://forums.toadworld.com/u/jbowman)\
**Post date:** [October 26, 2018, 9:31pm UTC](https://forums.toadworld.com/t/toad-13-0-does-not-show-dependencies-in-schema-browser/42935/6 "2018-10-26T21:31:45Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Maaher](https://avatars.discourse-cdn.com/v4/letter/m/a587f6/32.png) [@Maaher](https://forums.toadworld.com/u/Maaher)\
**Post date:** [October 26, 2018, 9:44pm UTC](https://forums.toadworld.com/t/toad-13-0-does-not-show-dependencies-in-schema-browser/42935/7 "2018-10-26T21:44:07Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![jbowman](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/jbowman/32/22_2.png) [@jbowman](https://forums.toadworld.com/u/jbowman)\
**Post date:** [October 26, 2018, 9:46pm UTC](https://forums.toadworld.com/t/toad-13-0-does-not-show-dependencies-in-schema-browser/42935/8 "2018-10-26T21:46:00Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Maaher](https://avatars.discourse-cdn.com/v4/letter/m/a587f6/32.png) [@Maaher](https://forums.toadworld.com/u/Maaher)\
**Post date:** [October 26, 2018, 9:48pm UTC](https://forums.toadworld.com/t/toad-13-0-does-not-show-dependencies-in-schema-browser/42935/9 "2018-10-26T21:48:34Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Maaher](https://avatars.discourse-cdn.com/v4/letter/m/a587f6/32.png) [@Maaher](https://forums.toadworld.com/u/Maaher)\
**Post date:** [October 29, 2018, 11:08am UTC](https://forums.toadworld.com/t/toad-13-0-does-not-show-dependencies-in-schema-browser/42935/10 "2018-10-29T11:08:35Z")

</div>

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.