#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    1
    Rep Power
    0

    Sequence Number issue in 11g RAC


    Hi All,

    In our production, we have two nodes in the cluster. We use the sequence for one of the main table for primary key. Our application is expecting sequence number increments along with created date time stamp. Right now sequences are cached for each node and it creates problem for the application. We would not like to use NOCACHE option because it causes performance issue.

    This is the current scenario -

    Transaction #1 on Node 1 - Seq ID 1 - Time Stamp 12:01
    Transaction #2 on Node 2 - Seq ID 51 - Time Stamp 12:02
    Transaction #3 on Node 1 - Seq ID 2 - Time Stamp 12:03

    When I try to query based on the time stamp, primary should also go up. How do I achieve that?

    To be very clear on what I would like to have, please consider the following example.

    Without using NOCACHE option, I need to have the data in the following order.

    Transaction #1 on Node 1 - Seq ID 1 - Time Stamp 12:01
    Transaction #2 on Node 2 - Seq ID 2 - Time Stamp 12:02
    Transaction #3 on Node 1 - Seq ID 3 - Time Stamp 12:03

    In other words, sequence number should always increment along with the time.
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    spaceBAR Central
    Posts
    229
    Rep Power
    42
    Check out this info:https://forums.oracle.com/forums/thread.jspa?threadID=622674
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    Originally Posted by spacebar208
    Check out this info:https://forums.oracle.com/forums/thread.jspa?threadID=622674
    In other words. You can't do what you would like.
    You will have to find an alternate solution.

    Of course, I know nothing of your application; but ordering by
    timestamp alone will give you the correct sort.

IMN logo majestic logo threadwatch logo seochat tools logo