It occured to my thoughts also but I felt hesitant saying it because in such a scenario I would expect the operating system or the oracle system to throw an error saying file_size limit reached or exceeded.
Gurmohan
www.freemath.info
-----Original Message-----
From: Cirillo Joe
[mailto:oracledba-ezmlmshield-x92793029.[Email address protected]
Sent: Thursday, July 07, 2005 9:40 AM
To: LazyDBA Discussion
Subject: RE: Oracle sql query not returning.
You said it stopped after spooling approx 2 Gig. Are you running this
on an OS that still has the 2 Gig file limit?
Maybe that is why it has stopped because it cannot write out any more.
Just a thought ....
-----Original Message-----
From: Mulpuri family
[mailto:oracledba-ezmlmshield-x77657427.[Email address protected]
Sent: Thursday, July 07, 2005 9:35 AM
To: LazyDBA Discussion
Subject: RE: Oracle sql query not returning.
Yes, it does have exit 0 after the spool off.
-----Original Message-----
From: Kerber Andrew <oracledba-ezmlmshield-x94767851.[Email address
protected]
Sent: Jul 7, 2005 9:27 AM
To: LazyDBA Discussion <[Email address protected]
Subject: RE: Oracle sql query not returning.
Does your sql file have an exit command? Sometimes they just sit there
if you dont have the exit line in the file.
-----Original Message-----
From: Mulpuri family [mailto:oracledba-ezmlmshield-x87961445.[Email
address protected]
Sent: Thursday, July 07, 2005 8:26 AM
To: LazyDBA Discussion
Subject: Oracle sql query not returning.
Hi,
Query has been running for more than 12 hours. It ran for 3 to 4 hours
spooled about 2gig data and then the file size does not increase
anymore. But, the sqlplus session is inactive but the control never
returns back to the program. Running this sql through a shell script. Is
there anyother better way I could tune this sql. Any pointers is greatly
appreciated.
Thanks,
Radhika.
I have the following query:
SELECT /*+ FIRST_ROWS */
RPAD(B.terr_cd,10) b,
RPAD(A.MO_ID,6) c,
LPAD(A.GLX_SPEC_GRP_ID,10) d,
RPAD(' ',10) b,
LPAD(A.BRND_FM_STRN_ID,10) d,
LPAD(A.RTLRX_TRX_CNT,10) d ,
LPAD(A.RTLRX_TRX_QTY,10) d,
LPAD(A.RTLRX_TRX_DLR_AMT,10) d,
LPAD(A.RTLRX_EQU_TRX_CNT,10) d,
LPAD(A.RTLRX_NRX_CNT,10) d,
LPAD(A.RTLRX_NRX_QTY,10) d,
LPAD(A.RTLRX_NRX_DLR_AMT,10) d,
LPAD(A.RTLRX_EQU_NRX_CNT,10) d
FROM
icmsadm2.ics_retl_rx_algn_mv01 a,
icmsadm2.ics_terr_lkp b,
icmsadm2.ics_base_sell_team_lkp c,
capadm.cap_ftp_loc d,
capadm.cap_ftp_selling_team e
WHERE
A.terr_id=B.terr_id
AND A.BASE_SELL_TEAM_ID = C.BASE_SELL_TEAM_ID
AND C.BASE_SELL_TEAM_CD = E.BASE_SELL_TEAM_CD
AND E.FTP_ID = D.FTP_ID
AND D.DIVISION = 'PHL'
And it's using the following explain plan:
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Hint=HINT: FIRST_ROWS 46 M 4840775.45710259
NESTED LOOPS 46 M 2G 4840775.45710259
NESTED LOOPS 46 M 2G 1400420.21119554
NESTED LOOPS 4 64 44.9469050344828
NESTED LOOPS 4 40 19.45834
TABLE ACCESS BY INDEX ROWID CAP_FTP_LOC 1 5 7.8914
BITMAP CONVERSION TO ROWIDS
BITMAP INDEX FULL SCAN CAP_FTP_LOC_I1
TABLE ACCESS BY INDEX ROWID CAP_FTP_SELLING_TEAM 34 170 19.45834
BITMAP CONVERSION TO ROWIDS
BITMAP INDEX SINGLE VALUE CAP_FTP_SELLING_TEAM_I1
TABLE ACCESS BY INDEX ROWID ICS_BASE_SELL_TEAM_LKP 87 522
44.9469050344828
BITMAP CONVERSION TO ROWIDS
BITMAP INDEX SINGLE VALUE ICS_BASE_SELL_TEAM_LKP_I1
PARTITION RANGE ITERATOR
TABLE ACCESS BY LOCAL INDEX ROWID ICS_RETL_RX_ALGN_MV01 405 M 14G
1400420.21119554
BITMAP CONVERSION TO ROWIDS
BITMAP INDEX FULL SCAN ICS_RETL_RX_ALGN_MV01_I1
TABLE ACCESS BY INDEX ROWID ICS_TERR_LKP 10 K 106 K 4840775.45710259
BITMAP CONVERSION TO ROWIDS
BITMAP INDEX SINGLE VALUE ICS_TERR_LKP_I1
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these terms:http://www.lazydba.com/legal.html
Oracle LazyDBA home page