8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 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...

Anonymous said...

dbms online redefine does not work with identity columns, when this first came out a few years ago it was brilliant, now not so much,.

use default value of sequence every time

Robert Taylor said...

I believe it would be worth noting the restriction "CREATE TABLE AS SELECT will not inherit the identity property on a column." in the main article as this is counter to expected behaviour.

Tim... said...

Hi.

But CTAS doesn't inherit many features of the source table design, so I'm not sure this is a surprise. If you care about structure, you should always do CREATE TABLE, then INSERT INTO ... SELECT.

I've corrected the link to the restrictions, and summarised them, just in case...

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.