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.
|
|
|
|
|
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.
|
|
|
|
|
|
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.
|
|
|