########################################################################################################### ## This file is part of http://www.gplivna.eu/papers/v$session_longops.htm ########################################################################################################### ########################################################################################################### ## Table flag was created by another session and we just see here that it exists ## It actually contain one row ########################################################################################################### SQL> desc flag Name Null? Type ----------------------------------------- -------- ---------------------------- A NUMBER ########################################################################################################### ## We'll create a table to store the snapshots of v$session_longops ########################################################################################################### SQL> create table hashtrend as select rownum rn, v.* 2 from v$session_longops v where 1=0; Table created. ########################################################################################################### ## And now we just start to record changes in this table ## Each second we insert the last row from v$session_longops into hashtrend table ## recording runs until the other session deletes the row from flag table ## then this session simply errors out ########################################################################################################### SQL> declare 2 i number := 1; 3 f number; 4 begin 5 loop 6 insert into hashtrend select i, v.* from ( 7 select * from v$session_longops 8 order by start_time desc 9 ) v 10 where rownum < =1; 11 commit; 12 select a into f from flag; 13 dbms_lock.sleep(1); 14 i := i + 1; 15 end loop; 16 end; 17 / declare * ERROR at line 1: ORA-01403: no data found ORA-06512: at line 12 ########################################################################################################### ## Hashtrend table has some rows, several initial rows of them are not needed because ## these contains remains from previous long operation. ## Several others have the same information because v$session_longops is not updated that often ########################################################################################################### SQL> select count(*) from hashtrend; COUNT(*) ---------- 141 SQL> desc hashtrend Name Null? Type ----------------------------------------- -------- ---------------------------- RN NUMBER SID NUMBER SERIAL# NUMBER OPNAME VARCHAR2(64) TARGET VARCHAR2(64) TARGET_DESC VARCHAR2(32) SOFAR NUMBER TOTALWORK NUMBER UNITS VARCHAR2(32) START_TIME DATE LAST_UPDATE_TIME DATE TIME_REMAINING NUMBER ELAPSED_SECONDS NUMBER CONTEXT NUMBER MESSAGE VARCHAR2(512) USERNAME VARCHAR2(30) SQL_ADDRESS RAW(4) SQL_HASH_VALUE NUMBER QCSID NUMBER SQL> set pages 50000 SQL> ed Wrote file afiedt.buf 1* select start_time, last_update_time,TIME_REMAINING from hashtrend order by rn SQL> / START_TIME LAST_UPDATE_TIME TIME_REMAINING ------------------- ------------------- -------------- ########################################################################################################### ## These are rows from previous lonng operation ########################################################################################################### 2006-12-29:23:44:46 2006-12-29:23:46:50 0 2006-12-29:23:44:46 2006-12-29:23:46:50 0 2006-12-29:23:44:46 2006-12-29:23:46:50 0 2006-12-29:23:44:46 2006-12-29:23:46:50 0 2006-12-29:23:44:46 2006-12-29:23:46:50 0 2006-12-29:23:44:46 2006-12-29:23:46:50 0 2006-12-29:23:44:46 2006-12-29:23:46:50 0 2006-12-29:23:44:46 2006-12-29:23:46:50 0 2006-12-29:23:44:46 2006-12-29:23:46:50 0 2006-12-29:23:44:46 2006-12-29:23:46:50 0 2006-12-29:23:44:46 2006-12-29:23:46:50 0 2006-12-29:23:44:46 2006-12-29:23:46:50 0 2006-12-29:23:44:46 2006-12-29:23:46:50 0 2006-12-29:23:44:46 2006-12-29:23:46:50 0 2006-12-29:23:44:46 2006-12-29:23:46:50 0 2006-12-29:23:44:46 2006-12-29:23:46:50 0 2006-12-29:23:44:46 2006-12-29:23:46:50 0 2006-12-29:23:44:46 2006-12-29:23:46:50 0 2006-12-29:23:44:46 2006-12-29:23:46:50 0 2006-12-29:23:44:46 2006-12-29:23:46:50 0 2006-12-29:23:44:46 2006-12-29:23:46:50 0 2006-12-29:23:44:46 2006-12-29:23:46:50 0 2006-12-29:23:44:46 2006-12-29:23:46:50 0 ########################################################################################################### ## These are rows we are interested in ########################################################################################################### 2006-12-29:23:55:24 2006-12-29:23:55:31 24 2006-12-29:23:55:24 2006-12-29:23:55:33 26 2006-12-29:23:55:24 2006-12-29:23:55:33 23 2006-12-29:23:55:24 2006-12-29:23:55:34 24 2006-12-29:23:55:24 2006-12-29:23:55:36 29 2006-12-29:23:55:24 2006-12-29:23:55:36 29 2006-12-29:23:55:24 2006-12-29:23:55:36 29 2006-12-29:23:55:24 2006-12-29:23:55:38 33 2006-12-29:23:55:24 2006-12-29:23:55:38 33 2006-12-29:23:55:24 2006-12-29:23:55:40 37 2006-12-29:23:55:24 2006-12-29:23:55:40 37 2006-12-29:23:55:24 2006-12-29:23:55:42 42 2006-12-29:23:55:24 2006-12-29:23:55:42 42 2006-12-29:23:55:24 2006-12-29:23:55:45 48 2006-12-29:23:55:24 2006-12-29:23:55:45 48 2006-12-29:23:55:24 2006-12-29:23:55:46 50 2006-12-29:23:55:24 2006-12-29:23:55:46 50 2006-12-29:23:55:24 2006-12-29:23:55:46 50 2006-12-29:23:55:24 2006-12-29:23:55:49 57 2006-12-29:23:55:24 2006-12-29:23:55:49 57 2006-12-29:23:55:24 2006-12-29:23:55:51 60 2006-12-29:23:55:24 2006-12-29:23:55:51 60 2006-12-29:23:55:24 2006-12-29:23:55:54 67 2006-12-29:23:55:24 2006-12-29:23:55:54 67 2006-12-29:23:55:24 2006-12-29:23:55:54 67 2006-12-29:23:55:24 2006-12-29:23:55:56 71 2006-12-29:23:55:24 2006-12-29:23:55:56 71 2006-12-29:23:55:24 2006-12-29:23:55:58 74 2006-12-29:23:55:24 2006-12-29:23:55:58 74 2006-12-29:23:55:24 2006-12-29:23:55:58 74 2006-12-29:23:55:24 2006-12-29:23:56:01 80 2006-12-29:23:55:24 2006-12-29:23:56:01 80 2006-12-29:23:55:24 2006-12-29:23:56:03 84 2006-12-29:23:55:24 2006-12-29:23:56:03 84 2006-12-29:23:55:24 2006-12-29:23:56:06 89 2006-12-29:23:55:24 2006-12-29:23:56:06 89 2006-12-29:23:55:24 2006-12-29:23:56:07 91 2006-12-29:23:55:24 2006-12-29:23:56:07 91 2006-12-29:23:55:24 2006-12-29:23:56:07 91 2006-12-29:23:55:24 2006-12-29:23:56:10 97 2006-12-29:23:55:24 2006-12-29:23:56:10 97 2006-12-29:23:55:24 2006-12-29:23:56:12 100 2006-12-29:23:55:24 2006-12-29:23:56:13 101 2006-12-29:23:55:24 2006-12-29:23:56:13 101 2006-12-29:23:55:24 2006-12-29:23:56:15 105 2006-12-29:23:55:24 2006-12-29:23:56:15 105 2006-12-29:23:55:24 2006-12-29:23:56:15 105 2006-12-29:23:55:24 2006-12-29:23:56:18 111 2006-12-29:23:55:24 2006-12-29:23:56:18 111 2006-12-29:23:55:24 2006-12-29:23:56:21 115 2006-12-29:23:55:24 2006-12-29:23:56:21 115 2006-12-29:23:55:24 2006-12-29:23:56:22 117 2006-12-29:23:55:24 2006-12-29:23:56:22 117 2006-12-29:23:55:24 2006-12-29:23:56:24 121 2006-12-29:23:55:24 2006-12-29:23:56:24 121 2006-12-29:23:55:24 2006-12-29:23:56:27 127 2006-12-29:23:55:24 2006-12-29:23:56:27 124 2006-12-29:23:55:24 2006-12-29:23:56:27 124 2006-12-29:23:55:24 2006-12-29:23:56:30 130 2006-12-29:23:55:24 2006-12-29:23:56:30 130 2006-12-29:23:55:24 2006-12-29:23:56:31 132 2006-12-29:23:55:24 2006-12-29:23:56:31 132 2006-12-29:23:55:24 2006-12-29:23:56:33 133 2006-12-29:23:55:24 2006-12-29:23:56:33 133 2006-12-29:23:55:24 2006-12-29:23:56:33 133 2006-12-29:23:55:24 2006-12-29:23:56:36 139 2006-12-29:23:55:24 2006-12-29:23:56:36 139 2006-12-29:23:55:24 2006-12-29:23:56:39 144 2006-12-29:23:55:24 2006-12-29:23:56:39 144 2006-12-29:23:55:24 2006-12-29:23:56:40 143 2006-12-29:23:55:24 2006-12-29:23:56:40 143 2006-12-29:23:55:24 2006-12-29:23:56:40 143 2006-12-29:23:55:24 2006-12-29:23:56:43 149 2006-12-29:23:55:24 2006-12-29:23:56:43 149 2006-12-29:23:55:24 2006-12-29:23:56:45 152 2006-12-29:23:55:24 2006-12-29:23:56:45 152 2006-12-29:23:55:24 2006-12-29:23:56:48 157 2006-12-29:23:55:24 2006-12-29:23:56:48 155 2006-12-29:23:55:24 2006-12-29:23:56:48 155 2006-12-29:23:55:24 2006-12-29:23:56:51 160 2006-12-29:23:55:24 2006-12-29:23:56:51 160 2006-12-29:23:55:24 2006-12-29:23:56:52 162 2006-12-29:23:55:24 2006-12-29:23:56:52 162 2006-12-29:23:55:24 2006-12-29:23:56:54 162 2006-12-29:23:55:24 2006-12-29:23:56:54 162 2006-12-29:23:55:24 2006-12-29:23:56:54 162 2006-12-29:23:55:24 2006-12-29:23:56:57 167 2006-12-29:23:55:24 2006-12-29:23:56:57 167 2006-12-29:23:55:24 2006-12-29:23:57:00 173 2006-12-29:23:55:24 2006-12-29:23:57:00 173 2006-12-29:23:55:24 2006-12-29:23:57:00 173 2006-12-29:23:55:24 2006-12-29:23:57:03 174 2006-12-29:23:55:24 2006-12-29:23:57:03 174 2006-12-29:23:55:24 2006-12-29:23:57:03 174 2006-12-29:23:55:24 2006-12-29:23:57:05 178 2006-12-29:23:55:24 2006-12-29:23:57:05 178 2006-12-29:23:55:24 2006-12-29:23:57:08 171 2006-12-29:23:55:24 2006-12-29:23:57:09 154 2006-12-29:23:55:24 2006-12-29:23:57:09 137 2006-12-29:23:55:24 2006-12-29:23:57:10 123 2006-12-29:23:55:24 2006-12-29:23:57:12 112 2006-12-29:23:55:24 2006-12-29:23:57:12 99 2006-12-29:23:55:24 2006-12-29:23:57:13 88 2006-12-29:23:55:24 2006-12-29:23:57:15 79 2006-12-29:23:55:24 2006-12-29:23:57:15 69 2006-12-29:23:55:24 2006-12-29:23:57:16 60 2006-12-29:23:55:24 2006-12-29:23:57:18 54 2006-12-29:23:55:24 2006-12-29:23:57:18 46 2006-12-29:23:55:24 2006-12-29:23:57:19 39 2006-12-29:23:55:24 2006-12-29:23:57:21 34 2006-12-29:23:55:24 2006-12-29:23:57:21 28 2006-12-29:23:55:24 2006-12-29:23:57:22 23 2006-12-29:23:55:24 2006-12-29:23:57:24 18 2006-12-29:23:55:24 2006-12-29:23:57:24 14 2006-12-29:23:55:24 2006-12-29:23:57:25 9 2006-12-29:23:55:24 2006-12-29:23:57:27 5 2006-12-29:23:55:24 2006-12-29:23:57:28 1 2006-12-29:23:55:24 2006-12-29:23:57:29 0 141 rows selected. ########################################################################################################### ## Just another select to filter out unnecessary rows ########################################################################################################### SQL> ed Wrote file afiedt.buf 1 select start_time, last_update_time, time_remaining, sofar from ( 2 select start_time, last_update_time, time_remaining, sofar, 3 row_number() over (partition by start_time, last_update_time order by rn) rn 4 from hashtrend 5 ) 6* where rn = 1 SQL> / START_TIME LAST_UPDATE_TIME TIME_REMAINING SOFAR ------------------- ------------------- -------------- ---------- 2006-12-29:23:44:46 2006-12-29:23:46:50 0 29995 2006-12-29:23:55:24 2006-12-29:23:55:31 24 6842 2006-12-29:23:55:24 2006-12-29:23:55:33 26 7616 2006-12-29:23:55:24 2006-12-29:23:55:34 24 8857 2006-12-29:23:55:24 2006-12-29:23:55:36 29 8858 2006-12-29:23:55:24 2006-12-29:23:55:38 33 9011 2006-12-29:23:55:24 2006-12-29:23:55:40 37 9012 2006-12-29:23:55:24 2006-12-29:23:55:42 42 9013 2006-12-29:23:55:24 2006-12-29:23:55:45 48 9166 2006-12-29:23:55:24 2006-12-29:23:55:46 50 9167 2006-12-29:23:55:24 2006-12-29:23:55:49 57 9168 2006-12-29:23:55:24 2006-12-29:23:55:51 60 9321 2006-12-29:23:55:24 2006-12-29:23:55:54 67 9322 2006-12-29:23:55:24 2006-12-29:23:55:56 71 9323 2006-12-29:23:55:24 2006-12-29:23:55:58 74 9476 2006-12-29:23:55:24 2006-12-29:23:56:01 80 9477 2006-12-29:23:55:24 2006-12-29:23:56:03 84 9478 2006-12-29:23:55:24 2006-12-29:23:56:06 89 9631 2006-12-29:23:55:24 2006-12-29:23:56:07 91 9632 2006-12-29:23:55:24 2006-12-29:23:56:10 97 9633 2006-12-29:23:55:24 2006-12-29:23:56:12 100 9748 2006-12-29:23:55:24 2006-12-29:23:56:13 101 9786 2006-12-29:23:55:24 2006-12-29:23:56:15 105 9787 2006-12-29:23:55:24 2006-12-29:23:56:18 111 9788 2006-12-29:23:55:24 2006-12-29:23:56:21 115 9941 2006-12-29:23:55:24 2006-12-29:23:56:22 117 9942 2006-12-29:23:55:24 2006-12-29:23:56:24 121 9943 2006-12-29:23:55:24 2006-12-29:23:56:27 127 9962 2006-12-29:23:55:24 2006-12-29:23:56:30 130 10097 2006-12-29:23:55:24 2006-12-29:23:56:31 132 10098 2006-12-29:23:55:24 2006-12-29:23:56:33 133 10251 2006-12-29:23:55:24 2006-12-29:23:56:36 139 10252 2006-12-29:23:55:24 2006-12-29:23:56:39 144 10253 2006-12-29:23:55:24 2006-12-29:23:56:40 143 10406 2006-12-29:23:55:24 2006-12-29:23:56:43 149 10407 2006-12-29:23:55:24 2006-12-29:23:56:45 152 10408 2006-12-29:23:55:24 2006-12-29:23:56:48 157 10441 2006-12-29:23:55:24 2006-12-29:23:56:51 160 10562 2006-12-29:23:55:24 2006-12-29:23:56:52 162 10563 2006-12-29:23:55:24 2006-12-29:23:56:54 162 10716 2006-12-29:23:55:24 2006-12-29:23:56:57 167 10717 2006-12-29:23:55:24 2006-12-29:23:57:00 173 10718 2006-12-29:23:55:24 2006-12-29:23:57:03 174 10871 2006-12-29:23:55:24 2006-12-29:23:57:05 178 10872 2006-12-29:23:55:24 2006-12-29:23:57:08 171 11337 2006-12-29:23:55:24 2006-12-29:23:57:09 154 12143 2006-12-29:23:55:24 2006-12-29:23:57:10 123 13877 2006-12-29:23:55:24 2006-12-29:23:57:12 112 14693 2006-12-29:23:55:24 2006-12-29:23:57:13 88 16583 2006-12-29:23:55:24 2006-12-29:23:57:15 79 17531 2006-12-29:23:55:24 2006-12-29:23:57:16 60 19479 2006-12-29:23:55:24 2006-12-29:23:57:18 54 20390 2006-12-29:23:55:24 2006-12-29:23:57:19 39 22352 2006-12-29:23:55:24 2006-12-29:23:57:21 34 23242 2006-12-29:23:55:24 2006-12-29:23:57:22 23 25163 2006-12-29:23:55:24 2006-12-29:23:57:24 18 26035 2006-12-29:23:55:24 2006-12-29:23:57:25 9 27864 2006-12-29:23:55:24 2006-12-29:23:57:27 5 28805 2006-12-29:23:55:24 2006-12-29:23:57:28 1 29744 2006-12-29:23:55:24 2006-12-29:23:57:29 0 29995 60 rows selected.