Received this query from a customer this morning…
For your information, I’m average in SQL.
What I would really like to know is if you can give me some tips regarding the use of Oracle SQL Developer in relation to a completely new database. So, are there functions/tools in Oracle SQL Developer which can help me better understand a new database and it’s structure/tables/etc. I’m not looking for tips regarding creating a new database.
I’m hoping for the average customer, the system you will be working with has referential integrity defined IN THE DATABASE. I’m of course talking about foreign key constraints on your tables.
If that’s true…well, wait Jeff, how do we know?
Find a driving table – hopefully they are decently named, something like departments or customers or employees or parts or sales or beers…
Click on it to open it.
Go to the ‘Model’ page.
So I can clearly see that my DEPARTMENTS table is providing information to the EMPLOYEES table and it’s getting information from the LOCATIONS table. I can also see the column names, their data types/sizes, indexes, keys, and more.
Now, if you want to tackle this for your entire database/schema – then you’ll want to import your entire data dictionary to a data modeler design.
Once that’s open, you can see EVERYTHING.
And you can also add the object/column comments to the diagrams to help you answer questions like, what the heck is that column for. This of course assumes your data dictionary is documented via object/column comments.
The Problem with EVERYTHING
The problem with everything in many systems is that it will be too much info for you to process. So you’ll want to break things up into smaller (and hopefully RELATED) pieces.
We make fairly pretty easy with a featured called ‘SubViews.’
Find your driving table in the design/model tree, and right-click:
This will build a subview for you, automagically, of that table and all of the related tables. This will be much easier to get your mind around.
Ok, But We Don’t Have Any Foreign Keys
You’re not alone. We MIGHT be able to help.
You can ask the modeler to GUESS the relationships.
But Wait, There’s More!
We ship a bunch of reports you can run to learn more about your database.
So you can just ask questions, and these reports will help you get the answers. I’ve highlighted a few below that might be of interest…