Hi there, folks.
I have this nasty undo tablespace that grew up to 1 TB. I performed some verifications and found that it's mostly empty:
be87457@PODWEIN1> SELECT ROUND((alloc-NVL(free,0))/(1024*1024*1024),2) GB_USED,
2 ROUND(A.FREE/(1024*1024*1024),2) GB_FREE,
3 ROUND(ALLOC/(1024*1024*1024),2) TOTAL_TBS_GB
4 FROM
5 (SELECT SUM(bytes) free, tablespace_name
6 FROM sys.dba_free_space
7 GROUP BY tablespace_name
8 ) A,
9 (SELECT SUM(a3.bytes) Alloc,
10 a3.tablespace_name
11 FROM (SELECT a2.bytes,
12 a2.tablespace_name
13 FROM sys.dba_data_files a2
14 ORDER BY a2.tablespace_name
15 )a3
16 GROUP BY a3.tablespace_name
17 )b,
18 dba_tablespaces c
19 WHERE A.tablespace_name = 'UNDOTBS1'
20 AND A.tablespace_name ( +) = b.tablespace_name
21 AND b.tablespace_name = c.tablespace_name(+);
GB_USED GB_FREE TOTAL_TBS_GB
---------- ---------- ------------
262,54 890,75 1153,3
So, 262 GB used and 890 GB free. I tried to shrink its data file but it failed. Some more investigations and I found that there is a damn block sitting exactly in the end of the datafile:
be87457@PODWEIN1> SELECT max(block_id), round ((max(block_id)*16)/ (1024*1024),2) MAX_BLOCK_POSITION
2 FROM dba_extents
3 WHERE file_id = 3;
MAX(BLOCK_ID) MAX_BLOCK_POSITION
------------- ------------------
75447224 1151,23
I searched but I couldn't find any relevant information about identifiying blocks on undo segments. The only thing I got was the dba_undo_extents:
be87457@PODWEIN1> SELECT Segment_name, block_id, BLOCKS, STATUS
2 FROM dba_undo_extents
3 WHERE block_id = 75447224;
SEGMENT_NAME BLOCK_ID BLOCKS STATUS
------------------------------ ---------- ---------- ---------
_SYSSMU37_746482192$ 75447224 4 UNEXPIRED
So, some questions:
I thought the undo tablespace stored not yet commited data. Is it correct to assume that the transaction that wrote this block is still active (not commited yet)?
We're facing this situation for quite some time now, maybe a couple of days. I don't believe that there's a real session connected for so long. Could it be an zombie session?
If both assumptions are true, is there a way to find out wich session is holding the said block? If I kill it will the extent be freed (thus allowing the resize datafile)?
And no offense, but please spare me of the standard clean up solution for undo tablespaces (creating a new one and dropping the old one); I respect it and understand that this is the correct procedure to follow, but unfortunetally on this environment we need an easier and faster solution.
Any insights and tips are welcome.
Thanks in advance.