Show TOC

 Tablespace Analysis (Oracle)Locate this document in the navigation structure

Use

You can use this procedure to analyze tablespaces.

Procedure

Choose Tools →Administration →Computing Center →Management System →Control →Performance Menu →Database →Tables / Indexes.

Alternatively, use transaction code DB02.

In the Tablespaces section there are extensive options available for analyzing tablespaces. Some of these options are described below:

  • Current sizes

    You get a complete list of tablespaces with details on:

    • size
    • freespace
    • used space
    • number of objects
    • number of extents

    You can Sort the tablespaces according to a particular feature. For example, you can quickly find out which tablespaces are almost full.

    See also:Checking for Full Tablespaces (Oracle)

    Choose Storage parameter to display the storage parameters of a selected  tablespace.

    Choose Analysis to examine the selected tablespace in more detail.

    • Tables and indexes: displays the objects in this tablespace. 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. The DBA should check these details on a regular basis to avoid possible Problems with Maximum Number of Extents (Oracle). You can, if you wish, start a detailed storage analysis as a background job..
    • Files: displays the files which make up the tablespace. The assignment of files to individual disks is relevant to the DBA, as they may influence performance.
    • Detail Analysis: you can, if you wish, start a detailed storage space analysis as a background job.
    • Statistics: displays the history of a tablespace. The DBA can trace the growth of a tablespace over a particular time period. Changes to the size, freespace or number of extents of a tablespace, for example, are recorded.
    • Freespace analysis: displays a freespace analysis organized by the files of a tablespace. Freespace (in Kilobytes) and number of free blocks are displayed.

      See also:Checking for Freespace Problems (Oracle)

  • Space statistics

    Displays a history of all tablespaces.

    Choose Choose to display a detailed history of an individual tablespace.

  • Freespace statistics

    You get a breakdown of the freespace situation for all tablespaces. Alongside the total freespace available, the largest freespace area (Freespace Maximum) and the number of fragments are also displayed. This provides you with a good overview of the fragmentation of tablespaces. The size of the largest extent is also displayed. The DBA can then assess whether the next extent (if required) can be inserted in the freespace of the tablespace.

    Choose Critical tables/indexes to display the critical objects for the storage situation.

See also:

Storage Management Errors (Oracle)

Checking Storage Parameters (Oracle)

Monitoring Table and Index Fragmentation (Oracle)

Tables/Index Analysis (Oracle)

Extent Analysis (Oracle)