How can I obtain all the index creation statements in ORACLE ?, Oracle index creation statement

How can I obtain all the index creation statements in ORACLE ?, Oracle index creation statement

How can I obtain all the index creation statements in ORACLE?

Set pagesize 0

Sets long 90000

Set feedback off

Set echo off

Spool all_index. SQL

SELECT DBMS_METADATA.GET_DDL ('index', u. index_name)

FROM USER_INDEXES u;

Spool off;

Finally, find an editor to replace the tablespace name and add it after the tablespace name;

Then let ORACLE execute it slowly

The external process DBMS_METADATA.GET_DDL is called to obtain the DLL creation statement.

This is the DLL created by all users.

SELECT DBMS_METADATA.GET_DDL ('user', U. username)

FROM DBA_USERS U;

This is the DLL of all tables.

SELECT DBMS_METADATA.GET_DDL ('table', u. table_name)

FROM USER_TABLES u;

DLL for all tablespaces

SELECT DBMS_METADATA.GET_DDL ('tablespace', TS. tablespace_name)

FROM DBA_TABLESPACES TS;

Combine all tables and index stored procedures

SELECT DBMS_METADATA.GET_DDL (U. OBJECT_TYPE, u. object_name)

FROM USER_OBJECTS u

Where U. OBJECT_TYPE IN ('table', 'index', 'Procedure ');

Export only indexes: SELECT DBMS_METADATA.GET_DDL (U. OBJECT_TYPE, u. object_name)

FROM USER_OBJECTS u

Where U. OBJECT_TYPE IN ('index'); export as an SQL statement

Another related statement is used to generate a statement to delete all indexes of a user.

Select 'drop Index' | index_name | '; 'form user_indexes;