OraFAQ Forum: Server Administration » TEMP Tablespace shrink

Home » RDBMS Server » Server Administration » TEMP Tablespace shrink (Oracel 11g RAC on Solaris 10)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

TEMP Tablespace shrink [message #541844]

Thu, 02 February 2012 11:42

imran.reayat
Messages: 2
Registered: February 2012
Location: Kabul

Junior Member

Hello,

I wanted to free up allocated_space in the following query.

select TABLESPACE_SIZE/1024/1024/1024, ALLOCATED_SPACE/1024/1024/1024,FREE_SPACE/1024/1024/1024 from DBA_TEMP_FREE_SPACE;

TABLESPACE_SIZE/1024/1024/1024 ALLOCATED_SPACE/1024/1024/1024 FREE_SPACE/1024/1024/1024

----------------------------- ------------------------------ -------------------------

102.867096 102.843658 51.4394531

As we can see that total size and alloacted size were quite high , so in order to free up allocated space I applied following command.

14:12:53 SQL> alter tablespace CUST_TEMP shrink space;

Tablespace altered.

14:17:32 SQL>

and now I have following

14:17:48 SQL> select TABLESPACE_NAME, TABLESPACE_SIZE/1024/1024/1024,ALLOCATED_SPACE/1024/1024/1024,FREE_SPACE/1024/1024/1024 from DBA_TEMP_FREE_SPACE;

TABLESPACE_NAME TABLESPACE_SIZE/1024/1024/1024 ALLOCATED_SPACE/1024/1024/1024 FREE_SPACE/1024/1024/1024
------------------------------ ------------------------------ ------------------------------ -------------------------
CUST_TEMP .023345947 .011627197 .01171875

14:18:15 SQL>

I am unable to understand what went wrong , what can now I do to increase the total space in temp tablespace to 102 GB.

Regards,
~Imran R

Attachment: cust-session.log
(Size: 5.51KB, Downloaded 1608 times)

Report message to a moderator

Re: TEMP Tablespace shrink [message #541845 is a reply to message #541844]

Thu, 02 February 2012 11:55

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

Welcome to the forum - I hope you will both learn and contribute. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

You need to resize your temp file(s), run this to find out their names and sizes:

select file_name,bytes from dba_temp_files;

then resize it with

alter database tempfile 'whatever-it-is-called' resize 102M;

Report message to a moderator

Re: TEMP Tablespace shrink [message #541848 is a reply to message #541845]

Thu, 02 February 2012 12:02

imran.reayat
Messages: 2
Registered: February 2012
Location: Kabul

Junior Member

Hi John,

Thanks for the reply, following is output

SQL> l
1* select file_name,bytes from dba_temp_files
SQL> /

FILE_NAME | BYTES
------------------------------------------------------------|----------
+DG_RECARC/custdir/cust_temp | 2088960
+DG_RECARC/custdir/cust_temp_2 | 2088960
+DG_RECARC/custdir/cust_temp_3 | 2088960
+DG_RECARC/custdir/cust_temp_4 | 2088960
+DG_RECARC/custdir/cust_temp_5 | 2088960
+DG_RECARC/custdir/cust_temp_6 | 2088960

6 rows selected.

SQL>

Can you please inform if I need to restart DB or instance etc after doing resize on lets say +DG_RECARC/custdir/cust_temp ?

Regards,
~Imran R

Report message to a moderator

Re: TEMP Tablespace shrink [message #541850 is a reply to message #541848]

Thu, 02 February 2012 12:31

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

Please read the forum guide, and always format your copy/paste with [code] tags, as I did.
As for whether you need to re-start the instance, think! Did you need to restart after shrinking the files?

Report message to a moderator

Previous Topic:	Oracle database Configuration Assistant Failure
Next Topic:	how to copy rac database

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 29 02:25:03 CST 2024