| |
 |
|
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. |
 |
|
