oracle sql and dba tutorial logo

Oracle DBA

Learn Oracle 11g / 12c Database Admin step by step



Oracle SQL

Tutorial for Oracle SQL

 

Oracle DBA Interview Questions

Most asked Oracle DBA Interview Questions.

60 Technical Questions
42 Backup & Recovery Questions

Unix For Oracle DBA 20 Questions


Download Oracle 11g / 12 c Software

Links to Download Oracle 12c / 11g for Linux, Windows etc.



Using Sequences in Oracle (Auto Increment Feature)

SEQUENCES


A sequence is used to generate numbers in sequence. You can use sequences to insert unique values in Primary Key and Unique Key columns of tables. To create a sequence give the CREATE SEQUENCE statement.

CREATING SEQUENCES

create sequence bills
     start with 1
     increment by 1
     minvalue 1
     maxvalue 100
     cycle
     cache 10';

 

The above statement creates a sequence bills it will start with 1 and increment by 1. It’s maxvalue is 100 i.e. after 100 numbers are generated it will stop if you say NOCYCLE, otherwise if you mention CYCLE then again it will start with no. 1. You can also specify NOMAXVALUE in that case the sequence will generate infinite numbers.

The CACHE option is used to cache sequence numbers in System Global Area (SGA). If you say CACHE 10 then Oracle will cache next 10 numbers in SGA. If you access a sequence number then oracle will first try to get the number from cache, if it is not found then it reads the next number from disk. Since reading the disk is time consuming rather than reading from SGA it is always recommended to cache sequence numbers in SGA. If you say NOCACHE then Oracle will not cache any numbers in SGA and every time you access the sequence number it reads the number from disk.

 

Accessing Sequence Numbers.

To generate Sequence Numbers you can use NEXTVAL and CURRVAL for example to get the next sequence number of bills sequence type the following command.

Select bills.nextval from dual;

BILLS
-----
1

NEXTVAL gives the next number in sequence. Whereas,  CURRVAL returns the current number of the sequence. This is very handy in situations where you have insert records in Master Detail tables. For example to insert a record in SALES master table and SALES_DETAILS detail table.

insert into sales (billno,custname,amt)
   values (bills.nextval,’Sami’,2300);

insert into sales_details (billno,itemname,qty,rate) values
     (bills.currval,’Onida’,10,13400);

Sequences are usually used as DEFAULT Values for table columns to automatically insert unique numbers.
For Example,

create table invoices (invoice_no number(10) default bills.nextval,
       invoice_date date default sysdate,
       customer varchar2(100),
       invoice_amt number(12,2));

Now whenever you insert rows into invoices table ommiting invoice_no as follows

insert into invoices (customer,invoice_amt) values    ('A to Z Traders',5000);

Oracle will insert invoice_no from  bills sequence

 

ALTERING SEQUENCES

To alter sequences use ALTER SEQUENCE statement. For example to alter the bill sequence MAXVALUE give the following command.

ALTER SEQUENCE BILLS
     MAXVALUE 200;

Except Starting Value, you can alter any other parameter of a sequence. To change START WITH parameter you have to drop and recreate the sequence.

 

DROPPING SEQUENCES

To drop sequences use DROP SEQUENCE command. For example to drop bills sequence give the following statement

drop sequence bills;

Listing Information About Sequences

To see how many sequences are there in your schema and what are there settings give the following command.

select * from user_sequences;

 

 


HomeContact Us

Data Loader

Data Loader is a simple yet powerful tool to
export and import Data between many common database formats


Forms Data Loader

Tool to load data into Oracle E-Business Suite R12 / Oracle Apps using Macros and Forms Record and Playback

Interface Computers Academy © 2007-2017 All Rights Reserved