1 Reply Latest reply: Aug 5, 2013 6:53 PM by learner1 RSS

    Is DBA_OBJECTS screwed up ?

    learner1

      Hi,

       

      SOURCE DB

      Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod

      PL/SQL Release 10.2.0.4.0 - Production

      CORE    10.2.0.4.0    Production

      TNS for 32-bit Windows: Version 10.2.0.4.0 - Production

      NLSRTL Version 10.2.0.4.0 - Production

       

      SOURCE O/S : Win 2003

       

       

      My TARGET DB

      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

      PL/SQL Release 11.2.0.3.0 - Production

      CORE    11.2.0.3.0    Production

      TNS for 64-bit Windows: Version 11.2.0.3.0 - Production

      NLSRTL Version 11.2.0.3.0 - Production

       

      TARGET O/S : WIN 2008 R2


      I am in process of upgrading from 10g to 11g. I am following route of DataPump, since we can afford downtime and it's less than 10 gb db. I took a datapump for all of my user schemas from 10g box and imported it in 11g. Except for 1 function compilation error everything went fine.

       

      Out of curiosity i started looking into dba_objects on 10g and 11g against user schemas and found that somehow the no. of indexes that dba_objects reports on 10g is less than 11g system.I then checked dba_indexes and surprisingly both 10g and 11g shows me same count.

       

      It gives me impression that somehow dba_objects in 10g box is not good as far as Indexes are concerned. As far as i know the moment you create a index it inserts a records in dba_indexes as well as dba_objects. ... but in my case there is a mismatch as far as indexes are concerned.

       

      One more thing that i have noticed that the indexes which are not present in 10g box in dba_objects table are SYSTEM generated index like SYS_IL0000015381C00009$$

       

      Can anyone provide me some pointers... is my assumption correct that dba_objects is not good on 10gbox.

       

      -Learner