Oracle Creating ER Diagrams and Data Dictionaries

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.

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.