MySQL index extract to Oracle Create Index statement!

I am transforming MySQL to Oracle. The data on MySQL had transformed to Oracle, but the index hadn't.

Now, I want to extract index ddl from mysql and not found any tool like navicat for MySQL can do this.

So, I try to use mysqldump to dump whole database in sql script.

I viewed this dump file, the index would be displayed like ..


  KEY `adt_bed_basic_idx05` (`station_no`,`bed_status`),
  KEY `adt_bed_basic_idx06` (`sex`),
  KEY `adt_bed_basic_idx07` (`bed_level`),
  KEY `adt_bed_basic_idx08` (`charge_specific`),
  KEY `adt_bed_basic_idx09` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1356 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
/*!40101 SET character_set_client = @saved_cs_client */;

So, KEY is the index.

Now, I have to figure out come keyword to generate "CREATE INDEX" script

1. Schema Name

2. Index Name

3. Table Name

4. Column Name

============================================
This is my step.

1. Dump Whole MySQL DB

mysqldump --no-data --table --all-databases >>schema.dmp

2. Extract KEY words



egrep -i "CREATE TABLE|KEY|CURRENT" schema.dmp |egrep -v "PRIMARY|FOREIGN|50001|40014" >mysql.sql

3. Generate Create Index DDL

I use shell script. The command is .. 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

while read LineSQL

do

C1=`echo $LineSQL|cut -d" " -f1 `

C2=`echo $LineSQL|cut -d" " -f2 `

C3=`echo $LineSQL|cut -d" " -f3 `

C4=`echo $LineSQL|cut -d" " -f4 `

C5=`echo $LineSQL|cut -d" " -f5 `

C6=`echo $LineSQL|cut -d" " -f6 `

C7=`echo $LineSQL|cut -d" " -f7 `

#echo "C1 $C1"

#echo "C2 $C2"

#echo "C3 $C3"

#echo "C4 $C4"

#echo "C5 $C5"

#echo "C6 $C6"

#echo "C7 $C7"

if [ $C1 = "--" ];then

        #echo "Change User to $C4"

        export SCHEMA=$C4

elif [ $C1 == "CREATE" ];then

        #echo "Table Name is $C3"

        export TB=$C3

                elif [ $C1 == "KEY" ];then

                        #echo "Index Name is $C2"

                        #echo "Column Name is $C3"

                        export INDX=$C2

                        export COLS=$C3

                        echo "Create Index $SCHEMA.$INDX  on $SCHEMA.$TB $COLS"

                        echo "tablespace HIS_IDX_01;"

#echo  "Error Data is $C1,$C2,$C3,$C4,$C5,$C6,$C7"

fi

done

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The Result like this:

Create Index adt.adt_bed_basic_idx01  on adt.adt_bed_basic (bed_no)

tablespace HIS_IDX_01;

OK, I had done.