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?
1you might also be interested in this related question– Jack DouglasSep 26, 2012 at 10:47
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
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' /
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
USER_% equivalents of these views (eg: `USER_TABLES) that only show all objects you have permission on, and objects you own.
1Thanks 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
CustomerIDI might want to add 'Surrogate id generated by ETL-process`. See for example this link– jraraSep 26, 2012 at 10:04
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.