Convert Oracle DB structure into script

Posted on 2011-04-28
Last Modified: 2012-12-22
I have a problem.
I have DEV Oracle 10 DB, DB itself under specific schema.

I need convert into scripts all objects related to this schema (tables, views, procedures, trigers, etc).

Do we have any automated way to do that?
I'm schema owner, I have Embarcadero Rapid SQL 7.7.0.
Question by:C_sharp_beaver
    LVL 73

    Expert Comment

    dbms_metadata package will let you extract the ddl for objects
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    You can probably get everything with dbms_metadata.get_ddl but I don't think it has a 'entire schema' mode.  You will need to grab the individual object classes.

    There is an untested script over at:

    Author Comment

    sorry, I did not understand it at some parts.

    Do you mean that DBMS_METADATA package gives schema owner (not DBA) possibility to convert into scripts structure of ALL schema related objects automatically? Or just one-by-one?
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    While looking up the syntax, I have to apologize.  I guess you can do an entire schema.  There is a SCHEMA_EXPORT parameter.

    I found a script that shows how to use it.  Check out the example in:

    Also, if you don't need an actual text script and you are migrating to another Oracle database, you can use export/import.  The classic exp/imp or the new datapump versions expdp/impdp.

    Author Comment

    I'm reading your explanation.

    Just to clarify - I need scripts of objects, not data itself.
    Scripts of ALL related to schema objects.

    Author Comment

    The specified thread [0] was not found

    example does not exist
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    Think my clipboard screwed up.

    Try this:
    LVL 73

    Expert Comment

    Note,  SCHEMA_EXPORT won't grab objects that don't belong to your schema but your schema might be dependent on.  Such as objects owned by other schemas,  or things that don't have owners  like tablespaces and roles

    It also doesn't pick up certain attributes of your objects like auditing
    LVL 73

    Accepted Solution

    simpler solution,  use SQL*Developer that comes with Oracle.

    Tools->Database Export

    select your object types (defaults ot ALL),  unselect data,  choose schemas (defaults to current)

    write to a file and done
    LVL 5

    Expert Comment


    You can export the entrie schema and you can import the ddl alone into a  file using the indexfile option while importing.


    C:\>exp userid=manzoor/****@orcl_man file=all_objects.dmp rows=n owner=manzoor log=all_objects.log

    Export: Release - Production on Thu Apr 28 14:54:21 2011

    Copyright (c) 1982, 2005, Oracle.  All rights reserved.

    Connected to: Oracle Database 10g Enterprise Edition Release - Production
    With the Partitioning, OLAP and Data Mining options
    Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
    Note: table data (rows) will not be exported

    About to export specified users ...
    . exporting pre-schema procedural objects and actions
    . exporting foreign function library names for user MANZOOR
    . exporting PUBLIC type synonyms
    . exporting private type synonyms
    . exporting object type definitions for user MANZOOR
    About to export MANZOOR's objects ...
    . exporting database links
    . exporting sequence numbers
    . exporting cluster definitions
    . about to export MANZOOR's tables via Conventional Path ...
    . . exporting table                     DEPARTMENT
    . . exporting table                            EMP
    . . exporting table                       EMP_PART
    . . exporting table              HKA_FAILED_REPORT
    . . exporting table       HKA_FRAGMENTATION_REPORT
    . . exporting table          HKA_JOBSBROKEN_REPORT
    . . exporting table           HKA_MAXEXTENT_REPORT
    . . exporting table             HKA_SESSION_REPORT
    . . exporting table               HKA_STATS_REPORT
    . . exporting table          HKA_TABLESPACE_REPORT
    . . exporting table        ITEMISED_ATM_USAGE_TEST
    . . exporting table                      ITEM_SIED
    . . exporting table                      ITEM_SSSS
    . . exporting table                       LIVE_FAR
    . . exporting table                     LIVE_FAULT
    . . exporting table                         TEST_Q
    . . exporting table                            TTT
    . exporting synonyms
    . exporting views
    . exporting stored procedures
    . exporting operators
    . exporting referential integrity constraints
    . exporting triggers
    . exporting indextypes
    . exporting bitmap, functional and extensible indexes
    . exporting posttables actions
    . exporting materialized views
    . exporting snapshot logs
    . exporting job queues
    . exporting refresh groups and children
    . exporting dimensions
    . exporting post-schema procedural objects and actions
    . exporting statistics
    Export terminated successfully without warnings.

    C:\>imp userid=manzoor/*****@orcl_man file=all_objects.dmp full=y indexfile=scripts_of_all_objects.sql log=test.log

    Import: Release - Production on Thu Apr 28 14:57:27 2011

    Copyright (c) 1982, 2005, Oracle.  All rights reserved.

    Connected to: Oracle Database 10g Enterprise Edition Release - Production
    With the Partitioning, OLAP and Data Mining options

    Export file created by EXPORT:V10.02.01 via conventional path
    import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
    Import terminated successfully without warnings.


    Once the import is completed open the file scripts_of_all_objects.sql  , here you can file all you ddl staments, in order to use it you need to remove the REM in the begining of each line.


    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    It's been a while since I used it but I believe the indexfile just shows tables  and indexes.  I don't think it does stored code (functions/procedures).

    Author Comment

    I've tried all options and some of them seems to be correct.

    Unfortunatelly by some reason Oracle Developer just hangs and does not start.
    Other options CAN work but results do not meet requirements of my customers.
    I'll write in comments later what specifically does not work as expected

    As result I should do it by hand :o(

    I understand that some of these solutions more then perfect but due to some reasons (probably including security settings) none of them work on my box.

    I appreciate effort of each participant and in progress of awarding points.

    Author Comment

    *SQL Developer

    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
    Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now