We help IT Professionals succeed at work.

Convert Oracle DB structure into script

C_sharp_beaver
on
Medium Priority
321 Views
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.
Comment
Watch Question

Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
dbms_metadata package will let you extract the ddl for objects
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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:
http://www.dba-oracle.com/t_cloning_oracle_user_id.htm

Author

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?
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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:
http://forums.oracle.com/forums/thread.jspa?threadID=958743http://forums.oracle.com/forums/thread.jspa?threadID=958743


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

Commented:
I'm reading your explanation.

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

Author

Commented:
The specified thread [0] was not found

example does not exist
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Think my clipboard screwed up.

Try this:

http://forums.oracle.com/forums/thread.jspa?threadID=958743
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
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
Database Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Hi,

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

Example:-

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

Export: Release 10.2.0.1.0 - 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 10.2.0.1.0 - 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 10.2.0.1.0 - 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 10.2.0.1.0 - 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.

Thanks..


CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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

Author

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.

Author

Commented:
*SQL Developer
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.