Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 









Oracle extract schema tips

Oracle Database Tips by Donald Burleson
About Oracle schema extraction

Oracle professionals must frequently "punch," or extract, table and index definitions from Oracle and move them to different systems. Extracting Data Definition Language (DDL) for Oracle schema objects (e.g., tables, indexes, stored procedures, triggers, sequences, etc.) from the dictionary is very useful when you're migrating a system to a new platform and you want to pre-create the objects in a new tablespace.

Generally, in an Oracle migration, the schema and DDL are created in the target database, and then the rows are imported into the new database using the Oracle imp utility with the IGNORE=Y parameter. The IGNORE=Y parameters tells Oracle to use the new DDL definitions instead of the DDL inside the import data file.

But in Oracle9i and beyond, you can use the new utility package called dbms_metadata that will easily display DDL and stored procedures directly from the data dictionary. Using this powerful utility, you can punch individual objects or an entire Oracle schema. Best of all, it is easy to use. You simply execute dbms_metadata.get_ddl, specify the object names, and Oracle will extract ready-to-use DDL.

For large migrations, you can modify the dbms_metadata syntax to punch a whole schema. As you can see in Listing C, it is easily done by selecting dbms_metadata. get_ddl and specifying USER_TABLES and USER_INDEXES. This syntax will punch all table and index definitions for the entire schema.

This exercise will allow you to view the important statistics that are gathered within the Oracle database. Your first task is to analyze your pubs schema. To do this, you must "analyze" all of the tables and indexes in the database. For example, and analyze command for the authors table might look like this:

analyze table authors compute statistics;

Now, after having analyzed your schema, rerun the query from the previous step, and note the changes in the statistics. Contrast your personal findings with the topics in the reading "What is your optimizer philosophy?", and discuss how often it is "necessary" to re-analyze a schema for the cost-based optimizer. Post your thoughts and notes in the discussion forum.

The query below will extract all of the schema names from the Oracle data dictoinary.

select distinct

   owner in
   (select username
    from dba_users

    where default_tablespace not in ('SYSTEM','SYSAUX')


select distinct(owner) from dba_segments
where owner not in (


Related Schema Articles:

Oracle Schema Documentation
Get Oracle schema DDL syntax with dbms_metadata
Oracle Schema Components
Tracking Oracle schema changes
Oracle schema change control
Oracle Schema Statistics Management




Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.