14 Replies Latest reply: Feb 17, 2014 7:02 AM by rp0428 RSS

Table documentation in PROD?

petra-K Expert
Currently Being Moderated

Hi all,

 

11.2.0.1

aix6

 

Is it a good practice that the PROD database has tables fiield description documentation?

 

e.g. EMP:

 

Field Name                             Description

==========                             ================                     

 

EMPID                                  Employee ID

ENAME                                  Employee Name

 

 

 

Which V$Views can I select to see if these fields  have values? or is updated?

 

 

Thanks a lot,

pK

  • 1. Re: Table documentation in PROD?
    sb92075 Guru
    Currently Being Moderated

    f55237a7-2c38-4db3-a7a3-1d77256f0730 wrote:

     

    Hi all,

     

    11.2.0.1

    aix6

     

    Is it a good practice that the PROD database has tables fiield description documentation?

     

    e.g. EMP:

     

    Field Name                             Description

    ==========                             ================                     

     

    EMPID                                  Employee ID

    ENAME                                  Employee Name

     

     

     

    Which V$Views can I select to see if these fields  have values? or is updated?

     

     

    Thanks a lot,

    pK

    please learn to Read The Fine Manual before ask documentation questions

     

    DBA_COL_COMMENTS 

  • 2. Re: Table documentation in PROD?
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    Oracle DOES allow you to add documentation as Comments.  IMHO, most developers don't use this feature.  However, they ARE supposed to document Tables and Columns in their Technical / Schema / Design documents.

     

    Here is how Oracle allows this :

     

    HEMANT_SQL>create table my_emp_table (emp_id number primary key, first_name varchar2(50), last_name varchar2(50), hire_date date);

    Table created.

    HEMANT_SQL>comment on table my_emp_table is 'Custom Employees Table';

    Comment created.

    HEMANT_SQL>comment on column my_emp_table.emp_id is 'Primary Key column ; Employee ID';

    Comment created.

    HEMANT_SQL>comment on column my_emp_table.hire_date is 'Date of Hire';

    Comment created.

    HEMANT_SQL>

    HEMANT_SQL>select table_name, comments from user_tab_comments
      2  where table_name = 'MY_EMP_TABLE';

    TABLE_NAME
    ------------------------------
    COMMENTS
    ------------------------------------------------------------------------------------------

    MY_EMP_TABLE
    Custom Employees Table


    1 row selected.

    HEMANT_SQL>

    HEMANT_SQL>select table_name, column_name, comments
      2  from user_col_comments
      3  where table_name = 'MY_EMP_TABLE';

    TABLE_NAME                     COLUMN_NAME
    ------------------------------ ------------------------------
    COMMENTS
    --------------------------------------------------------------------

    MY_EMP_TABLE                   EMP_ID
    Primary Key column ; Employee ID

    MY_EMP_TABLE                   FIRST_NAME


    MY_EMP_TABLE                   LAST_NAME


    MY_EMP_TABLE                   HIRE_DATE
    Date of Hire


    4 rows selected.

    HEMANT_SQL>

     

     

    Hemant K Chitale

  • 3. Re: Table documentation in PROD?
    Suntrupth Expert
    Currently Being Moderated

    No harm, No foul

     

    Regards,

    Suntrupth

  • 4. Re: Table documentation in PROD?
    petra-K Expert
    Currently Being Moderated

    Thanks all,

     

    Hi all,

     

    Which database should this be appropriately defined? DEV, UAT, or PROD?

  • 5. Re: Table documentation in PROD?
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    >Which database should this be appropriately defined? DEV, UAT, or PROD?

    Ask your CIO. 

     

    Your organisation should have standards around documentation.

     

    Hemant K Chitale


  • 6. Re: Table documentation in PROD?
    petra-K Expert
    Currently Being Moderated

    Hi All,

     

    It seems our dev is not using it

     

      1  select distinct owner from dba_tab_comments

      2* where comments is not null

    SQL> /

     

     

    OWNER

    ------------------------------

    SYSTEM

    XDB

    FLOWS_FILES

    APEX_030200

    SYS

    SYSMAN

    EXFSYS

    DBSNMP

     

     

    8 rows selected.

     

      1  select distinct owner from dba_col_comments

      2* where comments is not null

    SQL> /

     

     

    OWNER

    ------------------------------

    SYSTEM

    XDB

    APEX_030200

    SYS

    SYSMAN

    EXFSYS

     

     

    6 rows selected.

  • 7. Re: Table documentation in PROD?
    petra-K Expert
    Currently Being Moderated

    Thanks all,

     

    ***Your organisation should have standards around documentation.


    Supposing the dev updated the comments on their DEV database, how can I transfer it to the PROD without messing up the tables, and data, and process running?

  • 8. Re: Table documentation in PROD?
    sb92075 Guru
    Currently Being Moderated

    f55237a7-2c38-4db3-a7a3-1d77256f0730 wrote:

     

    Thanks all,

     

    ***Your organisation should have standards around documentation.


    Supposing the dev updated the comments on their DEV database, how can I transfer it to the PROD without messing up the tables, and data, and process running?

    using SQL MERGE

  • 9. Re: Table documentation in PROD?
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    As I noted, most developers do not use this feature.  Organisational standards generally require such descriptions to be included in technical documentation which is outside the database.

     

    Hemant K Chitale


  • 10. Re: Table documentation in PROD?
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    I would generate a script file by querying DBA/USER_TAB_COMMENTS and DBA/USER_COL_COMMENTS.

     

    e.g.

    select 'comment on table  ' || table_name || '  is   ' || '''' || comments || '''' || ' ;'

    from user_tab_comments

    where comments is not null;

     

     

    Hemant K Chitale

  • 11. Re: Table documentation in PROD?
    petra-K Expert
    Currently Being Moderated

    Thanks all,

     

    Can you give me sample of sql merge?

  • 12. Re: Table documentation in PROD?
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    I don't know if it can be done with a MERGE.

     

     

    Hemant K Chitale

  • 13. Re: Table documentation in PROD?
    petra-K Expert
    Currently Being Moderated

    How about Sb?

  • 14. Re: Table documentation in PROD?
    rp0428 Guru
    Currently Being Moderated

    f55237a7-2c38-4db3-a7a3-1d77256f0730 wrote:

     

    How about Sb?

    SB was being facetious.

     

    NEVER perform DML on Oracle's tables - leave them alone!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points