We help IT Professionals succeed at work.

Temp Tablespace problem

nareshver
nareshver asked
on
7,008 Views
Last Modified: 2008-01-09
Hi there,

Temporary tablespace is full in Oracle and im not able to make any query result. it was autoextend.
whenever i make the query it gives problems like

ORA-12801: error signaled in parallel query server P019
ORA-04030: out of process memory when trying to allocate 1040 bytes (kxs-heap-w,kghsseg : kokeismo)
********************************************************************************
ORA-12801: error signaled in parallel query server P002
ORA-04030: out of process memory when trying to allocate 254476 bytes (QERHJ hash-joi,kllcqas:kllsltba)
********************************************************************************
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04050: error while attempting to allocate 264 bytes of memory
ORA-06512: at "SYS.ORACLE_LOADER", line 19
********************************************************************************
ORA-12801: error signaled in parallel query server P018
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-04030: out of process memory when trying to allocate 123404 bytes (QERGH hash-agg,kllcqas:kllsltba)
ORA-06512: at "SYS.ORACLE_LOADER", line 52


And aslo problem related to the segments.

Its size is above 20 GB now. please tell me the solution to it.

Regards
Comment
Watch Question

This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
I am using external table for my query output. this table has 500 millions of records. and i compare this table with other permanent tables. so after some queries TEMP Tablespace seems full. i made it auto extended and whenever i make query its size increases from 5000 MB to 20,000 MB.
Could you please post explain plan for the sql statement , number  of records  in each table and the indexe information

Author

Commented:
Hi all,

i reset the Temp tablespace to 5000 MB.  its done without any error. i think the Temp tablespace didn't release the used segment thats why the problem came.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.