Home » RDBMS Server » Server Administration » Tempoary tablespace (solaris 10, Oracle 10g)
Tempoary tablespace [message #396610] Tue, 07 April 2009 05:54 Go to next message
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


Re: Tempoary tablespace [message #396617 is a reply to message #396610] Tue, 07 April 2009 06:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A temporary tablespace can only have manual segment space management.

Regards
Michel
Re: Tempoary tablespace [message #396618 is a reply to message #396610] Tue, 07 April 2009 06:02 Go to previous messageGo to next message
ramoradba
Messages: 2454
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

Re: Tempoary tablespace [message #397343 is a reply to message #396610] Fri, 10 April 2009 02:59 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
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 Go to previous messageGo to next message
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
Re: Tempoary tablespace [message #397360 is a reply to message #397359] Fri, 10 April 2009 05:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Fix it.

Regards
Michel
Re: Tempoary tablespace [message #397361 is a reply to message #396610] Fri, 10 April 2009 05:56 Go to previous messageGo to next message
pokhraj_d
Messages: 117
Registered: December 2007
Senior Member
the reason why I raised the request in this forum.

Pokhraj
Re: Tempoary tablespace [message #397362 is a reply to message #397359] Fri, 10 April 2009 05:59 Go to previous messageGo to next message
babuknb
Messages: 1734
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
Re: Tempoary tablespace [message #397363 is a reply to message #397362] Fri, 10 April 2009 06:00 Go to previous messageGo to next message
babuknb
Messages: 1734
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
Re: Tempoary tablespace [message #397364 is a reply to message #397361] Fri, 10 April 2009 06:12 Go to previous message
Michel Cadot
Messages: 64121
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:
  


Current Time: Wed Dec 07 02:44:58 CST 2016

Total time taken to generate the page: 0.10569 seconds