Creating Sequences in Oracle 9i

Posted on 2005-04-11
Last Modified: 2012-06-27
What is the simplest way to create a sequence in Oracle --that is the SQL statement?

I have a trigger that generates an id from from dua0 but I need to create a sequence to go along with it.
Question by:lcor
    LVL 52

    Accepted Solution

    The basic syntax is something like the following:

        CREATE SEQUENCE YourSequence
            INCREMENT 1
            MINVALUE   1
            MAXVALUE  50000
            START 1

    You can find a little more detail and examples here:

    Hope this helps.
    LVL 5

    Expert Comment


    CREATE SEQUENCE Emp_sequence
        INCREMENT BY 1
        START WITH 1
        CACHE 10;

    Use the SQL command ALTER SEQUENCE to alter a sequence. For example:

    ALTER SEQUENCE Emp_sequence
        MAXVALUE 10000
        CACHE 20;

    Once a sequence is defined, it can be accessed and incremented by multiple users with no waiting. Oracle does not wait for a transaction that has incremented a sequence to complete before that sequence can be incremented again.

    u can use this syntax to insert into table

    INSERT INTO Orders_tab (Orderno, Custno)
        VALUES (Order_seq.NEXTVAL, 1032);

    The sequence number can also be referenced outermost SELECT of a query or subquery. For example:

    SELECT Order_seq.NEXTVAL FROM dual;


    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
    How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now