7

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?

Improve this question
1

4 Answers 4

Reset to default
10

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.

Improve this answer
8

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'
/
Improve this answer
0
0

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.

Improve this answer
1
  • 1
    Thanks for this answer but I think you misunderstood my question. I know that I can query these objects, but how can I write my own metadata e.g for columns? If if have a column CustomerID I might want to add 'Surrogate id generated by ETL-process`. See for example this link
    – jrara
    Sep 26, 2012 at 10:04
0

If you need to do it within Oracle database then comment command is your answer. But there are couple of disadvantages of documenting schema that way:

  • certain privileges are required (not always possible)
  • in some cases you can't interfere with (3rd party/legacy) database
  • you can't use long descriptions (limit to 4000 characters)
  • you can't use or formatted text (directly)
  • you can't add images
  • no easy way to browse documentation

I'd recommend storing documentation in dedicated repository - check Dataedo database documentation tool. It reads schema (and existing comments) and lets you describe every item with rich text and images (for your ERD diagrams). You can then export to nice PDF document or interactive HTML. When schema changes you can sync your documentation with couple clicks.

I'm the product manager of Dataedo.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.