Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

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

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

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

Oracleģ is the registered trademark of Oracle Corporation. 

Hit Counter