1 2 3 Previous Next 37 Replies Latest reply: Feb 2, 2013 10:06 AM by sb92075 Go to original post RSS
      • 30. Re: V$sessions
        Catfive Lander
        You do have to start doing some work yourself, at some point. This is now pretty basic SQL writing.

        The code I supplied (sb merely re-formatted it) is correct for 11gR2. I've also checked the documentation, and that column is there for 10gR2, too, as this link shows

        So what version of Oracle are you using?

        You can, of course, always remove the Q table from anywhere in that query, since it is the join from S to Q on SQL_CHILD_NUMBER that is causing the problem. Remove Q from the "from" clause, and remove all mention of q.<fields> from the WHERE clause and you will still have a query that correctly joins SESSIONS to LONGOPS and PROCESSES.
        • 31. Re: V$sessions
          jgarry
          >
          So what version of Oracle are you using?
          He should tell that every time, but get a clue from his Recent Questions.
          • 32. Re: V$sessions
            Catfive Lander
            I'm afraid I've just worked that out :-(
            • 33. Re: V$sessions
              yxes2013
              Sorry I forgot to mention... we are using 9.2.0.6 database.

              Hi Cat, Jq, Sb and all....can you give me the counterpart sql for 9.2.0.6 V$session?


              Another question :)

              Can this script be used to identify what the batch job is doing?

              Our users often run a form batch job. Most of the time it gets completed average in 1 minute or less. But sometimes it takes more than 1 hr or hanging. I want to check if the job is still running or hanging. I want to see if it has i/o, cpu, memory activity. So if not I can kill it or abort it.


              Thanks

              Edited by: yxes2013 on 23.1.2013 1:10
              • 34. Re: V$sessions
                jgarry
                It's been way too long since I've used the 9.2 OEM, but I seem to recall it did have some usefulness.
                • 35. Re: V$sessions
                  sb92075
                  yxes2013 wrote:
                  Sorry I forgot to mention... we are using 9.2.0.6 database.

                  Hi Cat, Jq, Sb and all....can you give me the counterpart sql for 9.2.0.6 V$session?


                  Another question :)

                  Can this script be used to identify what the batch job is doing?

                  Our users often run a form batch job. Most of the time it gets completed average in 1 minute or less. But sometimes it takes more than 1 hr or hanging. I want to check if the job is still running or hanging. I want to see if it has i/o,
                  SQL> desc v$sess_io
                   Name                                      Null?    Type
                   ----------------------------------------- -------- ----------------------------
                   SID                                                NUMBER
                   BLOCK_GETS                                         NUMBER
                   CONSISTENT_GETS                                    NUMBER
                   PHYSICAL_READS                                     NUMBER
                   BLOCK_CHANGES                                      NUMBER
                   CONSISTENT_CHANGES                                 NUMBER
                   OPTIMIZED_PHYSICAL_READS                           NUMBER
                  when any session is "doing something", one or more of the columns in this view will be increasing in value
                  • 36. Re: V$sessions
                    yxes2013
                    Thanks Sb :) hmmm another tips learned.

                    What is the counterpart V$ views to check if the program has still CPU or Memory activity?


                    By the way ....I have been asking this questions before in the forum but I did not get direct answer yet.

                    In linux to check how much memory is used by the OS or server itself the command is:

                    # cat /proc/meminfo | grep Active , then you just less it from the Total Ram to get the free memory.

                    What is the corresponding command in the Oracle database? To show free and used memory from SGA?


                    Thanks a lot,

                    Edited by: yxes2013 on 2.2.2013 7:53
                    • 37. Re: V$sessions
                      sb92075
                      yxes2013 wrote:
                      Thanks Sb :) hmmm another tips learned.

                      What is the counterpart V$ views to check if the program has still CPU or Memory activity?
                      V_$ARCHIVE_DEST_STATUS
                      V_$ASM_DISKGROUP_STAT
                      V_$ASM_DISK_IOSTAT
                      V_$ASM_DISK_STAT
                      V_$ASM_VOLUME_STAT
                      V_$BUFFER_POOL_STATISTICS
                      V_$CELL_STATE
                      V_$CLIENT_STATS
                      V_$CPOOL_CC_STATS
                      V_$CPOOL_STATS
                      V_$MYSTAT
                      V_$OSSTAT
                      V_$PGASTAT
                      V_$PQ_SESSTAT
                      V_$PQ_SYSSTAT
                      V_$PQ_TQSTAT
                      V_$PX_PROCESS_SYSSTAT
                      V_$PX_SESSTAT
                      V_$QMON_COORDINATOR_STATS
                      V_$QMON_SERVER_STATS
                      V_$QMON_TASK_STATS
                      V_$RECOVERY_FILE_STATUS
                      V_$RECOVERY_STATUS
                      V_$RESULT_CACHE_STATISTICS
                      V_$RMAN_STATUS
                      V_$ROLLSTAT
                      V_$RULE_SET_AGGREGATE_STATS
                      V_$SEGMENT_STATISTICS
                      V_$SEGSTAT
                      V_$SEGSTAT_NAME
                      V_$SERVICE_STATS
                      V_$SERV_MOD_ACT_STATS
                      V_$SESSTAT
                      V_$SGASTAT
                      V_$SQLSTATS
                      V_$SQLSTATS_PLAN_HASH
                      V_$SQL_CS_STATISTICS
                      V_$SQL_PLAN_STATISTICS
                      V_$SQL_PLAN_STATISTICS_ALL
                      V_$STATISTICS_LEVEL
                      V_$STATNAME
                      V_$STREAMS_POOL_STATISTICS
                      V_$SUBSCR_REGISTRATION_STATS
                      V_$SYSSTAT
                      V_$TEMPSTAT
                      V_$UNDOSTAT
                      V_$WAITSTAT
                      V_$WLM_PC_STATS
                      V_$DNFS_STATS
                      V_$DYNAMIC_REMASTER_STATS
                      V_$ENQUEUE_STAT
                      V_$ENQUEUE_STATISTICS
                      V_$FILESTAT
                      V_$FLASHBACK_DATABASE_STAT
                      V_$FS_FAILOVER_STATS
                      V_$IOSTAT_CONSUMER_GROUP
                      V_$IOSTAT_FILE
                      V_$IOSTAT_FUNCTION
                      V_$IOSTAT_FUNCTION_DETAIL
                      V_$IOSTAT_NETWORK
                      V_$IO_CALIBRATION_STATUS
                      V_$LOADISTAT
                      V_$LOADPSTAT
                      V_$LOBSTAT
                      V_$LOGMNR_STATS
                      V_$LOGSTDBY_STATE
                      V_$LOGSTDBY_STATS
                      V_$DATAGUARD_STATS
                      V_$DATAGUARD_STATUS
                      [oracle@localhost ~]$


                      >
                      >
                      By the way ....I have been asking this questions before in the forum but I did not get direct answer yet.

                      In linux to check how much memory is used by the OS or server itself the command is:
                      [oracle@localhost ~]$ free
                                   total       used       free     shared    buffers     cached
                      Mem:        909120     758824     150296          0      63368     397864
                      -/+ buffers/cache:     297592     611528
                      Swap:      1735012     179944    1555068
                      >
                      # cat /proc/meminfo | grep Active , then you just less it from the Total Ram to get the free memory.

                      What is the corresponding command in the Oracle database? To show free and used memory from SGA?
                      V_$SGASTAT

                      >
                      Thanks a lot,

                      Edited by: yxes2013 on 2.2.2013 7:53
                      1 2 3 Previous Next