Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Although my temporary tablespace is enough space,the error occurs ORA-01652

550769Dec 8 2006 — edited Dec 12 2006
Hello,
Although my temporary tablespace is enough space ,i am taking an error ORA-01652 and some dbfs in the tablespace are looks like as offline and it’s size looks like as 0 byte.
So that i am changing status of these datafiles to online with this command as follows;
ALTER DATABASE TEMPFILE '/QDBP/q023/q023/temp/qt_lm_tmp01.dbf' ONLINE;
Also, The disk that exist these datafiles was 0 in availibility.
When i issued the command df –k as follows
Filesystem kbytes used avail capacity Mounted on
/dev/vx/dsk/qdbpdg/volq23 20971520 20971520 0 100% /QDBP/q023
is pointed.
And then i have released space from disk
The current review is
/dev/vx/dsk/qdbpdg/volq14 157286400 21731141 127083885 15% /QDBP/q014

My queries and results are for the hint of about the problem:

SELECT A.tablespace_name TABLESPACE, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.NAME, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.NAME, C.block_size
) D
WHERE A.tablespace_name = D.NAME
GROUP BY A.tablespace_name, D.mb_total;

TABLESPACE |MB_TOTAL| MB_USED| MB_FREE
QT_LM_TMP |61440 |12| 61428 --temporary
TS_TEMP_SDPA |15360 |0| 15360 –temporary

Separately,i want to see you a view about alert log like points below.

KCF: write/open error block=0x1b6e0a online=1
file=3 /QDBP/q014/q014/temp/qt_lm_tmp03.dbf
error=27063 txt: 'SVR4 Error: 28: No space left on device
Additional information: -1
Additional information: 8192'
Automatic tempfile offline due to write error on
file 3: /QDBP/q014/q014/temp/qt_lm_tmp03.dbf


With sum,i have some question like below:
1)Although there is enough space on temporary tablespace why i take error ORA-01652?

2)Although there is enough space on temporary tablespace why databasefiles have offline status suddenly?

3) is it important fully rate of disk on operating system if there is enough space on temporary tablespace which has extend properties.

Best regards
Oya YALÇIN
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 9 2007
Added on Dec 8 2006
5 comments
495 views