OraFAQ Forum: Server Administration » How to Resize Temp Tablespace

Home » RDBMS Server » Server Administration » How to Resize Temp Tablespace (10g)

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

How to Resize Temp Tablespace [message #463226]

Wed, 30 June 2010 05:17

maniatniit
Messages: 49
Registered: June 2010
Location: Mumbai

Member

Hi All

i got a error temp tablespace cannot be resized..
How to Resize Temp Tablespace?

Thanks & Regards
Manish Hemnani

Report message to a moderator

Re: How to Resize Temp Tablespace [message #463231 is a reply to message #463226]

Wed, 30 June 2010 05:30

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

Create a new one of the required size and drop the old one. This is pretty easily found information via google by the way.

Report message to a moderator

Re: How to Resize Temp Tablespace [message #463234 is a reply to message #463231]

Wed, 30 June 2010 05:32

maniatniit
Messages: 49
Registered: June 2010
Location: Mumbai

Member

Can u give me query for that

Report message to a moderator

Re: How to Resize Temp Tablespace [message #463235 is a reply to message #463234]

Wed, 30 June 2010 05:34

cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester

Senior Member

It's documented like all oracle commands.

Report message to a moderator

Re: How to Resize Temp Tablespace [message #463236 is a reply to message #463235]

Wed, 30 June 2010 05:38

maniatniit
Messages: 49
Registered: June 2010
Location: Mumbai

Member

Thanks

Report message to a moderator

Re: How to Resize Temp Tablespace [message #463699 is a reply to message #463226]

Fri, 02 July 2010 11:34

rk44
Messages: 18
Registered: June 2010

Junior Member

u can resize a temp tablespace if resize is greater than already mentioned size ,otherwise it throws error

[Updated on: Fri, 02 July 2010 11:36]

Report message to a moderator

Re: How to Resize Temp Tablespace [message #463711 is a reply to message #463699]

Fri, 02 July 2010 12:29

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You can also reduce the size in some cases.

Don't use IM/SMS speak.

Regards
Michel

Report message to a moderator

Re: How to Resize Temp Tablespace [message #463834 is a reply to message #463699]

Sun, 04 July 2010 13:53

Solomon Yakobson
Messages: 3303
Registered: January 2010
Location: Connecticut, USA

Senior Member

rk44 wrote on Fri, 02 July 2010 12:34

u can resize a temp tablespace if resize is greater than already mentioned size ,otherwise it throws error

Really?

SQL> select file_name,bytes/1024/1024 from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
BYTES/1024/1024
---------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\SOL10\TEMP01.DBF
             64


SQL> alter database tempfile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\SOL10\TEMP01.DBF' resize 32M
  2  /

Database altered.

SQL> select file_name,bytes/1024/1024 from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
BYTES/1024/1024
---------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\SOL10\TEMP01.DBF
             32


SQL>

SY.

Report message to a moderator

Re: How to Resize Temp Tablespace [message #463856 is a reply to message #463226]

Sun, 04 July 2010 21:16

rk44
Messages: 18
Registered: June 2010

Junior Member

a temp tablespace can be resized when it is greater than previous mentioned memory..

Report message to a moderator

Re: How to Resize Temp Tablespace [message #463864 is a reply to message #463856]

Mon, 05 July 2010 00:14

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Which memory? What are you talking about?

Regards
Michel

Report message to a moderator

Re: How to Resize Temp Tablespace [message #464024 is a reply to message #463226]

Mon, 05 July 2010 22:38

suntrupth
Messages: 13
Registered: July 2010

Junior Member

Hi,

Make sure there are no users currently performing any sort operations.

SQL> select tablespace_name, current_users, total_blocks, used_blocks, free_blocks from v$sort_segment;

Altering the tablespace during normal operations should be avoided. Depending on the size of the original segment, you could experience ORA-1562 errors.

Regards,
Suntrupth

[Updated on: Mon, 05 July 2010 22:39]

Report message to a moderator

Previous Topic:	need help on issue of updating statistics
Next Topic:	need help with estimating size of database/datafiles

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Feb 23 21:16:21 CST 2025