| how to reclaim temp free blocks ? [message #517507] |
Tue, 26 July 2011 06:39  |
kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI
|
Senior Member |
|
|
Dear Sir / Madam,
we have 5 tempfile ( each of 65 gb ) allocated to TEMP tablespace...and still we are running in short of space..when i checked the TEMP segment usage, i am able to see much FREE blocks. how to release those space ?
TABLESPACE_N FILE_ID FILE_NAME Size(MB)
------------ ---------- ------------------------------------------- ----------
TEMP 1 +DATA/tedw/tempfile/temp.3043.727779755 65535.9688
TEMP 2 +DATA/tedw/tempfile/temp.3042.727779749 65535.9688
TEMP 3 +DATA/tedw/tempfile/temp.3041.727779741 65535.9688
TEMP 4 +DATA/tedw/tempfile/temp.4065.730387401 65535.9688
TEMP 5 +DATA/tedw/tempfile/temp.4075.731586241 65535.9688 SELECT tablespace_name,
total_blocks,
used_blocks,
free_blocks,
total_blocks*16/1024 as total_MB,
used_blocks*16/1024 as used_MB,
free_blocks*16/1024 as free_MB
FROM v$sort_segment;
TABLESPACE_N TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS TOTAL_MB USED_MB FREE_MB
------------ ------------ ----------- ----------- ---------- ---------- ----------
TEMP 9994624 1007360 8987264 156166 15740 140426
1 row selected.
further when i checked the session details using TEMP segment, i got bleow output:
SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,a.username, a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr
ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;
TABLESPACE SEGFILE# SEGBLK# BLOCKS SID SERIAL# USERNAME OSUSER STATUS
------------------------------- ---------- ---------- ---------- ---------- ---------- ------------------------------ ------------------------------ --------
TEMP 15001 3549184 576 475 1237 EQUIPMENT infa ACTIVE
TEMP 15001 4002368 64 796 4677 CRM infa ACTIVE
TEMP 15002 580608 20352 868 615 EDW infa ACTIVE
TEMP 15002 3962112 832 92 1065 EDWSTG infa ACTIVE
TEMP 15002 4021120 576 1236 7257 EQUIPMENT infa ACTIVE
TEMP 15003 23936 64 819 5586 EDW infa ACTIVE
TEMP 15003 3798400 832 855 1801 EDWSTG infa ACTIVE
TEMP 15004 205056 21632 795 8171 EDW infa ACTIVE
TEMP 15004 4031488 832 403 1299 EDWSTG infa ACTIVE
TEMP 15004 4131456 576 19 6802 EQUIPMENT infa ACTIVE
TEMP 15005 3617856 832 1166 6204 EDWSTG infa ACTIVE
TEMP 15005 3741760 576 862 953 EQUIPMENT infa ACTIVE
TEMP 15005 4042752 18176 1226 5379 CDM infa ACTIVE
13 rows selected.
please advise me if i killed the SID - 1226, then those temp blocks ( 18176 blocks ) will be released and can other session use that space further ?
there is one more column - SEGBLK#
could you please explain what is the exact meaning of this column ??
to reclaim the space, should i issue below command - ??
sql>alter tablespace TEMP coalesce;
please advise me.
Thank you
kesavan
|
|
|
|
|
|
| Re: how to reclaim temp free blocks ? [message #517512 is a reply to message #517508] |
Tue, 26 July 2011 07:08  |
kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI
|
Senior Member |
|
|
Hi John,
Yes, need to shrink as per your message. Let me check this and confirm you the same.
further i checked free space as below:
select tablespace_name,TABLESPACE_SIZE/(1024*1024),ALLOCATED_SPACE/(1024*1024),FREE_SPACE/(1024*1024) from dba_temp_free_space;
TABLESPACE_NAME TABLESPACE_SIZE/(1024*1024) ALLOCATED_SPACE/(1024*1024) FREE_SPACE/(1024*1024)
------------------------------ --------------------------- --------------------------- ----------------------
TEMP 659455.688 655359.688 320390
1 row selected.
I am confused..this query outupt shows, free space: 320 gb around. how it is possible... i have 5 temp files allocated ( each having 65 gb ) as below:
TABLESPACE_N FILE_ID FILE_NAME Size(MB)
------------ ---------- ------------------------------------------- ----------
TEMP 1 +DATA/tedw/tempfile/temp.3043.727779755 65535.9688
TEMP 2 +DATA/tedw/tempfile/temp.3042.727779749 65535.9688
TEMP 3 +DATA/tedw/tempfile/temp.3041.727779741 65535.9688
TEMP 4 +DATA/tedw/tempfile/temp.4065.730387401 65535.9688
TEMP 5 +DATA/tedw/tempfile/temp.4075.731586241 65535.9688
TEMP 6 +DATA/tedw/tempfile/temp.4544.757494059 2048
6 rows selected.
( ignore the last one, i have added just now ( 2gb ) , then how free space shows as 320gb in the prev output...?
could you pl advise me.
Thank you,
kesavan
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
