Maliki Hassani
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
So I ran the top query in Developer but nothing is in the results section?
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.
I believe the desc command will work in SQL Deverloper. I don't use the GUI tools to I'm not 100% sure.
ASKER
Ahh there was a space in desc. thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Brilliant! You all are awesome!
ASKER
Thanks
ASKER
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.
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.
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.
ASKER
Great! Thank you
ASKER