Software

Ask something
Discussion Locked

Oracle Data Dictionary

0 helpful
6 replies

by saint chet

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Help...

I'm very new to Oracle and in fact in the very earlier stages of learning. I would like to know how I can generate a data dictionary for an Oracle database. Are there any special tools that I need, is there any ground work that has to be done before I can do this?

We are running Oracle 8.*...

Any help with this would be much appreciated.

Thank You,
Chetan Parmar

One's first step in wisdom is to question everything - and one's last is to come to terms with everything.-- Georg Christoph Lichtenberg (1742 - 1799)

6 replies

by ragu t

Oracale database will create and maintain it's own datadictionary. We don't have to create it. We use it.

That's what I think :)

cheers

by saint chet

However, what if I wanted to print out the data dictionary? How would I do that? I need a hard copy of the dictionary...

Any input would be appreciated..

Thanks

by jack_swayze

Well,

As a previous post said, the dictionary, itself, is created and maintained by the DBA using DDL to create the database instance in the first place.

But, if what you mean is a human-readable report that describes what columns exist in what tables.... then let me point you in the right direction.

First off, you need to know what tables exist, that you have authority to 'see'.

I assume you have the ability to connect to SQL-Plus. If not, you will need the DBA to install SQL-Plusfor Windows and put an entry in your TNSNAMES.ORA file (and maybe assign you a user-id and password). For the below process to work, the DBA will also need to grant you select authority against the dictionary views.

So, to find out what tables you can see, you need to do this select:

Select owner, table_name from all_tables;

Next you can do a 'Describe' on each of the tables, one by one.

Lets say one of the rows returned by the above select shows an owner of 'ACCOUNTING' and a table of 'CHRT_OF_ACCTS'. To know what columns exist in CHRT_OF_ACCTS do this:

DESC ACCOUNTING.CHRT_OF_ACCTS

All you need now is to know which columns are the primary keys:

select i.index_name, ic.column_name
from all_indexes i, all_ind_columns ic
where i.table_owner = 'ACCOUNTING'
and i.table_name = 'CHRT_OF_ACCTS'
and i.uniqueness = 'UNIQUE'
and i.owner = ic.owner
and i.index_name = ic.index_name
order by ic.index_name, ic.column_position;

However, I should point out that all your questions should be answerable by your Data Administration group. You should have a Data Analyst who has created a Data Model of all this, already.

If you want to find out more about what information exists in the Oracle dictionary... do this query:

Select view_name from all_views
where substr(view_name,1,4) = 'ALL_';

Then just play around in all the glory that is the Oracle dictionary.

by saint chet

Thanks for the suggestions. I'll try it and post my results... Unfortunately, the company that we purchased the product from never developed a formal data dictionary or ERD for the application. Go figure...

Thanks again...
Chetan Parmar
cparmar@flaghouse.com

by tudorm

I would like to comment on one paragraph in Jack Swayze's answer (otherwise very good):

"So, to find out what tables you can see, you need to do this select:

Select owner, table_name from all_tables;

Next you can do a 'Describe' on eachof the tables, one by one. "

If you have 500 tables, will you describe them one by one ?

here is one example of using sql to create sql scripts:

select 'describe '||object_name from user_objects where object_type = 'TABLE';

this query will produce a result like this:

describe EMPLOYEES
describe DEPARTMENTS
.......

You have to spool the above results in a text file and execute it like any other sql script.
Note: for the above script you don't need special rights from the administrator, you must connect as the owner of the application tables.

Now, if you want to find more detailed info about your data, for example the relationships between tables (primary+foreign keys) you need to issue a join query between
USER_TAB_COLUMNS, USER_CONS_COLUMNS and USER_CONSTRAINTS, looking for constraints of type 'P' (primary keys) and 'R' (foreign keys). You will obtain the primary and foreign keys' structures (sequence of columns).
And if some of your tables reference other user's tables, then change USER_ to ALL_, but now you have to restrict the queried rows to your objects.

by rrice5158

Some of the tables are automatically generated but the majority of the data dictionary has to be created by running two sql scripts in the admin oracle\ora80(or 90)\admin directory. They are catproc.sql and catalog.sql. These can be run from sql plus by logging in as sysdba and using the syntax @c:\oracle\ora80\admin\catproc.sql;
(or the appropriate location for these scripts depending on your setup.

This discussion is closed to new replies.