Databases Reference
In-Depth Information
"USERS" ;
PL/SQL procedure successfully completed.
SQL>
Using Data Pump
You can also use the data pump utility to extract DDL for indexes as well as other database objects.
Underneath, the data pump actually uses the
DBMS_METADATA
package to extract DDL for database
objects. Generating DDL with data pump is easy. Make sure you use the following syntax to do so:
$ expdp content=metadata_only
When you specify the
content=metadata_only
option, Oracle database doesn't export any data; it
merely extracts the DDL for all database objects. If you are recreating all the indexes in your test
environment to your development environment, invoke the
expdp
utility first to get the dump file. You
can then copy the dump file to the development environment and run the
impdp
utility, as shown here:
$ impdp sqlfile=myfile
The advantage to using the
DBMS_METADATA
package to extract the DDL for your objects is that you get
to control the formatting of the output, making it much easier to run the index creation script. On the
other hand, the data pump output has line size problems. In addition, running the data pump utility
requires more privileges than executing procedures from the
DBMS_METADATA
package.
Dropping an Index
On occasion, you may find that you don't need an index because the index isn't providing any real
performance gains or because your application doesn't use the index. You can drop an index by using
the
drop index
command. You may also drop an index because the index is invalid and you want to
rebuild it. You must first drop the index before rebuilding it. You'll also find that if an index is too
fragmented, it is better to drop it and create a brand new index instead of rebuilding it; as you recall,
rebuilding an index requires twice the space of the index.
You execute the
drop index
command in the following manner:
SQL> drop index test_idx
SQL> /
Index dropped.
SQL>
You can drop any index that you have explicitly created through the
drop index
command.
However, you can't drop any implicitly created index, such as those created by defining a key constraint
