Hi Tanel, As you suggested i have upgraded to 9.2.0.4. Now i want to invalidate all the packages and revalidate again. So i did execute utlip.sql (to invalidate first) and below is the update sql as part of utlip.sql
update obj$ set status = 6 where ((type# in (7, 8, 9, 11, 12, 14, 22, 32, 33)) or (type# = 13 and subname is null)) and status not in (5,6) and linkname is null and (oid$ is null or oid$ not in (select toid from type$ where bitand(properties, 16) = 16)) / The above update SQL statement is taking long time. Enclosed is the v$session_wait table output. Could someone help me to figure out -to whom the above UPDATE sql statement is waiting for? select * from v$session_wait SID SEQ# EVENT P1TEXT P1 P1RAW P2TEXT P2 P2RAW P3TEXT P3 P3RAW WAIT_TIME SECONDS_IN_WAIT STATE --- ---- ---------------------------------------- ---------------------------------------- -- -------- ---------------------------------------- -- -------- ---------------------------------------- -- -------- ---------- --------------- ------------------- 1 779 pmon timer duration 30 0000012C 0 00 0 00 0 2244 WAITING 2 940 rdbms ipc message timeout 30 0000012C 0 00 0 00 0 34 WAITING 3 3242 rdbms ipc message timeout 29 00000128 0 00 0 00 0 0 WAITING 6 8 rdbms ipc message timeout 18 0002BF20 0 00 0 00 0 266 WAITING 9 78 rdbms ipc message timeout 30 00007530 0 00 0 00 0 99 WAITING 10 294 rdbms ipc message timeout 60 00001770 0 00 0 00 0 8 WAITING 7 455 rdbms ipc message timeout 50 000001F4 0 00 0 00 0 2015 WAITING 4 1766 rdbms ipc message timeout 30 0000012C 0 00 0 00 0 3 WAITING 11 2277 db file scattered read file# 1 00000001 block# 42 000010A1 blocks 8 00000008 0 0 WAITING 5 201 smon timer sleep time 30 0000012C failed 0 00 0 00 0 883 WAITING 12 2 jobq slave wait 0 00 0 00 0 00 0 4 WAITING 17 107 jobq slave wait 0 00 0 00 0 00 0 312 WAITING 19 42 jobq slave wait 0 00 0 00 0 00 0 122 WAITING 16 22 SQL*Net message from client driver id 14 54435000 #bytes 1 00000001 0 00 0 2148 WAITING 25 816 SQL*Net message from client driver id 14 54435000 #bytes 1 00000001 0 00 0 14 WAITING 27 285 SQL*Net message from client driver id 14 54435000 #bytes 1 00000001 0 00 0 40 WAITING 29 1261 SQL*Net message from client driver id 14 54435000 #bytes 1 00000001 0 00 -1 0 WAITED KNOWN TIME 28 41 SQL*Net message from client driver id 14 54435000 #bytes 1 00000001 0 00 0 332 WAITING 26 1133 SQL*Net message from client driver id 14 54435000 #bytes 1 00000001 0 00 0 0 WAITING 23 48 SQL*Net message from client driver id 14 54435000 #bytes 1 00000001 0 00 0 2121 WAITING 18 91 SQL*Net message from client driver id 14 54435000 #bytes 1 00000001 0 00 0 2148 WAITING 21 118 SQL*Net message from client driver id 14 54435000 #bytes 1 00000001 0 00 0 2144 WAITING 22 261 SQL*Net message from client driver id 14 54435000 #bytes 1 00000001 0 00 0 46 WAITING 15 282 wakeup time manager 0 00 0 00 0 00 0 8 WAITING 24 rows selected ----- Original Message ----- Date: Sunday, October 26, 2003 2:22 pm > Hi Tanel, > > As you suggested i have upgraded to 9.2.0.4. Now i want to > invalidate all the packages and revalidate again. > So i did execute utlip.sql (to invalidate first) and below is the > update sql as part of utlip.sql > > update obj$ set status = 6 > where ((type# in (7, 8, 9, 11, 12, 14, 22, 32, 33)) or > (type# = 13 and subname is null)) > and status not in (5,6) > and linkname is null > and (oid$ is null or oid$ not in (select toid from type$ > where bitand(properties, 16) = 16)) > / > > The above update SQL statement is taking long time. Enclosed is > the v$session_wait table output. Could someone help me to figure > out > -to whom the above UPDATE sql statement is waiting for? > > <html> > <body> > > <table border="0" width="100%"> > <tr> > <th align="left" bgcolor="#C0C0C0" bordercolor="#FFFFFF">SID</th> > <th align="left" bgcolor="#C0C0C0" bordercolor="#FFFFFF">SEQ#</th> > <th align="left" bgcolor="#C0C0C0" bordercolor="#FFFFFF">EVENT</th> > <th align="left" bgcolor="#C0C0C0" bordercolor="#FFFFFF">STATE</th> > <th align="left" bgcolor="#C0C0C0" > bordercolor="#FFFFFF">SECONDS_IN_WAIT</th> <th align="left" > bgcolor="#C0C0C0" bordercolor="#FFFFFF">P1TEXT</th> > <th align="left" bgcolor="#C0C0C0" bordercolor="#FFFFFF">P1</th> > <th align="left" bgcolor="#C0C0C0" bordercolor="#FFFFFF">P1RAW</th> > <th align="left" bgcolor="#C0C0C0" bordercolor="#FFFFFF">P2TEXT</th> > <th align="left" bgcolor="#C0C0C0" bordercolor="#FFFFFF">P2</th> > <th align="left" bgcolor="#C0C0C0" bordercolor="#FFFFFF">P2RAW</th> > <th align="left" bgcolor="#C0C0C0" bordercolor="#FFFFFF">P3TEXT</th> > <th align="left" bgcolor="#C0C0C0" bordercolor="#FFFFFF">P3</th> > <th align="left" bgcolor="#C0C0C0" bordercolor="#FFFFFF">P3RAW</th> > <th align="left" bgcolor="#C0C0C0" > bordercolor="#FFFFFF">WAIT_TIME</th> </tr> > <tr> <td>1</td> > <td>516</td> > <td>pmon timer</td> > <td>WAITING</td> > <td>1481</td> > <td>duration</td> > <td>300</td> > <td>0000012C</td> > <td>NULL</td> <td>0</td> > <td>00</td> > <td>NULL</td> <td>0</td> > <td>00</td> > <td>0</td> > </tr> > <tr> <td>19</td> > <td>477</td> > <td>jobq slave wait</td> > <td>WAITING</td> > <td>1400</td> > <td>NULL</td> <td>0</td> > <td>00</td> > <td>NULL</td> <td>0</td> > <td>00</td> > <td>NULL</td> <td>0</td> > <td>00</td> > <td>0</td> > </tr> > <tr> <td>16</td> > <td>22</td> > <td>SQL*Net message from client</td> > <td>WAITING</td> > <td>1385</td> > <td>driver id</td> > <td>1413697536</td> > <td>54435000</td> > <td>#bytes</td> > <td>1</td> > <td>00000001</td> > <td>NULL</td> <td>0</td> > <td>00</td> > <td>0</td> > </tr> > <tr> <td>18</td> > <td>91</td> > <td>SQL*Net message from client</td> > <td>WAITING</td> > <td>1385</td> > <td>driver id</td> > <td>1413697536</td> > <td>54435000</td> > <td>#bytes</td> > <td>1</td> > <td>00000001</td> > <td>NULL</td> <td>0</td> > <td>00</td> > <td>0</td> > </tr> > <tr> <td>21</td> > <td>118</td> > <td>SQL*Net message from client</td> > <td>WAITING</td> > <td>1381</td> > <td>driver id</td> > <td>1413697536</td> > <td>54435000</td> > <td>#bytes</td> > <td>1</td> > <td>00000001</td> > <td>NULL</td> <td>0</td> > <td>00</td> > <td>0</td> > </tr> > <tr> <td>23</td> > <td>48</td> > <td>SQL*Net message from client</td> > <td>WAITING</td> > <td>1358</td> > <td>driver id</td> > <td>1413697536</td> > <td>54435000</td> > <td>#bytes</td> > <td>1</td> > <td>00000001</td> > <td>NULL</td> <td>0</td> > <td>00</td> > <td>0</td> > </tr> > <tr> <td>6</td> > <td>6</td> > <td>rdbms ipc message</td> > <td>WAITING</td> > <td>1349</td> > <td>timeout</td> > <td>180000</td> > <td>0002BF20</td> > <td>NULL</td> <td>0</td> > <td>00</td> > <td>NULL</td> <td>0</td> > <td>00</td> > <td>0</td> > </tr> > <tr> <td>7</td> > <td>306</td> > <td>rdbms ipc message</td> > <td>WAITING</td> > <td>1252</td> > <td>timeout</td> > <td>500</td> > <td>000001F4</td> > <td>NULL</td> <td>0</td> > <td>00</td> > <td>NULL</td> <td>0</td> > <td>00</td> > <td>0</td> > </tr> > <tr> <td>9</td> > <td>69</td> > <td>rdbms ipc message</td> > <td>WAITING</td> > <td>867</td> > <td>timeout</td> > <td>30000</td> > <td>00007530</td> > <td>NULL</td> <td>0</td> > <td>00</td> > <td>NULL</td> <td>0</td> > <td>00</td> > <td>0</td> > </tr> > <tr> <td>30</td> > <td>344</td> > <td>SQL*Net message from client</td> > <td>WAITING</td> > <td>680</td> > <td>driver id</td> > <td>1413697536</td> > <td>54435000</td> > <td>#bytes</td> > <td>1</td> > <td>00000001</td> > <td>NULL</td> <td>0</td> > <td>00</td> > <td>0</td> > </tr> > <tr> <td>28</td> > <td>31</td> > <td>SQL*Net message from client</td> > <td>WAITING</td> > <td>472</td> > <td>driver id</td> > <td>1413697536</td> > <td>54435000</td> > <td>#bytes</td> > <td>1</td> > <td>00000001</td> > <td>NULL</td> <td>0</td> > <td>00</td> > <td>0</td> > </tr> > <tr> <td>5</td> > <td>199</td> > <td>smon timer</td> > <td>WAITING</td> > <td>120</td> > <td>sleep time</td> > <td>300</td> > <td>0000012C</td> > <td>failed</td> > <td>0</td> > <td>00</td> > <td>NULL</td> <td>0</td> > <td>00</td> > <td>0</td> > </tr> > <tr> <td>17</td> > <td>37</td> > <td>jobq slave wait</td> > <td>WAITING</td> > <td>107</td> > <td>NULL</td> <td>0</td> > <td>00</td> > <td>NULL</td> <td>0</td> > <td>00</td> > <td>NULL</td> <td>0</td> > <td>00</td> > <td>0</td> > </tr> > <tr> <td>27</td> > <td>197</td> > <td>SQL*Net message from client</td> > <td>WAITING</td> > <td>58</td> > <td>driver id</td> > <td>1413697536</td> > <td>54435000</td> > <td>#bytes</td> > <td>1</td> > <td>00000001</td> > <td>NULL</td> <td>0</td> > <td>00</td> > <td>0</td> > </tr> > <tr> <td>10</td> > <td>175</td> > <td>rdbms ipc message</td> > <td>WAITING</td> > <td>49</td> > <td>timeout</td> > <td>6000</td> > <td>00001770</td> > <td>NULL</td> <td>0</td> > <td>00</td> > <td>NULL</td> <td>0</td> > <td>00</td> > <td>0</td> > </tr> > <tr> <td>2</td> > <td>665</td> > <td>rdbms ipc message</td> > <td>WAITING</td> > <td>37</td> > <td>timeout</td> > <td>300</td> > <td>0000012C</td> > <td>NULL</td> <td>0</td> > <td>00</td> > <td>NULL</td> <td>0</td> > <td>00</td> > <td>0</td> > </tr> > <tr> <td>12</td> > <td>12</td> > <td>jobq slave wait</td> > <td>WAITING</td> > <td>36</td> > <td>NULL</td> <td>0</td> > <td>00</td> > <td>NULL</td> <td>0</td> > <td>00</td> > <td>NULL</td> <td>0</td> > <td>00</td> > <td>0</td> > </tr> > <tr> <td>15</td> > <td>229</td> > <td>wakeup time manager</td> > <td>WAITING</td> > <td>8</td> > <td>NULL</td> <td>0</td> > <td>00</td> > <td>NULL</td> <td>0</td> > <td>00</td> > <td>NULL</td> <td>0</td> > <td>00</td> > <td>0</td> > </tr> > <tr> <td>4</td> > <td>1157</td> > <td>rdbms ipc message</td> > <td>WAITING</td> > <td>3</td> > <td>timeout</td> > <td>300</td> > <td>0000012C</td> > <td>NULL</td> <td>0</td> > <td>00</td> > <td>NULL</td> <td>0</td> > <td>00</td> > <td>0</td> > </tr> > <tr> <td>22</td> > <td>189</td> > <td>SQL*Net message from client</td> > <td>WAITING</td> > <td>3</td> > <td>driver id</td> > <td>1413697536</td> > <td>54435000</td> > <td>#bytes</td> > <td>1</td> > <td>00000001</td> > <td>NULL</td> <td>0</td> > <td>00</td> > <td>0</td> > </tr> > <tr> <td>3</td> > <td>2261</td> > <td>rdbms ipc message</td> > <td>WAITING</td> > <td>1</td> > <td>timeout</td> > <td>114</td> > <td>00000072</td> > <td>NULL</td> <td>0</td> > <td>00</td> > <td>NULL</td> <td>0</td> > <td>00</td> > <td>0</td> > </tr> > <tr> <td>11</td> > <td>13716</td> > <td>db file scattered read</td> > <td>WAITING</td> > <td>1</td> > <td>file#</td> > <td>1</td> > <td>00000001</td> > <td>block#</td> > <td>40601</td> > <td>00009E99</td> > <td>blocks</td> > <td>16</td> > <td>00000010</td> > <td>0</td> > </tr> > <tr> <td>26</td> > <td>7307</td> > <td>SQL*Net message from client</td> > <td>WAITING</td> > <td>1</td> > <td>driver id</td> > <td>1413697536</td> > <td>54435000</td> > <td>#bytes</td> > <td>1</td> > <td>00000001</td> > <td>NULL</td> <td>0</td> > <td>00</td> > <td>0</td> > </tr> > <tr> <td>25</td> > <td>541</td> > <td>SQL*Net message from client</td> > <td>WAITING</td> > <td>1</td> > <td>driver id</td> > <td>1413697536</td> > <td>54435000</td> > <td>#bytes</td> > <td>1</td> > <td>00000001</td> > <td>NULL</td> <td>0</td> > <td>00</td> > <td>0</td> > </tr> > <tr> <td>29</td> > <td>1255</td> > <td>SQL*Net message from client</td> > <td>WAITED KNOWN TIME</td> > <td>0</td> > <td>driver id</td> > <td>1413697536</td> > <td>54435000</td> > <td>#bytes</td> > <td>1</td> > <td>00000001</td> > <td>NULL</td> <td>0</td> > <td>00</td> > <td>2</td> > </tr></table> > </body> > </html> > > thanks > -tamizh > > > ----- Original Message ----- > From: Tanel Poder <[EMAIL PROTECTED]> > Date: Sunday, October 26, 2003 9:24 am > Subject: Re: Oracle Streams > > > Hi! > > > > This statement shouldn't do much more than change some SGA and > > controlfilestructures, so an hour seems to bee too long. Have > you > > set 10046 trace on > > for this session and checked whether it's doing anything? Or check > > v$session_wait and see whether this session is waiting on something. > > > > Anyway, since Streams is fresh new functionality, I recommend to > > use 9.2.0.4 > > for it, first version is probably too buggy... > > > > Tanel. > > > > ----- Original Message ----- > > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > > Sent: Sunday, October 26, 2003 3:54 PM > > > > > > > Hi Gurus, > > > > > > I am trying to setup Oracle Streams(for replication purpose) > in > > oracle9.2.0.1 running on windows NT. > > > > > > The following statement is part of Stream confuguration at > > source DB but > > it is running for more than hour. > > > > > > What does it do? Or is it hanging? Does anyone experience this > > problem?> > > > ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY,UNIQUE > INDEX)> COLUMNS; > > > > > > Any help would be really appreciated. > > > -tamizh > > > > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > -- > > > Author: > > > INET: [EMAIL PROTECTED] > > > > > > Fat City Network Services -- 858-538-5051 > http://www.fatcity.com> > San Diego, California -- Mailing > list and web hosting > > services> ------------------------------------------------------- > -- > > ------------ > > > To REMOVE yourself from this mailing list, send an E-Mail message > > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') > and in > > > the message BODY, include a line containing: UNSUB ORACLE-L > > > (or the name of mailing list you want to be removed from). > You may > > > also send the HELP command for other information (like > subscribing).> > > > > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Tanel Poder > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > > San Diego, California -- Mailing list and web hosting > services> --------------------------------------------------------- > ---------- > > -- > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). > > > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).