Displaying Information About Space Usage for Schema Objects - Oracle 10g

Oracle Database provides data dictionary views and PL/SQL packages that allow you to display information about the space usage of schema objects. Views and packages that are unique to a particular schema object are described in the chapter of this book associated with that object. This section describes views and packages that are generic in nature and apply to multiple schema objects.

Using PL/SQL Packages to Display Information About Schema Object Space Usage

These Oracle-supplied PL/SQL packages provide information about schema objects:

Using PL/SQL Packages to Display Information About Schema Object Space Usage

Package and Procedure/Function Description table

Example: Using DBMS_SPACE.UNUSED_SPACE

The following SQL*Plus example uses the DBMS_SPACE package to obtain unused space information.

Using Views to Display Information About Space Usage in Schema Objects

These views display information about space usage in schema objects:

Using Views to Display Information About Space Usage in Schema Objects

View Description Table

The following sections contain examples of using some of these views.

Example 1: Displaying Segment Information

The following query returns the name and size of each index segment in schema hr:

The query output is:

query output

Example 2: Displaying Extent Information

Information about the currently allocated extents in a database is stored in the DBA_EXTENTS data dictionary view. For example, the following query identifies the extents allocated to each index segment in the hr schema and the size of each of those extents:

The query output is:

query output

10 rows selected.

Example 4: Displaying Segments that Cannot Allocate Additional Extents

It is possible that a segment cannot be allocated to an extent for any of the following reasons:

  • The tablespace containing the segment does not have enough room for the next extent.
  • The segment has the maximum number of extents.
  • The segment has the maximum number of extents allowed by the data block size, which is operating system specific.

The following query returns the names, owners, and tablespaces of all segments that satisfy any of these criteria:

Once you have identified a segment that cannot allocate additional extents, you can solve the problem in either of two ways, depending on its cause:

  • If the tablespace is full, add a datafile to the tablespace or extend the existing datafile.
  • If the segment has too many extents, and you cannot increase MAXEXTENTS for the segment, perform the following steps.
  1. Export the data in the segment
  2. Drop and re-create the segment, giving it a larger INITIAL storage parameter setting so that it does not need to allocate so many extents. Alternatively, you can adjust the PCTINCREASE and NEXT storage parameters to allow for more space in the segment.
  3. Import the data back into the segment.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Oracle 10g Topics