10 Replies Latest reply: Sep 14, 2009 5:10 PM by 301084 RSS

    How to query uncommited transactions

    yakub21
      Hi Does anyone know how to query Oracle database from SQL*Plus to view uncommitted transactions?

      Thanks
        • 1. Re: How to query uncommited transactions
          Aman....
          Check the transactions from V$transaction.For those whose status is shown as Active, they are still active.This is assuming that by saying "uncommitted", you meant still going on transactions.
          Aman....
          • 2. Re: How to query uncommited transactions
            247514
            view

            v$transaction show the list of uncommitted transaction in system.
            • 3. Re: How to query uncommited transactions
              damills
              I use this to join transaction and session:

              select t.start_time,s.sid,s.serial#,s.username,s.status,s.schemaname,
              s.osuser,s.process,s.machine,s.terminal,s.program,s.module,to_char(s.logon_time,'DD/MON/YY HH24:MI:SS') logon_time
              from v$transaction t, v$session s
              where s.saddr = t.ses_addr
              order by start_time;
              • 4. Re: How to query uncommited transactions
                669771
                Hi,

                set lines 250
                column start_time format a20
                column sid format 999
                column serial# format 999999
                column username format a10
                column status format a10
                column schemaname format a10
                column osuser format a10
                column process format a10
                column machine format a15
                column terminal format a10
                column program format a25
                column module format a10
                column logon format a20

                prompt ####################################################
                prompt # current transactions:
                prompt ####################################################
                select t.start_time,s.sid,s.serial#,s.username,s.status,s.schemaname,
                s.osuser,s.process,s.machine,s.terminal,s.program,s.module,to_char(s.logon_time,'DD/MON/YY HH24:MI:SS') logon_time
                from v$transaction t, v$session s
                where s.saddr = t.ses_addr
                order by start_time;

                Regards,
                Tom
                http://asktom.cz
                • 5. Re: How to query uncommited transactions
                  94799
                  You called your blog AskTom?
                  • 6. Re: How to query uncommited transactions
                    Centinul
                    That's kind of humorous... :)
                    • 7. Re: How to query uncommited transactions
                      669771
                      Hi,

                      yes, my name is Tom so I called my blog asktom.cz. I have other web oracledba.cz only for Czech people in czech language.

                      But dont confuse my web with Tom Kyte's web ;o)

                      Tom Kyte is guru and I am trying to be at least from 1/2 good as he is...

                      btw. What's about the question there? Is it solved or not?

                      Regards,
                      Tom
                      • 8. Re: How to query uncommited transactions
                        6363
                        Why on earth would we confuse AskTom with AskTom?
                        • 9. Re: How to query uncommited transactions
                          301084
                          Anyone running korn shell is offered this variation I pieced together. It loops and shows the active txn's every five seconds. If your $TERM variable is set to some model of a DEC VT and your terminal emulator understands the codes, it should move the cursor back up to the top left and refresh the display at 5 second intervals. We set the client_identifier in a login script and the value has spaces in it. So the parsing you see in the while loop is necessary.

                          Here the script:

                          #!/bin/ksh
                          #****************************************************************************************
                          #
                          # Name: montxn
                          #
                          # Purpose: every 5 seconds, show incomplete transactions, possibly uncommitted
                          #
                          # Author: Carl Bowman
                          #
                          # Date: 4/22/2009
                          #
                          #****************************************************************************************
                          clear
                          print '\0033[8m\0033[H\0033[0m\c'
                          if [[ $# > 0 ]]
                          then
                          instance=$1
                          else
                          instance=PROD
                          fi
                          . /usr/local/bin/oraenv $instance
                          export ORACLE_SID=$instance



                          pformat="%-6s %-10s %-43s %-8s %-7s %-8s %-8s %-13s\n"
                          while true
                          do
                          printf "$pformat" "SID " "USERNAME" "CLIENT_ID" "STATUS" "SERVER" "PROGRAM" "MODULE" "START_TIME"
                          printf "$pformat" "------" "----------" "-------------------------------------------" "--------" "-------" "--------" "--------" "-------------"
                          echo "
                          set pagesize 0 linesize 400
                          set feedback off
                          connect *YourUser*/*YourPassword*
                          select s.sid||'{'||s.username||'{'||client_identifier||'{'||s.status||'{'||s.machine||'{'||
                          bansecr.LIST.ITEM(s.program,1,'@, ()','','ANY')||'{'||
                          bansecr.LIST.ITEM(s.module,1,'@, ()','','ANY')||'{'||t.start_time
                          from v\$transaction t, v\$session s
                          where s.saddr = t.ses_addr
                          order by start_time;
                          exit
                          " | sqlplus -s /nolog |grep -v Connected|
                          while read sessline
                          do
                          sid=$(print ${sessline}|cut -d "{" -f1)
                          username=$(print ${sessline}|cut -d "{" -f2)
                          clientid=$(print ${sessline}|cut -d "{" -f3)
                          status=$(print ${sessline}|cut -d "{" -f4)
                          server=$(print ${sessline}|cut -d "{" -f5)
                          program=$(print ${sessline}|cut -d "{" -f6)
                          module=$(print ${sessline}|cut -d "{" -f7)
                          stime=$(print ${sessline}|cut -d "{" -f8)

                          printf "$pformat" "$sid" "$username" "$clientid" "$status" "$server" "$program" "$module" "$stime"
                          done
                          print '\0033[0J'
                          print '\0033[H\0033[0m\c'
                          sleep 5
                          done

                          Cheers,

                          Carl
                          • 10. Re: How to query uncommited transactions
                            301084
                            I noticed after I posted that I had used a package I found on the web called list that includes several useful functions. So in case someone wants it, here are the package and body:

                            ***PACKAGE***
                            CREATE OR REPLACE PACKAGE list AUTHID CURRENT_USER IS
                            -- All of these functions and procedures have the following parameters:
                            -- list_in - A delimited list to be parsed.
                            -- delimiter - The delimiter to be used for parsing the list. Defaults
                            -- to a comma.
                            -- null_item - What to do with null items in the list. A null item is created
                            -- by consecutive occurances of the delimiter. Valid values are
                            -- 'KEEP' to allow items in the list to be null, or 'SKIP' to ignore
                            -- null items, ie. treat consecutive occurances of delimiters as a
                            -- single delimiter. The default is 'KEEP'.
                            -- delimiter_use - How the delimiter is to be interpreted. Valid values are
                            -- 'ANY' to treat the entire delimiter string as a single occurance
                            -- of a delimiter which must be matched exactly, or 'ANY' to treat
                            -- the delimiter string as a set of single character delimiters, any
                            -- of which is a delimiter. The default is 'ANY'.
                            --
                            -- Return the first item in a list.
                            FUNCTION head(
                            list_in IN VARCHAR2,
                            delimiter IN VARCHAR2 DEFAULT ',',
                            null_item IN VARCHAR2 DEFAULT 'KEEP',
                            delimiter_use IN VARCHAR2 DEFAULT 'ANY') RETURN VARCHAR2;
                            PRAGMA RESTRICT_REFERENCES (head,WNDS,WNPS);
                            --
                            -- Return the remainder of a list after the first item and its delimiter.
                            FUNCTION tail(
                            list_in IN VARCHAR2,
                            delimiter IN VARCHAR2 DEFAULT ',',
                            null_item IN VARCHAR2 DEFAULT 'KEEP',
                            delimiter_use IN VARCHAR2 DEFAULT 'ANY') RETURN VARCHAR2;
                            PRAGMA RESTRICT_REFERENCES (tail,WNDS,WNPS);
                            --
                            -- Return the nth item in a list.
                            -- The parameter, item_num, denotes which item to return.
                            FUNCTION item(
                            list_in IN VARCHAR2,
                            item_num IN INTEGER DEFAULT 1,
                            delimiter IN VARCHAR2 DEFAULT ',',
                            null_item IN VARCHAR2 DEFAULT 'KEEP',
                            delimiter_use IN VARCHAR2 DEFAULT 'ANY') RETURN VARCHAR2;
                            PRAGMA RESTRICT_REFERENCES (item,WNDS);
                            --
                            -- Append an item to a list and return the new list.
                            -- The parameter, item_in, contains the new item to append.
                            FUNCTION append_item(
                            list_in IN VARCHAR2,
                            item_in IN VARCHAR2,
                            delimiter IN VARCHAR2 DEFAULT ',') RETURN VARCHAR2;
                            PRAGMA RESTRICT_REFERENCES (append_item,WNDS);
                            --
                            -- Return the number of items in a list.
                            FUNCTION num_items(
                            list_in IN VARCHAR2,
                            delimiter IN VARCHAR2 DEFAULT ',',
                            null_item IN VARCHAR2 DEFAULT 'KEEP',
                            delimiter_use IN VARCHAR2 DEFAULT 'ANY') RETURN INTEGER;
                            PRAGMA RESTRICT_REFERENCES (num_items,WNDS);
                            --
                            -- Search a list for an item, and give its location in the list,
                            -- or zero IF not found.
                            -- The parameter, item_in, gives the item to be found in the list.
                            FUNCTION in_list(
                            list_in IN VARCHAR2,
                            item_in IN VARCHAR2,
                            delimiter IN VARCHAR2 DEFAULT ',',
                            null_item IN VARCHAR2 DEFAULT 'KEEP',
                            delimiter_use IN VARCHAR2 DEFAULT 'ANY') RETURN INTEGER;
                            PRAGMA RESTRICT_REFERENCES (in_list,WNDS);
                            --
                            -- Convert an array to a delimited list.
                            -- The array to be input is a DBMS_UTILITY.uncl_array so that
                            -- the LIST package is compatible with the comma_to_table and
                            -- table_to_comma built ins.
                            -- In this function, delimiter is always treated as a single
                            -- string.
                            FUNCTION array_to_list(
                            array_in IN DBMS_UTILITY.UNCL_ARRAY,
                            arrlen_in IN INTEGER,
                            delimiter IN VARCHAR2 DEFAULT ',') RETURN VARCHAR2;
                            PRAGMA RESTRICT_REFERENCES (array_to_list,WNDS,WNPS);
                            --
                            -- Print a list using DBMS_OUTPUT.
                            PROCEDURE print_list(
                            list_in IN VARCHAR2,
                            delimiter IN VARCHAR2 DEFAULT ',',
                            null_item IN VARCHAR2 DEFAULT 'KEEP',
                            delimiter_use IN VARCHAR2 DEFAULT 'ANY');
                            --
                            -- Convert a list to an array and return the array and its size.
                            -- This is a procedure because it returns more than one value.
                            -- The array to be returned is a DBMS_UTILITY.uncl_array so that
                            -- the LIST package is compatible with the comma_to_table and
                            -- table_to_comma built ins.
                            PROCEDURE list_to_array(
                            list_in IN VARCHAR2,
                            arrlen OUT BINARY_INTEGER,
                            array_out OUT DBMS_UTILITY.uncl_array,
                            delimiter IN VARCHAR2 DEFAULT ',',
                            null_item IN VARCHAR2 DEFAULT 'KEEP',
                            delimiter_use IN VARCHAR2 DEFAULT 'ANY');
                            --
                            -- Sort a list
                            -- Null items are always skipped when sorting lists, since they would sort
                            -- to the end of the list anyway. CMPFNC is the name of a function to compare
                            -- two items. The default of '>' sorts in ascending order, '<' in descending order.
                            -- If you write your own function to be used for sorting, it must:
                            -- 1. Take two parameters of type VARCHAR2
                            -- 2. Return an INTEGER
                            -- 3. Return a negative number if the first item is to sort lower than
                            -- the second, a zero if they are to sort as if equal, or a positive
                            -- number if the first item is to sort higher than the second.
                            -- 4. Be executable by the user running the sort. Normal naming rules apply.
                            FUNCTION sort_list(
                            list_in IN VARCHAR2,
                            delimiter IN VARCHAR2 DEFAULT ',',
                            cmpfnc IN VARCHAR2 DEFAULT '>',
                            delimiter_use IN VARCHAR2 DEFAULT 'ANY') RETURN VARCHAR2;
                            PRAGMA RESTRICT_REFERENCES (sort_list,WNDS);
                            end;
                            /

                            ***END PACKAGE SPEC***

                            ***BEGIN BODY***

                            CREATE OR REPLACE PACKAGE BODY list IS
                            current_list VARCHAR2(32760) DEFAULT '';
                            current_delim VARCHAR2(30) DEFAULT ',';
                            TYPE list_array IS TABLE OF VARCHAR2(2000)
                            INDEX BY BINARY_INTEGER;
                            current_array list_array;
                            current_arrlen BINARY_INTEGER DEFAULT 0;
                            current_null_item VARCHAR2(4) DEFAULT '';
                            current_delimiter_use VARCHAR2(3) DEFAULT '';
                            --
                            -- Find the first delimiter.
                            FUNCTION find_delimiter(
                            list_in IN VARCHAR2,
                            delimiter IN VARCHAR2 DEFAULT ',',
                            null_item IN VARCHAR2 DEFAULT 'KEEP',
                            delimiter_use IN VARCHAR2 DEFAULT 'ANY') RETURN BINARY_INTEGER IS
                            delimiter_loc BINARY_INTEGER;
                            BEGIN
                            IF upper(delimiter_use) = 'ALL' THEN
                            delimiter_loc := INSTR(list_in,delimiter);
                            ELSIF upper(delimiter_use) = 'ANY' THEN
                            delimiter_loc := INSTR(TRANSLATE(list_in,delimiter,ltrim(RPAD(' ',LENGTH(delimiter)+1,CHR(31)))),CHR(31));
                            END IF;
                            RETURN delimiter_loc;
                            END find_delimiter;
                            --
                            -- Return the first item in a list.
                            FUNCTION head(
                            list_in IN VARCHAR2,
                            delimiter IN VARCHAR2 DEFAULT ',',
                            null_item IN VARCHAR2 DEFAULT 'KEEP',
                            delimiter_use IN VARCHAR2 DEFAULT 'ANY') RETURN VARCHAR2 IS
                            delimiter_loc BINARY_INTEGER;
                            BEGIN
                            delimiter_loc := find_delimiter(list_in,delimiter,null_item,delimiter_use);
                            IF delimiter_loc > 1 THEN
                            RETURN SUBSTR(list_in,1,delimiter_loc-1);
                            ELSIF delimiter_loc = 1 THEN
                            RETURN NULL;
                            ELSE
                            RETURN list_in;
                            END IF;
                            END head;
                            --
                            -- Return the remainder of a list after the first item and its delimiter.
                            FUNCTION tail(
                            list_in IN VARCHAR2,
                            delimiter IN VARCHAR2 DEFAULT ',',
                            null_item IN VARCHAR2 DEFAULT 'KEEP',
                            delimiter_use IN VARCHAR2 DEFAULT 'ANY') RETURN VARCHAR2 IS
                            start_ch BINARY_INTEGER;
                            BEGIN
                            start_ch := find_delimiter(list_in,delimiter,null_item,delimiter_use);
                            IF start_ch = 0 THEN
                            RETURN NULL;
                            ELSE
                            IF upper(delimiter_use) = 'ALL' THEN
                            start_ch := start_ch + LENGTH(delimiter);
                            ELSE
                            start_ch := start_ch + 1;
                            END IF;
                            IF start_ch > LENGTH(list_in) THEN
                            RETURN NULL;
                            ELSE
                            RETURN SUBSTR(list_in,start_ch);
                            END IF;
                            END IF;
                            END tail;
                            --
                            -- Convert a list to an array.
                            PROCEDURE parse_list(
                            list_in IN VARCHAR2,
                            delimiter IN VARCHAR2 DEFAULT ',',
                            null_item IN VARCHAR2 DEFAULT 'KEEP',
                            delimiter_use IN VARCHAR2 DEFAULT 'ANY') IS
                            list_to_parse VARCHAR2(32760);
                            BEGIN
                            IF list_in = current_list AND
                            delimiter = current_delim AND
                            null_item = current_null_item AND
                            delimiter_use = current_delimiter_use THEN
                            NULL;
                            ELSE
                            current_list := list_in;
                            current_delim := delimiter;
                            current_null_item := upper(null_item);
                            current_delimiter_use := upper(delimiter_use);
                            list_to_parse := list_in;
                            current_arrlen := 0;
                            WHILE list_to_parse IS NOT NULL LOOP
                            IF current_null_item <> 'SKIP' OR
                            head(list_to_parse,delimiter,null_item,delimiter_use) IS NOT NULL THEN
                            current_arrlen := current_arrlen + 1;
                            current_array(current_arrlen) := SUBSTR(head(list_to_parse,delimiter,null_item,delimiter_use),1,2000);
                            END IF;
                            list_to_parse := tail(list_to_parse, delimiter,null_item,delimiter_use);
                            END LOOP;
                            END IF;
                            END parse_list;
                            --
                            -- Convert a list to an array and return the array and its size.
                            PROCEDURE list_to_array(
                            list_in IN VARCHAR2,
                            arrlen OUT BINARY_INTEGER,
                            array_out OUT DBMS_UTILITY.uncl_array,
                            delimiter IN VARCHAR2 DEFAULT ',',
                            null_item IN VARCHAR2 DEFAULT 'KEEP',
                            delimiter_use IN VARCHAR2 DEFAULT 'ANY') IS
                            BEGIN
                            parse_list(list_in,delimiter,null_item,delimiter_use);
                            arrlen := current_arrlen;
                            FOR i IN 1..arrlen LOOP
                            array_out(i) := SUBSTR(current_array(i),1,240);
                            END LOOP;
                            END list_to_array;
                            --
                            -- Print a list using DBMS_OUTPUT.
                            PROCEDURE print_list(
                            list_in IN VARCHAR2,
                            delimiter IN VARCHAR2 DEFAULT ',',
                            null_item IN VARCHAR2 DEFAULT 'KEEP',
                            delimiter_use IN VARCHAR2 DEFAULT 'ANY') IS
                            BEGIN
                            DBMS_OUTPUT.ENABLE(100000);
                            parse_list(list_in,delimiter,null_item,delimiter_use);
                            FOR i IN 1..current_arrlen LOOP
                            dbms_output.put_line(SUBSTR(current_array(i),1,240));
                            END LOOP;
                            END print_list;
                            --
                            -- Return the number of items in a list.
                            FUNCTION num_items(
                            list_in IN VARCHAR2,
                            delimiter IN VARCHAR2 DEFAULT ',',
                            null_item IN VARCHAR2 DEFAULT 'KEEP',
                            delimiter_use IN VARCHAR2 DEFAULT 'ANY') RETURN INTEGER is
                            BEGIN
                            parse_list(list_in,delimiter,null_item,delimiter_use);
                            RETURN current_arrlen;
                            END num_items;
                            --
                            -- Return the nth item in a list.
                            FUNCTION item(
                            list_in IN VARCHAR2,
                            item_num IN INTEGER DEFAULT 1,
                            delimiter IN VARCHAR2 DEFAULT ',',
                            null_item IN VARCHAR2 DEFAULT 'KEEP',
                            delimiter_use IN VARCHAR2 DEFAULT 'ANY') RETURN VARCHAR2 is
                            BEGIN
                            parse_list(list_in,delimiter,null_item,delimiter_use);
                            IF item_num NOT BETWEEN 1 AND current_arrlen THEN
                            RETURN NULL;
                            ELSE
                            RETURN current_array(item_num);
                            END IF;
                            END item;
                            --
                            -- Append an item to a list and return the new list.
                            -- The parameter, item_in, contains the new item to append.
                            FUNCTION append_item(
                            list_in IN VARCHAR2,
                            item_in IN VARCHAR2,
                            delimiter IN VARCHAR2 DEFAULT ',') RETURN VARCHAR2 IS
                            BEGIN
                            IF list_in IS NULL THEN
                            RETURN item_in;
                            ELSE
                            RETURN list_in || delimiter || item_in;
                            END IF;
                            END append_item;
                            --
                            -- Search a list for an item, and give its location in the list,
                            -- or zero IF not found.
                            FUNCTION in_list(
                            list_in IN VARCHAR2,
                            item_in IN VARCHAR2,
                            delimiter IN VARCHAR2 DEFAULT ',',
                            null_item IN VARCHAR2 DEFAULT 'KEEP',
                            delimiter_use IN VARCHAR2 DEFAULT 'ANY') RETURN INTEGER is
                            BEGIN
                            parse_list(list_in,delimiter,null_item,delimiter_use);
                            FOR item_num IN 1..current_arrlen LOOP
                            IF current_array(item_num) = item_in THEN
                            RETURN item_num;
                            END IF;
                            END LOOP;
                            RETURN 0;
                            END in_list;
                            --
                            -- Convert an array to a delimited list.
                            FUNCTION array_to_list(
                            array_in IN DBMS_UTILITY.UNCL_ARRAY,
                            arrlen_in IN INTEGER,
                            delimiter IN VARCHAR2 DEFAULT ',') RETURN VARCHAR2 IS
                            list_out VARCHAR2(32760):= '';
                            BEGIN
                            FOR item_num IN 1 .. arrlen_in LOOP
                            EXIT WHEN LENGTH(list_out) +
                            LENGTH(array_in(item_num)) > 32760;
                            list_out := list_out||array_in(item_num);
                            IF item_num < arrlen_in THEN
                            list_out := list_out||delimiter;
                            END IF;
                            END LOOP;
                            RETURN list_out;
                            END array_to_list;
                            --
                            -- Sort a list
                            FUNCTION sort_list(
                            list_in IN VARCHAR2,
                            delimiter IN VARCHAR2 DEFAULT ',',
                            cmpFnc IN VARCHAR2 DEFAULT '>',
                            delimiter_use IN VARCHAR2 DEFAULT 'ANY') RETURN VARCHAR2 IS
                            temp_array list_array;
                            temp_len PLS_INTEGER := 0;
                            temp_item VARCHAR2(2000);
                            list_out VARCHAR2(32760);
                            PROCEDURE swap (
                            first_item IN OUT VARCHAR2,
                            second_item IN OUT VARCHAR2) IS
                            temp_item VARCHAR2(2000);
                            BEGIN
                            temp_item := first_item;
                            first_item := second_item;
                            second_item := temp_item;
                            END swap;
                            FUNCTION cmp (
                            first_item IN VARCHAR2,
                            second_item IN VARCHAR2,
                            cmpfnc IN VARCHAR2 DEFAULT '=') RETURN INTEGER IS
                            return_value INTEGER;
                            BEGIN
                            IF cmpfnc = '>' THEN
                            IF first_item < second_item THEN
                            return_value := -1;
                            ELSIF first_item = second_item THEN
                            return_value := 0;
                            ELSIF first_item > second_item THEN
                            return_value := 1;
                            END IF;
                            ELSIF cmpfnc = '<' THEN
                            IF first_item > second_item THEN
                            return_value := -1;
                            ELSIF first_item = second_item THEN
                            return_value := 0;
                            ELSIF first_item < second_item THEN
                            return_value := 1;
                            END IF;
                            ELSE
                            EXECUTE IMMEDIATE 'BEGIN :I := '||cmpfnc||'(:A,:B); END;'
                            USING OUT return_value, IN first_item, IN second_item;
                            END IF;
                            RETURN return_value;
                            END cmp;
                            BEGIN
                            parse_list(list_in,delimiter,'SKIP',delimiter_use);
                            FOR item_num IN 1..current_arrlen LOOP
                            temp_item := current_array(item_num);
                            FOR i IN 1..temp_len LOOP
                            IF cmp(temp_array(i),temp_item,cmpfnc) > 0 THEN
                            swap(temp_array(i),temp_item);
                            END IF;
                            END LOOP;
                            temp_len := temp_len + 1;
                            temp_array(temp_len) := temp_item;
                            END LOOP;
                            FOR item_num IN 1..temp_len LOOP
                            EXIT WHEN LENGTH(list_out) +
                            LENGTH(temp_array(item_num)) > 32760;
                            list_out := list_out||temp_array(item_num);
                            IF item_num < temp_len THEN
                            list_out := list_out||delimiter;
                            END IF;
                            END LOOP;
                            RETURN list_out;
                            END sort_list;

                            END;
                            /

                            *** END BODY***

                            Carl