asked on

export only indexes of a schema as sql file

Hello Experts,

i have a requirement to extract DDL of all indexes of a schema as a sql file.
by using this file i am need to create indexes.

here is my requirement :

i need to to extract index DDL table by table(one sql file for each table ). i have around 5000+ tables. is there any quickest way to get all Index ddl.

Thanks,
#! Kumar

MikeOM_DBA

Use SQL Developer.
:p

MikeOM_DBA

...or TOAD if you have it.

sk0227

ASKER

Thanks for quick response...

how does toad /sql developer will give all index ddl of a scham?

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

zofcentr

Easier way is to use statement:
select DBMS_METADATA.GET_DDL('INDEX','<index_name>') from DUAL;

And best to combine with SELECT INDEX_NAME FORM USER_INDEXES ...

slightwv (䄆 Netminder)

>>Easier way is to use statement:

Did I not already mention this?

MikeOM_DBA

Easiest: follow slightwv's suggestion of using the old "exp"ort utility with rows=no, but instead of creating the index file, download and use http://www.ddlwizard.com/ (free) which will create formatted separate files for your ddl.

Good luck!

zofcentr

Yes You did slightwv. SOme how I missed or different time zone we live in.
Minutes I quite near to each other.
Any way sorry if I missed.

sk0227

ASKER

Thanks for solutions. i have done this task with datapump..

here is what i did with datapump. below parameter file will create index create file.

directory=MY_DIR
parallel=4
remap_schema=SCOTT:MY_SOCTT
sqlfile=indexes.sql
logfile=IMP_20121030.LOG
dumpfile=MYDUMPS%U.DMP
INCLUDE=INDEX