| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Oracle Streams
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 WAITING24 rows selected
> 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 <tanel.poder.003_at_mail.ee>
> 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" <ORACLE-L_at_fatcity.com>
> > 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: tamizh_at_optonline.net
> > >
> > > 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: ListGuru_at_fatcity.com (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: tanel.poder.003_at_mail.ee
> >
> > 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: ListGuru_at_fatcity.com (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: tamizh_at_optonline.net 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: ListGuru_at_fatcity.com (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).Received on Sun Oct 26 2003 - 13:29:24 CST
 |
 |