Bug #24666 The application frozen when retrieve index from Oracle database

• Submitted: 28 Nov 2006 18:17
• Modified: 21 Oct 2008 15:54
• Reporter: Alejandro Battellini
• Status: Duplicate
• Category: MySQL Migration Toolkit
• Severity: S1 (Critical)
• Version: 1.1.6
• OS: Windows (Win XP(pt-brazil))
• CPU Architecture: Any
• Tags: Oracle index problem

[28 Nov 2006 18:17] Alejandro Battellini

Description:
When use the Migration Toolkit to migrate from Oracle to MySQL, on large table schema (more 1000 tables) when retrieve the index metadata the application hang.

How to repeat:
Create a oracle schema with more than 1000 tables and more than 3200 indexes; then run the migration tookit application.

Suggested fix:
Change the query that retrieve the index metada for a ruled query (Hint /*+Rule*/).

See how to....

SELECT /*+Rule*/ i.*,  ic.COLUMN_NAME, ic.COLUMN_LENGTH, ic.DESCEND FROM ALL_INDEXES i, ALL_IND_COLUMNS ic, ALL_CONSTRAINTS c, ALL_TABLES t WHERE i.TABLE_OWNER='TECON' AND t.OWNER=i.OWNER AND i.TABLE_NAME=t.TABLE_NAME AND  ic.TABLE_OWNER=i.TABLE_OWNER AND  ic.TABLE_NAME=i.TABLE_NAME AND  ic.INDEX_NAME=i.INDEX_NAME AND c.OWNER(+)=i.OWNER AND  c.CONSTRAINT_NAME(+)=i.INDEX_NAME AND  (c.CONSTRAINT_TYPE is null OR c.CONSTRAINT_TYPE<>'P') ORDER BY i.TABLE_NAME, ic.INDEX_NAME, ic.COLUMN_POSITION

[28 Nov 2006 18:25] Alejandro Battellini

screenshot where the application hang.

Attachment: imagem.zip (application/x-zip-compressed, text), 92.09 KiB.

[28 Nov 2006 23:33] Valeriy Kravchuk

Thank you for a problem report. What exact version of Oracle RDBMS do you use?

[29 Nov 2006 11:34] Alejandro Battellini

Hi, our version of the database is Oracle 9.2.1.0.

[23 Dec 2006 8:48] Valeriy Kravchuk

Please, try to repeat with a newer version, 1.1.8 (from GUI Tools 5.0r8), and inform about the results.

[24 Jan 2007 0:00] Bugs System

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

[21 Oct 2008 15:54] Valeriy Kravchuk

Duplicate of Bug #34117. Still happens with 1.1.13.