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

[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

Please help me out in this as I am basically from development side and have little exposure to DBA operations.

Thanks a Lot

 

[MikeLacey]

thread in oracle7 forum answered by carp
Mike
michael.j.lacey@ntlworld.com

 

[jcote]

The mesasge you receive is basically saying that a temporary segment is being created in the tablespace SYSTEM and that there is not enough space in that same tablespace.

A temporary segment is build when you do a SQL that needs to do a big sort or merge. That segment is temporary. Each user is assigned a temporary tablespace in which his temporary segment will be allocated. If you look at the dba_users table, you<ll see a column named TEMPORARY_TABLESPACE. No user should have his temporary tablespace set to SYSTEM. So in you case, change the temporary tablespace for the user who get the error to the temporary tablespace (you should define one if there is none defined yet) defined in your instance. To change the temporary tablespace of a user : ALTER USER TEMPORARY TABLESPACE your_temporary_tablespace;