1 2 Previous Next 18 Replies Latest reply: Dec 14, 2012 9:43 PM by 8398219 RSS

    Why the index count is different even import completed successfully?

    906790
      Hi All,

      I did the schema refresh successfully using expdp/impdp[11gR2] without any error. But while checking the count i can see the difference in index,please find the below for your reference and let me know the reason why its not exported /imported .

      Source :
      +++++++

      OBJECT_TYPE COUNT(1)
      ------------------- ----------
      SEQUENCE 17
      PROCEDURE 2
      DATABASE LINK 1
      LOB 10
      INDEX                       31
      TABLE 66

      Target :
      +++++++

      OBJECT_TYPE COUNT(1)
      ------------------- ----------
      SEQUENCE 17
      PROCEDURE 2
      DATABASE LINK 1
      LOB 10
      TABLE 66
      INDEX                         21


      While checking in source schema i found the below indexes are not there in target schema.
      ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

      OWNER OBJECT_TYPE OBJECT_NAME CREATED
      ------------------------------ ------------------- ----------------------------------- ---------
      EMP INDEX SYS_IL0000097754C00005$$ 01-MAR-12
      EMP INDEX SYS_IL0000097735C00009$$ 01-MAR-12
      EMP INDEX SYS_IL0000097735C00007$$ 01-MAR-12
      EMP INDEX SYS_IL0000097740C00005$$ 01-MAR-12
      EMP INDEX SYS_IL0000097743C00007$$ 01-MAR-12
      EMP INDEX SYS_IL0000122314C00004$$ 06-APR-12
      EMP INDEX SYS_IL0000123452C00007$$ 16-APR-12
      EMP INDEX SYS_IL0000097746C00007$$ 01-MAR-12
      EMP INDEX SYS_IL0000097749C00009$$ 01-MAR-12
      EMP INDEX SYS_IL0000097749C00007$$ 01-MAR-12

      Thanks
      Bala
        • 1. Re: Why the index count is different even import completed successfully?
          Richard Harrison .
          Hi,
          Try

          purge DBA_RECYCLEBIN;

          in the source db and redo the counts - I've a feeling that even dropped lob indexes still appear in some of the dictionary views.

          Regards,
          Harry
          • 2. Re: Why the index count is different even import completed successfully?
            906790
            Hi,

            SQL> select object_type,count(1) from dba_objects where owner = 'SEGMREF' and object_name not like '%BIN%' group by object_type;


            OBJECT_TYPE COUNT(1)
            ------------------- ----------
            SEQUENCE 17
            PROCEDURE 2
            DATABASE LINK 1
            LOB 10
            INDEX 31
            TABLE 66

            6 rows selected.

            SQL> SQL> purge dba_recyclebin;

            DBA Recyclebin purged.

            SQL> select object_type,count(1) from dba_objects where owner = 'SEGMREF' and object_name not like '%BIN%' group by object_type;

            OBJECT_TYPE COUNT(1)
            ------------------- ----------
            SEQUENCE 17
            PROCEDURE 2
            DATABASE LINK 1
            LOB 10
            INDEX 31
            TABLE 66

            6 rows selected.
            • 3. Re: Why the index count is different even import completed successfully?
              Srini Chavali-Oracle
              These are LOB indexes. Can you pl post the complete expdp and impdp commands used ?

              HTH
              Srini
              • 4. Re: Why the index count is different even import completed successfully?
                Richard Harrison .
                Hi,
                Are the indexes on tables in another schema?

                Cheers,
                Harry
                • 5. Re: Why the index count is different even import completed successfully?
                  906790
                  Hi Srini,

                  Please find the commands used for export and import this schema refresh.

                  expdp sys/****@AAAA directory=expdp dumpfile=emp_28nov2012.dmp schemas=emp exclude=statistics logfile=emp_exp.log

                  impdp sys/****@BBBB directory=expdp dumpfile=emp_28nov2012.dmp remap_schema=emp:emp logfile=emp_imp.log


                  Thanks
                  Bala
                  • 6. Re: Why the index count is different even import completed successfully?
                    Srini Chavali-Oracle
                    As the documentation explicitly states, do not use the SYS account for expdp and impdp. Pl retry the operation with SYSTEM account.

                    See the first "Note" sections here

                    http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm#i1012781
                    http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_import.htm#i1012504

                    HTH
                    Srini
                    • 7. Re: Why the index count is different even import completed successfully?
                      906790
                      Hi Srini,

                      Thanks for the note, Do you think if i take export using system account will it take LOB objects [LOBSegment,LOBIndex] ?

                      Please find the below for your reference and it seems to be LOB objects are created in target as well but in different name. i am really confused then why the index count is differ in dba_object view.

                      Source :
                      +++++++++++++++
                      SQL> select segment_name,segment_type,owner from dba_segments where owner='EMP' and segment_type like 'LOB%';

                      SEGMENT_NAME SEGMENT_TYPE OWNER
                      ------------------------------ ------------------ ------------------------------
                      SYS_IL0000097735C00007$$ LOBINDEX EMP
                      SYS_IL0000097735C00009$$ LOBINDEX EMP
                      SYS_IL0000097740C00005$$ LOBINDEX EMP
                      SYS_IL0000097743C00007$$ LOBINDEX EMP
                      SYS_IL0000097746C00007$$ LOBINDEX EMP
                      SYS_IL0000097749C00007$$ LOBINDEX EMP
                      SYS_IL0000097749C00009$$ LOBINDEX EMP
                      SYS_IL0000097754C00005$$ LOBINDEX EMP
                      SYS_IL0000123452C00007$$ LOBINDEX EMP
                      SYS_LOB0000097735C00007$$ LOBSEGMENT EMP
                      SYS_LOB0000097735C00009$$ LOBSEGMENT EMP
                      SYS_LOB0000097740C00005$$ LOBSEGMENT EMP
                      SYS_LOB0000097743C00007$$ LOBSEGMENT EMP
                      SYS_LOB0000097746C00007$$ LOBSEGMENT EMP
                      SYS_LOB0000097749C00007$$ LOBSEGMENT EMP
                      SYS_LOB0000097749C00009$$ LOBSEGMENT EMP
                      SYS_LOB0000097754C00005$$ LOBSEGMENT EMP
                      SYS_LOB0000123452C00007$$ LOBSEGMENT EMP

                      18 rows selected.


                      Target
                      +++++++++

                      SEGMENT_NAME SEGMENT_TYPE OWNER
                      ----------------------------------- ------------------ ------------------------------
                      SYS_IL0000169237C00005$$ LOBINDEX SEGMREF
                      SYS_IL0000169240C00007$$ LOBINDEX SEGMREF
                      SYS_IL0000169243C00007$$ LOBINDEX SEGMREF
                      SYS_IL0000169243C00009$$ LOBINDEX SEGMREF
                      SYS_IL0000169248C00007$$ LOBINDEX SEGMREF
                      SYS_IL0000169248C00009$$ LOBINDEX SEGMREF
                      SYS_IL0000169253C00005$$ LOBINDEX SEGMREF
                      SYS_IL0000169259C00007$$ LOBINDEX SEGMREF
                      SYS_IL0000169262C00007$$ LOBINDEX SEGMREF
                      SYS_LOB0000169237C00005$$ LOBSEGMENT SEGMREF
                      SYS_LOB0000169240C00007$$ LOBSEGMENT SEGMREF

                      SEGMENT_NAME SEGMENT_TYPE OWNER
                      ----------------------------------- ------------------ ------------------------------
                      SYS_LOB0000169243C00007$$ LOBSEGMENT SEGMREF
                      SYS_LOB0000169243C00009$$ LOBSEGMENT SEGMREF
                      SYS_LOB0000169248C00007$$ LOBSEGMENT SEGMREF
                      SYS_LOB0000169248C00009$$ LOBSEGMENT SEGMREF
                      SYS_LOB0000169253C00005$$ LOBSEGMENT SEGMREF
                      SYS_LOB0000169259C00007$$ LOBSEGMENT SEGMREF
                      SYS_LOB0000169262C00007$$ LOBSEGMENT SEGMREF

                      18 rows selected.

                      Thanks
                      Bala
                      • 8. Re: Why the index count is different even import completed successfully?
                        Srini Chavali-Oracle
                        Pl retry expdp/impdp with SYSTEM and post the results here. You are exporting the EMP schema, whereas in your sql to count objects, you are using SEGMREF - is this intentional ?

                        HTH
                        Srini
                        • 9. Re: Why the index count is different even import completed successfully?
                          User286067
                          903787 wrote:
                          While checking in source schema i found the below indexes are not there in target schema.
                          check of those partitions are empty?

                          Raj

                          Edited by: rjamya on Nov 29, 2012 11:43 AM
                          • 10. Re: Why the index count is different even import completed successfully?
                            906790
                            Hi Srini,

                            Sorry, i missed to replace SEGMREF to EMP since its production schema name. I tried export using system and import using system user but no change in object count.

                            Target :
                            +++++++++

                            SQL> select object_type,count(1) from dba_objects where owner = 'TEST' and object_name not like '%BIN%' group by object_type;

                            OBJECT_TYPE COUNT(1)
                            ------------------- ----------
                            SEQUENCE 17
                            PROCEDURE 2
                            DATABASE LINK 1
                            LOB 10
                            TABLE 66
                            INDEX 21

                            6 rows selected.


                            EX : Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
                            Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 11:40:52


                            Thanks
                            • 11. Re: Why the index count is different even import completed successfully?
                              Richard Harrison .
                              Hi,
                              Possibly something to do with deferred segment creation - which table do these lob objects belong to - is it empty?

                              what if you try

                              ALTER TABLE xxxx ALLOCATE EXTENT;

                              Cheers,
                              Harry
                              • 12. Re: Why the index count is different even import completed successfully?
                                906790
                                Hi All,

                                Please find the below, whether export/import of LOB segments will change the segment name ?


                                Source :
                                ++++++++++++

                                OWNER TABLE_NAME COLUMN_NAME SEGMENT_NAME INDEX_NAME
                                ------------- ------------------------- ------------------------- ------------------------------ ------------------------------
                                EMP SGMNT SGMNT_XML SYS_LOB0000097746C00007$$ SYS_IL0000097746C00007$$
                                EMP SGMNT_20110913 SGMNT_XML SYS_LOB0000097743C00007$$ SYS_IL0000097743C00007$$
                                EMP SGMNT_BKP0416 SGMNT_XML SYS_LOB0000123452C00007$$ SYS_IL0000123452C00007$$
                                EMP SGMNT_ND SGMNT_ND_XML SYS_LOB0000097749C00007$$ SYS_IL0000097749C00007$$
                                EMP SGMNT_ND SGMNT_ND_CHLD SYS_LOB0000097749C00009$$ SYS_IL0000097749C00009$$
                                EMP SGMNT_ND_20110913 SGMNT_ND_CHLD SYS_LOB0000097735C00009$$ SYS_IL0000097735C00009$$
                                EMP SGMNT_ND_20110913 SGMNT_ND_XML SYS_LOB0000097735C00007$$ SYS_IL0000097735C00007$$
                                EMP SGMNT_RL SGMNT_RL_CHLD SYS_LOB0000097754C00005$$ SYS_IL0000097754C00005$$
                                EMP SGMNT_RL_20110913 SGMNT_RL_CHLD SYS_LOB0000097740C00005$$ SYS_IL0000097740C00005$$



                                Target :
                                ++++++++++++


                                OWNER TABLE_NAME COLUMN_NAME SEGMENT_NAME INDEX_NAME
                                ------------ -------------------- --------------------------------------- ------------------------------ ------------------------------
                                EMP SGMNT SGMNT_XML SYS_LOB0000169262C00007$$ SYS_IL0000169262C00007$$
                                EMP SGMNT_20110913 SGMNT_XML SYS_LOB0000169240C00007$$ SYS_IL0000169240C00007$$
                                EMP SGMNT_BKP0416 SGMNT_XML SYS_LOB0000169259C00007$$ SYS_IL0000169259C00007$$
                                EMP SGMNT_ND SGMNT_ND_XML SYS_LOB0000169243C00007$$ SYS_IL0000169243C00007$$
                                EMP SGMNT_ND SGMNT_ND_CHLD SYS_LOB0000169243C00009$$ SYS_IL0000169243C00009$$
                                EMP SGMNT_ND_20110913 SGMNT_ND_CHLD SYS_LOB0000169248C00009$$ SYS_IL0000169248C00009$$
                                EMP SGMNT_ND_20110913 SGMNT_ND_XML SYS_LOB0000169248C00007$$ SYS_IL0000169248C00007$$
                                EMP SGMNT_RL SGMNT_RL_CHLD SYS_LOB0000169253C00005$$ SYS_IL0000169253C00005$$
                                EMP SGMNT_RL_20110913 SGMNT_RL_CHLD SYS_LOB0000169237C00005$$ SYS_IL0000169237C00005$$
                                • 13. Re: Why the index count is different even import completed successfully?
                                  Srini Chavali-Oracle
                                  Yes, the names are internally generated, so do not expect the LOB index names to be identical

                                  HTH
                                  Srini
                                  • 14. Re: Why the index count is different even import completed successfully?
                                    906790
                                    Hi Srini,

                                    ok, I accept it. But why the index count mismatch between source & target schemas? Any idea ?

                                    Thanks
                                    Bala
                                    1 2 Previous Next