• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 311
  • Last Modified:

Convert Oracle DB structure into script

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.
  • 5
  • 4
  • 3
  • +1
1 Solution
dbms_metadata package will let you extract the ddl for objects
slightwv (䄆 Netminder) Commented:
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:
C_sharp_beaverAuthor Commented:
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?

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

slightwv (䄆 Netminder) Commented:
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.
C_sharp_beaverAuthor Commented:
I'm reading your explanation.

Just to clarify - I need scripts of objects, not data itself.
Scripts of ALL related to schema objects.
C_sharp_beaverAuthor Commented:
The specified thread [0] was not found

example does not exist
slightwv (䄆 Netminder) Commented:
Think my clipboard screwed up.

Try this:

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
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

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.


slightwv (䄆 Netminder) Commented:
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).
C_sharp_beaverAuthor Commented:
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.
C_sharp_beaverAuthor Commented:
*SQL Developer

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now