hi guys,
need some clarification.
SQL> select file_name, bytes/1024/1024/1024, maxbytes/1024/1024/1024
2 from dba_temp_files
3 where tablespace_name='TEMP';
FILE_NAME BYTES/1024/1024/1024 MAXBYTES/1024/1024/1024
---------------------------------------- -------------------- -----------------------
/oradata/PROD/temp01.dbf 8.7890625 8.7890625
/oradata/PROD/temp02.dbf 2.9296875 2.9296875
/oradata/PROD/temp03.dbf 2.9296875 2.9296875
/oraindex/PROD/temp04.dbf 2.9296875 2.9296875
/oraindex/PROD/temp05.dbf 2.9296875 2.9296875
/oraindex/PROD/temp06.dbf 2.9296875 2.9296875
/oradata/PROD/temp06.dbf 2.9296875 2.9296875
the bytes is the allocated space right?
Base on the result above let say for temp06, can i also said that it have somehow hit the maxsize and there is a need to add 1 more datafile?
I am monitoring the db cos i bum into some error like:
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP
i understand also the need to catch the sql statement that might needed tuning.
Lastly, can i confirm that allocated space and used space are 2 different thing?
-- To report true free space within the used portion of the TEMPFILE:
SQL> SELECT A.tablespace_name tablespace, D.mb_total,
2 SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
3 D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
4 FROM v$sort_segment A,
5 (
6 SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
7 FROM v$tablespace B, v$tempfile C
8 WHERE B.ts#= C.ts#
9 GROUP BY B.name, C.block_size
10 ) D
11 WHERE A.tablespace_name = D.name
12 GROUP by A.tablespace_name, D.mb_total;
TABLESPACE MB_TOTAL MB_USED MB_FREE
------------------------------- ---------- ---------- ----------
TEMP 28400 3 28397
is alittle confusing here, cos when i see from the result above, it seem that there are alot of free space to be used for other sql statement sorting.
So can i say, during one period of the time, there may be 1 bad sql statement that might 'pushes' the allocation up?