Database Documentation Software for Oracle

To get the table and column level description you can use
SQL>describe <table_name>;

or you can use the data dictionary views in the oracle database i.e
 
SQL>SELECT a.table_name,b.column_name,substr(b.data_type||'('||data_length||')', 0, 20) as data_type,
decode(b.nullable,'N','NOT NULL', '') as null_status, d.comments, c.created
FROM user_tables a, user_tab_columns b, user_objects c, user_Col_Comments d
WHERE a.table_name = b.table_name AND a.table_name = c.object_name AND object_type = 'TABLE'
AND B.Column_Name=D.Column_Name
ORDER BY a.TABLE_NAME;


Since you are and SQL Server DBA i suggest you to with the book Oracle Database Administration for Microsoft SQL Server DBAs

http://www.amazon.com/Oracle-Database-Administration-Microsoft-Server/dp/0071744312

Also the Oracle Concepts Doc
http://docs.oracle.com/cd/E11882_01/server.112/e40540.pdf

I am a user, not the dba. My job is to support application development and respond to user requests for changes.  However the data dictionary SQL will be useful.

I will try it Monday morning and send feedback.

Something like DBScribe would be useful.  I would export the data dictionary to the people who have been working with the database for 10 years and get column descriptions in business terms.

Thanks,

From the POV of another old player:  I would deconstruct the problem down to distinguish between needs and wants.  

Lastly for now, who can afford the labor cost to maintain a pretty but static document of the data dictionary, just for the sake of "documentation"?  In a world of high turnover and neglected training, you will do quite well if your development team can consistently code to site standards from year to year.

In actuality, what are your highest priorities?  Any data modeling tool can reverse engineer your physical DDL.  What use is that data once you have it?

Regards,
dvz

We cross-posted.  I'll modify my harangue since you're not the DBA with one more comment.  There may be more value in capturing the business rules and process flow (how the data is moved and used).  Any developer can readily find the dictionary.  A sharp developer will need to know about relationships and constraints.

We use Toad for Oracle products, like the Data Modeler, which I believe would suit your needs.

Quest Software has a freeware version that you could use to play around, and see if it can meet your requirements.

Toad Data Modeler Freeware Edition
http://www.toadworld.com/m/freeware/553.aspx

Dovberman, BTW, I went to look over DBScribe based upon your first comment -- odd that the links to the prerequisites are broken.

These are all great suggestions.  My reputation as an IT support contractor over the past 25 years was built on solid functional and business objective documentation.  Only once was I needed after a project was completed. This is a legacy system which the old-timers protect for job security. I spent 3 days identifying an issue simply because the lead developer neglected to tell me that "Planning Letters", abbreviated in the report header as PL, have three type codes.  There is no Letter_Type table in the database. The types are hard coded into a three page SQL statement.

My job is to convince management that documentation can reduce the support effort by as much as 66 percent. BTW. The company is a DOD contractor.

The company has Toad and Struts installed. No one told me what these plug-ins do. I will find out on line.

Thanks,

The objective of all dedicated employees, er, system analysts, should be to thoroughly analyze
all situations, anticipate all problems prior to their occurrence, have
answers for these problems, and move swiftly to solve these problems
when called upon.
However...
It is difficult to remember that your objective is to drain the swamp when you are up to your ass in alligators.

I heard the alligator analogy 30 years ago when I worked at Kodak in Rochester. I repeated it to my manager just 3 days ago here on the Mississippi Gulf Coast. It should be more significant here where we often see alligators. She agreed that we need to consider documentation more seriously.  We will develop a strategic plan. In fact, I was chosen out of 12 other candidates by her and the division manager because of my presentation of previous documentation.

All the best, then.

>>The company is a DOD contractor.

The contract world is always "MOVE FORWARD AT ALL COSTS.  It doesn't have to work 100%, 70-80% is close enough and we'll 'fix' it later".  If a band-aid will keep it from bleeding, good enough!!!

Besides, it is not in a contractor's best interest to make things self-manageable.  It is to provide EXACTLY what is contracted for.  No more, no less.

Then at the end of the contract, they can win the re-compete to continue on for as long as they can ride the wave!

If everything was delivered with a nice bow on top, there would be no need to continue the contract extensions.

or you can use the data dictionary views in the oracle database i.e
 
 SQL>SELECT a.table_name,b.column_name,substr(b.data_type||'('||data_length||')', 0, 20) as data_type,
 decode(b.nullable,'N','NOT NULL', '') as null_status, d.comments, c.created
FROM user_tables a, user_tab_columns b, user_objects c, user_Col_Comments d
WHERE a.table_name = b.table_name AND a.table_name = c.object_name AND object_type = 'TABLE'
 AND B.Column_Name=D.Column_Name
 ORDER BY a.TABLE_NAME;


What do I need to change to avoid replicated rows?

T_PSA_SWBS      DELETED_REASON      VARCHAR2(50)
T_PSA_SWBS      DELETED_REASON      VARCHAR2(50)
T_PSA_SWBS      DELETED_REASON      VARCHAR2(50)
T_PSA_SWBS      DELETED_REASON      VARCHAR2(50)
T_PSA_SWBS      DELETED_REASON      VARCHAR2(50)
T_PSA_SWBS      DELETED_REASON      VARCHAR2(50)
T_PSA_SWBS      DELETED_REASON      VARCHAR2(50)
T_PSA_SWBS      DELETED_REASON      VARCHAR2(50)
T_PSA_SWBS      DELETED_REASON      VARCHAR2(50)
T_PSA_SWBS      DELETED_REASON      VARCHAR2(50)
T_PSA_SWBS      DELETED_REASON      VARCHAR2(50)
T_PSA_SWBS      DELETED_BY          VARCHAR2(35)

Thanks,

Might be missing a join but I'm not going to walk through that select looking for it for reasons I'll suggest below.

That said, a distinct will work...
SELECT distinct a.table_name ...

Now the reasons:
Instead of trying to CRAM all the information into a single output, I would look at different selects to get each specific item you are after.

dbms_metadata.get_ddl is much easier for 90% of what you are after:
select dbms_metadata.get_ddl('TABLE','T_PSA_SWBS') from dual;

Trying to add the comments at the same time as the fields would be confusing.

What output are you after if the table also has comments in addition to the column's comments?

I would really look at separating the two 'reports'.

I agree that it is best to se distinct to split the output into manageable portions.

Thanks,

Thank you