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

advertisements

_____________________________________________________________________________________________________________________

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!!!

_____________________________________________________________________________________________________________________

Archive