Probably letting out some well kept DBA secret query, circulated internally but never shared publicly!
Hi Gurus,
We are using the below query to check the Defragmentation level of a table.
The query however, is not showing too much difference after Defrag activity (MOVE for tables, REBUILD for indexes).
SELECT a.owner,a.table_name,round(SUM ((b.BYTES)/1024/1024),0) occupied_mb,round((a.num_rows*a.avg_row_len/1024/1024),0) "Actual_Used(MB)",
SUM ((b.BYTES)/1024/1024) - round((a.num_rows*a.avg_row_len/1024/1024),0) Wasted, a.PARTITIONED,a.LAST_ANALYZED,a.TABLESPACE_NAME
FROM dba_tables a,dba_segments b
WHERE a.table_name = b.segment_name and a.owner='ABC' and round((b.bytes/1024/1024),0) > 100
group by a.owner,a.table_name, a.num_rows,a.avg_row_len,a.PARTITIONED,a.LAST_ANALYZED,a.TABLESPACE_NAME order by 6,3 desc;
Can anyone please take a look and suggest something that is better ?
Hoping some more well kept DBA secret queries (with respect to calculating Database Object Defrag Level) to tumble out!
I am looking for something outside the DB Advisors within the database.
Cheers,
Trith