SQL Scripts for Indexes and Data Dictionary Queries in Oracle and Sample Outputs

advertisements

_____________________________________________________________________________________________________________________

1. Retrieve Information About Indexes from Data Dictionary Views 

 This script retrieves information about indexes from the data dictionary views (all_indexes, dba_indexes, or user_indexes). Replace 'YOUR_SCHEMA_NAME' with the name of the schema you want to query. The script returns details about the indexes in the specified schema, such as index name, associated table, uniqueness, status, tablespace, and indexed columns.

-- Query to retrieve information about indexes from the data dictionary views
SELECT index_name, table_name, uniqueness, status, tablespace_name, column_name
FROM all_indexes
WHERE table_owner = 'YOUR_SCHEMA_NAME';

advertisements

 

INDEX_NAME      TABLE_NAME    UNIQUENESS    STATUS   TABLESPACE_NAME  COLUMN_NAME
-------------   -----------   -----------   -------  ---------------  -------------
EMPLOYEES_IDX   EMPLOYEES     NONUNIQUE     VALID    USERS_IDX_TS     EMPLOYEE_ID
DEPARTMENTS_PK  DEPARTMENTS   UNIQUE        VALID    USERS_IDX_TS     DEPARTMENT_ID

2. Find Indexes with a Specific Name Pattern or Index Type 

 This script finds indexes with a specific name pattern and index type. Replace 'YOUR_PATTERN' with the desired pattern you want to search for. You can also change 'NORMAL' to other index types like 'BITMAP', 'CLUSTER', or 'FUNCTION-BASED NORMAL' to search for specific index types.

-- Query to find indexes with a specific name pattern or index type
SELECT index_name, table_name, index_type
FROM all_indexes
WHERE index_name LIKE 'YOUR_PATTERN%'
  AND index_type = 'NORMAL';

INDEX_NAME      TABLE_NAME    INDEX_TYPE
-------------   -----------   -----------
EMP_IDX_1       EMPLOYEES     NORMAL
EMP_IDX_2       EMPLOYEES     NORMAL

3. Identify Indexes with a High Degree of Fragmentation

This script identifies indexes with a high degree of fragmentation. It queries the dba_indexes data dictionary view to find indexes where the B-level (height of the index) is greater than 3. A high B-level may indicate that the index is fragmented and might benefit from rebuilding or reorganizing.

-- Query to identify indexes with a high degree of fragmentation
SELECT index_name, table_name, blevel, leaf_blocks, num_rows, degree
FROM dba_indexes
WHERE blevel > 3
ORDER BY blevel DESC;

INDEX_NAME      TABLE_NAME    BLEVEL  LEAF_BLOCKS  NUM_ROWS  DEGREE
-------------   -----------   ------  -----------  --------  ------
EMP_IDX_1       EMPLOYEES     4       500          10000     1
DEPT_IDX_1      DEPARTMENTS   5       200          50        1

_____________________________________________________________________________________________________________________

Website Stats