Link to home
Start Free TrialLog in
Avatar of Maliki Hassani
Maliki HassaniFlag for United States of America

asked on

Oracle: Data Dictionary

Greetings,

I have a new employee in my team and wanted to provide her with a data dictionary of all the table we are using.  Is there a way to do this easily?

Also, I would like to show a table's fields by showing the name and the type (number, Varchar, etc..)  Then I can pull the data into excel and create a data dictionary.

Is there a query that I can run?

Thanks!!

I am using Aqua data studio and Oracle Developer
SOLUTION
Avatar of Steve Wales
Steve Wales
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Maliki Hassani

ASKER

Okay great..  Should I be running this in Oracle SQL developer?
So I ran the top query in Developer but nothing is in the results section?
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

The select should run wherever.

I believe the desc command will work in SQL Deverloper.  I don't use the GUI tools to I'm not 100% sure.
Ahh there was a space in desc.  thanks
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Brilliant!  You all are awesome!
Thanks
One more question though.   It only shows the tables/views that are in myschema.  How can I pull for all other tables?  I am also using the query for user_tab_columns and removing the where clause
You'll need the DBA level views:  dba_tab_columns
and add a where:  where owner in ('MYSCHEMA','MYOTHERSCHEMA')

Oracle has 3 level for all of it's views:  DBA, ALL and USER.

DBA is everything.
ALL is everything you have permission to see.
USER is what you own.

So if you use a view say, USER_TABLES, there is also an ALL_TABLES and DBA_TABLES.  Same goes for all the other available Oracle provided views.
I suppose I should also point out that just a list of tables and columns isn't all that useful to anyone.

What would make more sense is a physical model of the database complete with primary and foreign key relationships.

You can reverse engineer your database using SQL Developer Data Modeler:
http://www.oracle.com/technetwork/developer-tools/datamodeler/overview/index.html

Point it to a schema/database and let it generate the model.  Then all you'll need to do is move the tables around so it makes a nice pretty picture.
Great! Thank you