10 Replies Latest reply: Dec 16, 2013 10:44 AM by TSharma-Oracle RSS

    state is UNDEFINED dba_datapump_jobs

    Prakash_dba

      Hi,

       

      oracle version : 10.2.0.5

      i am unable to find export master table

       

      PINDB> select
         o.status,
         o.object_id,
        2    3    4     o.object_type,
         o.owner||'.'||object_name "OWNER.OBJECT"
        5    6  from
        7     dba_objects o,
         dba_datapump_jobs j
      where
        8    9   10     o.owner=j.owner_name
      and
      11   12     o.object_name=j.job_name
      and
      13   14     j.job_name not like 'BIN$%'
      15  order by 4, 2;

      no rows selected

       

      but the below  query says the export state is undefined

      PINDB> select * from dba_datapump_jobs;

      OWNER_NAME                     JOB_NAME                       OPERATION                      JOB_MODE
      ------------------------------ ------------------------------ ------------------------------ ------------------------------
      STATE                              DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
      ------------------------------ ---------- ----------------- -----------------
      SYS                            SYS_EXPORT_FULL_01             EXPORT                         FULL
      UNDEFINED                               0                 1                 2

       

       

      and from os level i am unable to see any export backup

       

      PINDB> !

      youtele:/home/oracle=> ps -ef | grep export

        oracle 12954 12902  0 15:09:53 pts/4     0:00 grep export

       

      Please can you help me to clean up this job

       

      Thanks

      PGR

        • 1. Re: state is UNDEFINED dba_datapump_jobs
          sb92075

          why do you think any action is required?

          • 2. Re: state is UNDEFINED dba_datapump_jobs
            Anar Godjaev

            Hi can you please paste result:

             

            ps -ef | grep expdp

             

            Note: can you please more explain this issue...

             

            Thank you

            • 3. Re: state is UNDEFINED dba_datapump_jobs
              Prakash_dba

              Hi,

               

              Thanks for the update

              i am guessing it may be effect the future backup , is  i am wrong here ?

               

              Thanks

              PGR

              • 4. Re: state is UNDEFINED dba_datapump_jobs
                Prakash_dba

                Hi,

                 

                ps -ef | grep export shows nothing

                 

                PINDB:/home/oracle=> ps -ef | grep expdp

                  oracle  5917 25625  0 15:44:26 pts/4     0:00 grep expdp

                 

                i am able to see the output from dba_datapump_jobs and i need to clean this

                 

                PINDB> select * from dba_datapump_jobs;

                OWNER_NAME                     JOB_NAME                       OPERATION                      JOB_MODE
                ------------------------------ ------------------------------ ------------------------------ ------------------------------
                STATE                              DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
                ------------------------------ ---------- ----------------- -----------------
                SYS                            SYS_EXPORT_FULL_01             EXPORT                         FULL
                UNDEFINED                               0                 1                 2

                 

                Thanks

                PGR

                • 5. Re: state is UNDEFINED dba_datapump_jobs
                  Anar Godjaev

                  HI,


                  export it is not backup strategy. Export/import does not "restore" anything. When you import, the object being imported is a new object. It gets assigned a different OBJECT_ID. So to the database, this objects isn't the old one. Export only exports the objects of the database along with the CREATE TABLESPACE DDL statements.

                   

                  Thank you

                  • 6. Re: state is UNDEFINED dba_datapump_jobs
                    Anar Godjaev

                    select 'DROP TABLE '||owner||'.'||table_name||';'
                    from dba_tables
                    where table_name like '%SYS%EXPORT%'


                    SQL> /
                    'DROPTABLE'||OWNER||'.'||TABLE_NAME||';'
                    -------------------------------------------------------------------------
                    DROP TABLE SYSTEM.SYS_EXPORT_FULL_01;

                     

                    SQL> DROP TABLE SYSTEM.SYS_EXPORT_FULL_01;

                    Table dropped

                     

                    Update :

                     

                    Oracle support id : How to Cleanup Rows In DBA_DATAPUMP_JOBS For Stopped Export/Import Jobs When Dumpfile Is Not There Or Corrupted (Doc ID 294618.1)

                     

                    Update:

                     

                    Oracle in World: How to cleanup orphaned datapump jobs from DBA_DATAPUMP_JOBS

                    • 7. Re: state is UNDEFINED dba_datapump_jobs
                      Prakash_dba

                      Hi,

                       

                      Thanks for the update

                       

                      1)PINDB> select 'DROP TABLE '||owner||'.'||table_name||';' from dba_tables where table_name like '%SYS%EXPORT%';

                      no rows selected

                      2) i refered Doc ID 294618.1 but not helped me

                      3)i refered the link Oracle in World: How to cleanup orphaned datapump jobs from DBA_DATAPUMP_JOBS but did not help me

                      PINDB> DECLARE
                        2  job1 NUMBER;
                        3  BEGIN
                        4  job1 := DBMS_DATAPUMP.ATTACH('SYS_EXPORT_FULL_01','SYS');
                        5  DBMS_DATAPUMP.STOP_JOB (job1);
                        6  END;
                        7  /
                      DECLARE
                      *
                      ERROR at line 1:
                      ORA-31626: job does not exist
                      ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
                      ORA-06512: at "SYS.DBMS_DATAPUMP", line 938
                      ORA-06512: at "SYS.DBMS_DATAPUMP", line 3304
                      ORA-06512: at line 4

                       

                      please can anyone share the resolution

                       

                      Thanks

                      PGR

                      • 8. Re: state is UNDEFINED dba_datapump_jobs
                        TSharma-Oracle

                        Why do you think it would cause problem in future job? Did you try re-run the job? If you are worried it would affect your performance, you can always kill the job right away using KILL_JOB?

                        Sometimes the best way to clear the doubt is to TRY.

                        • 9. Re: state is UNDEFINED dba_datapump_jobs
                          Prakash_dba

                          ok thanks for the update, As it a scheduled job i will let you know  the status  after next run

                           

                          Thanks to all for the inputs and time

                          i will update this thread after the next run of this job

                           

                          Thanks

                          PGR 

                          • 10. Re: state is UNDEFINED dba_datapump_jobs
                            TSharma-Oracle

                            Like I said , you can run it manually anytime and check the status. If it runs , you can just kill the job. In this case you will know whether you have a successful execution of the job. You so not need to run the full job. If you see that it started and running, you can just kill it. Why to wait until failure?