| Temp tablespace: 0 bytes free ! [message #64150] |
Wed, 22 December 2004 06:12  |
sujit
Messages: 94
Registered: April 2002
|
Member |
|
|
Hi,
if the BYTES_FREE in temp tablespace datafile is 0, what does it mean?
Well, I started all these checking when my DML statements started to 'hang'. Simple insert into select * from started hanging, which used to take about a couple of seconds.
And my select queries are running exceptionally fine. But my DMLs are getting problematic. Moreover, my database (9.2.0.5) has also now a problem of not letting any new logins. It simply hangs. The existing sessions are running fine (may be something to do with archivelog ?)
I ran the following queries.
SELECT TABLESPACE_NAME TS, BYTES_USED, BYTES_FREE
FROM V$TEMP_SPACE_HEADER
gives me 0 bytes free,
TS | BYTES_USED | BYTES_FREE
TMPSEG | 2147483648 | 0
And also:
SELECT
d.tablespace_name ts_name,
NVL(d.bytes / 1024 / 1024, 0) file_size,
NVL(t.bytes_used/1024/1024, 0) used,
d.autoextensible autoxtnd
FROM
dba_temp_files d,
v$temp_space_header t
WHERE (t.file_id (+)= d.file_id) gives me the
following:
TS_NAME | FILE_SIZE | USED | AUTOXTND
TMPSEG | 2048 | 2048 | YES
Does it need any repairs? Or some other diagnostics need be run?
Thanks,
Sujit
(urgently need to fix the dml problem)
|
|
|
|
| Re: Temp tablespace: 0 bytes free ! [message #64157 is a reply to message #64150] |
Thu, 23 December 2004 06:48  |
croK
Messages: 170
Registered: April 2002
|
Senior Member |
|
|
When you exetute some ordering statement, oracle will allocate a temporary segment into the temp tablespace and will never deallocate it because it will be more performant next time for reuse. So, it is ok when you see o bytes free for tem tablespace.
I believe that archiving destination full is most probably the reason for users not being able to login.
What additional errors show up in alert.log file?
Best luck.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
