How to generate/spool index creation script for a schema using dbms_metadata.get_ddl?

Step 1
$ vi gen_index_script.sql

set pages 0
set lines 90
set echo off feedback off verify off heading off
set termout off
spool cr_index_script.sql
select 'set long 50000' from dual;
select 'spool cr_index.sql' from dual;
select 'select dbms_metadata.get_ddl(''INDEX'''||','''||INDEX_NAME||''','''||OWNER||''') ||'';'' from dual;' from dba_indexes where owner ='SCOTT' ;
select 'spool off' from dual;
spool off
@cr_index_script.sql

Step 2
Connect to sqlplus “sys as sysdba” and run gen_table_script.sql
SQL> @gen_index_script.sql

Step 3
$!ls -ltr
-rw-r--r--   1 oracle   dba          400 Jun 14 10:22 gen_index.sql
-rw-r--r--   1 oracle   dba          728 Jun 14 10:22 cr_index_script.sql
-rw-r--r--   1 oracle   dba         9828 Jun 14 10:22 cr_index.sql

Now your script is ready in file cr_table.sql

Check it out!!!
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google