Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
Gaps occur in the blocks due to deletion operations in the tables. These spaces remain as wasted space. These spaces should be determined and shrink or move related table to make wasted spaces to usable.
Find Wasted Space Size in Tables in Oracle
You can determine the size of wasted space in the tables with the following query. If the waste space in the tables in the query is too large, the corresponding tables must be moved or shrink. Block size is considered 8K in the query. If there is a different block size, you must change the sections indicated by “* 8” to “* block_size”.
1 2 3 4 5 6 7 8 | SELECT TABLE_NAME, ROUND((BLOCKS * 8),2) "SIZE (KB)", ROUND((NUM_ROWS * AVG_ROW_LEN / 1024), 2) "ACTUAL DATA (KB)", (ROUND((BLOCKS * 8),2) - ROUND((NUM_ROWS * AVG_ROW_LEN / 1024), 2)) "WASTED (KB)" FROM DBA_TABLES WHERE (ROUND((BLOCKS * 8),2) > ROUND((NUM_ROWS * AVG_ROW_LEN / 1024), 2)) AND OWNER LIKE 'ADURUOZ' ORDER BY 4 DESC |
