| 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: Re: Export Hanging
Thanks Prasad. I have looked in Metalink but could not find anything that relates
exactly to my situation - as I mentioned before, I can't see any non-idle wait in
either v$session_wait or v$session_event. But I'll try tracing the session to see if
that helps (though I still can't figure out why I can't see the session waiting on
anything). If, as you said, the session was hanging while trying to read a table,
wouldn't I see at least some db file reads ? In your case how did did you find that
the session was waiting on that particular event ?
Mandar.
ORACLE-L_at_fatcity.com wrote:
Hi Mandar,
We had similar problem when we did full db export. What we found was, export process was hanging when it was reading one of the system table (in our case, it is SYS.EXU8REFIC). When we saw the execution plan, it is using ALL_ROWS (there are no stats on sys objects but still it is doing ALL_ROWS). We recreated the view with RULE hint and export worked without any problems. I also did research in metalink and I found that the same solution was given to similar questions by oracle tech support. If you have access to metalink, please search with 'export hung' and you will get related information.
hope this helps.
Prasad
"Mandar Shete"
times.com> cc:
Sent by: Subject: Export Hanging
root_at_fatcity.com
10/22/2001 09:50 AM
Please respond to
ORACLE-L
--=_MAILER_ATTACH_BOUNDARY1_200110221181548701943705
Content-Type: text/plain; charset=us-ascii
Hi All,
Oracle 8.1.6.1 NT 4 SP5
I'm facing a problem with all exports hanging on our production database. Tried it several times, but the export seems to get stuck at different points each time. The strange part is that at this point v$session_event shows absolutely nothing happening - all the wait numbers just freeze indefinitely and v$session_wait shows a 'SQL*Net message from client' wait - the session seems to be doing nothing at all. This is the output from v$session_event for my latest try
SID EVENT TOTAL_WAITS
TOTAL_TIMEOUTS
63 latch free 2
2
63 db file sequential read 178 0
63 db file scattered read 6195 0
63 file open 3
0
63 SQL*Net message to client 244859 0 63 SQL*Net more data to client 149282 0 63 SQL*Net message from client 244858 0
I'm running this on the server itself, so the network's not an issue. Tried different export modes and parameters (full, owner, tables, buffer, direct, etc.) but with the same result. I'm pretty new to this site, but I'm told that this problem's been there for some time now but was never really taken seriously since we don't take regular exports.
I'm about to raise a TAR on this one, but any pointers before that would be useful.
TIA, Mandar.
Get Your Private, Free E-mail from Indiatimes at
http://email.indiatimes.com
Buy Music, Video, CD-ROM, Audio-Books and Music Accessories from
http://www.planetm.co.in
--=_MAILER_ATTACH_BOUNDARY1_200110221181548701943705 Content-Type: text/html; charset=us-ascii
Hi All,
Oracle 8.1.6.1 NT 4 SP5
I'm facing a problem with all exports hanging on our production database. Tried it several times, but the export seems to get stuck at different points each time. The strange part is that at this point v$session_event shows absolutely nothing happening - all the wait numbers just freeze indefinitely and v$session_wait shows a 'SQL*Net message from client' wait - the session seems to be doing nothing at all. This is the output from v$session_event for my latest try
SID EVENT
TOTAL_WAITS TOTAL_TIMEOUTS
63
latch free
2
2
63 db file
sequential read
178
0
63
&nb!
sp;db file scattered read
6195
0
63 file open
3
0
63
SQL*Net message to client 244859
0
63
SQL*Net more data to client 149282
!
0
I'm running this on the server itself, so the network's not an issue. Tried different export modes and parameters (full, owner, tables, buffer, direct, etc.) but with the same result. I'm pretty new to this site, but I'm told that this problem's been there for some time now but was never really taken seriously since we don't take regular exports.
I'm about to raise a TAR on this one, but any pointers before that would be useful.
TIA, Mandar.
Get Your Private, Free E-mail from
Indiatimes at
="Arial" size="2">http://email.indiatimes.com
Buy Music,
Video, CD-ROM, Audio-Books and Music Accessories from
http://www.planetm.co.in"http://www.planetm.co.in
--=_MAILER_ATTACH_BOUNDARY1_200110221181548701943705--
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mandar Shete INET: mandar_shete_at_indiatimes.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- 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.com -- Author: INET: Prasada.Gunda1_at_hartfordlife.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- 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). Get Your Private, Free E-mail from Indiatimes at http://email.indiatimes.com Buy Music, Video, CD-ROM, Audio-Books and Music Accessories from http://www.planetm.co.in --=_MAILER_ATTACH_BOUNDARY1_2001102212345401867107722 Content-Type: text/html; charset=us-ascii <P>Thanks Prasad. I have looked in Metalink but could not find anything that relates exactly to my situation - as I mentioned before,<EM> I can't see any non-idle wait in either v$session_wait or v$session_event. But I'll try tracing the session to see if that helps (though I still can't figure out why I can't see the session waiting on anything). If, as you said, the session was hanging while trying to read a table, wouldn't I see at least some db file reads ? In your case how did did you find that the session was waiting on that particular event ?</EM></P> <P><EM>Mandar.</EM></P> <P><BR><BR><B><I>ORACLE-L_at_fatcity.com</B></I> wrote:<BR><BR></P> <BLOCKQUOTE style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #1010ff 2px solid"><BR><BR>Hi Mandar,<BR><BR>We had similar problem when we did full db export. What we found was,<BR>export process was hanging when it was reading one of the system table (in<BR>our case, it is SYS.EXU8REFIC). When we saw the execution plan, it is using<BR>ALL_ROWS (there are no stats on sys objects but still it is doing<BR>ALL_ROWS).<BR><BR>We recreated the view with RULE hint and export worked without any<BR>problems. I also did research in metalink and I found that the same<BR>solution was given to similar questions by oracle tech support. If you have<BR>access to metalink, please search with 'export hung' and you will get<BR>related information.<BR><BR>hope this helps.<BR>Prasad<BR><BR><BR><BR><BR>"Mandar Shete" <BR><MANDAR_SHETE_at_INDIA <ORACLE-L_at_fatcity.com ORACLE-L list of recipients Multiple To:><BR>times.com> cc: <BR>Sent by: Subject: Export Hanging <BR>root_at_fatcity.com <BR><BR><BR!Received on Mon Oct 22 2001 - 13:13:03 CDT
!
!
>10/22/2001 09:50 AM <BR>Please respond to <BR>ORACLE-L ><BR><BR><BR><BR><BR><BR><BR>--=_MAILER_ATTACH_BOUNDARY1_200110221181548701943705<BR>Content-Type: > text/plain; charset=us-ascii<BR><BR><BR>Hi All,<BR><BR><BR><BR>Oracle 8.1.6.1 NT 4 >SP5<BR><BR><BR>I'm facing a problem with all exports hanging on our production >database.<BR>Tried it several times, but the export seems to get stuck at >different<BR>points each time. The strange part is that at this point >v$session_event<BR>shows absolutely nothing happening - all the wait numbers just >freeze<BR>indefinitely and v$session_wait shows a 'SQL*Net message from client' >wait<BR>- the session seems to be doing nothing at all. This is the output >from<BR>v$session_event for my latest try<BR><BR><BR>SID EVENT >TOTAL_WAITS<BR>TOTAL_TIMEOUTS<BR>63 latch free 2<BR>2<BR>63 db file sequential read >178 0<BR>63 db file scattered read 6195 0<BR>63 file open 3<BR>0<BR>63 SQL*Net >message to client 244859 0<BR>63 SQL*Net more data to client 149282 !
!
!
0<BR>63 SQL*Net message from client 244858 0<BR><BR><BR><BR>I'm running this on the server itself, so the network's not an issue. Tried<BR>different export modes and parameters (full, owner, tables, buffer, direct,<BR>etc.) but with the same result. I'm pretty new to this site, but I'm told<BR>that this problem's been there for some time now but was never really taken<BR>seriously since we don't take regular exports.<BR><BR><BR>I'm about to raise a TAR on this one, but any pointers before that would be<BR>useful.<BR><BR><BR>TIA,<BR><BR><BR>Mandar.<BR><BR><BR><BR><BR><BR><BR>Get Your Private, Free E-mail from Indiatimes at<BR>http://email.indiatimes.com<BR>Buy Music, Video, CD-ROM, Audio-Books and Music Accessories from<BR>http://www.planetm.co.in<BR><BR>--=_MAILER_ATTACH_BOUNDARY1_200110221181548701943705<BR>Content-Type: text/html; charset=us-ascii<BR><BR><BR><BR>Hi All,<BR> <BLOCKQUOTE style="MARGIN-LEFT: 5px; BORDER-LEFT: #1010ff 2px solid; br: 5px"><BR> <P>Oracle 8.1.6.1 NT 4 SP5</P><BR> <P>I'm facing a problem with all exports hanging on our production<BR>database. Tried it several times, but the export seems to get stuck at<BR>different points each time. The strange part is that at this point<BR>v$session_event shows absolutely nothing happening - all the wait numbers<BR>just freeze indefinitely and v$session_wait shows a 'SQL*Net message from<BR>client' wait - the session seems to be doing nothing at all. This is the<BR>output from v$session_event for my latest try</P><BR> <P>SID EVENT <BR> <BR> <BR> TOTAL_WAITS TOTAL_TIMEOUTS<BR>63 <BR> latch free <BR> <BR> <BR> 2 <BR> <BR> 2<BR>63 db file<BR>sequential read <BR> 178 <BR> 0<BR>63&nbs!
!
!
p;<BR> &nb!<BR>!<BR>!<BR>!<BR>sp;db file scattered read <BR> 6195 <BR> 0<BR><BR>63 file open <BR> <BR> <BR> 3 <BR> <BR> 0<BR>63 <BR> SQL*Net message to client 244859 <BR> <BR> 0 <BR>63 <BR>SQL*Net !
!
!
more data to client 149282 <BR> !<BR>!<BR>!<BR>!<BR>; 0 <BR>63 <BR> SQL*Net message from client 244858 <BR> 0<BR></P><BR> <P>I'm running this on the server itself, so the network's not an issue.<BR>Tried different export modes and parameters (full, owner, tables, buffer,<BR>direct, etc.) but with the same result. I'm pretty new to this site, but<BR>I'm told that this problem's been there for some time now but was never<BR>really taken seriously since we don't take regular exports.</P><BR> <P>I'm about to raise a TAR on this one, but any pointers before that would<BR>be useful.</P><BR> <P>TIA,</P><BR> <P>Mandar.</P><BR> <P> </P><BR> <P> </P></BLOCKQUOTE><BR> <HR> <FONT face=Arial size=2><B>Get Your Private, Free E-mail from<BR>Indiatimes at </FONT><A href="http://email.indiatimes.com/"><FONT face<br>="Arial" size="2">http://email.indiatimes.com</FONT></A></B><BR>Buy Music,<BR>Video, CD-ROM, Audio-Books and Music Accessories from <A href="http://email.indiatimes.com/file/scriptlet/<br>http://www.planetm.co.in">http://www.planetm.co.in</A><BR><BR>--=_MAILER_ATTACH_BOUNDARY1_200110221181548701943705--<BR><BR>--<BR>Please see the official ORACLE-L FAQ: http://www.orafaq.com<BR>--<BR>Author: Mandar Shete<BR>INET: mandar_shete_at_indiatimes.com<BR><BR>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051<BR>San Diego, California -- Public Internet access / Mailing Lists<BR>--------------------------------------------------------------------<BR>To REMOVE yourself from this mailing list, send an E-Mail message<BR>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in<BR>the message BODY, include a line containing: UNSUB !
!
!
ORACLE-L<BR>(or the name of mailing list you want to be removed from). You may<BR>also send the HELP command for other information (like subscribing).<BR><BR><BR><BR>-- <BR>Please see the official ORACLE-L FAQ: http://www.orafaq.com<BR>-- <BR>Author: <BR>INET: Prasada.Gunda1_at_hartfordlife.com<BR><BR>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051<BR>San Diego, California -- Public Internet access / Mailing Lists<BR>--------------------------------------------------------------------<BR>To REMOVE yourself from this mailing list, send an E-Mail message<BR>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in<BR>the message BODY, include a line containing: UNSUB ORACLE-L<BR>(or the name of mailing list you want to be removed from). You may<BR>also send the HELP command for other information (like subscribing).</BLOCKQUOTE><BR> <hr><font face="Arial" size="2"><b>Get Your Private, Free E-mail from Indiatimes at </font><a href="http://email.indiatimes.com"><font face="Arial" size="2">http://email.indiatimes.com</font></a></b><br>Buy Music, Video, CD-ROM, Audio-Books and Music Accessories from <A href="http://www.planetm.co.in">http://www.planetm.co.in</A> --=_MAILER_ATTACH_BOUNDARY1_2001102212345401867107722-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mandar Shete INET: mandar_shete_at_indiatimes.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- 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).
 |
 |