--Create script to drop objects

SELECT 'DROP '||object_type||' '||object_name||';' ddl_script
FROM  all_objects
WHERE  object_type = 'VIEW'
AND    owner = NVL(UPPER('&i_user'),user);

--Create Grants script

SELECT
 'GRANT '||
  LISTAGG(privilege,',') WITHIN GROUP (ORDER BY table_name)||
 ' ON '||table_name||' TO '||grantee||';' ddl_q
FROM  dba_tab_privs
WHERE owner = user
AND   grantee = UPPER('&grant_to_usr')
GROUP BY  table_name, grantee
ORDER BY table_name
;

--Create Synonym script

SELECT 'CREATE OR REPLACE SYNONYM '||owner||'.'||synonym_name||
  ' FOR '||table_owner||'.'||table_name||';' ddl_q
FROM  all_synonyms
WHERE  owner = UPPER('&p_owner')
AND    table_owner = 'EDW'
ORDER BY table_name
;

--Create Index script (for database migration)

SELECT 'CREATE '||
 DECODE(uniqueness,'UNIQUE',uniqueness,' ')||' INDEX '||
 ai.index_name||' ON '||ai.owner||'.'||ai.table_name||' ('||
 q_idx_col.idx_cols||');'  ddl_idx
FROM all_indexes ai,
 (SELECT index_name, table_name,
    LISTAGG(column_name,', ')
      WITHIN GROUP (ORDER BY column_position)  idx_cols
  FROM  all_ind_columns
  GROUP BY index_name, table_name) q_idx_col
WHERE ai.table_name = q_idx_col.table_name
AND   ai.index_name = q_idx_col.index_name
AND   ai.owner = UPPER('&i_owner')
ORDER BY  ai.table_name
;