Home » SQL & PL/SQL » SQL & PL/SQL » Data Dictionary join issue (DB: 10.2.0.3.0 & Platform: Linux IA (32-bit))
Data Dictionary join issue [message #383815] Fri, 30 January 2009 06:36 Go to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Hi Everybody,
We have a weird issue, which can be reproducible internally in one of the environments.

The select from the following sql is returning 2 rows. But the insert is inserting zero rows. In all other environments that is working perfectly fine.
INSERT INTO xla_extract_objects_gt
           (object_name,
            owner)
SELECT ext.object_name,
       (SELECT syn.table_owner
        FROM   user_objects usr,
               user_synonyms syn
        WHERE  ext.object_name = usr.object_name
               AND usr.object_name = syn.synonym_name
               AND usr.object_type = 'SYNONYM')
FROM   xla_extract_objects ext
WHERE  EXISTS (SELECT /*+ no_unnest */ 'c'
               FROM   user_objects usr,
                      user_synonyms syn
               WHERE  ext.object_name = usr.object_name
                      AND usr.object_name = syn.synonym_name
                      AND usr.object_type = 'SYNONYM')
       AND ext.application_id = 555
       AND entity_code = 'REVALUATION'
       AND event_class_code = 'ACTCOSTADJ';
Also there is no issue with the permissions. The following in working fine.
INSERT INTO xla_extract_objects_gt
           (object_name,
            owner)
SELECT 'ABC','XYZ' from dual;
We tried to change the join between data dictionaries like the following:

ext.object_name = usr.object_name
AND usr.object_name = syn.synonym_name

to

ext.object_name = usr.object_name
AND ext.object_name = syn.synonym_name -- changed the join (in either of the inner queries)

then it is working fine in that environment. We couldn't figure out why this is happening only in one environment.
Even we tried to check the v$parameter, but not much difference with other environments. (except _index_join_enabled, global_names. after setting to the value at session level, it is not inserting the rows with previous sql)
It is working fine in the other environments, which are on same OS and same database version.

Kindly advice.

Thanks,
Vamsi

[Updated on: Fri, 30 January 2009 06:39]

Report message to a moderator

Re: Data Dictionary join issue [message #383819 is a reply to message #383815] Fri, 30 January 2009 06:53 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Have you tried turning on 10053 trace and compare the query between the two environments ?

However for what it is worth check this link. But I wouldn't expect it to yield different results either you apply the transitive closure or oracle does it for you. But with data dictionary views I am not sure.

http://jonathanlewis.wordpress.com/2007/01/01/transitive-closure/

Regards

Raj

[Updated on: Fri, 30 January 2009 06:54]

Report message to a moderator

Previous Topic: executing ORADEBUG inside stored procedure
Next Topic: Dynamic display of columns in Pivot & Crosstab [merged 3]
Goto Forum:
  


Current Time: Sun Dec 11 00:45:09 CST 2016

Total time taken to generate the page: 0.09445 seconds