How to Monitor Space in Your Segments in Oracle 12c

By Chris Ruel, Michael Wessler

Segments are objects that take up space in the database in Oracle 12c. Segments are objects that, when created, allocated one or more extents from the free space in your tablespaces. The two most common database segments are

  • Tables

  • Indexes

Tables are what hold data, and indexes are access pointers to data elements within a table. Management of space for tables and indexes are very similar; however, the same concepts and techniques apply for index space management.

Before putting any data in a table, Oracle goes to the tablespace where it lives and allocates an extent. You can call this a used extent because it belongs to an object. The remaining space in the tablespace is free extents you can use when objects grow or new objects are created.

As you start putting data into that table, the extent that was allocated upon creation begins to fill up. When the extent reaches capacity, the table goes out to the tablespace and grabs another extent. This cycle continues until either you stop adding data or the tablespace runs out of free space.

If the tablespace runs out of free space, the process requesting the space generates an error message and either fails or temporarily suspends until space is added.

How to grow and shrink tables in Oracle 12c

A table that fits this category might be loaded every night and then deleted from throughout the day, like a batch processing table.

For example, consider an ORDERS table that’s batch loaded at night from all the orders that were taken from a website; as the orders are processed the next day, rows are deleted one by one. At the end of the day, all the rows are gone. What do you need to monitor for this table?

You should be most concerned with how big the table gets each day after the batch load. Businesses want orders to increase. What about the deletes? Should you shrink the table at the end of the day before the next batch load to free up space? Absolutely not.

Although it’s small, the growth of an object is overhead on the system processing. By leaving it the same size day to day, you know the space will be constantly reused. You mainly want to monitor this type of object for growth.

What about a table that you add to and delete from on a frequent basis? Say for every 1 million rows inserted in a week, 30 percent are deleted. This table can present an interesting challenge.

Take a quick look at how rows are inserted to better understand how objects grow and use space:

  • You have a new table with one 64k extent.

  • Your block size is 4k, so that extent is 16 blocks.

  • You start inserting data; 100 rows fit in a block.

  • By default, Oracle fills blocks to 90 percent full and then stops. For example, some fields are left null until a later date, and then you fill them in. You don’t want the block to get filled too easily by an update; otherwise Oracle has to move the row to a new block that fits it. This rows migration degrades performance.

  • When all the blocks are filled in the free extents, the table allocates a new extent, and the process starts over.

Although Oracle has reduced the performance overhead that comes with managing blocks and which ones you can insert data into, managing block storage still has a cost associated with it.

Imagine you have a table with 10,000 blocks and you’re constantly inserting and deleting from that table. Oracle could spend all the CPU cycles managing what blocks can have inserts and which can’t if there were only a one-row difference between full and not-full blocks.

That’s why Oracle uses the 40-percent rule. A block takes all the inserts it can until it’s 90 percent full, but the block can’t get back in line until it’s been reduced to 40 percent full.

How to shrink tables in Oracle 12c

You must determine whether you can release an object’s space after evaluating the object’s usage pattern. Determining whether you can release an object’s space requires a little arithmetic. Before you can decide whether to make room in a table, you need to analyze the table to gather statistics.

The ANALYZE command gets the necessary statistics to do this computation; DBMS_STATS doesn’t get those stats.

This example uses the emp table. To analyze the table appropriately, take these steps:

  1. Log in to SQL*Plus and type

    < analyze table emp compute statistics;>

    You see this:

    Table analyzed.
  2. Run a query against the USER_TABLES view by typing

    <select table_name, avg_space, blocks
    from user_tables
    where table_name = 'EMP'>

    You see something like this:

    ------------------------------ ---------- ----------
    EMP                 3264    4528

    The AVG_SPACE column shows the average amount of free space per block.

  3. Use the following formula to calculate the amount of empty space in the emp table:


    For this example, the formula looks like this:

    (3623 – (8192 × .10)) × 4528 = 11066432 (approximately 11MB)

  4. Decide whether there’s enough space to make it worthwhile to shrink the object.

  5. To enable Oracle to move rows around in the table type, issue this SQL command:

    <alter table emp enable row movement;>

    You see this:

    Table altered.
  6. Issue this SQL command to do the shrink:

    <alter table emp shrink space;>

    You see this:

    Table altered.
  7. Re-analyze the table and re-execute the query to check the statistics.

    <select table_name, avg_space, blocks
    from user_tables
    where table_name = 'EMP'>

    You should see something like this:

    ------------------------------ ---------- ----------
    EMP                  933    2979

    As you can see, the AVG_SPACE is about 10 percent of the block size. This is normal for default block space organization.