Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Oracle Streams

Re: Oracle Streams

From: <tamizh_at_optonline.net>
Date: Sun, 26 Oct 2003 11:24:25 -0800
Message-ID: <F001.005D46FC.20031026112425@fatcity.com> 





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






> 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:24:25 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US