OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » Tempoary tablespace (solaris 10, Oracle 10g)

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

Tempoary tablespace [message #396610]

Tue, 07 April 2009 05:54

pokhraj_d
Messages: 117
Registered: December 2007

Senior Member

Hi,

I am creating one tempoary tablespace using beloe syntax:-
==========================

create temporary tablespace TEMP
tempfile '<PATH>' size 300M
extent management local;

After creating the above tablespaces I found that the segment space management option is "manual".

Can I use "segment space management" at the above syntax, as I am getting error when I am using this option.

Please help how to use this option at "CREATE TEMPORARY TABLESPACE" syntax.

Pokhraj

Report message to a moderator

Re: Tempoary tablespace [message #396617 is a reply to message #396610]

Tue, 07 April 2009 06:02

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

A temporary tablespace can only have manual segment space management.

Regards
Michel

Report message to a moderator

Re: Tempoary tablespace [message #396618 is a reply to message #396610]

Tue, 07 April 2009 06:02

ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

Please read this

http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/statements_7003.htm
Temporary Tablespaces : Temporary tablespaces ARE NOT a candidate for automatic segment-space management:

  CREATE TEMPORARY TABLESPACE temp
  TEMPFILE '/u07/app/oradata/ORA901/temp01.dbf' SIZE 500M REUSE
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 500K
  SEGMENT SPACE MANAGEMENT AUTO;
  ERROR at line 4:
  ORA-30573: AUTO segment space management not valid for this
  type of tablespace

Report message to a moderator

Re: Tempoary tablespace [message #397343 is a reply to message #396610]

Fri, 10 April 2009 02:59

pokhraj_d
Messages: 117
Registered: December 2007

Senior Member

Hi,

The below are the temporary tablespace status:-
============================================
TABLESPACE_NAME FILE_NAME MB AUT
------------------------- ----------------------------------- ---------- ---
PSTEMP /ghrmsqa/oradata01/PSTEMP01.dbf 2000 YES
PSTEMP /ghrmsqa/oradata04/PSTEMP04.dbf 2000 YES

I want to set auto extend max size to 1500 MB for the PSTEMP tablespaces.

Please help me out if there is any command for the same.

Pokhraj.

[Updated on: Fri, 10 April 2009 03:00]

Report message to a moderator

Re: Tempoary tablespace [message #397347 is a reply to message #397343]

Fri, 10 April 2009 03:31

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

ALTER DATABASE TEMPFILE ...

Regards
Michel

[Updated on: Fri, 10 April 2009 03:31]

Report message to a moderator

Re: Tempoary tablespace [message #397359 is a reply to message #396610]

Fri, 10 April 2009 05:41

pokhraj_d
Messages: 117
Registered: December 2007

Senior Member

Hi,

I have tried "ALTER DATABASE TEMPFILE..." but still I am getting error.

Please help.

Pokhraj

Report message to a moderator

Re: Tempoary tablespace [message #397360 is a reply to message #397359]

Fri, 10 April 2009 05:55

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Fix it.

Regards
Michel

Report message to a moderator

Re: Tempoary tablespace [message #397361 is a reply to message #396610]

Fri, 10 April 2009 05:56

pokhraj_d
Messages: 117
Registered: December 2007

Senior Member

the reason why I raised the request in this forum.

Pokhraj

Report message to a moderator

Re: Tempoary tablespace [message #397362 is a reply to message #397359]

Fri, 10 April 2009 05:59

babuknb
Messages: 1736
Registered: December 2005
Location: NJ

Senior Member

SQL> select tablespace_name,bytes,status,maxbytes,autoextensible from dba_temp_files;

TABLESPACE_NAME                     BYTES STATUS      MAXBYTES AUT
------------------------------ ---------- --------- ---------- ---
TEMP                             20971520 AVAILABLE 3.4360E+10 YES

 

SQL> alter  database tempfile 'f:\oracle\product\10.2.0\oradata\KnbDb\TEMP01.DBF' autoextend on next 10m maxsize 2g;

Database altered.

SQL> select tablespace_name,bytes,status,maxbytes,autoextensible from dba_temp_files;

TABLESPACE_NAME                     BYTES STATUS      MAXBYTES AUT
------------------------------ ---------- --------- ---------- ---
TEMP                             20971520 AVAILABLE 2147483648 YES

What's your error message ??

Babu

Report message to a moderator

Re: Tempoary tablespace [message #397363 is a reply to message #397362]

Fri, 10 April 2009 06:00

babuknb
Messages: 1736
Registered: December 2005
Location: NJ

Senior Member

>>the reason why I raised the request in this forum

With out error message details; How we try to help you??

Babu

Report message to a moderator

Re: Tempoary tablespace [message #397364 is a reply to message #397361]

Fri, 10 April 2009 06:12

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

pokhraj_d wrote on Fri, 10 April 2009 12:56

the reason why I raised the request in this forum.

Pokhraj

This is only answer we can give we what you posted.
Do you think there is only ONE possible error or only ONE answer to fix ALL errors?

Regards
Michel

[Updated on: Fri, 10 April 2009 06:13]

Report message to a moderator

Previous Topic:	segment size
Next Topic:	Oracle 10.2.0.4 on SAN Storage.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jul 02 23:39:24 CDT 2024