ORA-01652: error

[vijayblr]

Hi,
when i was working with the database i got an error like


ORA-01652: unable to extend temp segment by 320 in tablespace SYSTEM
there are still more free space left in the system tablespace
can any one help me this out
--------------------------------------------------------------------
I also checked for the specific user default tablespace -users
temporary tablespace is temp
-------------------------------------------------------------------

SELECT username, temporary_tablespace, default_tablespace
FROM USER_USERS
USERNAME TEMPORARY_TABLESPACE DEFAULT_TABLESPACE
------------------------------ ------------------------------ ------------------------------
SYS TEMP SYSTEM

[Halo]

You might want to change the temp tablespace from system to temp for whichever user is getting this error.

[gandolf989]

Originally posted by Halo
You might want to change the temp tablespace from system to temp for whichever user is getting this error.

While you are at it you may want to see what other users are defaulted to.

SELECT username, temporary_tablespace, default_tablespace 
  FROM USER_USERS;

Ediso, NJ. I used to work in Raritan Center, AKA Raritan Arsenal. Home of world war I munitions and mustard gas. They were pulling TNT out of the ground accross the street from where I worked.

[jmodic]

You simply need to enlarge your SYSTEM tablespace, that's all. It has nothing to do with the temporary tablespace assigment.

Another question arises, however: why are you creating new segment under user SYS? What have you been doing? Creating the database? Installing some new database options in the data dictionary? Because it is obvious that you've got that error while either creating a new table (CREATE TABLE AS SELECT) or creating a new index on one of the SYS's tables. Are you sure you know what you are doing?

[Gabor]

You may also want to coalesce the SYSTEM tablespace:
ALTER TABLESPACE SYSTEM COALESCE;
If you don't, the total free space may seem to be enough, but it may be comprised of small chunks. (In other words it may be fragmented.)

Since an extent must always be contiguous space, Oracle cannot find a big enough chunk of contiguous space. When you coalesce the fragmented small chunks that happen to be directly next to each other, you are defragmenting your tablespace into fewer but larger chunks of space. Now Oracle may be able to allocate the extent.

To see the free chunks of space, SELECT BYTES FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME='SYSTEM' ORDER BY BYTES DESC;

Gabor

[jmodic]

Originally posted by Gabor
You may also want to coalesce the SYSTEM tablespace:
ALTER TABLESPACE SYSTEM COALESCE;
If you don't, the total free space may seem to be enough, but it may be comprised of small chunks. (In other words it may be fragmented.)

Since an extent must always be contiguous space, Oracle cannot find a big enough chunk of contiguous space. When you coalesce the fragmented small chunks that happen to be directly next to each other, you are defragmenting your tablespace into fewer but larger chunks of space. Now Oracle may be able to allocate the extent.

To see the free chunks of space, SELECT BYTES FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME='SYSTEM' ORDER BY BYTES DESC;

That's all true, however again the question arises: why the SYSTEM tablespace got fragmented!!??? SYSTEM tablespace shoul only hold database dictionary segments and as such it could never get fragmented. There is no "DROP TABLE" or "DROP INDEX" from the data dictionary during the life of the database, so how could (properly managed) system tablespace become fragmented?

[marist89]

Originally posted by jmodic
(properly managed)

being the key words...