Home » RDBMS Server » Server Administration » sql suspension (Oracle 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production, AIX 5.2)
sql suspension [message #423806] Mon, 28 September 2009 07:19 Go to next message
ducasio
Messages: 38
Registered: November 2003
Member
Hi everybody,
i am telling that i am not a newbye in oracle sql but now i have given up fronting this issue. The problem i am having is the suspension of a query execution. i explain better. I have a script like this:

--------------
connect my_user/my_password
whenever sqlerror exit 1

create table tab_1 as select ... from ... where ...
;

create table tab_2 as select ... from ... where ...
;
--------------------------

this script i called from sqlplus in unix aix like this:
-----------------
sqlplus -s /nolog <<eof
@my_script
eof
-----------------

the problem i am having is that after execution of the first query and the creation of tab_1 it doesn't start the creation of tab_2. And the most strange is that i don't have the feedback "Table created" after the creation of tab_1. This script runs dayly and sometimes runs without problems and sometimes gives the rpoblem i just described.
So i would really appreciate any idea from all of you.
Thank you in advance,
ducasio

Report message to a moderator

Re: sql suspension [message #423809 is a reply to message #423806] Mon, 28 September 2009 07:27 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Seems to me if your script is dieing half way through, it is because you are getting an error and it is exiting as your WHENEVER told it to.

Can you not find an error message in a log file somewhere or a process results file? You do you such a thing being produced right? If there is an error causing termination of your script then you should see the error. Examine the error to figure out what to do.

Good luck, Kevin

Report message to a moderator

Re: sql suspension [message #423813 is a reply to message #423806] Mon, 28 September 2009 07:40 Go to previous messageGo to next message
ducasio
Messages: 38
Registered: November 2003
Member
Hi Kevin,

i have used the WHENEVER statement because i wanted to manage the errors in unix ksh and to interrupt the execution of the rest of the sql script. Actually the whenever statement is not trigered, otherwise the script would end. It never ends and the session remains active. The first table is created but no feedback is given. The second part of the script doesn't start. And as i told, it does NOT happen always.
I am getting mad from this.

Report message to a moderator

Re: sql suspension [message #423815 is a reply to message #423813] Mon, 28 September 2009 07:54 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
It might help if you can post the exact script and how you are calling it (mask sql/username etc) and a screen session/log.
Generally, using SYSDBA authentication for regular database jobs are just bad practice. For the feedback, check the sql*plus options that were used (set feed etc) or manually set them again within
the sql*plus session.
Just tried your code and it works as expected.

Report message to a moderator

Re: sql suspension [message #423821 is a reply to message #423813] Mon, 28 September 2009 08:17 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Maybe your feedback is set off. Try putting these set commands at the beginning of your script to get additional information.

set feedback 1
set timing on
set time on
set echo on
set termout on
set trimspool on

Kevin

Report message to a moderator

Re: sql suspension [message #423822 is a reply to message #423813] Mon, 28 September 2009 08:18 Go to previous messageGo to next message
ducasio
Messages: 38
Registered: November 2003
Member
i am not using authetification as sysdba
ok the sql script is this one:
-----------
WHENEVER SQLERROR EXIT 1
CONNECT pippo_user/pippo_pwd@db_sid

WHENEVER SQLERROR CONTINUE
DROP TABLE TAB_1;
DROP TABLE TAB_2;

CREATE INDEX TABLE_PIL_IDX ON TABLE_PIL (ID_TAB)
;

WHENEVER SQLERROR EXIT 1
CREATE TABLE TAB_1 AS
SELECT /* ORDERED FULL(A) FULL(B) FULL(C) FULL(CO) FULL(CLI) FULL(GRSGR) */
DISTINCT C.X_OM_INVARIANT_CODE AS UTEN_COD,
CLI.X_CC_STATO_MIGRAZIONE AS STATO_MIGRAZIONE,
DECODE(C.X_OM_IMSI,NULL,NULL,'22201'||C.X_OM_HLR_SIM||C.X_OM_IMSI) AS IMSI,
CO.AGREE_NUM AS CODICE_CONTRATTO,
A.AVAIL_DT AS DATA_INI_OI,
A.REQ_SHIP_DT AS DATA_END_OI,
C.START_DT AS DATA_INI_ASS,
C.END_DT AS DATA_END_ASS,
TO_NUMBER(C.VERSION) AS VERSIONE,
B.REF_NUMBER_4 AS BUNDLED,
C.SERIAL_NUM AS PREFISSO_NUMERO,
C.X_OM_F_TIFB_NUMERO AS NUMERO,
C.INSTALL_DT,
C.X_OM_HLR AS HLR,
C.X_OM_ICCID AS ICCID,
CO.X_OM_PRICE_LST_ID,
C.X_OM_NMU AS NMU,
C.X_OM_RC_DISCOUNT AS SCT_UTE,
C.X_OM_NRC_DISCOUNT AS SCT_PROF,
A.STATUS_CD AS STATO_OI,
C.STATUS_CD AS STATO_ASS,
A.LAST_UPD AS DTA_MOD_OI,
C.LAST_UPD AS DTA_MOD_ASS,
B.PART_NUM,
B.NAME,
B.TYPE,
C.X_OM_ASSET_TYPE,
C.TYPE_CD,
GRSGR.X_OM_GROUP_ID AS GRUPPO,
GRSGR.ITEM_GROUP_NAME,
DECODE(C.X_OM_DESTINATION,'AFFARI','A','RESIDENZIALE','F') AS USO,
CO.X_OM_BILLDETAILS,
C.X_OM_CAUSALE AS COD_CAUSALE,
C.X_OM_LIVELLO_FRODE AS LIVELLO_FRODE,
C.X_OM_TIPO_SOSP AS TIPO_SOSPENSIONE
FROM SIEBEL.S_ORDER_ITEM A,
SIEBEL.S_PROD_INT B,
SIEBEL.S_ASSET C,
SIEBEL.S_DOC_AGREE CO,
SIEBEL.S_ORG_EXT CLI,
(SELECT GR.* FROM SIEBEL.S_AGREE_ITEM GR WHERE X_OM_AGR_ITEM_TYPE = 'GRUPPI E SOTTOGRUPPI') GRSGR,
TABLE_PIL PILOTA
WHERE C.PROD_ID = B.ROW_ID
AND C.CUR_AGREE_ID = CO.ROW_ID
AND B.TYPE IN ('ABBONAMENTO','XBASE','XPLUS','PABX','CARRIER SELECTION')
AND B.PROD_CD = 'PROFILO TARIFFARIO' --GSM + EXTENTION
AND CO.TARGET_OU_ID = CLI.ROW_ID
AND CLI.X_CC_STATO_MIGRAZIONE IN ('MIGRATO MOBILE','INSERITO')
AND CO.ROW_ID = GRSGR.DOC_AGREE_ID (+)
AND C.STATUS_CD IN ('ATTIVO','SOSPESO','DA ATTIVARE','CESSATO')
AND A.ASSET_INTEG_ID(+) = C.INTEGRATION_ID
AND A.X_OM_VERSIONE(+) = NVL(C.VERSION,0)
AND C.SERIAL_NUM = PILOTA.CHIAVE
;

CREATE TABLE TAB_2 AS
SELECT /* ORDERED FULL(A) FULL(C) FULL(P) FULL(PX) FULL(CO) FULL(CLI) */
CLI.X_CC_STATO_MIGRAZIONE AS STATO_MIGRAZIONE,
CO.AGREE_NUM AS CODICE_CONTRATTO,
CASE
WHEN P.SUB_TYPE_CD = 'ADDITIONAL NUMBER'
THEN (SELECT SERIAL_NUM FROM S_ASSET PA WHERE ROW_ID = C.ROOT_ASSET_ID)
WHEN P.SUB_TYPE_CD = '2IN1'
THEN (SELECT SERIAL_NUM FROM S_ASSET PA WHERE ROW_ID = C.ROOT_ASSET_ID)
WHEN P.PART_NUM = 'STS_08_00000179'
THEN (SELECT SERIAL_NUM FROM S_ASSET PA WHERE ROW_ID = C.ROOT_ASSET_ID)
ELSE C.SERIAL_NUM
END AS NUMERO,
TO_CHAR(A.AVAIL_DT, 'YYYY-MM-DD') AS DATA_INI_OI,
TO_CHAR(A.REQ_SHIP_DT, 'YYYY-MM-DD') AS DATA_END_OI,
TO_CHAR(C.START_DT, 'YYYY-MM-DD') AS DATA_INI_ASS,
TO_CHAR(C.END_DT, 'YYYY-MM-DD') AS DATA_END_ASS,
PX.ATTRIB_44 AS TIPO_SERV,
PX.ATTRIB_01 AS SOTTOTIPO_SERV,
PX.ATTRIB_42 AS NATURA_SERV,
C.X_OM_RC_DISCOUNT AS SCT_UTE,
C.X_OM_NRC_DISCOUNT AS SCT_PROF,
A.STATUS_CD AS STATO_OI,
C.STATUS_CD AS STATO_ASS,
TO_CHAR(A.LAST_UPD, 'YYYY-MM-DD') AS DTA_MOD_OI,
TO_CHAR(C.LAST_UPD, 'YYYY-MM-DD') AS DTA_MOD_ASS,
P.PART_NUM,
P.SUB_TYPE_CD AS SOTTOTIPOLOGIA,
P.NAME,
CASE
WHEN p.SUB_TYPE_CD = '2IN1'
THEN C.SERIAL_NUM
ELSE ''
END AS NUMERO_2IN1,
CASE
WHEN p.SUB_TYPE_CD = 'ADDITIONAL NUMBER'
THEN C.SERIAL_NUM
ELSE ''
END AS NUMERO_AGGIUNTIVO,
DECODE(C.X_OM_IMSI,NULL,NULL,'22201'||C.X_OM_HLR_SIM||C.X_OM_IMSI) AS IMSI,
C.X_OM_ICCID AS ICCID,
CASE
WHEN p.PART_NUM = 'STS_08_00000179'
THEN C.SERIAL_NUM
ELSE ''
END AS NUMERO_OLO,
(SELECT PROD.NAME
FROM S_ASSET PA,
S_PROD_INT PROD
WHERE PA.ROW_ID = C.PAR_ASSET_ID
AND PA.PROD_ID = PROD.ROW_ID
AND PROD.TYPE = 'ONE PACK') AS LABEL_PACK
FROM SIEBEL.S_ORDER_ITEM A,
SIEBEL.S_ASSET C,
SIEBEL.S_PROD_INT P,
SIEBEL.S_PROD_INT_X PX,
SIEBEL.S_DOC_AGREE CO,
SIEBEL.S_ORG_EXT CLI,
TABLE_PIL PILOTA
WHERE C.PROD_ID = P.ROW_ID
AND P.TYPE = 'SERVIZIO SUPPLEMENTARE'
AND P.ROW_ID = PX.ROW_ID
AND C.CUR_AGREE_ID = CO.ROW_ID
AND CO.TARGET_OU_ID = CLI.ROW_ID
AND CLI.X_CC_STATO_MIGRAZIONE IN ('MIGRATO MOBILE','INSERITO')
AND C.INTEGRATION_ID = A.ASSET_INTEG_ID(+)
AND NVL(C.VERSION, 0) < A.X_OM_VERSIONE(+)
AND C.SERIAL_NUM = PILOTA.CHIAVE
and nvl(C.END_DT,to_date('01/01/2070','DD/MM/YYYY'))>=to_date('01/04/2009','DD/MM/YYYY')
;
--------------------

the shell with whom i run the sql script is:
---------------
export FILE_SQL='pippo.sql'
echo "[$SHELLNAME] Start Execution $FILE_SQL il `date '+%d/%m/%Y %T'`"
sqlplus /nolog <<eof
@$CTL_PATH/SQL/$FILE_SQL
eof
if test $? -ne 0
then
echo "[ERR $SHELLNAME] Execution $FILE_SQL"
exit
fi
echo "[$SHELLNAME] End Execution $FILE_SQL il `date '+%d/%m/%Y %T'`"
----------------------------

the log produced is:
-------------------------
[CRMA_NBS.sh] Start Execution pippo.sql il 28/09/2009 07:02:12

SQL*Plus: Release 9.2.0.8.0 - Production on Mon Sep 28 07:02:12 2009

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected.
DROP TABLE TAB_1
*
ERROR at line 1:
ORA-00942: table or view does not exist


DROP TABLE TAB_2
*
ERROR at line 1:
ORA-00942: table or view does not exist

Index created.

-------------------------------

As you see the feedback is enabled. I want to make clear that not always the script remains suspended.

Report message to a moderator

Re: sql suspension [message #423824 is a reply to message #423822] Mon, 28 September 2009 08:24 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
If you are not being kicked out because of an error, my next guess would be you are being tossed out of sqlplus because of a serious error generated by one of the create table statements. This is just a wild guess though.

I shudder at the thought, but try using plsql to do dynamic sql. This way you can interrogate the results of each operation (maybe), or possibly bypass being dumped to the OS on error if this is indeed what is happening.

First try something simple like this:

begin
  execute immediate (...);
  execute immediate (...);
  execute immediate (...);
  execute immediate (...);
end;
/

Then get more sophisticated as you go. At this point we are trying to figure out what is happening, not correct it. I am hoping plsql will behave differently from sql in some regards and maybe lead us to some additional information.

Kevin

Report message to a moderator

Re: sql suspension [message #423825 is a reply to message #423824] Mon, 28 September 2009 08:33 Go to previous message
ducasio
Messages: 38
Registered: November 2003
Member
thank you for the suggestion, i am gonna provide for trying it in an anomynous pl/sql block. Anyway, just for information, the sqlplus session does not fall off. I can see it with ps -ef|grep sqlplus. What i find weird is that i can't trap the error.

I am trying now to use dinamic pl/sql in an anonym block.

thank you

Report message to a moderator

Previous Topic: why commit doesnt make dbwr to write in datafile?
Next Topic: Migration from oracle 9i to 10g (merged)
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Jul 01 00:11:32 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.