How to document Oracle database?

Question

I'm new to Oracle databases. I have used to SQL Server and the description fields (MS_Description) for tables and columns for documentation purposes. Is there an equivalent for Oracle? What is the best practice for documenting Oracle database?

Answer 1

In Oracle, you would use the COMMENT command to:

[...] add to the data dictionary a comment about a table or table column, view, materialized view, operator, indextype, mining model, or edition.

Most tools (PL/SQL Developer, Toad...) will display these comments in appropriate fields when you browse the database schema.

The comments can be queried directly with the *COMMENTS dictionary views, such as ALL_TAB_COMMENTS.

Answer 2

In Oracle there are table and column comments that can be used for documentation.

These comments can easily be added by the following commands:

COMMENT ON TABLE my_table IS 'Documentation of my table'
/


COMMENT ON COLUMN my_table.my_columns IS 'Documentation of my column'
/

Answer 3

Oracle exposes its data dictionary through views. You can use these views to query the information you need.

The main views that you'll need to use are:

• DBA_OBJECTS - lists all DB objects
• DBA_TABLES - list all tables (and their owners, which are schemas in Oracle)
• DBA_TAB_COLUMNS - list all tables and their columns

There are also ALL_% and USER_% equivalents of these views (eg: `USER_TABLES) that only show all objects you have permission on, and objects you own.