Click here to Skip to main content
15,882,113 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
Hi all,

Below are my query statement to retrieve information to be display. But it hang and give error at Oralce SQL Plus. How come this happen and how to avoid this?

QUERY
SQL
select P.VMP_ACCTNO,P.VMP_NEWACC,C.VMI_INNAME,
C.VMI_ADDR01,C.VMI_ADDR02,C.VMI_ADDR03,C.VMI_POSTCD,
C.VMI_ADDR04,T.ATX_DTBILL,V.VCR_NWRATE,T.ATX_DTBILL,
P.VMP_ADDR01,R.MCD_RDADR1,R.MCD_RDADR3,P.VMP_ANNVAL,
P.VMP_DISCNT,P.VMP_LOTNUM,P.VMP_LNDTLE, T.ATX_NOBILL,
A.MCA_ARDESC,P.MCR_MCD_ROADCD,C.VMI_INEWIC
FROM MVALU.VMPROPINFO P,
MVALU.VMICLIENTN C,
ADMIN.MCROADCODE R,
MVALU.VCRATECODE V,
ADMIN.MCAREACODE A,
MASES.ATTRANSTRX T,
MASES.ATRANSBIL2 T2
WHERE P.VMP_ACCTNO = '32862'
AND P.MCR_MCD_ROADCD = R.MCD_RDCODE
AND R.MCA_MCA_ARCODE = A.MCA_ARCODE
AND P.MCR_MCD_ROADCD = V.MCR_MCD_RDCODE
AND P.VCB_VCB_USEDCD = V.VCB_VCB_USEDCD
AND P.VMP_ACCTNO = T.VMP_VMP_ACCTNO
AND T.VMP_VMP_ACCTNO = T2.VMP_VMP_ACCTNO
ORDER BY P.VMP_ACCTNO;


ERROR DISPLAY
ORA-01652: unable to extend temp segment by 128 in tablespace SIM_TEMP01

MY FINDOUT

SQL
select inst_id,tablespace_name,total_blocks,used_blocks,free_blocks from gv$sort_segment;


   INST_ID TABLESPACE_NAME                 TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
---------- ------------------------------- ------------ ----------- -----------
         1 TEMP                                   38656           0       38656
         1 SIM_TEMP01                            255872           0      255872
         1 SIM_TEMP03                            255872       27392      228480
         1 SIM_TEMP04                             56704           0       56704


My other question, what is the flow of query data to oracle database? Is it use the TEMP, then use SIM_TEMP01 when TEMP is full? I need some explanation how the process done for my knowledge if someone pleasure to help.
Posted
Updated 23-Sep-13 22:14pm
v2

1 solution

 
Share this answer
 
Comments
Luiey Ichigo 2-Oct-13 23:27pm    
I can't extend the segment by myself since the customer database is run by another vendor to handle..whether they extend the temp size or give me another query to optimize the join table searching

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900