DDL queries for all materialized views Indexes

~ Get DDL queries for all materialized views Indexes in Oracle
~ ( to be collected from source database)
~ Requirement: When a database is migrated / restored using expdp/impdp method, then there is a chance of missing indexes in some scenarios. As indexes are created exclusively for materialized views, can't be seen in table/ mview definition. You can follow below dynamic query build approach to collect DDL script.
OR
When any Materialized view dropped and re-created, Indexes created on the materialized view should be taken care and re-created again. Otherwise, following query can be used to collect DDL scripts from source environment and run again if any index is missed during drop and re-create of materialized view.
~ Applicable to all Oracle versions.

-- step:1: If Index is not owned by table owner
select 'select dbms_metadata.get_ddl(''INDEX'',''' || INDEX_NAME || ''',''' ||
       OWNER || ''') from dual;'
  from all_indexes
 where owner != table_owner
   and table_owner not like '%SYS%';


-- All mview Index DDL - to generate GET_DDL script

select 'select dbms_metadata.get_ddl(''INDEX'',''' || INDEX_NAME || ''',''' ||
       OWNER || ''') from dual;' from all_indexes a where a.table_name in (
select v.MVIEW_NAME from all_mviews v )
and index_name not like '%PK%'

e.g., select dbms_metadata.get_ddl('INDEX','INDX_SERVICEID','BILLING') from dual;

-- Step:2: keep the above output and spool the output again for Create Script, eg.,
set heading off;
 set echo off;
 Set pages 999;
 set long 90000;
spool c:\temp\ehishyd.log

select 'select dbms_metadata.get_ddl(''INDEX'',''' || INDEX_NAME || ''',''' ||
       OWNER || ''') from dual;' from all_indexes a where a.table_name in (
select v.MVIEW_NAME from all_mviews v )
and index_name not like '%PK%'

spool off;

Now you can run spooled DDL query in target database environment.
Thanks