查询信息时Oracle异常 [英] Oracle exception while query information

问题描述

大家好，



以下是我的查询语句，用于检索要显示的信息。但它挂起并在Oralce SQL Plus上出错。为什么会发生这种情况以及如何避免这种情况？



QUERY

< pre lang =sql> 选择 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;

错误显示

ORA-01652：无法在表格空间SIM_TEMP01中将临时段扩展128个



MY FINDOUT

 选择 inst_id，tablespace_name，total_blocks，used_blocks，free_blocks 来自 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  

我的另一个问题是，oracle数据库的查询数据流量是多少？它是否使用TEMP，然后在TEMP满时使用SIM_TEMP01？如果有人乐意提供帮助，我需要解释一下如何为我的知识完成这个过程。

解决方案

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

我的另一个问题是，oracle数据库的查询数据流量是多少？它是否使用TEMP，然后在TEMP满时使用SIM_TEMP01？如果有人乐意提供帮助，我需要解释一下这个过程如何为我所知。

这是一个经常出现的问题...



请看，类似的问题和答案：

http://www.dbforums.com/oracle/1648225-ora-01652-unable-extend-temp-segment-128-tablespace-psaptemp.html [ ^ ]

http://www.dba-oracle.com/sf_ora_01652_unable_to_extend_temp_segment_by_string_in_tablespace_string.htm [ ^ ] - 我最喜欢的;）

http://stackoverflow.com/questions/11839576/ora-01652-unable-to-extend-temp-segment-by-in-tablespace [ ^ ]

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

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

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.

解决方案

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.

It's a quite often question...

Please, see similar questions and answers:
http://www.dbforums.com/oracle/1648225-ora-01652-unable-extend-temp-segment-128-tablespace-psaptemp.html[^]
http://www.dba-oracle.com/sf_ora_01652_unable_to_extend_temp_segment_by_string_in_tablespace_string.htm[^] - my favorite ;)
http://stackoverflow.com/questions/11839576/ora-01652-unable-to-extend-temp-segment-by-in-tablespace[^]

这篇关于查询信息时Oracle异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！