OraFAQ Forum: Server Administration » MAXEXTENTS UNLIMITED IN TEMP SEGMENT

Home » RDBMS Server » Server Administration » MAXEXTENTS UNLIMITED IN TEMP SEGMENT

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

MAXEXTENTS UNLIMITED IN TEMP SEGMENT [message #204691]

Tue, 21 November 2006 13:17

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

Report message to a moderator

Re: MAXEXTENTS UNLIMITED IN TEMP SEGMENT [message #204702 is a reply to message #204691]

Tue, 21 November 2006 15:46

joy_division
Messages: 4963
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.

Report message to a moderator

Re: MAXEXTENTS UNLIMITED IN TEMP SEGMENT [message #204948 is a reply to message #204702]

Wed, 22 November 2006 18:56

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 06:28:52 CDT 2024