• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 311
  • Last Modified:

Database Documentation Software for Oracle

I am a new employee attempting to support an Oracle database. I have 20 years experience building and maintaining SQL Server applications.  There are over 100 tables in the Oracle database.  Application, database, and code modules are not documented.  The company and I depend on getting information from 10 year veterans. It is not surprising that IT support people last less than 6 months.

What Oracle database documentation software do you recommend.

I am looking at DBScribe. I need to list table names and descriptions, and column names and descriptions.

ie.

Table
Name: xyz-Master

Columns
Column Name      Column Description
Master_RevNum  Foreign key to the Revision table
Master_OrdNum Foreign key to the Order table
Master_ChgNum Foreign key the Changes table

I will enter the object descriptions.

Thanks,
0
Dovberman
Asked:
Dovberman
  • 6
  • 5
  • 3
  • +2
1 Solution
 
Praveen Kumar ChandrashekatrDatabase Analysist Senior Commented:
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
0
 
DovbermanAuthor Commented:
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,
0
 
DavidSenior Oracle Database AdministratorCommented:
From the POV of another old player:  I would deconstruct the problem down to distinguish between needs and wants.  
A site "needs" to maintain data non-repudiation -- so determine and document what touches the data from its origin to its destination.  
A site "needs" business continuity -- so determine and document, and test, and resolve, every single point of failure that you can identify.
A site "needs" data integrity -- so determine a set of critical requirements, and document your successful and failures.  Overkill for a small shop but I strongly recommend the U.S. DoD STIG guidelines.

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
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
DavidSenior Oracle Database AdministratorCommented:
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.
0
 
Bob LearnedCommented:
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
0
 
DavidSenior Oracle Database AdministratorCommented:
Dovberman, BTW, I went to look over DBScribe based upon your first comment -- odd that the links to the prerequisites are broken.
0
 
slightwv (䄆 Netminder) Commented:
Oracle also has a free Data Modeler that will help with the tables and relationships:
SQL Developer Data Modeler

http://www.oracle.com/technetwork/developer-tools/datamodeler/overview/index.html

The problems you may have is your level of access to the database to aid in reverse engineering it.

Another issue that you may find is relationships in the schema that aren't defined by foreign keys.  These can only be found by looking at the code or from the 'old timers'.

If you are willing to take the lead on the documentation effort, I would suggest as you go you add comments on the tables and columns.  This way the data dictionary holds the descriptions and the schema is sort of self-documenting.

I did this when I built my database and I have a SQL script called dictionary.sql that prints everything out.

It sort of impresses auditor types when they ask 'if' I have anything.  They aren't used to it...

Comments:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_4009.htm#SQLRF01109
0
 
DovbermanAuthor Commented:
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,
0
 
DavidSenior Oracle Database AdministratorCommented:
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.
0
 
DovbermanAuthor Commented:
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.
0
 
DavidSenior Oracle Database AdministratorCommented:
All the best, then.
0
 
slightwv (䄆 Netminder) Commented:
>>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.
0
 
DovbermanAuthor Commented:
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,
0
 
slightwv (䄆 Netminder) Commented:
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'.
0
 
DovbermanAuthor Commented:
I agree that it is best to se distinct to split the output into manageable portions.

Thanks,
0
 
DovbermanAuthor Commented:
Thank you
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 5
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now