Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

Monitoring Table Columns

Oracle Database Tips by Donald Burleson

To round out the general table reports, we need a report on the columns within a table. The script in Source 10.8 fulfills this need.

Note:  Monitoring table columns is is not used after release 10.2.0.4

SOURCE 10.8 Script to report table columns by owner and table.

rem
rem tab_col.sql
rem
rem FUNCTION: Report on Table and View Column Definitions
rem
rem MRA 9/18/96
rem MRA 6/14/97 Added table level selectivity
rem
COLUMN owner             FORMAT a10       HEADING Owner
COLUMN table_name        FORMAT a30       HEADING "Table or View Name"
COLUMN COLUMN_name       FORMAT a32       HEADING "Table or View|Attribute"
COLUMN data_type         FORMAT a15       HEADING "Data|Type"
COLUMN data_type_owner   FORMAT a13       HEADING "Type|Owner"
COLUMN data_length                        HEADING Length
COLUMN nullable          FORMAT a5        HEADING Null
BREAK ON owner ON table_name SKIP 1
SET LINES 132 PAGES 48 FEEDBACK OFF VERIFY OFF
START title132 "Table Columns Report"
SPOOL rep_out/&db/tab_col
SELECT
      a.owner,
      table_name||' '||object_type table_name,
      column_name,
      data_type,
      data_type_owner,
      data_length,
      DECODE(nullable,'N','NO','YES') nullable
FROM
      dba_tab_columns a, dba_objects b
WHERE
      a.owner=UPPER('&owner') AND
      a.owner=b.owner AND
      a.table_name LIKE UPPER('%&table%') AND
      a.table_name=b.object_name AND
      object_type IN ('TABLE','VIEW','CLUSTER')
ORDER BY
      owner,
      object_type,
      table_name,
      column_id
/
SPOOL OFF
TTITLE OFF
SET LINES 80 PAGES 22 FEEDBACK ON VERIFY ON


The script in Source 10.8 allows you to specify for a specific owner the table for which you want to see the columns. If a naming convention that includes prefix or suffix designations is used when naming tables, then the prefix or suffix can be specified to pull the values for a specific type of table. The output from the script is shown in Listing 10.7.

LISTING 10.7 Example of the output from the table column report.

Enter value for owner: tele_dba
Enter value for table: CLIENTS_INFO_NUMBERSV8i
Date: 05/09/99                                                                        Page:   1
Time: 11:43 AM                     Table Columns Report                                     SYS
                                     ORTEST1 database

                                       Table or View         Data            Type
Owner    Table or View Name            Attribute             Type            Owner    Length Null
-------- ----------------------------  --------------------  --------------- -------- ------ ---
TELE_DBA CLIENTS_INFO_NUMBERSV8i TABLE CLIENTS_INFO_NMBRS_ID NUMBER                       22 NO
                                       CLIENTS_ID_R          CLIENT_T        TELE_DBA     50 YES
                                       LISTED_NAME           VARCHAR2                    100 YES
                                       EARNING_NUMBER        CHAR                         13 YES
                                       SERVICE_CLASS         VARCHAR2                      5 YES
                                       NO_OF_LINES           NUMBER                       22 YES
                                       DISCONNECT_DATE       DATE                          7 YES
                                       DISCONNECT_REASON     CHAR                          2 YES
                                       BILLING_NAME          VARCHAR2                     40 YES
                                       PHONE                 VARCHAR2                     10 YES
                                       BTN                   CHAR                         13 YES
                                       OLD_CLIENTS_NUMBER    CHAR                         13 YES
                                       SERVICE_ADDRESS       VARCHAR2                    100 YES
                                       CON_CTRL_NUMBER       CHAR                         15 YES                                              TERM_AGREEMENT        CHAR                         13 YES
                                       SHARED_TENANT_SVCS    VARCHAR2                     10 YES
                                       INSTALLATION_DATE     DATE                          7 YES
                                       CONTRACTS             CONTRACT_LIST  TELE_DBA      16 YES
                                       CIRCUITS              CIRCUIT_LIST   TELE_DBA      16 YES

Monitoring Table Column Statistics  

Oracle has provided column-level statistics in the DBA_TAB_COLUMNS view. Several of the statistics, such as average length, number of null values, and so on, are useful to the DBA. These table column statistics aren't populated unless the table is analyzed. Source 10.9 shows a report script for these table column statistics. The output from the script in Source 10.9 is shown in Listing 10.8.

SOURCE 10.9 Example of table column statistics.

rem
rem tab_col_stat.sql
rem
rem FUNCTION: Report on Table and View Column Definitions
rem
rem MRA 9/18/96
rem MRA 6/14/97 Added table level selectivity
rem MRA 5/8/99 Converted to do stats
rem
COLUMN owner            FORMAT a12      HEADING Owner
COLUMN table_name       FORMAT a20      HEADING "Table Name"
COLUMN COLUMN_name      FORMAT a13      HEADING "Table|Attribute"
COLUMN data_type        FORMAT a10      HEADING "Data|Type"
COLUMN avg_col_len      FORMAT 99,999   HEADING "Aver|Length"
COLUMN density          FORMAT 9.9999   HEADING "Density"
COLUMN last_analyzed                    Heading "Analyzed"
COLUMN num_distinct                     HEADING "Distinct|Values"
COLUMN num_nulls                        HEADING "Num.|Nulls"
COLUMN sample_size                      HEADING "Sample|Size"
BREAK ON owner ON table_name SKIP 1
SET LINES 132 PAGES 48 FEEDBACK OFF VERIFY OFF
START title132 "Table Column Stats Report"
SPOOL rep_out/&db/tab_col
SELECT
   owner,table_name,column_name,data_type,
   num_distinct,density,num_nulls,
   TO_CHAR(last_analyzed,'dd-mon-yyyy hh24:mi') last_analyzed,
   sample_size, avg_col_len
FROM
  dba_tab_columns
WHERE
   owner LIKE UPPER('%&owner%')
   and table_name LIKE UPPER('%&tabname%')
/
SPOOL OFF
TTITLE OFF
SET LINES 80 PAGES 22 FEEDBACK ON VERIFY ON

LISTING 10.8 Example of output from the table column statistics report.

Enter value for owner: graphics_dba
Enter value for tabname:
Date: 05/09/99
Page:   1     

Time: 01:39 PM                           Table Column Stats Report              
SYS

                                             ORTEST1 database

                                  Table         Data  Distinct         Num.  
                Sample    Aver
Owner        Table Name        Attribute     Type     Values Density  Nulls
Analyzed            Size  Length

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

GRAPHICS_DBA BASIC_LOB_TABLE   X             VARCHAR2      0    .0000     0 09-may-1999 10:44              0

                               B             BLOB             

                               C             CLOB

                        

             GRAPHICS_TABLE    BFILE_ID      NUMBER       32    .0313     0 09-may-1999 10:44     32       2 

                               BFILE_DESC    VARCHAR2      0    .0313    32 09-may-1999 10:44              0      

                               BFILE_LOC     BFILE        30    .0333     0 09-may-1999 10:44     32      40      

                               BFILE_TYPE    VARCHAR2      2    .5000     0 09-may-1999 10:44     32       1      

             INTERNAL_GRAPHICS GRAPHIC_ID    NUMBER       32    .0313     0 09-may-1999 10:45     32       2     

                               GRAPHIC_DESC  VARCHAR2     31    .0323     0 09-may-1999 10:45     32      18      

                               GRAPHIC_BLOB  BLOB                                                                              

                               GRAPHIC_TYPE  VARCHAR2      2    .5000     0 09-may-1999 10:45     32       1      

Monitoring Table Keys  

Per the requirements of Third Normal Form, each table is required to have a unique identifier that consists of one or more of the table's columns. As an alternative, a derived key can be used that consists of a number pulled from an Oracle sequence, but this method should be used only if the key would be excessively long (over three columns). This is called the primary key of the table, and it should be identified using a constraint clause when the table is created. A second type of key, called a foreign key, is also present in most tables. The foreign key is used to enforce relationships between two or more tables. The foreign key consists of the primary key from the related table. The foreign key, too, should be identified by a constraint clause when the table is created.

If the two types of keys have been identified via the constraint clause during table creation, they can be readily monitored via the PK_FK_RPT.SQL script, which is available on the Wiley Web site. For an example of this script's output, see Listing 10.9.

LISTING 10.9 Example of output of the primary/foreign key report.

Date: 05/09/99                                                                          Page:   1     

Time: 03:13 PM                   Primary Key - Foreign Key Report                       SYSTEM        

                                           ORTEST1 database                                                           

Pri Table  Pri Table         Pri Key           For Table  For Table            For Key

Owner      Name              COLUMNs           Owner      Name                 COLUMNs
---------- ----------------- ----------------- ---------- -------------------- -----------------TELE_DBA   BBS_DUNS_PROFILE  SITE_ID           TELE_DBA   BBS_FRANCHISE_CODES  SITE_ID
                             SITE_ID                      BBS_SIC_CODES        SITE_ID
           BBS_EARNINGS_INFO EARNING_NO_DOCID  TELE_DBA   BBS_AUDIT_RECORD     EARNING_NO_DOCID
                             EARNING_NO_DOCID             BBS_CIRCUIT_ID_INFO  EARNING_NO_DOCID
                             EARNING_NO_DOCID             STI_ADDRESS          EARNING_NO_DOCID
           CUSTOMER_SITES    SITE_ID           TELE_DBA   BBS_DUNS_PROFILE     SITE_ID
                             SITE_ID                      BBS_EARNINGS_INFO    SITE_ID
TEMP_USER  DEPT              DEPTNO            TEMP_USER  EMP                  DEPTNO

Monitoring Tables for Chained Rows  

Row chaining occurs as data is added to an existing record. When there is insufficient room for the addition, the row is chained to another block and added there. Block chaining occurs when a row is too long to fit into a single block (such as with long raw or LOB columns or when a row has more than 255 columns). If chaining is occurring regularly, it can lead to significant performance degradation. This degradation is caused by the requirement to read multiple blocks to retrieve a single record. An example of a script to monitor a single table for chained rows is shown in Source 10.10. Note that this script is limited, in that the table must have a primary or unique key defined in order for it to work. With a companion script, all tables in an application can be checked with this script. As an alternative, you can analyze the tables of concern and use the table statistics report, as described in the following subsection.

 
This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".


 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.