Oracle Data Files
I was looking at the contents of a tablespace recently – just the extents, not a block dump – with the intention of shrinking a couple of the associated datafiles (don’t ask why – the reason is both stupid and irrelevant). I needed to know just how much I could shrink immediately, and if there were any quick wins in terms of moving objects from near the end of files so even more space could be reclaimed. I was a little surprised to discover that something which I have been doing for over 20 years was not as common knowledge as I thought it was, so I though I would try to let some more people know how to do this.
So, what’s in a tablespace? It’s made up of a number of datafiles (whether in ASM, RAW or on a journalled file-system is irrelevant for this conversation). Within the datafiles are extents. These are logical groupings of blocks – a multiple of the block size – which show where your objects are stored within the datafile.
I wrote this piece of SQL to show where the extents live in 1992. It still works today, regardless of whether your tablespaces are dictionary or locally managed:
SELECT tablespace_name, file_id, owner, segment_name, block_id begin, blocks, block_id+blocks-1 end, bytes/1024 KB, '' free FROM sys.dba_extents where tablespace_name = 'USERS' UNION SELECT tablespace_name, file_id, '' owner, '' segment_name, block_id begin, blocks, block_id+blocks-1 end, bytes/1024 KB, 'FREE' free FROM sys.dba_free_space where tablespace_name = 'USERS' ORDER BY 1,2,5 TSPC ID OWNER SEGMENT_NAME BEGIN BLOCKS END KB FREE USERS 4 SCOTT DEPT 128 8 135 64 USERS 4 SCOTT PK_DEPT 136 8 143 64 USERS 4 SCOTT EMP 144 8 151 64 USERS 4 SCOTT PK_EMP 152 8 159 64 USERS 4 SCOTT SALGRADE 160 8 167 64 USERS 4 168 128 295 1024 FREE USERS 4 SYS TABLE_1 296 8 303 64 USERS 4 SYS TABLE_1 304 8 311 64 USERS 4 SYS TABLE_1 312 8 319 64 USERS 4 SYS TABLE_1 320 8 327 64 USERS 4 SYS TABLE_1 328 8 335 64 USERS 4 SYS IND_1 336 8 343 64 USERS 4 344 40 383 320 FREE USERS 4 384 128 511 1024 FREE USERS 4 512 128 639 1024 FREE USERS 6 SYS TABLE_2 128 128 255 1024 USERS 6 256 12544 12799 100352 FREE USERS 6 SYS IND_2 12800 256 13055 2048
So, as we can see from the output above, the tablespace USERS has 2 datafiles; “4″ and “6″ (you can identify them fully using view DBA_DATA_FILES). We can shrink file 4 immediately by 1024K+1024K+320K = a bit over 2M.
File 6 is a bit more of a problem. There’s an index IND_2 in the way, unhelpfully located at the end of the datafile, which stops us from shrinking the datafile. If you try to resize the file using ALTER DATABASE DATAFIL E ‘+DATA/orcl/…..’ 10M, it will fail with the error ORA-03297: file contains used data beyond requested RESIZE value. In this case, the remedy may be as simple as performing an ALTER INDEX IND_2 REBUILD TABLESPACE USERS ONLINE; This will (probably) relocate the index somewhere lower in the file and allow us to shrink the datafile. Moving tables with an ALTER TABLE … MOVE command may prove more difficult depending upon your throughput due to the extra locks needed at the start and end of the transaction, and extra care needs to be take with any LOB objects involved.