Extent Analysis (Oracle)

Use

You can use this procedure to analyze the extent structure.

Procedure

Choose Tools →Administration →Computing Center →Management System →Control →Performance Menu →Database →Tables / Indexes.
Alternatively, use transaction code DB02.
Choose Checks in the database system section
The Check for reorganization section provides the following options:

Extents of tables and indexes

Displays all objects with more than 10 extents. Alongside the size of the objects (in Kilobytes and blocks), the number of used extents and the value defined for the object for MAXEXTENTS are displayed. The DBA should check these details on a regular basis to avoid possible Problems with Maximum Number of Extents (Oracle). The value for the NEXT storage parameter is also listed. You can, if you wish, start a detailed storage space analysis as a background job.
Extents per tablespace
You can analyze the extent structure for all tablespaces. In addition to this list output, a second overview displays all tables and indexes with more than 4 extents. Alongside the size of the objects (in Kilobytes and blocks), the number of used extents and the value defined for the object for MAXEXTENTS are also displayed. You can, if you wish, start a detailed storage space analysis as a background job.
Check next extent size
Displays all objects that have exhibited critical growth within the last four weeks. This allows you to trace the growth in number and size of extents. You can immediately see the size of the first extent and the NEXT value defined for the object. You will also find details on how close the number of extents has come to reaching the limit for the number of extents set in the MAXEXTENTS parameter.

You can display a detailed history of an object.