BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 
 

Using the V$ and DB_ Views for Monitoring Sequences

Oracle Tips by Burleson Consulting

Sequences are used to generate integer numbers for use in keys or in any other column that requires either repeating or nonrepeating numbers. No changes were made to sequences under Oracle8, Oracle8i, and Oracle9i. Essentially, therefore, the only monitoring that the DBA can do is to identify the sequences, their owners, and so on. The DBA can query the sequence’s values, but then those values are lost. The view used in the SQL script in Source 11.19 holds the last value written to disk; this is all the data on the actual sequence value that the DBA can get nondestructively (use of the NEXTVAL function destroys the value selected). 

TIP: One technique I have found useful is to use a SELECT against the DBA_SEQUENCES table LAST_NUMBER column when I use a sequence for the primary key of a table I am loading, instead of using a COUNT(*) to determine the progress of the load. This technique provides a virtually instantaneous return of the value and doesn’t contend with the load process. Of course, this only applies if the sequence is used as a part of a SQLLOADER load or an SQL load, and is reset to start at 1 at the beginning of the load.

The DBA should monitor the last value written against the maximum value for ascending sequences and the minimum value for descending. If the sequence is near its limit, and is not a cycled sequence, the DBA will have to alter the minimum or maximum values using the ALTER SEQUENCE command if the sequence value is approaching the minimum or maximum value. If this isn’t done, the tables depending on the sequence will fail any selects to retrieve sequence values.

As with other objects, if sequences are used in applications, they should be owned by a central DBA account for the application. This report, if used with the wild card (%) option, will report on all sequences, thus showing privately owned sequences. To alter the ownership of a sequence, it must either be dropped and re-created, with possible loss of continuity in sequence numbers, or exported and then imported into the new owner with no loss of values.

In addition, the DBA should monitor the number of values being cached. If this value is excessive, large numbers of cached sequence values are lost during shutdown. If the value is too small, and the sequence is accessed frequently, performance can suffer. The default value for cache is 20.

A heads-up is also required for the following scenario: If someone adds a value or values to the DUAL table (this is a SYS-owned table with a single column, DUMMY, and a single value, X), then any selects in PL/SQL against DUAL to fetch a NEXTVAL or CURRVAL will error out with “ORA-01422: exact fetch returns more than the requested number of rows.” In addition, several of the DBMS_ packages depend on this table, so if they start returning ORA-01422 errors, check this table immediately. Source 11.19 shows a report format from a sequence report. Listing 11.16 shows the output from the script in Source 11.19.

SOURCE 11.19 SQL script to generate a sequence report.

rem  NAME: Seq_rep.sql
rem
rem  HISTORY:
rem  Date            Who                          What
rem  --------    ----------------------        ---------------
rem  5/10/93     Mike Ault                     Creation
rem  5/16/99     Mike Ault                     Verified for Oracle8i
rem  FUNCTION: Generate report on Sequences
rem  INPUTS:
rem
rem     1 - Sequence Owner or Wild Card
rem     2 - Sequence Name or Wild Card
rem
rem  **************************************************************
SET HEADING OFF VERIFY OFF PAUSE OFF
PROMPT ** Sequence Report **
PROMPT
PROMPT Percent signs are wild
ACCEPT sequence_owner char  PROMPT 'Enter account to report on (or pct sign):';
ACCEPT sequence_name char  PROMPT 'Enter sequence to report on (or pct sign):';
PROMPT
PROMPT Report file name is SEQUENCE.LIS
SET HEADING ON
SET LINESIZE 80 PAGESIZE 56 NEWPAGE 0 TAB OFF SPACE 1
SET TERMOUT OFF VERIFY OFF FEEDBACK OFF
BREAK ON sequence_owner SKIP 2
COLUMN sequence_owner      FORMAT A10     HEADING 'Sequence|Owner'
COLUMN sequence_name       FORMAT A16     HEADING 'Sequence|Name'
COLUMN min_value                          HEADING 'Minimum'
COLUMN max_value                          HEADING 'Maximum'
COLUMN increment_by        FORMAT 999     HEADING 'Inc'
COLUMN cycle_flag                         HEADING 'Cycle'
COLUMN order_flag                         HEADING 'Order'
COLUMN cache_size          FORMAT 99999   HEADING 'Cache'
COLUMN last_number         FORMAT 99999   HEADING 'Last|Value'
START title80 "SEQUENCE REPORT"
SPOOL rep_out/&&db/seq_rep
SELECT
     sequence_owner,sequence_name,
     min_value,max_value,
     increment_by,
     DECODE(cycle_flag,'Y','YES','N','NO') cycle_flag,
     DECODE(order_flag,'Y','YES','N','NO') order_flag,
     cache_size,last_number
FROM
     DBA_sequences
WHERE
     sequence_owner LIKE UPPER('&sequence_owner') AND
     sequence_name LIKE UPPER('&sequence_name')
ORDER BY
     1,2;
SPOOL OFF
SET LINESIZE 80 PAGESIZE 22 NEWPAGE 0 TAB ON SPACE 1
SET TERMOUT ON VERIFY ON FEEDBACK ON
CLEAR BREAKS
CLEAR COLUMNS
TTITLE OFF

LISTING 11.16 Example of report format from sequence script.

Date: 10/14/01                                                   Page:   1
Time: 01:45 PM                SEQUENCE REPORT                    DBAUTIL
                              galinux1 databa

Sequence   Sequence                                                   Last
Owner      Name               Minimum   Maximum  Inc Cyc Ord  Cache  Value
---------- ---------------- --------- --------- ---- --- --- ------ ------
WKSYS      WK$ATTR_SEQ              1 1.000E+27    1 NO  NO      20      1
           WK$CHARSET_SEQ           1 1.000E+27    1 NO  NO      20     58
           WK$CRAWLERID_SEQ         1 1.000E+27    1 NO  NO      20   1000
           WK$DS_ID_SEQ             1 1.000E+27    1 NO  NO      20      1
           WK$INST_SEQ              1 1.000E+27    1 NO  NO      20      1
           WK$JOB_ID_SEQ            1 1.000E+27    1 NO  NO      20      1
           WK$MAILLIST$SEQ          1 1.000E+27    1 NO  NO      20      1
           WK$MIMETYPES_SEQ         1 1.000E+27    1 NO  NO      20     36
           WK$SCHED_ID_SEQ          1 1.000E+27    1 NO  NO      20      1
           WK$SG_ID_SEQ             1 1.000E+27    1 NO  NO      20      1
           WK$TRACE_SEQ             1 1.000E+27    1 NO  NO      20     11

Monitoring Synonyms Using the V$ and DBA_ Views

Synonyms remain the same in Oracle8, Oracle8i, and Oracle9i. The major changes that have affected synonyms are those to the structure of the connection strings from SQL*NET V1 to V2 to NET8. Despite the fact that synonyms are the key to providing cross-database access for queries, and a means of implementing distributed data across nodes, systems, and databases, in all the reports reviewed for this book, not one seemed to cover synonyms. Recall that a synonym allows a shorthand version of an object name to be specified. The parts of a synonym are the object name (which usually includes an owner) and, possibly, a database link that will also provide an Oracle user name and password to a remote system. A complete report will show all of these items.

Why is it important to monitor synonyms? Synonyms can be used to access data, sometimes data that shouldn’t be accessed if object grants have been too widely granted. In addition, they are the means for reaching other nodes and databases. If a connect string becomes invalid, a user name is disconnected or its password changes or node name changes, it is good to be able to see which object synonyms will be affected. Source 11.20 shows a script for a synonym report, and Listing 11.17 shows an example of output from a synonym script.

SOURCE 11.20 Script for synonym report.

REM
REM NAME        : SYNONYM.SQL
REM PURPOSE     : GENERATE REPORT OF A USERS SYNONYMS
REM USE         : FROM SQLPLUS
REM Limitations : None
REM Revisions:
REM Date                 Modified by Reason for change
REM 12/MAY/93     Mike Ault     Initial Creation
REM 15/Jun/97     Mike Ault     Verified for Oracle8
REM 16/May/99     Mike Ault     Verified for Oracle8i
REM
PROMPT Percent signs are Wild Cards
PROMPT
ACCEPT own PROMPT 'Enter the user who owns synonym: '
SET PAGES 56 LINES 130 VERIFY OFF FEEDBACK OFF TERM OFF
START title132 "Synonym Report"
SPOOL rep_out/&&db/synonym
COLUMN host          FORMAT a24 HEADING "Connect String"
COLUMN owner         FORMAT a15
COLUMN table         FORMAT a35
COLUMN db_link       FORMAT a6  HEADING Link
COLUMN username      FORMAT a15
SELECT
     a.owner, synonym_name ,
     table_owner ||'.'|| table_name "Table" ,
     b.db_link,username,host
FROM
     DBA_synonyms a,
     DBA_db_links b
WHERE
     a.db_link = b.db_link(+) AND
     a.owner LIKE UPPER('&own');
SPOOL OFF
SET PAGES 22 LINES 80 VERIFY ON FEEDBACK ON TERM ON
CLEAR COLUMNS
CLEAR BREAKS
TTITLE OFF

LISTING 11.17 Example of output from synonym script.

Date: 10/14/01                                                                         Page:   1
Time: 01:57 PM                        Synonym Report                      DBAUTIL
                                    galinux1 database

                                     Table or
Owner      Synonym                   Object Name                      Link   Username Host
---------- ------------------------- -------------------------------- ------ ---------- SYSTEM     CATALOG                   SYS.CATALOG
SYSTEM     COL                       SYS.COL
SYSTEM     PRODUCT_USER_PROFILE      SYSTEM.SQLPLUS_PRODUCT_PROFILE
SYSTEM     PUBLICSYN                 SYS.PUBLICSYN
SYSTEM     SYSCATALOG                SYS.SYSCATALOG
SYSTEM     SYSFILES                  SYS.SYSFILES
SYSTEM     TAB                       SYS.TAB
SYSTEM     TABQUOTAS                 SYS.TABQUOTAS


See Code Depot for Full Scripts


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

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

   
 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter