+2  A: 

Considering i have to create a sequence for each table in Oracle (almost), would this be the standard accepted implementation for simulating Identity or is there a better / easier way to achieve this kind of implementation in Oracle?

Yes, it is very typical in Oracle to create a sequence for each table. It is possible to use the same sequence for several tables, but you run the risk of making it a bottleneck by using a single sequence for many/all tables: see this AskTom q/a

Are there any specific GOTCHA's related to having so many sequences in Oracle?

None that I can think of.

Tony Andrews  2010-09-24 10:49:04
Thanks Tony - appreciate the inputs!!! Exactly the help that i was looking for.
InSane  2010-09-25 03:51:38
+2  A: 

100+ tables is not very many. I routinely work on databases with several hundred sequences, one for each table. The more the merrier.

It's even conceivable to have more sequences than tables - unlike identity columns in other DBMSs, sequences can be used for more than just creating surrogate key values.

An alternative is to use GUIDs - in Oracle you can call SYS_GUID to generate unique values.

A good article, followed by comments with pros and cons for both approaches: http://rwijk.blogspot.com/2009/12/sysguid.html

Jeffrey Kemp  2010-09-25 00:08:15
@Jeffrey - The advice is reassuring as it confirms that i am not doing something silly due to my inexperience with the product. Appreciate it. +1. Thanks!!
InSane  2010-09-25 03:49:20

related questions

N-Grams for sequence correction
How can I remove an item from a sequence in Clojure?
Cygwin BASH and ANSI control sequences
Avoiding stack overflow (with F# infinite sequences of sequences)
Recursively generate ordered substrings from an ordered sequence of chars?
How do I generate character sequences like hexadecimal with a different base?
Computing A Specific Generating Function Sequence
what causes an f# sequence to re-enumerate?
Can anyone provide a more pythonic way of generating the morris sequence?
F# how to abstract Console.ReadLine() as string seq
iPhone SDK: Record time of buttons in a sequence to play back
How to write the Fibonacci Sequence in Python
Why do you hate sequences on Oracle?
How are managed the sequences by JPA and Hibernate?
Comprehension for flattening a sequence of sequences?
Autoincrement in Oracle
How to use a Oracle function for the ID in Hibernate
Correct way to detect sequence parameter?
How would you implement sequences in Microsoft SQL Server?
What's the best way to write [0..100] in C#?
Spring + Hibernate: how to have a configurable PK generator?
How to update unique values in SQL using a PostgreSQL sequence?
Manually inserting data in a table(s) with primary key populated with sequence
C++ API for returning sequences in a generic way
Where can I find the time and space complexity of the built-in sequence types in Python