Databases Reference
In-Depth Information
SQL> /
Index created.
SQL>
The
GET_DDL
procedure of the
DBMS_METADATA
packages provides a quick way to generate the DDL for
recreating both indexes as well as tables. If you want to generate the DDL for creating all indexes in a
schema, execute the
DBMS_METADATA.GET_DDL
procedure in the following way:
SQL> select dbms_metadata.get_ddl('INDEX', d.index_name)
2 from dba_indexes d
3* where owner='HR'
SQL>
One thing you'd want to do when executing the
DBMS_METADATA
package is to set the following SQL*Plus
parameters to get nicely formatted (wrapped without inconvenient word breaks) output. If you're
generating the DDL for creating all the indexes in a schema, you'll thank yourself!
set linesize 80 (or some reasonable number)
column xyz format a100 word_wrapped
column x format a200 word_wrapped
A big advantage of using the
DBMS_METADATA
package is that just about anyone can execute the package
through SQL*Plus. As shown in this example, you use the
GET_DDL
procedure to extract the DDL for an
index. The
DBMS_METADATA
package contains several other procedures as well, and the following sections
explain how to use two important procedures:
SESSION_TRANSFORM
and
SET_FILTER
.
Using the SESSION_TRANSFORM Procedure
You can use the
SESSION_TRANSFORM
procedure to modify or customize the output generated by the
GET_DDL
procedure. You can specify various controls such as the following (note that some of the
“transform parameters” are applicable to only certain object types):
•
PRETTY
formats output with indentation and line feeds.
SQLTERMINATOR
appends a SQL terminator to each DDL statement.
•
STORAGE
outputs the storage clause
•
•
CONSTRAINTS
outputs all non-referential constraints.
BODY
outputs the package body for a package.
•
Several of the transform parameters are set to the value of
TRUE
by default, but some, such as the
value of the
SQLTERMINATOR
parameter, are set to
FALSE
. The following code chunk shows how to set
various transform parameters before you run the
GET_DDL
procedure to generate the DDL for an index:
SQL> begin
2 dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM,
'STORAGE', false );
