Tuesday, March 02, 2010

Oracle: how to get the DDL of a certain object

I have a sql script here to extract DDL of all the objects in a schema, the example below will extract the indexes of a certain schema. this is already formatted so you can spool it in a sql file and later run it.

set feedback off echo off
set long 200000 pages 0 lines 400 trimspool on linesize 400
column txt format a400 word_wrapped
variable ind_owner varchar2(100);

begin
:ind_owner := &schema;
end;
/

exec dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', TRUE);
exec dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE );
spool $ORACLE_SID-get_idx_ddl.sql
select dbms_metadata.get_ddl('INDEX',u.INDEX_NAME,:ind_owner) txt from dba_indexes u where owner = :ind_owner;
spool off
exit

2 comments:

Natalia said...

Before getting your blog I was in a myth that there is no way out to get the DDL of a certain object. I am really surprised and excited to find a solution on your post. I will try this query now to see the results. Thanks.
sap support pack implementation

Natalia said...

Before getting your blog I was in a myth that there is no way out to get the DDL of a certain object. I am really surprised and excited to find a solution on your post. I will try this query now to see the results. Thanks.
sap support pack implementation