Home » RDBMS Server » Server Administration » MAXEXTENTS UNLIMITED IN TEMP SEGMENT
MAXEXTENTS UNLIMITED IN TEMP SEGMENT [message #204691] Tue, 21 November 2006 13:17 Go to next message
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
Senior Member

Hi:

I'm getting problems with the creation of an table...

create table table1 as (select * from table2)

My table has 2,000,000 records an i have the follow message error:

ORA-01630: max # extents (505) reached in temp segment in tablespace DM05_DAT...

i´m not a DBA...

How can i specify a storage (MAXEXTENTS UNLIMITED)... or someone knows another option??


Greetings
Alex
Re: MAXEXTENTS UNLIMITED IN TEMP SEGMENT [message #204702 is a reply to message #204691] Tue, 21 November 2006 15:46 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
Oy vey, what version of the database are you using that your tablespaces were created with MAXEXTENTS of 505?

The problem is not with the table you are creating but with the temporary segment that is being created as it is building your table.

Try to add an ancient storage clause to your create table script as such:

create table table1 storage (maxextents unlimited)
as (select * from table2);


You might want to give an INITIAL and/or NEXT parameter also. However, it may just be that the tablespace is inadequately designed and that storage clauses won't help because the TEMP segment is just using the default for the tablespace. In that case, you'll have to ask the DBA to change the default INITIAL, NEXT and/or MAXEXTENTS for the tablespace.

Re: MAXEXTENTS UNLIMITED IN TEMP SEGMENT [message #204948 is a reply to message #204702] Wed, 22 November 2006 18:56 Go to previous message
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
Senior Member

I´m in oracle 9i release 2

I try to create the table... with maxextents unlimited but the user can´t sufficient privileges... i created the table in another tablespace...

Thanks Joy... the next time i'll remember your recommendation

Greetings
Alex
Previous Topic: ORA-12520: TNS:listener could not find available handler for requested type of server
Next Topic: creating schema
Goto Forum:
  


Current Time: Wed Dec 07 06:53:35 CST 2016

Total time taken to generate the page: 0.22373 seconds