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)
|
|
|
|
|
|
| Re: how to find all indexes scripts in particular schema [message #563759 is a reply to message #563629] |
Thu, 16 August 2012 18:00   |
 |
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 |
|
|
|
|
|
Goto Forum:
Current Time: Sun Nov 24 10:18:55 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
