Space Management   «Prev  Next»

Lesson 10Freespace in the data dictionary
ObjectiveLearn about freespace parameters from the data dictionary.

Oracle Data Dictionary Freespace

As with all other attributes of database objects, you can find out more about the way your tables and indexes work with free space through several data dictionary views.

Oracle Database objects

The USER_TABLES and USER_INDEXES data dictionary views have columns that store the values for the storage parameters that apply to the amount of free space in their particular objects. The USER_TABLES view contains columns for PCT_FREE and PCT_USED, while the USER_INDEXES view only has a column for the PCT_FREE value.


Freelists are lists of free data blocks for a database table or index. The USER_SEGMENTS view has information about the number of freelists allocated to a segment for an object, while the USER_TABLES and USER_INDEXES views have information about the number of freelists allocated to their particular objects.
The USER_TABLES and USER_INDEXES views also have columns for the NUM_FREELIST_BLOCKS, which give the number of blocks on the freelist for the object, and the AVG_SPACE_FREELIST_BLOCKS, which give the average amount of space for each of the freelist blocks.

Determining if Foreign Key Columns are Indexed

If you are creating an application from scratch, it is fairly easy to create the code and ensure that each foreign key constraint has a corresponding index. However, if you have inherited a database, it is prudent to check if the foreign key columns are indexed. You can use data dictionary views to verify if all columns of a foreign key constraint have a corresponding index. The basic idea is to check each foreign key constraint to see if there is a corresponding index. The task is not as simple as it might first seem.
For example, here is a query that gets you started in the right direction:

a.owner owner
,a.constraint_name cons_name
,a.table_name tab_name
,b.column_name cons_column
,NVL(c.column_name,'***Check index****') ind_column
FROM dba_constraints a
,dba_cons_columns b
,dba_ind_columns c
WHERE constraint_type = 'R'
AND a.owner = UPPER('&&user_name')
AND a.owner = b.owner
AND a.constraint_name = b.constraint_name
AND b.column_name = c.column_name(+)
AND b.table_name = c.table_name(+)
AND b.position = c.column_position(+)
ORDER BY tab_name, ind_column;

This query, while simple and easy to understand, does not correctly report on un-indexed foreign keys for all situations. For example, in the case of multi-column foreign keys, it does not matter if the constraint is defined in a different order from the index columns, as long as the indexed columns are in the leading edge of the index. In other words, if the constraint is defined to be COL1 and COL2, then it is okay to have a B-tree index defined on leading edge COL2 and then COL1.
Another issue is that a B-tree index protects you from locking issues, but a bitmap index does not. In this situation, the query should also check the index type. In these scenarios you will need a more sophisticated query to detect indexing issues related to foreign key columns.

Free Diction - Quiz

Click the Quiz link below to answer a few questions about free space.
Free Diction - Quiz
The next lesson is about the perils of fragmentation of extents within a tablespace.