6 Replies Latest reply: Aug 2, 2012 4:36 AM by DBA112 RSS

    V$SESSION_LONGOPS --- erratic?

    DBA112
      Dear Experts,

      *1 Node RAC, RDBMS - 11.1.0.7 on RHEL 5*

      I'm working on online reorganization of a huge table (1TB) using DBMS_REDEFINITION. It's been running fine over 2 days and is about 85% complete (Based on TOAD, Long ops tab from session browser) before % status disappeared from TOAD and no entry in V$SESSION_LONGOPS. However, I see SID is active and also able to see the current running statement.
      SID appears active in v$session and no entry in session longops.

      It doesn't sound normal, what could be reason for SID to suddenly disappear from session longops? Below is the current running statement captured from TOAD.. session browser.
      From sqlplus, DBMS_REDEFINITION.START_REDEF_TABLE is the procedure running.

      Appreciate your help.
      /* Formatted on 7/31/2012 11:37:20 PM (QP5 v5.163.1008.3004) */
      INSERT                                   /*+ BYPASS_RECURSIVE_CHECK APPEND  */
            INTO                                        "SCHEMA1"."SCHEMA1_TABLE_ANALYSIS_INT" (
                                                           "TABLE_EVAL_ID",
                                                           "TABLE_ID",
                                                           "TABLE_EVAL_BLK",
                                                           "TABLE_EVAL_STA_CD",
                                                           "CRE_DTTM",
                                                           "PRCS_FLG",
                                                           "CRE_USR_ID",
                                                           "UPD_DTTM",
                                                           "UPD_USR_ID",
                                                           "VERS_NUM",
                                                           "TABLE_EVAL_PRCS_CD")
         SELECT "SCHEMA1_TABLE_ANALYSIS"."TABLE_EVAL_ID",
                "SCHEMA1_TABLE_ANALYSIS"."TABLE_ID",
                "SCHEMA1_TABLE_ANALYSIS"."TABLE_EVAL_BLK",
                "SCHEMA1_TABLE_ANALYSIS"."TABLE_EVAL_STA_CD",
                "SCHEMA1_TABLE_ANALYSIS"."CRE_DTTM",
                "SCHEMA1_TABLE_ANALYSIS"."PRCS_FLG",
                "SCHEMA1_TABLE_ANALYSIS"."CRE_USR_ID",
                "SCHEMA1_TABLE_ANALYSIS"."UPD_DTTM",
                "SCHEMA1_TABLE_ANALYSIS"."UPD_USR_ID",
                "SCHEMA1_TABLE_ANALYSIS"."VERS_NUM",
                "SCHEMA1_TABLE_ANALYSIS"."TABLE_EVAL_PRCS_CD"
           FROM "SCHEMA1"."SCHEMA1_TABLE_ANALYSIS" "SCHEMA1_TABLE_ANALYSIS"
        • 1. Re: V$SESSION_LONGOPS --- erratic?
          damorgan
          It may well be that the portion of the operations tracked by Oracle in the view has completed and now it is onto another, untracked, step.

          All you've told us is DBMS_REDEFINITION is in use. We do not know about indexes, constraints, triggers, etc.

          Did you register dependent objects? Copy table dependents? Sync the interim table? Get to finish redef?

          We do not know.
          • 2. Re: V$SESSION_LONGOPS --- erratic?
            vlethakula
            Please refer below link, on what gets recorded/listed in v$session_longops

            http://www.gplivna.eu/papers/v$session_longops.htm
            • 3. Re: V$SESSION_LONGOPS --- erratic?
              DBA112
              Thanks for your reply. No I am not executing copy of dependent objects/ sync interim steps. It was just START_REDEF_TABLE procedure that was running and I have pasted the underlying insert statement that was shown as currently running. Eventually terminated without throwing any errors from sqlplus.

              I see ORA-1555 error from alert log and I believe it to be the root cause of this issue. There is about 60GB free space in undo TBS when 1555 occurred, could it be due to insufficient retention? (It's at default 900 seconds)?

              Thanks
              • 4. Re: V$SESSION_LONGOPS --- erratic?
                sb92075
                Ora DBA wrote:

                I see ORA-1555 error from alert log and I believe it to be the root cause of this issue. There is about 60GB free space in undo TBS when 1555 occurred, could it be due to insufficient retention? (It's at default 900 seconds)?
                Was any other session doing DML against table being moved & issuing COMMIT?
                • 5. Re: V$SESSION_LONGOPS --- erratic?
                  DBA112
                  I believe so, when I started reorg target table had - 3.5 million records, and as it appears some data was deleted somewhere during reorg and I see 2.3 million records now. However, during entire activity UNDO always had >50GB freely available, I'm going to set undo retention to 1 week and restart reorganization.
                  • 6. Re: V$SESSION_LONGOPS --- erratic?
                    DBA112
                    Just an update if anyone's interested to know:

                    After I increased UNDO retention to a week and reran (This time using ROW ID constant vs primary key), it completed in 21 hours reclaiming 600GB disk space.

                    Thanks all for your inputs.