Question re: ora-1652

Hi Ed,

every user has a default tablespace and a temporary tablespace. check the temporary tablespaces of all users

select USERNAME, TEMPORARY_TABLESPACE
from dba_users;

alter those users who have a temporary tablespace 'SYSTEM' alter user x temporary tablespace <your temp tablespace>;

HtH
Gerd Kock
www.BITS-on-the.net

-----Ursprüngliche Nachricht-----
Von: Ed_Stevens_at_nospam.noway.nohow (Ed Stevens) [mailto:Ed_Stevens_at_nospam.noway.nohow]
Bereitgestellt: Mittwoch, 21. November 2001 20:55 Bereitgestellt in: server
Unterhaltung: Question re: ora-1652
Betreff: Question re: ora-1652

I know that I should know the answer, but don't.

Occasionally my alert logs turn up an ora-1652: "unable to extend temp segment
by 256 in tablespace SYSTEM." Usually it is the SYSTEM tablespace, less often
it is the application data tablespace.

I know that the official fix is to add another file to the tablespace. It also
seems that resizing an existing tablespace file upward should also fix the
problem.

What I'm fuzzy about is why it's trying to allocate a temporary segment in
something other than the temp tablespace. I just double checked the latest
occurance of this, and the only user with the SYSTEM ts specified as its temporary ts is SYS.

Just as puzzeling is why this doesn't seem to be causing any problems. That is,
we've never gotten a user complaint about anything failing. I'm aware that
users will often retry a failed operation and if the failure was due to a
transient problem the retry will work. But I see these just often enough that I
would expect to be hearing from the user community if it were causing failures
at their end.

--
Ed Stevens
(Opinions expressed do not necessarily represent those of my employer.)