Home » SQL & PL/SQL » SQL & PL/SQL » connection problem
connection problem [message #255005] Mon, 30 July 2007 06:56 Go to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Hi All,

One pl/sql jobs is getting failed due to following error.

0RA-03114 end of file on communication channel


This script is creating on MV. MV script is taking around more then 1 HR. Plz suggest me what can i do for that

--Yash
Re: connection problem [message #255016 is a reply to message #255005] Mon, 30 July 2007 07:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ora-03114: not connected to ORACLE

Post what you really had if you want some help.

In addition:
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: connection problem [message #255028 is a reply to message #255016] Mon, 30 July 2007 07:54 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
pl/sql script is as:

BEGIN
   erp_util_pkg.set_context ('ERP_TK_TARGET_BACKLOG_DAILY_MV');
   erp_util_pkg.begin_refresh ('ERP_TK_TARGET_BACKLOG_DAILY_MV');
   DBMS_SNAPSHOT.REFRESH ('ERP_TK_TARGET_BACKLOG_DAILY_MV', 'c');
   erp_util_pkg.set_context ('ERP_TK_TARGET_BACKLOG_DAILY_MV');
   erp_util_pkg.end_refresh ('ERP_TK_TARGET_BACKLOG_DAILY_MV', 'C');
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE ('Exception : ' || 'SQLcode=' || SQLCODE);
      DBMS_OUTPUT.PUT_LINE (SQLERRM);
END;
/



MV Script is as:-

CREATE MATERIALIZED VIEW CME.ERP_TK_TARGET_BACKLOG_DAILY_MV
PCTFREE    0
PCTUSED    85
INITRANS   2
MAXTRANS   255
STORAGE (
  INITIAL         64 K
  MINEXTENTS      1
  MAXEXTENTS      UNLIMITED
  PCTINCREASE     0
  FREELISTS       1
  FREELIST GROUPS 1
  BUFFER_POOL DEFAULT)
TABLESPACE TRANSD
LOGGING
NOCACHE
PARALLEL 16
USING INDEX PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE (
  BUFFER_POOL DEFAULT)
REFRESH FORCE
  WITH ROWID
 USING DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE AS
(SELECT report_identifier,
component_item_id,
-- inventory_item_id,
organization_id,
targeted_fcd_date,
SUM(qty) qty
FROM (
SELECT /*+ use_nl(b, etfb, eb, hdr) */ 'TURNKEY' report_identifier,
etfb.component_item_id,
esic.part item,
b.inventory_item_id,
b.organization_code org,
b.organization_id,
decode(nvl(hdr.target_date,b.fcd_date),null, TRUNC(SYSDATE)-1,
decode(sign(decode(sign(trunc(b.fcd_date)-trunc(nvl(hdr.target_date,trunc(b.fcd_date+20))-nvl(hdr.transit_pad,0))),-1,
trunc(b.fcd_date),decode(sign(trunc(hdr.target_date)-trunc(eb.request_date)),-1,
(trunc(eb.request_date)-nvl(hdr.transit_pad,0)),
trunc(hdr.target_date-nvl(hdr.transit_pad,0))))- trunc(sysdate)), -1, trunc(sysdate),
decode(sign(trunc(b.fcd_date) - nvl(trunc(NVL(hdr.target_date,
NVL(hdr.promise_date,eb.request_date))-nvl(hdr.transit_pad,0)),trunc(b.fcd_date))),-1, to_char(b.fcd_date, 'DD-MON-YYYY'),
decode(eb.early_ship_flag,'NO',nvl(to_char(b.fcd_date, 'DD-MON-YYYY'),TRUNC(SYSDATE)-1),'YES',
to_char(nvl(decode(sign(trunc(hdr.target_date)-trunc(eb.request_date)),-1,(trunc(eb.request_date)-nvl(hdr.transit_pad,0)),
trunc(hdr.target_date-nvl(hdr.transit_pad,0))),to_char(b.fcd_date,'DD-MON-YYYY')),'DD-MON-YYYY'))) ) )
targeted_fcd_date,((DECODE(SIGN(NVL(b.ordered_quantity,0) - NVL(b.staged_quantity,0)),-1,0,
(NVL(b.ordered_quantity,0) - NVL(b.staged_quantity,0)))) * etfb.component_quantity) qty
FROM erp_system_items esic,
erp_tk_flat_bom etfb,
erp_backlog eb,
erp_dbp_backlog b,
erp_organizations eo,
em_organization emo,
erp_system_items esi,
om_xxcmf_atp_hdr_archive hdr
WHERE 1=1
--and etfb.component_item_id = 7216682--1989471--7216682--1989471 --4407 --, 4387, 4385, 4409, 4411)
--and etfb.organization_id = 1288
-- and b.inventory_item_id = 4407 --, 4387, 4385, 4409, 4411)
-- and b.organization_id = 16
AND esi.organization_id = b.organization_id
AND esi.inventory_item_id = b.inventory_item_id
AND esi.item_type NOT IN ('OPTION CLASS', 'PRINTABLE OC')
AND b.organization_code = eo.organization_code
AND b.time_zone_id IN (1,4,6,8,11,12) --P_time_zone
AND emo.TIME_ZONE_ID = b.time_zone_id
AND eo.lean_flag='Y'
AND eo.organization_code = emo.organization_code
AND emo.as2_time_zone_id IN (1,4,6,8,11,12) --P_time_zone
AND (b.ship_set_hold_status is null or
(b.ship_set_hold_status = 'HOLD'
AND NOT EXISTS (SELECT 1
FROM OM_OE_ORDER_HOLDS_ALL hold
, OM_OE_HOLD_DEFINITIONS od
WHERE 1=1
AND hold.header_id = eb.header_id
AND (hold.line_id = eb.line_id or hold.line_id is null)
AND hold.global_name = eb.global_name
AND hold.hold_release_id is null
AND od.hold_id = to_number(hold.attribute13)
AND od.global_name = eb.global_name
AND od.name in ('Cancellation', 'Auto Cancellation Hold')
)
))
AND b.inventory_item_id = etfb.inventory_item_id
AND b.organization_id = etfb.organization_id
AND esic.inventory_item_id = etfb.component_item_id
AND esic.organization_id = etfb.organization_id
AND b.line_id = eb.line_id
AND b.global_name = eb.global_name
AND eb.inventory_item_id = etfb.inventory_item_id
AND hdr.so_header_id(+) = eb.header_id
AND hdr.ship_set_number(+) = eb.ship_set_number
AND NVL(hdr.user_group_code,'X')
= (SELECT NVL(MAX (atph1.user_group_code),'X')
FROM om_xxcmf_atp_hdr_archive atph1
WHERE eb.header_id = atph1.so_header_id(+)
AND eb.ship_set_number = atph1.ship_set_number(+)
AND hdr.global_name = atph1.global_name (+)
) )
WHERE targeted_fcd_date is not null
AND targeted_fcd_date >= trunc(sysdate)
GROUP BY report_identifier,
component_item_id,
--inventory_item_id,
organization_id,
targeted_fcd_date
UNION ALL
SELECT report_identifier,
component_item_id,
-- inventory_item_id,
organization_id,
targeted_fcd_date,
SUM(qty) qty
FROM (
SELECT /*+ use_nl(b, etfb, eb, hdr) */ 'TURNKEY' report_identifier,
etfb.component_item_id,
esic.part item,
b.inventory_item_id,
b.organization_code org,
b.organization_id,
trunc(sysdate) targeted_fcd_date,
((DECODE(SIGN(NVL(b.ordered_quantity,0) - NVL(b.staged_quantity,0)),-1,0,(NVL(b.ordered_quantity,0) - NVL(b.staged_quantity,0)))) * etfb.component_quantity) qty
FROM erp_system_items esic,
erp_tk_flat_bom etfb,
erp_dbp_backlog b,
erp_backlog eb,
erp_organizations eo,
em_organization emo,
erp_system_items esi,
om_xxcmf_atp_hdr_archive hdr
WHERE 1=1
--and etfb.component_item_id = 7216682--418196--22351--1989471--7216682--1989471 --4407 --, 4387, 4385, 4409, 4411)
--and etfb.organization_id = 1288
-- and b.inventory_item_id = 4407 --, 4387, 4385, 4409, 4411)
AND esi.organization_id = b.organization_id
AND esi.inventory_item_id = b.inventory_item_id
AND esi.item_type NOT IN ('OPTION CLASS', 'PRINTABLE OC')
AND b.organization_code = eo.organization_code
AND b.time_zone_id IN (1,4,6,8,11,12) --P_time_zone
AND emo.TIME_ZONE_ID = b.time_zone_id-- and b.organization_id = 16
AND eo.lean_flag='Y'
AND eo.organization_code = emo.organization_code
AND emo.as2_time_zone_id IN (1,4,6,8,11,12) --P_time_zone
AND (b.ship_set_hold_status is null or
(b.ship_set_hold_status = 'HOLD'
AND NOT EXISTS (SELECT 1
FROM OM_OE_ORDER_HOLDS_ALL hold
, OM_OE_HOLD_DEFINITIONS od
WHERE 1=1
AND hold.header_id = eb.header_id
AND (hold.line_id = eb.line_id or hold.line_id is null)
AND hold.global_name = eb.global_name
AND hold.hold_release_id is null
AND od.hold_id = to_number(hold.attribute13)
AND od.global_name = eb.global_name
AND od.name in ('Cancellation', 'Auto Cancellation Hold')
)
))
AND b.inventory_item_id = etfb.inventory_item_id
AND b.organization_id = etfb.organization_id
AND esic.inventory_item_id = etfb.component_item_id
AND esic.organization_id = etfb.organization_id
AND b.line_id = eb.line_id
AND b.global_name = eb.global_name
AND eb.inventory_item_id = etfb.inventory_item_id
AND hdr.so_header_id(+) = eb.header_id
AND hdr.ship_set_number(+) = eb.ship_set_number
AND NVL(hdr.user_group_code,'X')
= (SELECT NVL(MAX (atph1.user_group_code),'X')
FROM om_xxcmf_atp_hdr_archive atph1
WHERE eb.header_id = atph1.so_header_id(+)
AND eb.ship_set_number = atph1.ship_set_number(+)
AND hdr.global_name = atph1.global_name (+)
) )
WHERE targeted_fcd_date is not null
AND targeted_fcd_date < trunc(sysdate)
GROUP BY report_identifier,
component_item_id,
--inventory_item_id,
organization_id,
targeted_fcd_date)


oracle version -9.0.2.6


let me know if i mised some thing . plz let me know where is the problem

--Yash
Re: connection problem [message #255034 is a reply to message #255028] Mon, 30 July 2007 08:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You missed:
1/ to cut your line to 80 characters
2/ to give the error

Regards
Michel
Re: connection problem [message #255049 is a reply to message #255034] Mon, 30 July 2007 08:59 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
OP, you're working on 8i I guess? (because of the dbms_snapshot, that's dbms_mview from 9i up). If so, it could be you're hitting bug 1314292, see Metalink.

Regards,
Sabine

Re: connection problem [message #255051 is a reply to message #255034] Mon, 30 July 2007 09:07 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
error is as

0RA-03114 end of file on communication channel



genrally this view took 40 minutes times to refresh but now it is taking more time, meanwhile oracle has lost the connection ..

i am working on 9i i am sure.


any suggestion michle ..or i need to cut the line then you will understand MV Laughing

--Yash
Re: connection problem [message #255053 is a reply to message #255051] Mon, 30 July 2007 09:11 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
In that case, I would suggest first replacing the code with non-obsolete code. Dbms_snapshot is not even in de 9i documentation any more. But hey, if you are personally attached to dbms_snapshot, by all means use it.
(just don't complain about any errors you get while doing so...)
Re: connection problem [message #255054 is a reply to message #255051] Mon, 30 July 2007 09:12 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
i have run the following query

SELECT STALENESS,COMPILE_STATE FROM ALL_MVIEWS 
WHERE MVIEW_NAME =  'ERP_TK_TARGET_BACKLOG_DAILY_MV'


output is as:-

UNUSABLE,NEEDS_COMPILE


that means some base table has chnage ..so we need to compile once again?

--Yash
Re: connection problem [message #255055 is a reply to message #255051] Mon, 30 July 2007 09:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But
YOU ERROR MESSAGE IS WRONG
Quote:
ora-03114: not connected to ORACLE

I just need you paste reality and not fantasy.

Regards
Michel

Re: connection problem [message #255059 is a reply to message #255055] Mon, 30 July 2007 09:25 Go to previous message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Yes i am sorry Michle!! i chked log table once again.


select ERROR_MESSAGE from erp_extracts where table_name='ERP_AVP_TRANSACTIONS'


output is as-:

03113: end-of-file on communication channel ORA-03114: not connect




--Yash

Previous Topic: Identical records with little time difference
Next Topic: Passing list of values to Stored Procedure
Goto Forum:
  


Current Time: Fri Dec 09 23:19:04 CST 2016

Total time taken to generate the page: 0.10773 seconds