Simplifying Oracle Databases with DbSchema's Unique Features
For Oracle developers and DBAs who are at home in SQL*Plus and want the schema on a diagram, in documentation, and under version control.
On this page
An Oracle schema reaches most people as a list of object names, and nobody on the team can say from that list which table feeds which. DbSchema connects over the Oracle thin driver, reverse-engineers the schema into a diagram you can rearrange, and saves that diagram in a file you commit to Git beside the code.
Connect DbSchema to an Oracle database
The thin driver, oracle.jdbc.OracleDriver, is pure Java and needs no Oracle Client on your machine, and DbSchema downloads it for you when you pick Oracle from the database list. The Connection Dialog then opens with the fields that driver wants: a host, a port, a name for the database, a user and a password.
The name is where Oracle connections usually go wrong, because Oracle accepts three of them and they are not interchangeable. The Connection Mode list carries an entry per form, and each builds a different URL:
jdbc:oracle:thin:@HOST[:PORT]:DB
jdbc:oracle:thin:@//HOST[:PORT]/DB
"Connect via SID" builds the first and takes the instance name. "Connect via Service Name" builds the second and takes a service the listener publishes. "TNS" takes a full descriptor instead of either. The listener's default port is 1521. Run lsnrctl status on the database host and its output fills in both fields: the listening endpoints give you the host and the port, the services summary gives you the names you are allowed to connect to.
Click Connect and DbSchema reads the schema. The connection is direct, because the JDBC driver runs inside DbSchema on your computer and talks to the Oracle listener itself, with no DbSchema server in between. DbSchema installs on Windows, macOS and Linux, and the Oracle page walks through the connection step by step, including the wallet and the ojdbc-full bundle that Autonomous Database on Oracle Cloud needs.
The diagram DbSchema draws from an Oracle schema
Reverse engineering puts every table on a diagram and draws each foreign key as a line between the two tables it joins, with crow-foot symbols for the cardinality. Right-click the canvas and choose New Table to add one; drag from a column in the child table to a column in the parent and the foreign key line appears at once. Double-click a table header to open the Table Dialog, which is where you rename the table, edit columns and types, set the primary key on the Indexes tab, and manage the foreign keys.
Which of those edits reaches Oracle depends on the mode you are in. Connected, a change is executed against the live database immediately and the statement DbSchema sent is listed in the SQL History panel. Choose Disconnected from the connection menu first and the same edits go only into the design model file, to be reviewed and applied when you reconnect.
A single project holds as many diagrams as you want, so one diagram per subsystem reads better than one diagram of everything. The same table can sit on several of them, each with its own layout, and all of them live in the one project file.
Oracle objects the model carries beside the tables
The JDBC API exposes tables, columns and foreign keys and stops there. Triggers, procedures and functions are outside it, so DbSchema reads them with queries of its own, one set per database engine, and those queries return the object's schema, its name and its source text. You can read and adjust them in the database settings if an Oracle object comes back wrong.
Everything DbSchema found sits in the Project Structure panel down the left side: schemas, tables, views, procedures, indexes, constraints, and the diagrams you saved. Right-click any of those objects to open its data, edit its structure, or drop it onto a diagram.
Oracle stores an unquoted identifier in uppercase, and DbSchema has a setting for exactly that. Open Edit → Settings, go to the DBMS Specific tab and pick Formatting: that is where the letter case DbSchema applies is set, and where you switch on quoting when a name has to keep the exact case you typed. DbSchema converts the case in the Table Dialog itself, so a name reads there the way Oracle will store it. The same tab lists the Oracle data types and how each takes its arguments, which is why NUMBER asks for a precision and a scale and INTEGER asks for neither.
HTML5, PDF and Markdown documentation from the model
Go to Diagram → Export HTML5 or PDF Documentation and the documentation dialog asks for three things: the format, which is HTML5, PDF or Markdown; which diagrams go in, from the current one to all diagrams tagged documentation; and which schema elements go in, from columns and foreign keys to indexes and comments.
The HTML5 output opens in any browser with no server behind it. It carries the diagram as a vector image, a searchable table list and the full column details, and anything you typed into a table's or a column's Description field shows up there as a mouse-over tooltip. Markdown is the format to commit next to the code, and PDF is the one to hand to a reviewer who will print it. Documentation export is in the Pro edition, and the same export runs without the interface from a Groovy script, which is how you regenerate the Oracle documentation on every build. The Automation Scripts page has the API.
For PDF containing Cyrillic, Chinese or Japanese text, switch on Embed Unicode Font in the PDF options before you generate.
The Oracle design model in Git
DbSchema saves the whole design, tables, columns, foreign keys and diagrams together, into a single .dbs file in XML. Put that file in a Git repository and an Oracle schema change gets the history, the branches and the review that a code change gets.
Open the Model menu and choose Git — Collaborative Design, which opens the Git dialog. Paste the repository URL, clone into an empty local folder, then open the .dbs file from the Model menu. Stage, Commit and Push are buttons in that dialog; for GitHub and Bitbucket, authenticate with a personal access token rather than your account password. Pull brings down what your teammates committed, and Compare with Current opens the Synchronization Dialog so you can read what their commit changed before any of it reaches Oracle.
Nothing in that loop touches the database. Git moves the model file between machines, and only the synchronization step writes to Oracle.
Comparing the model with the live Oracle database
Two things drift apart: the model on your disk and the Oracle schema a colleague deployed to last week. Schema → Refresh Schema from Database pulls the current database state into the model, and where the two differ, DbSchema lists every added, removed and modified table, column, index and foreign key, and lets you decide each one separately: update the model, push the change to Oracle, or skip it.
Pushing the other way starts at Schema → Synchronize Model with Database. Pick the target schema, and DbSchema generates the SQL migration statements that bring Oracle in line with the model. Those statements are editable in place, so a clause Oracle needs and the generator did not write goes in before anything runs. Schema synchronization is in the Pro edition. Execute is the click that changes the database, so save the .dbs file first: that file is what you restore the previous state from.
Point DbSchema at your Oracle listener and let it draw the schema; the download is at https://dbschema.com/download.html. Connecting, reverse engineering, the diagrams and the SQL editor cost nothing, in the Community Edition. The saved .dbs file, the documentation export and schema synchronization are Pro features, and the purchase page prices them in your own currency under Business, Personal and Universities & Students.
