Oh! no. Yes, I am using unix. How do I find out if there is a file size limit.
-----Original Message-----
From: Harish Harbham <Harbh.[Email address protected]
Sent: Jul 7, 2005 9:44 AM
To: [Email address protected]
Subject: RE: Oracle sql query not returning.
**A LazyDBA.com subscriber has responded to your lazydba.com post**
**LazyDBA.com mail shield has forwarded you this email,
**and removed any attachments, and kept your email address secret
**from this person, and any viruses/trojans.
**If you reply to this email, the person will see your email address as normal
**Anything below this line is the original email text
You are probably spooling to a file which has 2G limit. Create your
spool file on file-systems that support large files ( > 2G) ... ask
your Unix person ( assuming you are using unix )
Harish
-----Original Message-----
From: Mulpuri family
[Email address protected]
Sent: 07 July 2005 14:26
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
**********************************************************
If you are not the intended recipient, employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination or copying of this communication and its attachments is strictly prohibited.
If you have received this communication and its attachments in error, please return the original message and attachments to the sender using the reply facility on e-mail.
Internet communications are not secure and therefore the UCLES Group does not accept legal responsibility for the contents of this message. Any views or opinions presented are solely those of the author and do not necessarily represent those of the UCLES Group unless otherwise specifically stated.
The UCLES Group is getting a new look - we are adopting a new name and a new identity which expresses what we do more clearly - from July this year (2005) we will be trading as Cambridge Assessment.
**********************************************************
Oracle LazyDBA home page