SB "Used by" for table not showing anything

Toad for Oracle - Beta
1

Hi,

I just noticed that if I click on a table in the Schema Browser, then select the “Used by” tab in the RHS, it doesn’t show any objects, even for a table which (in my case) is used by a package, which in turn is used by other packages.

It seems to have broken with 12.7, and is still broken in 12.8.0.10 beta. With 12.6 GA, it works fine.

I studied the spooled SQL from both 12.6 and 12.7, and it looks like 12.7 somehow gets the object id of the table changed to something else along the way.

I can see the query which finds the object id:

Select created, last_ddl_time, object_id, status
from sys.user_objects
where object_name = :nm
and object_type = :t
:nm(VARCHAR[23],IN/OUT)=(my table name)
:t(VARCHAR[5],IN/OUT)=‘TABLE’

It is the same in both versions. If I execute that myself, I get the correct id (100833 in my case).

Later, the following query, also the same in the two version, should give the dependency tree:

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 prior pd.object_id = pd.referenced_object_id
start with pd.referenced_object_id = :ObjID
:ObjID(INTEGER,IN/OUT)=84534

But in 12.7, the :ObjID is 84534 (as shown here), while in 12.6, it uses the correct one, 100833.

I don’t seem to have any object at all (of any type) in USER_OBJECTS with id 84534 (nor, for that matter, in ALL_OBJECTS when loggod on as SYS).

Best regards,

Niels

2

It’s working right for me. It may seem unrelated, but do you have Team Coding set up?

3

Yes, Team Coding is on, but with no VCS.

We have one TC project set up for the owner schema in our application, covering packages, package bodies, tables, triggers, constraints and indexes (I should probably add functions and procedures, although we only have a few, and views).

Best regards,

Niels

4

Thanks, I can reproduce this problem. It happens when a table is added to Team Coding (at which point the object id is stored in TC tables), then the table is dropped and recreated (giving it a new object ID).

I don’t have a solution yet, but we’ll let you know when it’s fixed.

5

Cool, thanks John.

Hmm, we do drop and recreate all our tables (and other objects) in the development base every time we import fresh data into it with data pump.

So I guess a workaround could be to remove and recreate the TC project after the import? We’d of course lose our checkout info, but we might be able to coordinate our way around that (we’re only two developers currently).

Best regards,

Niels

6

Yes, that would do it. Before you do that though, try this script - It will update the object_ids and preserve your history. As long as there is no other piece that I’m missing, this should do it.

We’re looking at a change where we would stop using object_id and use object_owner, name, and type instead, but in the meantime, I think this will do the trick:

alter table toad.tcx_object_history

disable constraint tcx_objhist_obj_proj_fk;

update toad.tcx_object_history h

set h.object_id = (select d.object_id

from dba_objects d, toad.tcx_objects o

where d.object_type in (‘TABLE’, ‘PROCEDURE’, ‘PACKAGE’, ‘PACKAGE BODY’, ‘TRIGGER’, ‘SEQUENCE’, ‘INDEX’, ‘FUNCTION’)

and o.object_owner = d.owner

and o.object_name = d.object_name

and d.object_type = upper(o.object_type)

and h.object_id = o.object_id);

update toad.tcx_objects o

set o.object_id = (select d.object_id

from dba_objects d

where d.object_type in (‘TABLE’, ‘PROCEDURE’, ‘PACKAGE’, ‘PACKAGE BODY’, ‘TRIGGER’, ‘SEQUENCE’, ‘INDEX’, ‘FUNCTION’)

and o.object_owner = d.owner

and o.object_name = d.object_name

and d.object_type = upper(o.object_type)

and o.object_id = o.object_id);

alter table toad.tcx_object_history

enable constraint tcx_objhist_obj_proj_fk;

commit;

7

Oh, and if Toad is running while you run that script, you’ll need to close and restart it (or at least disconnect/reconnect that connection)

8

Oh, darn …

I already removed and recreated the Team Project before I read this reply.

It worked fine, BTW. I even automatically got the missing object types included.

And now the Used By function works.

But sorry I can’t test your script any more, at least until after the next time we import new data in one of our development bases (which happens relatively rarely).

I’ll save it for that occasion and get back when/if it happens.

Best regards,

Niels

9

Hey Niels,

This should be fixed in the next beta. Please feel free to let us know if it continues to be a problem.

Thanks!

-John

10

Oh, and by the way, this will also be fixed for the 12.7 patch.

Thanks!

-John

11

Great! My PM (who doesn’t run the betas) will appreciate that :-).

Best regards,

Niels