8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Misc » Here

Comments for Identity Columns in Oracle Database 12c Release 1 (12.1)


Danicela said...

GENERATED ALWAYS AS IDENTITY does not seem to work, I get a 'missing right parenthesis' error.

Tim... said...

Hi.

Are you sure you are on a 12c database? This is a 12c new feature. I only get that problem if I try to run the code on 11g. :)

Cheers

Tim...

Sayan Malakshinov said...

A couple additions, which i already wrote: http://orasql.org/2013/07/13/oracle-12c-behavior-tests-of-the-inline-functions-identities-and-defaults/
1. we can see all sequences for identities in the sys.idnseq$
2. we can see sequence usages in the plans now

Regards,
Sayan Malakshinov

Tim... said...

Sayan: Thanks for that. I will check it out and amend the article when I've played with it. :)

Cheers

Tim...

Stacy said...

Since the identity column has a sequence behind it, the unique values are not Gap free correct? I have users who demand a gap free number for the primary key in one table and the sequence does not suffice.
Thanks!

Tim... said...

Hi.

That's correct, it is not guaranteed gap free.

Cheers

Tim...

Samir said...

Actually creating the table with Identity by default doesn't restrict the duplication of the number value. or at least this is what I noticed from my few tries with this new feature.

Tim... said...

Hi.

The word "duplication" is not mentioned in the article. You prevent duplicate entries with a primary or unique key constraint. It is not the job of identity columns to police constraints. They just populate a column. I think you've misunderstood the purpose of identity columns.

Cheers

Tim...

DO NOT ask technical questions here! They will be deleted!

These comments should relate to the contents of a specific article. Constructive criticism is good. Advertising and offensive comments are bad and will be deleted!

If you post personal information (name, email address etc.) you are agreeing to them being stored and displayed. Feel free to remain anonymous.