Re: TEMP Tablespace problem

Dear Moustafa,

Sorry for delay replay your request, then i wanna know something to allow me
help you as much as i can.
First i wanna know why you are warry about TEMP TS Size, i think you should
catsh first what is use the TEMP Tablespace instead of recreate a new one,
because the Temptablespace is completlly freed once you shuting the database
down, and refilled when needed by recursive and shared SQL Statment (ORDER
BY,GROUP BY and SQL Statments that used by more than one user almost
probably using the Default Temporary TS to proccess the sort operations)
if you warray because the Hard DISK Capacity, you can move the Temporary
datafiles to another Disk drive if there, then you can monitore which
transactions use a hug number of sort in tempTS, to catch how you can
enhance that trx as it will affect your APPS Performance.

Do you have Oracle Discoverer configured on your PROD Database?
IF Yes, kindly consider that the Oracle Discoverer use the TEMP tablseppace
by a high frequance to proccess the sort operations

Finnally i wanna notice that, if you decreased the Temp table space the sort
operations will not completed at once a time, but it will continued on a
sperat number of times depends on the space amount of sort needed and amount
available.
Review how to keep most of sort operation done on memory to increase the
performance.

For your Question about statment required to recreate a New TEMP TS
Note: Cange the File pathes, files size and also the Tabelspace name
A. Temporarly Tablespace creation SQL Statment:
CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE
'/disk4/oracle/proddata/TEMP_01.dbf' SIZE 3000M
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 100M;
B. Set Temporary Tablespace as Default
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP";
NOTE: Please apply this scenario on the TEST Server first[which is simmillar
of the PROD], and write down each step that you doing to be applied on PROD

In your case you will need to create a new temp ts by a new name ex. temp1
and then set it as default then drop the Old exists TS TEMP then create a
new one called TEMP and set it as default and finnally you can drop the
temporrary created one TEMP1

Also i recommend you to open an SR to invstigate about how to find the trx
which use the hug number of

If you need more assistant, please do not hiestate to contact me.
Kindest Regards,
Mohamed A. Mostafa
+20104618039
On Fri, Feb 20, 2009 at 11:54 PM, Mostafa Eletriby <m_etrib@xxxxxxxxx>wrote:

>   Hi DBAs
>
> I have a problem in my Temp Tablespace which can cause to me a disaster.
> This day I found my TEMP Tablespace in production system grows so large and
> exceeded the limits.
> I dont know why this happened, it became so fast very large.
>
> As You see in the print screen. It reached 12 GB for the two temp
> datafiles.
> I need a scenario please to drop that tablespace and create it again with
> two temp datafiles each file is 1800 MB   Reusable and auto extend with 100
> MB as It was before.
>
> & what precautions should I take before I do the action plan.
>
> Please rsend me a quick reply if possible before working hours start and
> these files grows larger and I can't manage the free space in drive.
>
> we are working over Oracle Applications 11i.5.10.2
> Database  9.2.0.8
> Operating syatem windows 2000
>
> Thanks alot
> Regards,
>
>
>
>

Other related posts: