5.2 How do I…Determine all of the indexes owned by a particular user?

Problem

My database has several owners, tables, and indexes. For better organization and management, I need to see what indexes are owned by each of the database’s user accounts. How do I determine all of the indexes owned by a particular user?

Technique

The DBA_INDEXES data dictionary view contains information on each index within the database. How-To 5.1 describes how to determine the indexes on a table. By querying the same data dictionary view, but providing different WHERE clause conditions, you can determine which indexes exist that are owned by a user account.

Steps

1. Connect to SQL*Plus as the WAITE user account. If you have not created the EMPLOYEE05 table, run the CHP5_1.SQL script to create it. Also, if you have not created the SSN_INDEX on the EMPLOYEE05 table, run the CHP5_2.SQL script to create it.

2. Run the CHP5_4.SQL script, shown in Figure 5.3, to determine which indexes exist for a given user account.

When the script is run, the user will be prompted for an owner. Enter WAITE for the owner. This interaction is shown in Figure 5.4. The script can be run for any owner in the database. Because there is only one index for the WAITE user account at this point, the query result appears identical to the result from How-To 5.1.

3. Run the CHP5_4.SQL script again, passing SYS as the owner. The result, part of which is shown in Figure 5.4, has many more indexes than the WAITE user account.

How It Works

This How-To works very similarly to How-To 5.1. The DBA_INDEXES data dictionary view contains information on each index in the database. If not already created, you can create the EMPLOYEE05 sample table by running CHP5_1.SQL. Also, the SSN_INDEX sample index on the EMPLOYEE05 table is created if it does not already exist.

First, CHP5_4.SQL queries the DBA_INDEXES data dictionary view for the WAITE owner. The result is a list of all indexes on the EMPLOYEE05 table, identical to that of How-To 5.1 because there is just one index. The CHP5_4.SQL script is run again for the SYS account, and many indexes are returned.

Comments

The CHP5_4.SQL script will order the index list by OWNER first, then TABLE_NAME, and finally INDEX_NAME. This will improve readability across several index owners.