OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » ORA-01652

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

ORA-01652 [message #212763]

Mon, 08 January 2007 03:59

KenJ
Messages: 69
Registered: July 2006
Location: London

Member

All,

I received the error as below when running an index creation:-

SQL> create unique index data_mgr_key_idx1 on data_mgr_aud_keys(dmat_pkey,primary_key_name,primary_key_value);
create unique index data_mgr_key_idx1 on data_mgr_aud_keys(dmat_pkey,primary_key_name,primary_key_value)
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace ACC_CBI

ACC_CBI is a premanenet tablespace. Why is Oracle using temp segments in a permanent tablespace and not the temp tablespace??

Thanks in advance,

Ken.

Report message to a moderator

Re: ORA-01652 [message #212764 is a reply to message #212763]

Mon, 08 January 2007 04:07

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

http://www.orafaq.com/forum/m/196786/42800/?srch=ORA-01652%3A+unable+to+extend+temp+segment#msg_196786

Report message to a moderator

Re: ORA-01652 [message #212794 is a reply to message #212763]

Mon, 08 January 2007 07:12

user82
Messages: 49
Registered: June 2006
Location: Romania

Member

try to add e new data file to that tablespace...

Report message to a moderator

Re: ORA-01652 [message #212797 is a reply to message #212763]

Mon, 08 January 2007 07:16

KenJ
Messages: 69
Registered: July 2006
Location: London

Member

Thanks guys. I knew that by adding space to the permanent tablespace that this error would disappear.

I didn't know that the index sort would be done in temp segments in the permanent tablespace. I thought that this operation would be carried out in the team tablespace.

We live and learn.

Cheers,

Ken.

Report message to a moderator

Re: ORA-01652 [message #212803 is a reply to message #212797]

Mon, 08 January 2007 07:30

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

Hi,
It is not your Permanent tablespace. it is your temporary tablespace.

and also you can check. what is your database temporary tablespace.

SQL> column property_value format a25
SQL> column property_name format a25
SQL> select property_value ,property_name
  2  from database_properties
  3  where property_name in ( 'DEFAULT_PERMANENT_TABLESPACE','DEFAULT_TEMP_TABLE
SPACE');

PROPERTY_VALUE            PROPERTY_NAME
------------------------- -------------------------
TEMP                      DEFAULT_TEMP_TABLESPACE
MAKE                      DEFAULT_PERMANENT_TABLESP
                          ACE

regards
Taj

Report message to a moderator

Re: ORA-01652 [message #212816 is a reply to message #212803]

Mon, 08 January 2007 08:01

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>> It is not your Permanent tablespace. it is your temporary tablespace.
The OP is talking about the sort segments within the perm tablespace.
@KenJ
Are you all set?

[Updated on: Mon, 08 January 2007 08:04]

Report message to a moderator

Re: ORA-01652 [message #212829 is a reply to message #212816]

Mon, 08 January 2007 08:31

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

Hi,
can you post below query output.


 select property_name
 from database_properties
 where property_value = 'ACC_CBI';

regards
Taj

Report message to a moderator

Re: ORA-01652 [message #212852 is a reply to message #212763]

Mon, 08 January 2007 09:55

KenJ
Messages: 69
Registered: July 2006
Location: London

Member

Hi,

SQL> select default_tablespace,temporary_tablespace from dba_users
2 where username = 'ACC_CBI';

DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------
ACC_CBI TEMP

SQL> select CONTENTS from dba_tablespaces
2 where TABLESPACE_NAME = 'ACC_CBI';

CONTENTS
---------
PERMANENT

The database_properties only returns the properties for the database and not for users/schemas.

Regards,

Ken.

Report message to a moderator

Previous Topic:	Memory Allocation.
Next Topic:	Refresh database

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 05:42:57 CDT 2024