Home » RDBMS Server » Server Administration » how to reclaim temp free blocks ? (oracle 11.2.0.1.0 linux 2.6)
how to reclaim temp free blocks ? [message #517507] Tue, 26 July 2011 06:39 Go to next message
kesavansundaram
Messages: 166
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 #517508 is a reply to message #517507] Tue, 26 July 2011 06:44 Go to previous messageGo to next message
John Watson
Messages: 4672
Registered: January 2010
Location: Global Village
Senior Member
Quote:
alter tablespace TEMP coalesce;

I think you mean
alter tablespace temp shrink space;
Re: how to reclaim temp free blocks ? [message #517512 is a reply to message #517508] Tue, 26 July 2011 07:08 Go to previous messageGo to next message
kesavansundaram
Messages: 166
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
Re: how to reclaim temp free blocks ? [message #517513 is a reply to message #517508] Tue, 26 July 2011 07:09 Go to previous message
Michel Cadot
Messages: 59405
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
John Watson wrote on Tue, 26 July 2011 13:44
alter tablespace temp shrink space;


Finally, they give it!
Unfortunately it is only on temp tablespace.

Regards
Michel

Previous Topic: Add tnsentry to TNSNAMES.ORA
Next Topic: database link usage
Goto Forum:
  


Current Time: Mon Oct 20 23:14:19 CDT 2014

Total time taken to generate the page: 0.11233 seconds