Data Blocks  «Prev 

Oracle Table Statistics

Oracle lists the following table statistics in the DBA_INDEXES, USER_INDEXES and ALL_INDEXES data dictionary views:
  1. Number of rows
  2. Number of chained rows
  3. Exact number of data blocks storing data
  4. Exact number of data blocks allocated, but never used
  5. Average row length, including row overhead
  6. Average available free space in each data block

Allocating and deallocating index Space

Like all other segments, indexes may need to have additional extents created or have extra extents deallocated to free up disk space. Oracle adds extents dynamically for indexes, but it is always better to pre-allocate extents to avoid expensive dynamic extent allocation. Here is the syntax for manually allocating extents: 

ALTER INDEX [schema.]index
ALLOCATE EXTENT ([SIZE integer [K|M]]
[DATAFILE ‘filename’])

If the SIZE option were not specified, the size used would be the size of the next extent if it were created automatically. The size of the next extent is stored in the NEXT_EXTENT column of the DBA_INDEXES data dictionary view.
Manually deallocating space enables you to deallocate to a specific size or to deallocate down to the high-water mark of an index. You can never deallocate space below the high-water mark. Reorganizing the index is the only way to lower the high-water mark. Here is the syntax for deallocating extents: 
ALTER INDEX [schema.]index
DEALLOCATE UNUSED ([KEEP integer [K|M]])

  1. DBA_TAB_MODIFICATIONS
  2. ALL_TAB_MODIFICATIONS
  3. USER_TAB_MODIFICATIONS
The views listed above describe tables that have been modified since the last time table statistics were gathered on them. They are not populated immediately, but after a time lapse (usually 3 hours).

Oracle DBA Cloud