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 -----
Date: Sunday, October 26, 2003 9:24 am
> 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).
