 |
|
Dropping
a Sequence
Oracle Tips by Burleson
|
Sequences are dropped using the DROP SEQUENCE
command. The format of this command follows:
DROP
SEQUENCE [schema.]sequence_name;
If triggers and procedures reference the
sequence, these triggers and procedures will fail if the sequence is
dropped. It may be advisable to add an exception that will perform the
trigger creation of sequences in this situation.
Use of Sequences
Sequences are used by selecting, using the
sequence name and the parameters CURRVAL AND NEXTVAL. For example:
INSERT INTO
purchase_orders (po_num, po_date, originator)
VALUES (po_seq.NEXTVAL,
SYSDATE, USER) FROM dual;
Here, po_seq is the name of a sequence, and
INSERT would update the po_num, po_date, and originator fields of the
purchase_orders table with the next sequence value from po_seq, the
current system date, and the current user name.
CURRVAL will retrieve the same value into
multiple fields in the same session. CURRVAL cannot be used unless the
NEXTVAL for the sequence has already been referenced in the current
session. The following lists itemize the uses and restrictions of
NEXTVAL and CURRVAL.
Uses:
* Used with the VALUES clause on an INSERT
command.
* Used with the SELECT subclause of a SELECT
command.
* Used in the SET clause of an UPDATE command.
Restrictions:
* Neither can be used in a subquery.
* Neither can be used in a view or snapshot
query.
* Neither can be used with a DISTINCT clause.
* Neither can be used with GROUP BY or ORDER
BY.
* Neither can be used in a SELECT command in
combination with another SELECT using UNION, MINUS, or INTERSECT.
* Neither can be used in the WHERE clause.
* Neither can be used in the DEFAULT column
value in a CREATE TABLE or ALTER TABLE command.
* Neither can be used in a CHECK in a
constraint.
Sequences and Import, Export, and SQLLOADER
Sequences are ignorant objects, in that they
know nothing about the tables or clusters they service; furthermore,
they don’t care that they don’t know. This puts the onus on the DBA to
ensure that sequence values and table values are synchronized after
the use of imports, manual data insertion, or use of SQLLOADER to load
values into tables whose key values depend on sequences.
IMPORT Scenario
For this scenario, assume that a database is
being used for testing. The test plan calls for the database to be
periodically purged of data by using the combined deletion and
truncation of key data tables. After the first test cycle, it is
decided that a larger data set is required. A larger, second test
database is exported, and its data values are imported with the
IGNORE=Y option. What will happen? The following:
* The sequences will be out of sync with the
tables.
* The sequences will have a lesser value than
the key fields in the database.
* Any attempted insert will most likely result
in a duplicate key error being returned.
What can you do to correct it? Actually, the
solution is quite simple:
1. Determine the affected tables by doing
simple
SELECT COUNT(primary key field) FROM
table;
type selects and comparing them to known data
counts before the drop occurred, or by comparing to a
SELECT last_number FROM dba_sequences
WHERE
sequence_name = 'affected_sequence'; (Note: name must be uppercase)
2. Determine the difference between the
affected sequences and the row count of the tables they support.
3. Adjust the sequence parameter
INCREMENT_BY to this difference plus one:
ALTER SEQUENCE affected_sequence INCREMENT BY difference+1;
4. Select the NEXTVAL from the sequence:
SELECT
affected_sequence.NEXTVAL FROM dual;
5. Reset the INCREMENT_BY value to 1 (or
whatever it is supposed to be):
ALTER SEQUENCE affected_sequence INCREMENT BY 1;
For example, let’s say that table clients in
phase 1 of testing has 1.2 million rows. The table is truncated and
reloaded with 2.5 million rows. The clients_seq sequence shows its
LAST_VALUE to be 1,200,000, and any attempted inserts generate the
duplicate primary key error. To fix this:
SQL>ALTER
SEQUENCE clients_seq INCREMENT BY 1300001;
SQL>SELECT clients_seq.NEXTVAL FROM dual;
clients_seq.NEXTVAL
25000001
one row returned.
SQL>ALTER SEQUENCE clients_seq INCREMENT BY 1;
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. |
|
