Regarding creating the index

Question

there are two db we migrated the data from db1 to db2 now the db2 have some indexes missing in it and we need to create all those index in db 2 but we only have the indexes name nothing else is known

i want to all index should be created but i shouldnt create it manually i take the uncreated index from db1 and create it to db2

Answer 1

One option is to extract DDL yourself.

First, check which indexes you have:

SQL> set long 10000
SQL> select table_name, index_name from user_indexes;

TABLE_NAME                     INDEX_NAME
------------------------------ ------------------------------
ROLES                          PK_ROLES
DEPT                           PK_DEPT
EMP                            PK_EMP
TEST                           SYS_IL0000590731C00001$$

Then use dbms_metadata.get_ddl for any index you want. If you remove where clause, you'll get them all.

SQL> select dbms_metadata.get_ddl('INDEX', index_name) from user_indexes where index_name = 'PK_ROLES';

DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME)
--------------------------------------------------------------------------------

  CREATE UNIQUE INDEX "SCOTT"."PK_ROLES" ON "SCOTT"."ROLES" ("ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  TABLESPACE "USER_DATA"


SQL>

This option might be interesting because you can spool result into a file and then just run that script in target database.

---

Another option is to use some GUI tool which lets you visually examine list of indexes and copy/paste create index commands.