Sequences

What is Sequence?

A sequence is a sequential list of numbers that is generated by the Oracle server. Oracle provides a sequence number generator that is used for automatically generating unique primary keys for your data.

The Oracle Server generates unique sequence numbers that can be used as primary keys This is beneficial

Without the Oracle sequences, sequential numbers would have to be generated in your application. primary key can be created by selecting the most recently produced sequential value. Additionally, creating sequential primary keys programmatically causes a lock to be placed on the table that contains the sequential numbers. This method requires other database users to wait their turn to obtain the next value of the primary key. Oracle sequences elirminate this locking and improve the concurrence of your application.

Sequences are created to simplify programming tasks by creating a sequential list of numbers that I programmers can draw upon for generating primary key values. Sequence number are Oracle integers of up to 38 digits.

Used to generate unique integers.

CREATING SEQUENCE[SCHEMAJ SEQUENCE NAME
[INCREMENT BY INTEGER]
[START WITH INTEGER]
[MAXVALUE INTEGER/NOMAXVALUE]
[MINVALE INTEGER/NOMINVALUE1
[CYCLE/NOCYCLE]
[CACHE INTEGER/NOCACHEI
[ORDER/NOOR
DER]

CREATE SEQUENCE S EMPNO
INCREMENT BY 1
START WITH I
NOMAXVALUE
NOCYCLE
CACHE 15

The preceding example creates a sequence named s empno. The sequential list of numbers start with the value I and is incremented by 1 for each successive number. There is no maximum value for this sequence, so the sequence will not cycle over and begin from its initial. Finally, the Oracle server pre generates up to 15 sequence values in memory for faster performance.

You can alter any of the parameters specified in the create sequence command by issuing the alter sequence command:

Used to change the increment between future generated values.

Setting or eliminating the min/max values

change the number of cached sequence

Format

specify whether or not the sequence no. be ordered.

ALTER SEQUENCE[SCHEMA.1 SEQ_NAME
[INCREMENT BY INTEGER]
(START WITH INTEGER]
[MAXVALUE INTEGER/NOMAXVALUE]
[MINVALE INTEGER/NCIMINVALUE]
[CYCLE/NOCYCLE]
[CACHE INTEGER/NOCACHEI
[ORDER/NOORDER]

Example:

ALTER SEQUENCE S EMPNO MAXVALUE 50000 CACHE 20 CYCLE;
This example changes the cache value to 20 and now allows the sequence generator to start over when the maxvalue is reached.

To drop a sequence, use the DROP SEQUENCE SQL command. For example, the following line drops the sequence s _empno:

DROP SEQUENCE s empno

When you INSERT, UPDATE or DELETE data from the database, the changes are not normally committed (made permanent) until You exit from SQL*Plus, or until you execute an ALTER, AUDIT, CREATE, DISCONNECT, DROP, GRANT, NOAUDIT or REVOKE command.

You can reverse or rollback the work you have done, This can be very helpful if an error is discovered in your work.

The process of committing or rolling back work is controlled by two SQL*Plus commands COMMIT and ROLLBACK. Additionally, SQL*Plus has the facility to automatically commit your work without you explicitly telling it to do so, this is controlled by the AUTOCOMMIT feature of SET. Like other SET features, you can SHOW it, like this:

SQL> SHOW AUTOCOMMIT

autocommit OFF:

OFF is the default setting of AUTOCOMMIT. This means that INSEXTs, UPDATEs and DELETEs are not made final until you commit them.

The different forms of AUTOCOMMIT that can be set are as:

SET AUTOCOMMIT IMMEDIATE

SET AUTOCOMMIT ON

SET AUTOCOMMIT OFF

turns autocommit on

turns autocommit on

turns autocommit off (the default)

Your work can be committed explicitly as:

SQL> COMMIT;

commit complete.

Until you COMMIT, only you can see how your work affects the tables. Anyone else with access 0 your tables will continue to get the old, information. You will see new information, whenever you SELECT from the table.

Your work is, in effect, in a "staging" area, which you interact with until you COMMIT. You can do quite a large number of INSERTS, UPDATES and DELETES, and still undo the work (return the tables the way used to be) by issuing the following command ;

SQL> ROLLBACK

rollback complete

The message "rollback complete" can be misleading. It means that it has rolled back the work that has not been committed. If you commit a series of transactions, either explicitly with the word COMMIT, or implicitly by another action, the "rollback complete" message will not mean anything.

Implicitly commit can take place through the following actions (These actions force a commit to occur, even without your instructing it to): QUIT, EXIT (which is equivalent to QUIT), CREATE TABLE or CREATE VIEW, DROP TABLE / VIEW, GRANT to REVOKE, CONNECT or DISCONNECT, ALTER, AUDIT, and NOAUDIT. Using any of these commands is Just like using COMMIT.

A transaction (or a logical unit of work) is a sequence SQL statements that ORACLE treats as a single unit. A transaction begins with the first executable SQL statement after a COMMIT, ROLLBACK or connection or ORACLE.

A transaction end s with a COMMIT statement, a ROLLBACK statement, or disconnection from ORACLE.

To rollback portion of the current transaction SAVEPOINTS can be used in conjunction with the ROLLBACK command.

Savepoints are useful in interactive programs, because you can create and name intermediate steps of a program. This allows you more control over longer, more complex programs.
For example, you can use savepoints throughout a long complex series of updates, so that if you make an error, you need not resubmit every statement. Savepoints are useful in application programs in a similar way.

Save point names must be distinct within a given transaction. If you create a second savepoint with the same identifier as an earlier savepoint, the earlier savepoint is erased.

After a savepoint has been created, you can either continue processing, commit your work, rollback the entire transaction or rollback to the savepoint.

Domain Name Search