Home » RDBMS Server » Performance Tuning » how to find all indexes scripts in particular schema (Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi)
how to find all indexes scripts in particular schema [message #563626] Thu, 16 August 2012 01:32 Go to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
Dear Experts,

Please help for getting all indexes script in particular schema.

Report message to a moderator

Re: how to find all indexes scripts in particular schema [message #563628 is a reply to message #563626] Thu, 16 August 2012 02:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use DBMS_METADATA.GET_DDL function.

Regards
Michel

Report message to a moderator

Re: how to find all indexes scripts in particular schema [message #563629 is a reply to message #563628] Thu, 16 August 2012 02:05 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
Thanks Michel !!!

Report message to a moderator

Re: how to find all indexes scripts in particular schema [message #563759 is a reply to message #563629] Thu, 16 August 2012 18:00 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
I use the following sql to generate all the dbms_metadata calls for the "RUNTIME" schema.
Unfortunately if you call the dbms_metadata procedure with the primary key index
it returns the create for a unique index only without the Primary Key constraint.
The primary key index creation is correct in the table definition.
ECSESBD > -- Table Creates WITH Primary Keys:
ECSESBD > select
  2  'select dbms_metadata.get_ddl(''TABLE'','''||table_name||''','''||owner||''') FROM DUAL;'
  3  from dba_tables where owner=upper('RUNTIME');
select dbms_metadata.get_ddl('TABLE','CONSUMERS','RUNTIME') FROM DUAL;
select dbms_metadata.get_ddl('TABLE','DESTINATIONS','RUNTIME') FROM DUAL;
select dbms_metadata.get_ddl('TABLE','SEEDS','RUNTIME') FROM DUAL;
select dbms_metadata.get_ddl('TABLE','SYSTEM_DATA','RUNTIME') FROM DUAL;
select dbms_metadata.get_ddl('TABLE','USERS','RUNTIME') FROM DUAL;
select dbms_metadata.get_ddl('TABLE','ACTIVEMQ_MSGS','RUNTIME') FROM DUAL;
select dbms_metadata.get_ddl('TABLE','ACTIVEMQ_ACKS','RUNTIME') FROM DUAL;
select dbms_metadata.get_ddl('TABLE','ACTIVEMQ_LOCK','RUNTIME') FROM DUAL;
select dbms_metadata.get_ddl('TABLE','MESSAGES','RUNTIME') FROM DUAL;
select dbms_metadata.get_ddl('TABLE','MESSAGE_HANDLES','RUNTIME') FROM DUAL;

ECSESBD > -- Index Creates Without Primary Keys:
ECSESBD > select 
  2  'select dbms_metadata.get_ddl(''INDEX'','''||upper(index_name)||''','''||upper('RUNTIME')||''') FROM DUAL;'
  3  from dba_indexes
  4  where owner=upper('RUNTIME')
  5  and upper('RUNTIME')||'.'||upper(index_name) not in (select index_owner||'.'||index_name from dba_constraints where constraint_type='P');

select dbms_metadata.get_ddl('INDEX','SYS_IL0000167871C00006$$','RUNTIME') FROM DUAL;
select dbms_metadata.get_ddl('INDEX','ACTIVEMQ_MSGS_MIDX','RUNTIME') FROM DUAL;
select dbms_metadata.get_ddl('INDEX','ACTIVEMQ_MSGS_CIDX','RUNTIME') FROM DUAL;
select dbms_metadata.get_ddl('INDEX','ACTIVEMQ_MSGS_EIDX','RUNTIME') FROM DUAL;
select dbms_metadata.get_ddl('INDEX','CONSUMERS_PK','RUNTIME') FROM DUAL;
select dbms_metadata.get_ddl('INDEX','DESTINATIONS_PK','RUNTIME') FROM DUAL;
select dbms_metadata.get_ddl('INDEX','MESSAGES_PK','RUNTIME') FROM DUAL;
select dbms_metadata.get_ddl('INDEX','MESSAGE_HANDLES_PK','RUNTIME') FROM DUAL;
select dbms_metadata.get_ddl('INDEX','SEEDS_PK','RUNTIME') FROM DUAL;
select dbms_metadata.get_ddl('INDEX','SYSTEM_DATA_PK','RUNTIME') FROM DUAL;
select dbms_metadata.get_ddl('INDEX','USERS_PK','RUNTIME') FROM DUAL;

ECSESBD > -- Primary Keys as unique indexes:
ECSESBD > select 
  2  'select dbms_metadata.get_ddl(''INDEX'','''||upper(index_name)||''','''||upper('RUNTIME')||''') FROM DUAL;'
  3  from dba_indexes
  4  where owner=upper('RUNTIME')
  5  and upper('RUNTIME')||'.'||upper(index_name) in (select index_owner||'.'||index_name from dba_constraints where constraint_type='P');
select dbms_metadata.get_ddl('INDEX','SYS_C0043467','RUNTIME') FROM DUAL;
select dbms_metadata.get_ddl('INDEX','SYS_C0043465','RUNTIME') FROM DUAL;
select dbms_metadata.get_ddl('INDEX','SYS_C0043461','RUNTIME') FROM DUAL;

[Updated on: Thu, 16 August 2012 18:04]

Report message to a moderator

Re: how to find all indexes scripts in particular schema [message #563780 is a reply to message #563759] Fri, 17 August 2012 01:34 Go to previous message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is useless to generate all queries from dual just:

select dbms_metadata.get_ddl('INDEX',index_name,user) from user_indexes;


Of course, some index types have to be excluded, so the axtual query is a little bit complex.

Regards
Michel

[Updated on: Fri, 17 August 2012 01:34]

Report message to a moderator

Previous Topic: Buffer_gets/Buffer_cache
Next Topic: query recommedation
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 18 00:21:43 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.