[askdba] Re: ORA-03113: end-of-file on communication channel
- From: Nisar Tareen <ntareen@xxxxxxxxx>
- To: askdba@xxxxxxxxxxxxx
- Date: Mon, 18 Oct 2004 06:19:27 -0700 (PDT)
Chirag,
There could be some some Patch regarding Network missing in this database.
Which version of the Database you are using for it.
I could not pin point but all ... road of mind goes to network error. Yes u
could be correct it could be sqlnet parameter setting so again it comes the
banner of Network.
Nisar Tareen
Subodh Deshpande <subodh_deshpande@xxxxxxxxx> wrote:
hi chirag,
apprently it seems so and while doing the procedural replication of some ob=
jects, we have observed that
using curosr is faster and maintainable..too...if your client says do not u=
se cursor use subqueries..they will be further faster..
if you are sure that you have done some changes in sqlnet.ora to resolve th=
is error..last time.. then what naseer and other are prompting about networ=
k issue, do not over look that..
check with tnsping/ping whether there are any packet loss, send=3Dreceived =
you should get
have they come across tns packet failure error or tns12564 errors, specific=
ally if the user keeps his clinet idle (idle mean really no work) say for 1=
5-20 minutes..these errors are due to poor networking conditions..
about sqlnet.ora there is only tcp/ip and named pipes, do you mean tnsnames=
.ora and have you checked the logs associated with them..
take care..subodh
----- Original Message -----
From: Chirag DBA
To: askdba@xxxxxxxxxxxxx
Subject: [askdba] Re: ORA-03113: end-of-file on communication channel
Date: Mon, 18 Oct 2004 12:49:57 +0530
>=20
> Thank you Subodh,
>=20
> but Cursor is very slow I think. My client has asked me not to use
> cursor as it will take lot many time to execute.
>=20
> and I tried running each statement seperately, goes fine.
>=20
> I think I need to change some parameter in SQLNET.ORA Im sure but what
> parameter and what value not sure. I was getting this error in
> previous company where I was working as a Production DBA and did
> something in SQLNET.ORA and resolved. I need to check it again anyhow.
>=20
> Im getting all the outputs after the completion of the Procedure. Any
> idea to get it as and when they occur.
>=20
> - Chirag Majmundar=20
>=20
>=20
> On Mon, 18 Oct 2004 02:12:19 -0500, Subodh Deshpande
> wrote:
> > hello chirag,
> >=20
> > end-of-file communication is due to network, as you are saying there is=
no =3D
> > problem with network.
> > then check the joins the table joins, i feel the joins are not precise.=
.or =3D
> > go for subquery..
> > too many corss joins are happening i suppose..
> > a=3D3Db, b=3D3Dc, b=3D3Df, b=3D3Dg, f=3D3Dd,f=3D3De, g=3D3Dh are these =
correct..
> >=20
> > > > > where a.oprid =3D3D b.oprid
> > > > > and b.person_id =3D3D c.person_id
> > > > > and f.person_id =3D3D b.person_id
> > > > > and f.location=3D3D d.location
> > > > > and f.deptid =3D3D e.deptid
> > > > > and g.person_id =3D3D b.person_id
> > > > > and g.provider_grp_id =3D3D h.provider_grp_id;
> >=20
> > what happens if you execute only select part..
> >=20
> > are you inserting all rows of a to h tables into temp_profile..
> >=20
> > if you are using pl/sql then why are you not using cursor to select the=
row=3D
> > s then insert in a loop..
> >=20
> > to monitor row execution put dbms_output in insert loop..
> >=20
> > e.g.=3D20
> > declare cursor c1 as select... from a to g where conditions
> >=20
> > column declarations...
> > =3D20
> > begin
> > open c1
> > loop
> > fetch c1 into... fetch column list
> > exit when c1%notfound;
> > begin
> > insert into temp_profile values(fetch column lists);
> > dbms_output.put_line(fetch column list);
> > commit;
> > end;
> > end loop;
> > close c1;
> > end;
> >=20
> > ps : some syntex may be here are there..this just simple, basic example=
..
> > set serverouput on; will show only few rows..on set serverput on si=
ze =3D
> > 100000; etc will show more rows..on screen..
> >=20
> > take care..subodh
> > ----- Original Message -----
> > From: Chirag DBA
> > To: tim@xxxxxxxxxxxxx, Nisar Tareen , askdba@freelis=
ts.o=3D
> > rg
> > Subject: [askdba] Re: ORA-03113: end-of-file on communication channel
> > Date: Mon, 18 Oct 2004 11:58:49 +0530
> >=20
> > >=3D20
> > > U r riget tim,
> > >=3D20
> > > I will update the code, that will definitely help me.
> > >=3D20
> > > and Nissar,
> > > as u said it can a problem of NETWORK, well, I can run each statement
> > > seperately without any problem. So I cant say that there is a
> > > networking issue with this.
> > >=3D20
> > > and is there any way that I can get the msg immediately after
> > > executing the something.
> > >=3D20
> > > I mean all the DBMS_OUTPUT messages comes after the completion of the=
Pro=3D
> > cedure.
> > >=3D20
> > > I wanna give some msg in between finishing each action.
> > >=3D20
> > > Any alternative of DBMA_OUTPUT??
> > >=3D20
> > > - Chirag=3D20
> > >=3D20
> > >=3D20
> > > On Sat, 16 Oct 2004 11:24:03 -0600, Tim Gorman wr=
ote:
> > > > Um, what's with the EXECUTE IMMEDIATE 'COMMIT' command? PL/SQL sup=
port=3D
> > s the
> > > > COMMIT and ROLLBACK commands natively. Also, it is a bad habit to =
left=3D
> > -pad
> > > > or right-pad spaces into the EXECUTE IMMEDIATE ' TRUNCATE TABLE
> > > > TEMP_PROFILE_USERS ' command.
> > > >=3D20
> > > > The code shows lack of clarity on the nature of the DBMS_OUTPUT pac=
kage.
> > > > All of the strings "printed" using the procedure PUT_LINE are simply
> > > > buffered into a text string. If SET SERVEROUTPUT ON is run in SQL*=
Plus,
> > > > then this text string is implicitly allocated/cleared by SQL*Plus a=
t the
> > > > start of the procedure call and will be output to standard output by
> > > > SQL*Plus implicitly only at the conclusion of the procedure call.
> > > >=3D20
> > > > So, outputting messages that say "Please Wait ..." will appear stra=
nge =3D
> > as
> > > > all of the messages will be seen only at the conclusion of the proc=
edur=3D
> > e.
> > > > Just an FYI...
> > > >=3D20
> > > > As far as diagnosing the ORA-03113, first try commenting out the IN=
SERT
> > > > involving the database links, and see if that helps it compile. If
> > > > uncommenting the INSERT causes the ORA-03113 again, you may want to=
loo=3D
> > k at
> > > > the setup of your database links and most particularly attempt to r=
un t=3D
> > he
> > > > INSERT statement outside of PL/SQL, to see if it works. In other w=
ords,
> > > > diagnose the database link problems outside of PL/SQL.
> > > >=3D20
> > > > Hope this helps...
> > > >=3D20
> > > >=3D20
> > > > on 10/16/04 3:43 AM, Chirag DBA at ChiragDBA@xxxxxxxxx wrote:
> > > >=3D20
> > > > > Each individual statements are working fine. But when I put them =
in
> > > > > the PROCEDURE, it throws me out n disconnects the session.
> > > > >
> > > > > I m inserting all data into temporary table n don wanna do filter=
on =3D
> > DB Link.
> > > > > after putting them in temporary table I m inserting into my origi=
nal
> > > > > table with filtering.
> > > > >
> > > > > No firewall on DB as I m able to run the SQLs seperately.
> > > > >
> > > > > Any Idea why it gives me this error...???
> > > > >
> > > > > - Chirag Majmundar
> > > > >
> > > > > create or replace procedure populateiCareUsers IS
> > > > > begin
> > > > > dbms_output.put_line('Please Wait Deleting data from the Temp tab=
le');
> > > > > -- delete from temp_profile_users;
> > > > > execute immediate ' TRUNCATE TABLE TEMP_PROFILE_USERS ';
> > > > > dbms_output.put_line('-------------------------------------------=
');
> > > > > dbms_output.put_line('Deleted data from the Temp table....');
> > > > > dbms_output.put_line('-------------------------------------------=
');
> > > > >
> > > > > dbms_output.put_line('-------------------------------------------=
');
> > > > >
> > > > > dbms_output.put_line('Inserting data in to the Temp table....');
> > > > > dbms_output.put_line('-------------------------------------------=
');
> > > > >
> > > > > insert into temp_profile_users
> > > > > select a.oprid
> > > > > , c.last_name
> > > > > , c.first_name
> > > > > , e.descr department
> > > > > , d.descr location
> > > > > , h.name1 provider_grp_name
> > > > > from psoprdefn@icaeurp1 a
> > > > > , psopralias@icaeurp1 b
> > > > > , ps_rd_person_name@icaeurp1 c
> > > > > , ps_location_tbl@icaeurp1 d
> > > > > , ps_dept_tbl@icaeurp1 e
> > > > > , ps_rb_worker@icaeurp1 f
> > > > > , ps_rf_grp_member@icaeurp1 g
> > > > > , ps_rf_provider_grp@icaeurp1 h
> > > > > where a.oprid =3D3D b.oprid
> > > > > and b.person_id =3D3D c.person_id
> > > > > and f.person_id =3D3D b.person_id
> > > > > and f.location=3D3D d.location
> > > > > and f.deptid =3D3D e.deptid
> > > > > and g.person_id =3D3D b.person_id
> > > > > and g.provider_grp_id =3D3D h.provider_grp_id;
> > > > >
> > > > > dbms_output.put_line('Please Wait, Updating the Profile_Users tab=
le..=3D
> > ..');
> > > > >
> > > > > insert into
> > > > > profile_users(BEPUSER_ID,ORIGIN,OPRID,LAST_NAME,FIRST_NAME,DESC_D=
EPT,=3D
> > LOCATION,
> > > > > NAME)
> > > > > select
> > > > >
> > > > > SQ_iCAREUSER.nextval,'iCare',OPRID,LAST_NAME,FIRST_NAME,DEPARTMEN=
T,LO=3D
> > CATION,PR
> > > > > OVIDER_GRP_NAME
> > > > > from temp_profile_users where
> > > > >
> > > > > oprid not in(select oprid from profile_users);
> > > > >
> > > > > EXECUTE IMMEDIATE 'commit';
> > > > > dbms_output.put_line('Profile Users from iCare are Updated and
> > > > > Committed........!!! ');
> > > > > end;
> > > > > /
> > > > >
> > > > > create or replace procedure populateiCareUsers IS
> > > > > *
> > > > > ERROR at line 1:
> > > > > ORA-03113: end-of-file on communication channel
> > > >=3D20
> > > > --
> > > >=3D20
> > > >=3D20
> > > > //www.freelists.org/webpage/oracle-l
> > > >
> > >=3D20
> > >=3D20
> >=20
> > Good Luck..Subodh Deshpande
> >=20
> > =3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=
=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D
> > =3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=
=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D
> > Experience Is Knowledge
> > Wisdom Is Philosophy
> > =3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=
=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D
> > =3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=
=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D
> >=20
> > --=3D20
> > _______________________________________________
> > Find what you are looking for with the Lycos Yellow Pages
> > http://r.lycos.com/r/yp_emailfooter/http://yellowpages.lycos.com/defaul=
t.as=3D
> > p?SRC=3D3Dlycos10
> >=20
> >
>=20
>=20
Good Luck..Subodh Deshpande
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Experience Is Knowledge
Wisdom Is Philosophy
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
--=20
_______________________________________________
Find what you are looking for with the Lycos Yellow Pages
http://r.lycos.com/r/yp_emailfooter/http://yellowpages.lycos.com/default.as=
p?SRC=3Dlycos10
---------------------------------
Do you Yahoo!?
vote.yahoo.com - Register online to vote today!
Other related posts:
