Home » RDBMS Server » Server Administration » ORA-01652
ORA-01652 [message #212763] Mon, 08 January 2007 03:59 Go to next message
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.
Re: ORA-01652 [message #212764 is a reply to message #212763] Mon, 08 January 2007 04:07 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
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
Re: ORA-01652 [message #212794 is a reply to message #212763] Mon, 08 January 2007 07:12 Go to previous messageGo to next message
user82
Messages: 49
Registered: June 2006
Location: Romania
Member
try to add e new data file to that tablespace...
Re: ORA-01652 [message #212797 is a reply to message #212763] Mon, 08 January 2007 07:16 Go to previous messageGo to next message
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.
Re: ORA-01652 [message #212803 is a reply to message #212797] Mon, 08 January 2007 07:30 Go to previous messageGo to next message
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
Re: ORA-01652 [message #212816 is a reply to message #212803] Mon, 08 January 2007 08:01 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
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 Go to previous messageGo to next message
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
Re: ORA-01652 [message #212852 is a reply to message #212763] Mon, 08 January 2007 09:55 Go to previous message
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.
Previous Topic: Memory Allocation.
Next Topic: Refresh database
Goto Forum:
  


Current Time: Thu Dec 08 20:09:55 CST 2016

Total time taken to generate the page: 0.08693 seconds