Oracle error:Unable to extend temp segment by 140 in tablespace SYSTEM 2

[greatrohit]

Hi,
On running a SQL statement the following error was reported:
ORA-01652: Unable to extend temp segment by 140 in tablespace SYSTEM
(DBDexfet error)

I think the problem is due to less space in tablespace(Which one?)
Please clarify following points:
1) Do we have to increase size of SYSTEM tablespace or temp tablespace.
2) Whether we will be using "Alter tablespace...add datafile..." command or
there is another better way to do it.(Pls dont recommend deleting
tablespace and creating new one as I am not allowed to do it)
3) How do we run this command... that is what are the things we have to take care of
Do we have to make tablespace offline before running this command.
4) In case we have to increase size of SYSTEM tablespace then any precautions we have to take first.
5) Is there any command to increase the size of a specific segment inside a tablespace.
The query is run on approximately 20 thousand records. Please answer the above queries if you are very sure of it because the system is live and with heavy traffic. If anything happens to it then I will be sacked.
On quering "dba_free_space" I was able to get the free space available in the tablespaces. They are as follows:
Tablespace Size(in MB)
RBS 0.625
System 2.082
Temp 0.5351
Temporary_data 0.5351
TradePost 106.373
Tools 4.978
users 0.9492
user_data 0.9980

Thanks a Lot

 

[carp]

This error message is alerting you to a more serious problem than getting an extent! You have at least one user configured to user your SYSTEM tablespace for creating/dropping temporary segments. This can lead to a severely fragmented SYSTEM tablespace - you should NEVER have users set up this way.

STRONGLY recommend that you reconfigure ALL users who are set up with a DEFAULT or TEMPORARY tablespace of SYSTEM. You probably want the DEFAULT tablespace to be users and the TEMPORARY tablespace to be Temp.

The syntax is:
ALTER USER user_name
DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;

 

[shefea]

You should NOT be running transactions in the SYSTEM tablespace, nor should your SYSTEM tablespace be used to house the TEMPORARY Segments.
I sugges you speak with you DBA immediately.
If you have no other choice then to use the current tablespace you can resize the datafile if you know the name of it with the following command:

ALTER DATABASE Datafile
'/your_path/oracle/datafile_name.dbf' resize 500M;

you remember your skating on this ice by using SYSTEM tablespace like this.

viel Gluck !!
shefea