cancel
Showing results for 
Search instead for 
Did you mean: 

Error in Communication Channel

Former Member
0 Kudos

Hi Experts,

I'm facing the below error in RWB, communication channel, did any one face the this error before, Please guide me about this

Database-level error reported by JDBC driver while executing statement 'SELECT DISTINCT I., M.PC_DESCRIPTION FROM PS_BUD_PROJECTS_INTF I, PS_BUD_PROJECTS_EST_INTF M where i.work_order_number = m.work_order_number and i.work_order_number in (select x.work_order_number from (SELECT DISTINCT H.WORK_ORDER_NUMBER from PS_BUD_PROJECTS_INTF H WHERE (H.SAP_PROC_STATUS IS NULL OR H.SAP_PROC_STATUS != 'X') AND H.WORK_ORDER_NUMBER IN (SELECT DISTINCT T.WORK_ORDER_NUMBER FROM PS_BUD_PROJECTS_INTF T,PS_BUD_PROJECTS_EST_INTF S WHERE T.WORK_ORDER_NUMBER = S.WORK_ORDER_NUMBER)) x where rownum < 1000)'. The JDBC driver returned the following error message: 'java.sql.SQLException: ORA-01652: unable to extend temp segment by 25 in tablespace TEMP '. For details, contact your database server vendor.*

Any ideas would be appreciated. Thanks.

Venkat Anil

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

HI,

You are trying to create a temporary segment but there is not enough space left in the tablespace.

When you try to create a new table or index in a tablespace, it is first created as a temporary segment inside the tablespace before the actual segment is created. There was not enough space left inside the tablespace to successfully complete the creation.

eg:

SQL> create tablespace very_small datafile 'C:\ORACLE\ORADATA\VS01.DBF' size 128k;

Tablespace created.

SQL> create table very_large tablespace very_small as select * from dba_objects;

create table very_large tablespace very_small as select * from dba_objects

*ERROR at line 1:ORA-01652:

unable to extend temp segment by 8 in tablespace VERY_SMALL

In order to solve the problem either extend the datafile attached to the tablespace, add a new datafile to the tablespace or turn on the autoextend feature of the datafile(s).

To check what datafile are attached to the tablespace, use following command:

select file_name, bytes, maxbytes, autoextensiblefrom dba_data_files

where tablespace_name='VERY_SMALL'

To add more space to a file issue following command:

alter database datafile 'C:\ORACLE\ORADATA\VS01.DBF' resize 1m;

To turn on the autoextend feature on a datafile use following command:

alter database datafile 'C:\ORACLE\ORADATA\VS01.DBF' autoextend on next 100m maxsize 2000m;

Regds,

Pinangshuk.

Former Member
0 Kudos

Hi Pinangshuk,

Thanks for your quick responce, actually it is an updated statement, any suggestions on a way to make the following update statement not use so much temp space? Do you have a suggestion on a way to run the update without so many imbedded SQL statements or requiring the rownum. Couldn't a cursor type process be used if you are only processing 1000 records at a time?

The update statement should not be using 24 GB of temp tablespace and not finishing - the analysis by the DBAs implies that a Cartesian join is being done when this update is run.

Any ideas would be appreciated. Thanks.

Shabarish_Nair
Active Contributor
0 Kudos

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:36859040165792

when you create a new segment, Oracle uses temporary extents to initially build it and 
then at the end of the process -- converts (via a simple dictionary update) the extents 
into permanent ones.

It does this so that if the instance crashed 1/2 the way through the index rebuild, SMON 
would find the temporary extents out there and clean them up -- nothing special needs to 
be done.

So, these "temporary" extents are really your INDEX extents and this message is saying 
"sorry, insufficient space to create your index in this tablspace"

Add more space to the ACCT tablespace so it can hold the index.

hope this is similar to what you are facing.

other ref: http://forums.dbasupport.com/forums/printthread.php?t=48185&pp=40