1 2 Previous Next 18 Replies Latest reply: Mar 14, 2013 12:15 AM by yxes2013 RSS

    Impdp Extra Objects

    yxes2013
      Hi all,

      10gR2
      SLES 11

      I have a weird situation.
      I am importing a impdp dump.
      My schema/users and tablespaces have been pre-created. Plus the tables themselves.
      My question is why is that there are objects that are created which is not part of my impdp dump? Which is not existing before in my database.
      These tables have "weird" names too :(
      SYS     TABLE     SYSNT1jpytTa5hBngQAIKMxAoqA==
      SYS     TABLE     SYSNT1jpytTa7hBngQAIKMxAoqA==
      SYS     TABLE     SYSNT1jpytTa9hBngQAIKMxAoqA==
      SYS     INDEX     SYS_C005394
      SYS     LOB     SYS_LOB0000052514C00144$$
      SYS     LOB     SYS_LOB0000052514C00139$$
      SYS     LOB     SYS_LOB0000052514C00136$$
      SYS     LOB     SYS_LOB0000052514C00135$$
      SYS     LOB     SYS_LOB0000052514C00072$$
      SYS     LOB     SYS_LOB0000052514C00070$$
      SYS     LOB     SYS_LOB0000052514C00034$$
      SYS     INDEX     SYS_C005395
      SYS     INDEX     SYS_C005396
      SYS     INDEX     SYS_C005397
      SYS     VIEW     AQ$KUPC$DATAPUMP_QUETAB_R
      How do I know if they exist in my dump file?
      How do I know if the LOBs here are part of the unknown tables?


      Thanks a lot,

      zxy
        • 1. Re: Impdp Extra Objects
          Salman Qureshi
          Hi,
          You need to purge your dba/user_recyclebin to avoid objects in recyclebin in export file. These are names of objects in recyclebin (first introduced in 10g).
          How do I know if they exist in my dump file?
          If these exist in recyclebin, these will also exist in your dump file if you do schema or database level export
          How do I know if the LOBs here are part of the unknown tables?
          DBA_LOBS will show you this information. But if these are from recyclebin, you will not be able to see it
          select * from dba_recyclebin; -- To see objects in recyclebin
          purge dba_recyclebin; -- To purge the dba recyclebin (recycle bin purging at database level)
          Salman

          Edited by: Salman Qureshi on Feb 26, 2013 3:49 PM
          • 2. Re: Impdp Extra Objects
            yxes2013
            Thanks salman :) you are good
            • 3. Re: Impdp Extra Objects
              yxes2013
              Huhuhu :( but my recycle bin is empty so what are these kinds of table?
              • 4. Re: Impdp Extra Objects
                asahide
                Hi,
                How do I know if they exist in my dump file?
                I think that you can create sqlfile like follows.
                $ impdp directory=expdir dumpfile=myexp.dmp sqlfile=sample.sql
                Then, You can check sample.sql whether or not there is create SQL.

                Regards,
                • 5. Re: Impdp Extra Objects
                  yxes2013
                  Yes I know, :P I already did that but when I search those objects they are not in my sql file list. Are these a performance table?
                  • 6. Re: Impdp Extra Objects
                    asahide
                    Hi,
                    Can you clean up your schema, and re-try impdp ?
                    And Have you ever stopped/killed in the middle of impdp ?

                    Regards,
                    • 7. Re: Impdp Extra Objects
                      yxes2013
                      yes i did that 3 times, and these similar objects are being created. But i can not view them. I thought it is a security virus :(
                      How do you investigate a suspicious objects? I tried viewing them but they have no rows. similar to a dynamic performance tables.
                      • 8. Re: Impdp Extra Objects
                        Osama_Mustafa
                        Please check the below link
                        http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:6729304326802
                        • 9. Re: Impdp Extra Objects
                          yxes2013
                          The link is about naming convention of objects :(


                          Why is that :
                          SQL> select * from dba_lobs where table_name like 'SYSNT1%';
                          
                          no rows selected
                          So the lobs here are not related with the tables?

                          It seems that these object can not be qualified in the where condition?

                          How do I find other link details for related object views for the ff. object?
                          SYS      SYS_LOB0000054376C00034$$     LOB
                          • 10. Re: Impdp Extra Objects
                            Girish Sharma
                            Try to check with this query :

                            SELECT owner, table_name, column_name
                            FROM dba_lobs
                            WHERE segment_name = 'SYS_LOB<<identifier>>$$'

                            Edited by: Girish Sharma on Feb 26, 2013 2:23 PM
                            For example :

                            Source:http://stackoverflow.com/questions/531488/understanding-lob-segments-sys-lob-in-oracle
                            • 11. Re: Impdp Extra Objects
                              yxes2013
                              SQL> SELECT owner, table_name, column_name
                              2 FROM dba_lobs
                              3 WHERE segment_name = 'SYS_LOB<<identifier>>$$';

                              no rows selected
                              • 12. Re: Impdp Extra Objects
                                Girish Sharma
                                For Example :

                                SELECT owner, table_name, column_name
                                FROM dba_lobs
                                WHERE segment_name = 'SYS_LOB0000052514C00144$$';

                                and then for next like :

                                SELECT owner, table_name, column_name
                                FROM dba_lobs
                                WHERE segment_name = 'SYS_LOB0000052514C00139$$';

                                Regards
                                Girish Sharma
                                • 13. Re: Impdp Extra Objects
                                  yxes2013
                                  SQL> SELECT owner, table_name, column_name
                                  2 FROM dba_lobs
                                  3 WHERE segment_name = 'SYS_LOB0000054376C00147$$';

                                  OWNER TABLE_NAME
                                  ------------------------------ ------------------------------
                                  COLUMN_NAME
                                  --------------------------------------------------------------------------------
                                  SYS AQ$_KUPC$DATAPUMP_QUETAB_P
                                  USER_PROP
                                  • 14. Re: Impdp Extra Objects
                                    yxes2013
                                    SQL> SELECT owner, table_name, column_name
                                      2  FROM dba_lobs
                                      3  WHERE segment_name in(
                                      4  'SYS_LOB0000054376C00147$$',
                                      5  'SYS_LOB0000054376C00142$$',
                                      6  'SYS_LOB0000054376C00139$$',
                                      7  'SYS_LOB0000054376C00138$$',
                                      8  'SYS_LOB0000054376C00099$$',
                                      9  'SYS_LOB0000054376C00088$$',
                                     10  'SYS_LOB0000054376C00073$$',
                                     11  'SYS_LOB0000054376C00071$$',
                                     12  'SYS_LOB0000054376C00034$$');
                                    
                                    OWNER                          TABLE_NAME
                                    ------------------------------ ------------------------------
                                    COLUMN_NAME
                                    --------------------------------------------------------------------------------
                                    SYS                            AQ$_KUPC$DATAPUMP_QUETAB_P
                                    TREAT("USER_DATA" AS "KUPC$_API_ACK")."ERROR"."ERRSTAT_INFO"
                                    
                                    SYS                            AQ$_KUPC$DATAPUMP_QUETAB_P
                                    TREAT("USER_DATA" AS "KUPC$_MASTERERROR")."ERROR"."ERRSTAT_INFO"
                                    
                                    SYS                            AQ$_KUPC$DATAPUMP_QUETAB_P
                                    TREAT("USER_DATA" AS "KUPC$_MASTERJOBINFO")."JOBINFO"."ERRSTAT_INFO"
                                    
                                    
                                    OWNER                          TABLE_NAME
                                    ------------------------------ ------------------------------
                                    COLUMN_NAME
                                    --------------------------------------------------------------------------------
                                    SYS                            AQ$_KUPC$DATAPUMP_QUETAB_P
                                    TREAT("USER_DATA" AS "KUPC$_DATA_FILTER")."FILTER_VALUE_L"
                                    
                                    SYS                            AQ$_KUPC$DATAPUMP_QUETAB_P
                                    TREAT("USER_DATA" AS "KUPC$_METADATA_FILTER")."CLOB_VALUE"
                                    
                                    SYS                            AQ$_KUPC$DATAPUMP_QUETAB_P
                                    TREAT("USER_DATA" AS "KUPC$_BAD_FILE")."BAD_ROW"
                                    
                                    
                                    OWNER                          TABLE_NAME
                                    ------------------------------ ------------------------------
                                    COLUMN_NAME
                                    --------------------------------------------------------------------------------
                                    SYS                            AQ$_KUPC$DATAPUMP_QUETAB_P
                                    TREAT("USER_DATA" AS "KUPC$_TABLE_DATA_ARRAY")."TABLE_DATAS"
                                    
                                    SYS                            AQ$_KUPC$DATAPUMP_QUETAB_P
                                    TREAT("USER_DATA" AS "KUPC$_WORKERERROR")."ERROR"."ERRSTAT_INFO"
                                    
                                    SYS                            AQ$_KUPC$DATAPUMP_QUETAB_P
                                    USER_PROP
                                    
                                    
                                    9 rows selected.
                                    1 2 Previous Next