Wednesday, 21 May 2008

Captured And Converted Indexes Report

Heres a little SQL Developer user defined report to list the captured and converted indexes. You can change the query to list 1 or the other.

Copy the following text and create a file called MigratedIndexes.xml with it.
The In the SQL Developer User Defined Reports , Import the file. When you open the report you should choose the migration repository.


<?xml version="1.0" encoding="UTF-8" ?><displays><display id="0b9c6f78-011a-1000-8001-0aa970396f91" type="" style="Table" enable="true">
<name><![CDATA[List Captured And Converted Indexes]]></name>
<description><![CDATA[List Captured And Converted Indexes]]></description>
<tooltip><![CDATA[List Captured And Converted Indexes]]></tooltip>
<drillclass><![CDATA[null]]></drillclass>
<CustomValues>
<TYPE>horizontal</TYPE>
</CustomValues>
<query>
<sql><![CDATA[SELECT DISTINCT NVL(c.type, 'captured') type, t.project_name, t.catalog_name, t.schema_name, t.table_name, i.index_name, i.index_type
FROM md_indexes i, mgv_all_tables t, md_connections c
WHERE i.table_id_fk = t.table_id AND c.id = t.connection_id

ORDER BY type, t.project_name, t.catalog_name, t.schema_name, t.table_name, i.index_name]]></sql>
</query>
</display>
</displays>