1 2 Previous Next 26 Replies Latest reply: Apr 27, 2013 1:30 AM by 974882 RSS

    check oracle import progress

    785836
      Dear Expert,

      Any SQL query can check the progress of the import table as it take longer to perform the import. It look like hang there but no error write to log file Thanks


      Regard
      Liang
        • 1. Re: check oracle import progress
          asifkabirdba
          -- analyze how fast import is working

          select substr(sql_text,instr(sql_text,'INTO "'),30) table_name,
          rows_processed,
          round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,
          trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_min
          from sys.v_$sqlarea
          where sql_text like 'INSERT %INTO "%'
          and command_type = 2
          and open_versions > 0;


          Regards
          Asif Kabir
          • 2. Re: check oracle import progress
            Pavan Kumar
            Hi,

            SELECT
            SUBSTR(sql_text, INSTR(sql_text,\'INTO \"\'),30) table_name
            , rows_processed
            , ROUND( (sysdate-TO_DATE(first_load_time,\'yyyy-mm-dd hh24:mi:ss\'))*24*60,1) minutes
            , TRUNC(rows_processed/((sysdate-to_date(first_load_time,\'yyyy-mm-dd hh24:mi:ss\'))*24*60)) rows_per_minute
            FROM
            sys.v_$sqlarea
            WHERE
            sql_text like \'INSERT %INTO \"%\'
            AND command_type = 2
            AND open_versions > 0;

            - Pavan kumar N
            • 3. Re: check oracle import progress
              785836
              Dear Expert,

              I try this SQL statement but it show no rows selected. Why ?



              Regard
              Liang
              • 4. Re: check oracle import progress
                asifkabirdba
                May be your import is not running. Kill your current import session and run again.


                Regards
                Asif Kabir
                • 5. Re: check oracle import progress
                  Pavan Kumar
                  hi,

                  Prior to killing the session - try to check the session what it is waiting for - wait event and segment it referring to - further you can decide to go for killing session and re-starting import.

                  - Pavan kumar N
                  • 6. Re: check oracle import progress
                    785836
                    Dear Expert,

                    Will it posibile because of insert milion of row, so lt take time. i have run this SQL script before, it is return something insert. but now totally return no row. Tks


                    Regard
                    Liang
                    • 7. Re: check oracle import progress
                      785836
                      Hi,

                      How do i check the sesson wait event. Tks

                      Regard
                      Liang
                      • 8. Re: check oracle import progress
                        Pavan Kumar
                        Hi,

                        If you are working on unix - check for the logfile -where it is getting created
                        tail -f <log file> provides some information
                        check the dba_datapump_jobs and dba_datapump_sessions views

                        select
                        sid,
                        serial#,
                        sofar,
                        totalwork
                        from
                        v$session_longops
                        where sid = <sid_of_user> -- get the id from v$session.

                        For wait events with respect to session go for v$session join with v$session_waits

                        - Pavan Kumar N
                        • 9. Re: check oracle import progress
                          Anand...
                          Any SQL query can check the progress of the import table as it take longer to perform the import. It look like hang there but no error write to log file
                          Check with the below query
                          set verify off
                          col table_name for a30
                          
                          select substr(sql_text,instr(sql_text,' INTO '),30) table_name,
                                   rows_processed,
                                   round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,
                                   trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_min   
                            from   v$sqlarea
                            where   (ADDRESS,HASH_VALUE) in (select sql_address,sql_hash_value from v$session where sid= &sid_number)
                              and  command_type = 2
                              and  open_versions > 0;
                          If no row is select, then constraints would be getting created/enabled and hence import seem hanged.Check the sql_text from v$open_cursor where sid=<sid_of_imp_sess>


                          HTH
                          Anand
                          • 10. Re: check oracle import progress
                            Pavan Kumar
                            Hi anand,

                            How about indexes... :-)

                            - Pavan Kumar N
                            • 11. Re: check oracle import progress
                              Anand...
                              How about indexes...
                              Yes, sure :)

                              Anand
                              • 12. Re: check oracle import progress
                                Hemant K Chitale
                                Query V$TRANSACTION and see if USED_UREC and USED_UBLK are increasing. If you have only the import running, there would only be 1 row in V$TRANSACTION.

                                (else you'd have to join it to V$SESSION on session_addr = saddr)


                                Hemant K Chitale
                                • 13. Re: check oracle import progress
                                  785836
                                  Dear Expert

                                  I have the SQL squery as provided. Below is result. How do we read this. Tks

                                  SID SERIAL# SOFAR TOTALWORK
                                  ---------- ---------- ---------- ----------
                                  8 1894 578332 578332
                                  8 5505 578332 578332
                                  8 5510 578332 578332
                                  8 7029 578332 578332
                                  8 7151 578332 578332
                                  8 9982 33952 33952
                                  8 9982 3255 3255
                                  8 9982 3694 3694
                                  8 9982 3235 3235
                                  8 9982 3234 3234
                                  8 9982 3234 3234

                                  SID SERIAL# SOFAR TOTALWORK
                                  ---------- ---------- ---------- ----------
                                  8 9982 3236 3236
                                  8 9982 3238 3238
                                  8 9982 3237 3237
                                  8 9982 22041 22041
                                  8 9982 2720 2720
                                  8 9982 3205 3205
                                  8 9982 3205 3205
                                  8 9982 3948 3948
                                  8 9982 4296 4296
                                  8 63897 578332 578332
                                  8 63901 578332 578332

                                  SID SERIAL# SOFAR TOTALWORK
                                  ---------- ---------- ---------- ----------
                                  8 1900 578332 578332
                                  8 2022 578332 578332
                                  8 7113 578332 578332
                                  8 10124 33952 33952
                                  8 10124 3255 3255
                                  8 10124 3690 3690
                                  8 10124 3239 3239
                                  8 10124 2822 2822
                                  8 10124 3233 3233
                                  8 10124 3233 3233
                                  8 10124 3238 3238

                                  SID SERIAL# SOFAR TOTALWORK
                                  ---------- ---------- ---------- ----------
                                  8 10124 33952 33952
                                  8 10124 3235 3235
                                  8 10124 3235 3235
                                  8 10124 22041 22041
                                  8 10124 2720 2720
                                  8 10124 3205 3205
                                  8 10124 3205 3205
                                  8 10124 22041 22041
                                  8 10124 4298 4298
                                  8 10124 2289 2289
                                  8 10124 1303465 1303465

                                  SID SERIAL# SOFAR TOTALWORK
                                  ---------- ---------- ---------- ----------
                                  8 10124 236360 236360
                                  8 10124 122532 122532
                                  8 10124 1303465 1303465
                                  8 10124 477584 477584
                                  8 10124 172105 172105
                                  8 10124 1303465 1303465
                                  8 10124 268611 477584

                                  51 rows selected.
                                  • 14. Re: check oracle import progress
                                    Pavan Kumar
                                    Hi,

                                    Its monitoring the undo segments

                                    SELECT SID, SERIAL#, opname, SOFAR, TOTALWORK,
                                    ROUND(SOFAR/TOTALWORK*100,2) COMPLETE
                                    FROM V$SESSION_LONGOPS
                                    WHERE
                                    TOTALWORK != 0
                                    AND SOFAR != TOTALWORK
                                    order by 1;

                                    the above query will provide the better information

                                    - Pavan Kumar N
                                    1 2 Previous Next