Once we upgraded our database to 10.2.0.4. If we login as schema owner , the list of objects are duplicated into thousands. This is only happening for procedures , packages , functions. Other objects are just fine. The issue is when connecting as the schema owner, not as DBA roles viewing other schemas.
The issue looks like it is coming from this query which seems to populate the list of objects (tracing):
select o.*, NVL(d.debuginfo, 'F') DEBUGINFO, nvl(p.AUTHID, 'CURRENT_USER') authid
from (
Select object_name, object_type, decode(status, 'VALID', 'V', 'I') status, last_ddl_time, object_id, created
from sys.user_objects
where 1=1
and object_type in ('PACKAGE', 'PACKAGE BODY')
) o, sys.all_probe_objects d
,(SELECT object_name, AUTHID
FROM sys.user_procedures
where 1=1
GROUP BY object_name, AUTHID) p
where p.object_name (+) = o.object_name
and o.OBJECT_ID = d.object_id (+)
And D.Owner (+) = :own
And O.Object_Name = D.Object_Name (+)
And ((d.object_type is null) or (d.object_type in ('PACKAGE', 'PACKAGE BODY')))
order by 2, 1
Is Toad compatible with 10.2.0.4?
This issue is found to be an Oracle bug on 10.2.0.4 databases.
Toad 8.6.1 and up should be compatible with Oracle 10.2.x.
MetaLink bug/patch:
Subject: Bug 6972343 - Wrong results with push predicate and cost based transformation
Doc ID: Note:6972343.8 Type: PATCH
Last Revision Date: 24-SEP-2008 Status: PUBLISHED
Wrong results are possible with join predicate pushdown and transitive predicate generation.
ORACLE WORKAROUND:
Disable join predicate pushdown.
_optimizer_enhanced_filter_push = false
