Hello, folks.
My case is a bit particular. Long story short, I have a proc to reclaim empty space in a tablespace and free it to the SO. I'm not meant to be indelicate, but please spare me the "you don't need to defrag tablespaces" talk, I'm well aware of Oracle space management, but my case is specific and we do need this operation. Also I know about expdp/impdp, but again, this solution not really applicable in my case.
I'm using the ALTER TABLE MOVE / ALTER INDEX REBUILD into the same tablespace approach so the objects are moved in the same tablespace into begining of the datafile. The proc works really well effectivelly freeing up space by reorganizing the tables and then resizing the datafile. The thing is that sometimes it tends to not reclaim enough of the freespace inside the datafile, so I started investigating it and found a curious comportment. It's kind hard to explain with words, so let me give you a real exemple. First let's check the last ten objects sitting in the end of the datafile, identifiying its blockids:
SQL> select * from ( SELECT MAX(BLOCK_ID), OWNER, SEGMENT_NAME, SEGMENT_TYPE
2 FROM DBA_EXTENTS
3 WHERE TABLESPACE_NAME = 'SVX2020_UG'
4 AND BLOCK_ID > 1873634
5 GROUP BY OWNER, SEGMENT_NAME, SEGMENT_TYPE
6 ORDER BY 1 DESC)
7 where rownum<11;
MAX(BLOCK_ID) OWNER SEGMENT_NAME SEGMENT_TYPE
------------- ------------------------ ------------------------ ------------------
2059648 SVX2020_UG SYS_IL0012161040C00004$$ LOBSEGMENT
2059552 SVX2020_UG ZONE_IDX1 INDEX
2059544 SVX2020_UG WPCHOICE_IDX_A INDEX
2059536 SVX2020_UG WORKFORCEVERSION_IDXA INDEX
2059528 SVX2020_UG WORKDAYBLOB_IDXA INDEX
2058496 SVX2020_UG WDYSTATE_IDX_D INDEX
2044032 SVX2020_UG WDYSTATE_IDX_C INDEX
2038912 SVX2020_UG WDYSTATE_IDX_B INDEX
2029952 SVX2020_UG WDYSTATE_IDX_A INDEX
2017536 SVX2020_UG WDYSTATEINFO_IDX_A INDEX
10 rows selected.
Let's take ZONE1_IDX as an exemple: it's last bit is written in the blockid 2059552 (with blocks of 8k its position is roughly at 15.7GB inside the datafile). So, let's check the empty blocks inside the datafile:
SQL> select *
2 from dba_free_space
3 where tablespace_name = 'SVX2020_UG'
4 order by block_id desc;
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------------------------------ ---------- ---------- ---------- ---------- ------------
SVX2020_UG 35 2059776 207618048 25344 1024
SVX2020_UG 35 2059560 720896 88 1024
SVX2020_UG 35 1006464 344981504 42112 1024
SVX2020_UG 35 1005376 524288 64 1024
SVX2020_UG 35 1005056 2097152 256 1024
SVX2020_UG 35 997816 65536 8 1024
SVX2020_UG 35 997248 4194304 512 1024
SVX2020_UG 35 996352 6291456 768 1024
SVX2020_UG 35 985408 131072 16 1024
SVX2020_UG 35 985392 65536 8 1024
SVX2020_UG 35 985352 131072 16 1024
SVX2020_UG 35 982272 16777216 2048 1024
SVX2020_UG 35 978432 25165824 3072 1024
SVX2020_UG 35 978312 851968 104 1024
(hundred more lines...)
So according to dba_free_space there are plenty of empty blocks below the block_id 1873634 mark from the first select. Performing the ALTER INDEX REBUILD into the same tablespace is supposed to recreate the index in a lower block near at the begining of the file. So let's try and check it:
SQL> ALTER INDEX SVX2020_UG.ZONE_IDX1 rebuild noparallel;
Index altered.
SQL> select *
2 from (
3 SELECT MAX(BLOCK_ID), OWNER, SEGMENT_NAME, SEGMENT_TYPE
4 FROM DBA_EXTENTS
5 WHERE TABLESPACE_NAME = 'SVX2020_UG'
6 GROUP BY OWNER, SEGMENT_NAME, SEGMENT_TYPE
7 ORDER BY segment_name DESC
8 )
9 where segment_name = 'ZONE_IDX1'
10 AND ROWNUM<10;
MAX(BLOCK_ID) OWNER SEGMENT_NAME SEGMENT_TYPE
------------- ------------------------ ------------------------ ------------------
2059560 SVX2020_UG ZONE_IDX1 INDEX
What? The index is still sitting near the end of the datafile? Its block_id effectively changed, but from 2059552 to 2059560. Why? The rebuild instead of rewriting it into some free block at the begining choose to write it just few blocks apart, in fact in a block higher than of its original position. What could explain this comportment? Well, let's give another try and rebuild it again:
SQL> ALTER INDEX SVX2020_UG.ZONE_IDX1 rebuild noparallel;
Index altered.
SQL> select *
2 from (
3 SELECT MAX(BLOCK_ID), OWNER, SEGMENT_NAME, SEGMENT_TYPE
4 FROM DBA_EXTENTS
5 WHERE TABLESPACE_NAME = 'SVX2020_UG'
6 GROUP BY OWNER, SEGMENT_NAME, SEGMENT_TYPE
7 ORDER BY segment_name DESC
8 )
9 where segment_name = 'ZONE_IDX1'
10 AND ROWNUM<10;
MAX(BLOCK_ID) OWNER SEGMENT_NAME SEGMENT_TYPE
------------- ------------------------ ------------------------ ------------------
2059552 SVX2020_UG ZONE_IDX1 INDEX
You gotta be kidding me, it just moved it back to the 2059552 original posistion. Is Oracle deliberated messing with me? I few more tries and all the rebuild does is move the index back and forth to the positions 52 and 60, but never effectivelly rebuilding it in a empty block at the begining of the file.
WHY IS IT DOING THIS? IT'S DRIVING ME NUTS!!!!!
OK, calm down, let's start all over, with a new connection. Maybe it will help? Alright, new window, new session, let's try again:
(working in the NEW window/session)
SQL> ALTER INDEX SVX2020_UG.ZONE_IDX1 rebuild noparallel;
Index altered.
SQL>
SQL> select *
2 from (
3 SELECT MAX(BLOCK_ID), OWNER, SEGMENT_NAME, SEGMENT_TYPE
4 FROM DBA_EXTENTS
5 WHERE TABLESPACE_NAME = 'SVX2020_UG'
6 GROUP BY OWNER, SEGMENT_NAME, SEGMENT_TYPE
7 ORDER BY segment_name DESC
8 )
9 where segment_name = 'ZONE_IDX1'
10 AND ROWNUM<10;
MAX(BLOCK_ID) OWNER SEGMENT_NAME SEGMENT_TYPE
------------- ------------------------ ------------------------ ------------------
786696 SVX2020_UG ZONE_IDX1 INDEX
WUT? It worked now? Why? How??? Well, at least it finally moved to somewhere near the begining of the file as intented. I wonder why it did not worked with my first session. Speaking of, it is still open. What happens if I try to rebuild it again there? Let's see:
(working in the FIRST window/session)
SQL> select *
2 from (
3 SELECT MAX(BLOCK_ID), OWNER, SEGMENT_NAME, SEGMENT_TYPE
4 FROM DBA_EXTENTS
5 WHERE TABLESPACE_NAME = 'SVX2020_UG'
6 GROUP BY OWNER, SEGMENT_NAME, SEGMENT_TYPE
7 ORDER BY segment_name DESC
8 )
9 where segment_name = 'ZONE_IDX1'
10 AND ROWNUM<10;
MAX(BLOCK_ID) OWNER SEGMENT_NAME SEGMENT_TYPE
------------- ------------------------ ------------------------ ------------------
786696 SVX2020_UG ZONE_IDX1 INDEX
SQL>ALTER INDEX SVX2020_UG.ZONE_IDX1 rebuild noparallel;
Index altered.
SQL> select *
2 from (
3 SELECT MAX(BLOCK_ID), OWNER, SEGMENT_NAME, SEGMENT_TYPE
4 FROM DBA_EXTENTS
5 WHERE TABLESPACE_NAME = 'SVX2020_UG'
6 GROUP BY OWNER, SEGMENT_NAME, SEGMENT_TYPE
7 ORDER BY segment_name DESC
8 )
9 where segment_name = 'ZONE_IDX1'
10 AND ROWNUM<10;
MAX(BLOCK_ID) OWNER SEGMENT_NAME SEGMENT_TYPE
------------- ------------------------ ------------------------ ------------------
2059552 SVX2020_UG ZONE_IDX1 INDEX
SOOOOOO.... back to its 2059552 original posistion? This means that the first session somehow cached the block_ids used previously by the object? And every time this session tries to rebuild it Oracle will simply juggle the object in these blocks?
This makes no sense at all to me.
Please folks, for those who were patient enough to read until here, could you shed some light in this phenom? I read once in the docs that a move or rebuild into the same tablespace will always try to use the free blocks in the begining of the tablespace, but mentions nothing about caching nor reuising previous block_ids to this operation. No matter how many times I try to move it in the first sessions, it will only move into those two block IDs.
Does anybody have any plausible explanation for this? Am I missing something here????
Thanks in advance for you time.