SELECT idx.index_name, SUM(bytes)
FROM dba_segments seg,
dba_indexes idx
WHERE idx.table_owner = <<owner of table>>
AND idx.table_name = <<name of table>>
AND idx.owner = seg.owner
AND idx.index_name = seg.segment_name
GROUP BY idx.index_name
will show you the amount of space actually consumed by each index. I'm not clear if that's exactly what sorts of overhead you're trying to account for and how you are distinguishing "used" and "allocated" in the context of an index. If you want to account for free space in the index, you can use the DBMS_SPACE.SPACE_USAGE procedure to determine how many partially empty blocks are in the index.
The short answer is No. Unfortunately the way to do this in Oracle does require the "big stack of boring queries". The articles you linked to are some of the best information available on the subject. The datafile does indeed become fragmented, so that even if free space exists below the highest segment, Oracle will not automatically consolidate it when a RESIZE
is done.
To "defragment" the tablespace you need to move these segments to the start of the datafile rather than at the end. For tables this is an offline process meaning the table will be unavailable while the move is taking place. Indexes can be moved either offline or with Enterprise Edition they can be moved online. Since you have an outage window I recommend you follow these steps.
A. Shrink datafiles with free space beyond the high water mark. This can be done as follows (the query is similar to Frosty Z's procedure):
SELECT ceil( blocks*(a.BlockSize)/1024/1024) "Current Size",
ceil( (nvl(hwm,1)*(a.BlockSize))/1024/1024 ) "Smallest Poss.",
ceil( blocks*(a.BlockSize)/1024/1024) -
ceil( (nvl(hwm,1)*(a.BlockSize))/1024/1024 ) "Savings",
'alter database datafile '''|| file_name || ''' resize ' ||
ceil((nvl(hwm,1)*(a.BlockSize))/1024/1024/100)*100 || 'm;' "Command"
FROM (SELECT a.*, p.value BlockSize FROM dba_data_files a
JOIN v$parameter p ON p.Name='db_block_size') a
LEFT JOIN (SELECT file_id, max(block_id+blocks-1) hwm FROM dba_extents GROUP BY file_id ) b
ON a.file_id = b.file_id
WHERE ceil( blocks*(a.BlockSize)/1024/1024) - ceil( (nvl(hwm,1)*(a.BlockSize))/1024/1024 )
> 100 /* Minimum MB it must shrink by to be considered. */
ORDER BY "Savings" Desc;
B. After shrinking things above the high water mark, find out what tablespaces would still benefit from having segments moved.
SELECT DISTINCT tablespace_name FROM
(
SELECT tablespace_name, block_id + blocks LastBlock,
lead(block_id) OVER (PARTITION BY File_ID
ORDER BY tablespace_name, file_id, block_id) NextBlock
FROM dba_free_space
) WHERE LastBlock <> NextBlock AND NextBlock IS NOT NULL;
C. For each of these tablespaces determine which segments need to be moved. (Replace USERS with the name of your tablespace or join it with the previous query)
SELECT distinct de.segment_name
FROM dba_extents de
JOIN
(
SELECT tablespace_name, file_id, MIN(block_id) LowestFreeBlock
FROM dba_free_space
WHERE tablespace_name = 'USERS'
GROUP BY tablespace_name, file_id
) dfs ON dfs.tablespace_name = de.tablespace_name AND dfs.file_id = de.file_id
WHERE de.tablespace_name = 'USERS'
AND de.block_id > dfs.LowestFreeBlock;
D. Move each table and rebuild the indexes and statistics.
E. Repeat step A.
I just built most of these queries, so you will want to thoroughly test them before use. I suppose you could create a procedure that would use EXECUTE IMMEDIATE
to create the actual statements to run dynamically, but because queries will receive ORA-08103: Object no longer exists while the move is in progress, I think it is best to control that process manually even if it does mean a bit more time/effort.
Best Answer
Answer is complex and involves the knowing of different Oracle concepts. I try to explain (sorry if I am going to be too wordy).
The High Water Mark (HWM) is the point in a segment beyond which data blocks are unformatted and have never been used (Oracle Concept definition). Considering that you are using ASSM (Automatic Segment Space Management)
all blocks above HWM are unformatted and never been used. All blocks below HWM might be:
Formatted and contain data
Formatted and empty because the data was deleted
Allocated, but currently unformatted and unused
DML operations do not release free space from the table below the HWM. Therefore a lot of DML on a table might leave the table fragmented. DDLs always reset the HWM. More consideration about the block use can be done considering two impotant storage parameters: PCTFREE and PCTUSED.
What about HWM in files? The high water mark of a file shows you the smallest size the file could be "shrunk" down to. Any DBA has its own query to find the HWM in a datafile. Personally I use one taken by Tom Kyte and is this:
The HWM position in a datafile does no represent necessarily the free space available in a datafile. You may have a 20Gb tablespace where dba_free_space shows 19gb free but latest HWM is set at 18Gb position.
In conclusion: the position of HWM in a datafile does not represent the real space freely available in the datafile. For this, the query on
sm$ts_free
is right. Additionally tables can have allocated more space than one is really used.