0

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

2 Answers 2

2

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.

0

There can be 2 option for creation of missing indexes:

  1. To get the list of missing indexes from the dynamic performance views:

This script list the top 10 columns most used in equality predicates or equijoin predicates and which are not indexed.

ACCEPT SCHEMA_NAME PROMPT 'Choose the schema to analyze:'
select * from (
  select 'the column ' || c.name || ' of the table ' || us.name || '.' || o.name || ' was used ' || u.equality_preds || ' times in an equality predicate and ' || u.equijoin_preds || ' times in an equijoin predicate and is not indexed' as colum_to_index
  from sys.col_usage$ u,
       sys.obj$ o,
       sys.col$ c,
       sys.user$ us
  where u.obj# = o.obj#
  and   u.obj# = c.obj#
  and   us.user# = o.owner#
  and   u.intcol# = c.col#
  and   us.name='&SCHEMA_NAME'
  and   c.name not in (select column_name from dba_ind_columns where index_owner ='&SCHEMA_NAME')
  and   (u.equality_preds > 100 OR u.equijoin_preds > 100)
  order by u.equality_preds+u.equijoin_preds desc)
WHERE rownum <11;
  1. Secondly get the DDL from source and after analysis you can create on DB where index are missing.
select table_name, index_name from user_indexes;


select dbms_metadata.get_ddl('INDEX', index_name) from user_indexes where index_name = 'index_name_as_mentioned';

Hope it helped !!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.