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?
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.
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.
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.
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.
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.
I answered same question in https://stackoverflow.com/questions/8140387/oracle-extract-graphical-database-representation/8149947#8149947
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:oracleproduct10.2.0client_1jdbclibojdbc14.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 .
We use Dataedo for generating data dictionaries and ERDs for all our databases – Oralce & SQL Servers. You just connect to a database, schema is imported into repository and you can describe each element with UI. Tool enables splitting large databases into logical modules, defining missing FKs and UKs, use rich text and paste images. Using physical and logical FKs tool creates simple ERDs.
Each time database schema changes you can import those changes and they are automatically reflected in repository.
Data dictionary with ERDs are exported to PDF or interactive HTML.
I’m the product manager of Dataedo.