Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Oracle:  Data Dictionary

Posted on 2014-10-10
13
Medium Priority
?
982 Views
Last Modified: 2014-10-10
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
0
Comment
Question by:Maliki Hassani
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 2
13 Comments
 
LVL 23

Assisted Solution

by:Steve Wales
Steve Wales earned 1336 total points
ID: 40373469
The following will get you a list of all your tables and their basic data layouts (looking somewhat like the output from a DESCRIBE command in SQLPlus:
set lines 132
set pages 10000
column table_name format a30
column column_name format a30
column data_type format a40
column nullable format a8

break on table_name skip 2;

select 
  table_name, 
  column_name,
  decode(data_type, 
   'VARCHAR2',data_type||'('||data_length||')',
   'CHAR'    ,data_type||'('||data_length||')',
   'NVARCHAR2'    ,data_type||'('||data_length||')',
   'NCHAR'    ,data_type||'('||data_length||')',
   'NUMBER' ,decode(data_precision, 
                    null, data_type, 
                    data_type||'('||data_precision||','||data_scale||')'),
   data_type) as DATA_TYPE,
  decode(nullable, 'Y', ' ', 'NOT NULL') as NULLABLE
from dba_tab_columns
where owner = 'MYSCHEMA'
order by table_name, column_id

Open in new window

0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 664 total points
ID: 40373478
The SQL above should work.

Since this is a one time thing, I prefer SQL to generate a script and just execute the script:

set pages 0
set feedback off
spool mylist.sql
select 'desc ' || table_name from user_tables;
spool off

@mylist
0
 

Author Comment

by:Maliki Hassani
ID: 40373509
Okay great..  Should I be running this in Oracle SQL developer?
0
Interactive Way of Training for the AWS CSA Exam

An interactive way of learning that will help you visualize core concepts so that you can be more effective when taking your AWS certification exam.  Built for students by a student to help them understand the concepts that they are being taught.

 

Author Comment

by:Maliki Hassani
ID: 40373512
So I ran the top query in Developer but nothing is in the results section?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40373514
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.
0
 

Author Comment

by:Maliki Hassani
ID: 40373529
Ahh there was a space in desc.  thanks
0
 
LVL 23

Accepted Solution

by:
Steve Wales earned 1336 total points
ID: 40373556
Just to make sure you did it, make sure you change 'MYSCHEMA' to the name of the owner of your tables.

I just ran it in SQL Developer and it ran fine.

Alternatively if you don't have SYS/SYSTEM/DBA access, connect as the user who owns the tables, change dba_tab_columns to user_tab_columns, remove the where clause and run it.

In SQL Developer, everything before the "select" will be ignored (these are formatting options for SQL Plus).

As an alternative, since you want the output in Excel, you could just try opening Excel and using MSQuery (as ugly as that interface is) to drag the output you want directly into Excel - it's all there in user_tab_columns.
0
 

Author Comment

by:Maliki Hassani
ID: 40373572
Brilliant!  You all are awesome!
0
 

Author Closing Comment

by:Maliki Hassani
ID: 40373574
Thanks
0
 

Author Comment

by:Maliki Hassani
ID: 40373661
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
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40373673
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.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40373685
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.
0
 

Author Comment

by:Maliki Hassani
ID: 40373745
Great! Thank you
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Via a live example, show how to take different types of Oracle backups using RMAN.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question