Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am new to Oracle Databases and would like to develop a data dictionary and ER Diagrams for our existing databases. Do you have any tips, scripts, tools for doing so?

share|improve this question

5 Answers 5

up vote 19 down vote accepted

Oracle's Data Modeller can reverse-engineer from an existing database. They were charging for it at one time, but it is now free to use.

share|improve this answer
4  
And it comes bundled with Oracle's SQL Developer 3 –  Sathya Jan 29 '11 at 8:36

I answered same question in http://stackoverflow.com/questions/8140387/oracle-extract-graphical-database-representation/8149947#8149947 See below.

For a free tool I recommend schema spy, http://schemaspy.sourceforge.net/. It needs Java Runtime. Look to http://schemaspy.sourceforge.net/sample/relationships.html to see diagrams it generate.

simple usage with oracle hr template is given below.

SET JDBC_ORACLE_JAR=C:\oracle\product\10.2.0\client_1\jdbc\lib\ojdbc14.jar
SET CONNECTION_USERNAME=hr
SET CONNECTION_PASSWORD=hr
SET TNS_DATABASE_NAME=orcl
SET SCHEMA_NAME=HR
SET TABLE_NAME='EMP'
SET OUTPUT_NAME=HR_EMP

java -jar schemaSpy_5.0.0.jar -dp %JDBC_ORACLE_JAR% -db %TNS_DATABASE_NAME% -o     schemaOutput%OUTPUT_NAME% -u %CONNECTION_USERNAME% -p %CONNECTION_PASSWORD% -i %TABLE_NAME%.* -schemas %SCHEMA_NAME% -hq -noviews -loglevel severe

java -jar schemaSpy_5.0.0.jar -dp %JDBC_ORACLE_JAR% -db %TNS_DATABASE_NAME% -o     schemaOutput%OUTPUT_NAME% -u %CONNECTION_USERNAME% -p %CONNECTION_PASSWORD%  -schemas %SCHEMA_NAME% -hq -noviews -loglevel severe

First one will give you diagram with tables starting with EMP . Second one will give you diagram with all tables in hr schema .

share|improve this answer
    
I would like to ask a question. What is etiquette of dealing with duplicate questions and answers? –  Atilla Ozgur Nov 16 '11 at 10:21

I know that SQLServer works with Visio; I'm not sure if Oracle does or not, never tried it, but Visio has a LOT of tricks up its sleeves.

Also, if you're in a reasonably large business, Visio isn't that hard to scrape up a copy of, albeit the license can be a little rich. But professional teams in reasonably large businesses usually already have Visio so it's not that big of a deal.

If anyone can deny or confirm that Visio will do this for Oracle as it does for SqlServer that would be awesome.

share|improve this answer
    
Visio comes with an Oracle driver - you can reverse engineer Oracle databases with it. Note that you need Visio Pro for the database reverse engineer feature. Another nice feature of visio is that you can add back FK relationships that are missing from the database. This makes the structure much clearer. You can also make multiple diagrams from the same repository to illustrate different parts of the model. –  ConcernedOfTunbridgeWells Nov 15 '11 at 21:01
    
Today I learned... –  jcolebrand Nov 15 '11 at 21:20

The free oracle sql developer data modeller works well. note that this is a seperate download than the one bundled with sql developer. The data model tool in sql developer does not have as many features.

http://www.oracle.com/technetwork/developer-tools/datamodeler/overview/index.html

It has some video walkthroughs.

file-> import -> data dictionary. You add a connection to a database and decide what you want to reverse engineer.

Note that the model part will be a mess and will take you time to make it neat enough to read. When oracle reverse engineers, it gives you a physical model and not an ERD. You can change the Physical Model into an ERD. However, most people do not know what an ERD is and think an ERD is a physical model. ERDs have minimal value at the stage where your data model exists. There is no good reason to keep a physical model (which is easier for developers to read) and an ERD.

As far as data dictionary... This usually means documentation about your data model. The best way to do that is to use the tool and to use the "comment" command. Oracle allows you to create comments for all tables, columns and objects and store them in the database. This way you can have documentation in the database that is queriable. Sql Developer data modeller can query this.

You can do this in the GUI, but I find it faster to use "comment" commands and put it in the database from sql scripts, then pull it, instead of clicking on each object. Google "oracle sql comment" for details on how this works.

share|improve this answer

We're using Sybase's PowerDesigner though I admit, its quite overkill for what you want. We're using to support our full software engineering effort, from diagraming use-cases for requirements gathering, capturing those requirements, conceptual and physical models (database, object oriented code, infrastructure, etc), requirements/functionality verification matrices...

Basically anything UML-oriented that helps us to discover, architect, track and implement business solutions from an IT perspective.

Don't be fooled by the "Sybase" name - it supports MANY major RDBMS's and UML diagram formats and has templates for them all. Its a very cool tool.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.