Skip to Main Content
  • Questions

  • Using identity columns in Oracle 12c

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Geraldo.

Asked: July 06, 2018 - 6:39 pm UTC

Last updated: June 19, 2020 - 5:43 am UTC

Version: 12.2.0.1.0

Viewed 10K+ times! This question is

You Asked

What is the difference between using sequence.netxval as DEFAULT value in a column or check the column as identity? Please, check the following scenarios:

SCENARIO 1:

CREATE TABLE USER1.TEST_TABLE
(
ID NUMBER GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 MAXVALUE 9999999999999999999999999999 MINVALUE 1 NOCYCLE CACHE 20 NOORDER NOKEEP) NOT NULL,
DESCRIPTION VARCHAR2(50 BYTE)
)

Here, Oracle RDBMS creates a sequence with a random name that can not be changed.

SCENARIO 2:

CREATE TABLE USER1.TEST_TABLE
(
ID NUMBER DEFAULT "USER1"."TEST_SEQ"."NEXTVAL",
DESCRIPTION VARCHAR2(50 BYTE)
)

Here, I manually created a sequence called "TEST_SEQ" and set the DEFAULT value for ID column to "USER1"."TEST_SEQ"."NEXTVAL".

When I query select * from all_tab_columns where owner='USER1' and identity_column='YES' and do not see my ID column with scenario 2.

I do not know if there will be a problem (when backing up and restoring the data, or something else).

Regards,
Geraldo.


and Connor said...

They are both fine to use...In the first case, the sequence is *locked* to the table - it can only be used for the table and for identity population. We did this really to provide an easy migration path from other databases that have this concept.

The second example is you have 2 distinct objects - the table and the sequence. You could drop the table, and the sequence will stay, and vice-versa. You could use that sequence for anything (other tables etc). It is a standalone object.

You can use either approach dependent on what suits you best. If you export/import the schema, all objects will come back. If you exported (say) *just* the tables, then on import (and you'd expect) the sequence in the second example would not come back because you did not export it in the first place.


Rating

  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Table (having sequence.nextval) is not available after import

Rajeshwaran, Jeyabal, July 09, 2018 - 7:01 am UTC

Team,

Was doing this and found that after import those table(s) having default sequence.nextval is not available for import.

did i missing something here?

demo@ORA12C> create sequence s;

Sequence created.

demo@ORA12C> create table t1(x int,y int,z int generated by default as identity);

Table created.

demo@ORA12C> create table t2(x int,y int,z int default s.nextval);

Table created.

demo@ORA12C> insert into t1(x,y) values(1,1);

1 row created.

demo@ORA12C> insert into t2(x,y) values(1,1);

1 row created.

demo@ORA12C> select * from t1;

         X          Y          Z
---------- ---------- ----------
         1          1          1

demo@ORA12C> select * from t2;

         X          Y          Z
---------- ---------- ----------
         1          1          1

demo@ORA12C> host
Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\179818>expdp demo/demo@ora12c directory=TMP tables=T1,T2 dumpfile =demo_exp.dmp log=demo_exp.log

Export: Release 12.2.0.1.0 - Production on Mon Jul 9 12:09:08 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "log=demo_exp.log" Location: Command Line, Replaced with: "logfile=demo_exp.log"
Legacy Mode has set reuse_dumpfiles=true parameter.
Starting "DEMO"."SYS_EXPORT_TABLE_01":  demo/********@ora12c directory=TMP tables=T1,T2 dumpfile=demo_exp.dmp logfile=demo_exp.log reuse_dumpfiles=true
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/IDENTITY_COLUMN
. . exported "DEMO"."T1"                                     0 KB       0 rows
. . exported "DEMO"."T2"                                     0 KB       0 rows
Master table "DEMO"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DEMO.SYS_EXPORT_TABLE_01 is:
  D:\TRASH\DEMO_EXP.DMP
Job "DEMO"."SYS_EXPORT_TABLE_01" successfully completed at Mon Jul 9 12:10:37 2018 elapsed 0 00:01:13

C:\Users\179818>impdp rajesh/oracle@ora12c directory=TMP dumpfile=demo_exp.dmp logfile=demo2_imp.log remap_schema=DEMO:DEMO2 table_exists_action=REPLACE

Import: Release 12.2.0.1.0 - Production on Mon Jul 9 12:13:39 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "RAJESH"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "RAJESH"."SYS_IMPORT_FULL_01":  rajesh/********@ora12c directory=TMP dumpfile=demo_exp.dmp logfile=demo2_imp.log remap_schema=DEMO:DEMO2 table_exists_action=REPLACE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "DEMO2"."T1"                                    0 KB       0 rows
. . imported "DEMO2"."T2"                                    0 KB       0 rows
Processing object type TABLE_EXPORT/TABLE/IDENTITY_COLUMN
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "RAJESH"."SYS_IMPORT_FULL_01" successfully completed at Mon Jul 9 12:14:10 2018 elapsed 0 00:00:29


C:\Users\179818>exit

demo@ORA12C> conn demo2/demo2@ora12c
Connected.
demo2@ORA12C>
demo2@ORA12C> select * from t1;

no rows selected

demo2@ORA12C> select * from t2;

no rows selected

demo2@ORA12C> insert into t1(x,y) values(1,1);

1 row created.

demo2@ORA12C> insert into t2(x,y) values(1,1);
insert into t2(x,y) values(1,1)
       *
ERROR at line 1:
ORA-00942: table or view does not exist


demo2@ORA12C> desc t2
 Name                 Null?    Type
 -------------------- -------- ---------------
 X                             NUMBER(38)
 Y                             NUMBER(38)
 Z                             NUMBER(38)
 
demo2@ORA12C> select dbms_metadata.get_ddl('TABLE','T2') from dual;

DBMS_METADATA.GET_DDL('TABLE','T2')
--------------------------------------------------------------------------------

  CREATE TABLE "DEMO2"."T2"
   (    "X" NUMBER(*,0),
        "Y" NUMBER(*,0),
        "Z" NUMBER(*,0) DEFAULT "DEMO"."S"."NEXTVAL"
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TS_DATA"


demo2@ORA12C> col object_name format a10
demo2@ORA12C> select object_name, object_type from user_objects where object_name ='T2';

OBJECT_NAM OBJECT_TYPE
---------- -----------------------
T2         TABLE

demo2@ORA12C>
demo2@ORA12C> create sequence s ;

Sequence created.

demo2@ORA12C> insert into t2(x,y) values(1,1);
insert into t2(x,y) values(1,1)
       *
ERROR at line 1:
ORA-00942: table or view does not exist

demo2@ORA12C> insert into t2(x,y,z) values(1,2,3);

1 row created.

demo2@ORA12C> insert into t2(x,y) values(1,2);
insert into t2(x,y) values(1,2)
       *
ERROR at line 1:
ORA-00942: table or view does not exist


demo2@ORA12C> select * from t2;

         X          Y          Z
---------- ---------- ----------
         1          2          3

demo2@ORA12C>

Didn't export the sequence

paul, July 09, 2018 - 1:00 pm UTC

Look at that DDL for the Table in DEMO2.
It references the original Sequence in DEMO which DEMO2 apparently doesn't have access to.

This is another good reason to use Identity columns, you don't need to worry about getting the related object (Sequence) exported to be able to re-map it to your new schema.

Some of that is covered here:
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9534535900346896812

To: paul from Ottawa, Canada

Rajeshwaran, Jeyabal, July 09, 2018 - 1:43 pm UTC

but did you see this in the above demo? even after creating the sequence, the insert doesn't sucess.

demo2@ORA12C> create sequence s ;

Sequence created.

demo2@ORA12C> insert into t2(x,y) values(1,1);
insert into t2(x,y) values(1,1)
       *
ERROR at line 1:
ORA-00942: table or view does not exist

@rajesh - sure you created the sequence, but not the one the DDL is expecting.

paul, July 09, 2018 - 2:17 pm UTC

As DEMO2 you created the 's' sequence, but the DDL is expecting DEMO.S.NEXTVAL.


 CREATE TABLE "DEMO2"."T2"
   (    "X" NUMBER(*,0),
        "Y" NUMBER(*,0),
        "Z" NUMBER(*,0) DEFAULT "DEMO"."S"."NEXTVAL"
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TS_DATA"



As DEMO 
GRANT SELECT on S to DEMO2;

Then what happens?

I don't have an oracle env to play with :( but I expect you need to include the sequence in the export and do a schema map for the table and the sequence because right now they don't line up.




To: paul from Ottawa, Canada

Rajeshwaran, Jeyabal, July 10, 2018 - 10:30 am UTC

Yes you are right and that worked.

was then wondering - when we did the import with remap_schema = DEMO:DEMO2 - why this sequence is not remapped with new schema name.

then got answered myself that - this sequence "S" was not part of identity column, instead it was the "default' value on the column. Thanks!

demo@ORA12C> grant select on s to demo2;

Grant succeeded.

demo@ORA12C> conn demo2/demo2@ora12c
Connected.
demo2@ORA12C>  insert into t2(x,y) values(55,42);

1 row created.

demo2@ORA12C> select * from t2;

         X          Y          Z
---------- ---------- ----------
         1          2          3
        55         42         21

demo2@ORA12C>

Connor McDonald
July 12, 2018 - 9:54 am UTC

Nice to see some community back-and-forth ... thanks for saving me some work

How to migrate this Identity column in R12.2 XDF table migration

Kranthi Juvva, September 04, 2019 - 5:46 pm UTC

We have similar approach in R12.2 Table, when we migrate this XDF file to upper instance Tale failed to create.

How to migrate identity column sequence in R12.2 concept.
Connor McDonald
September 27, 2019 - 2:28 am UTC

We need to some more background here

Alter auto generated sequence in oracle 12c

A reader, June 18, 2020 - 5:34 am UTC

create table table_name
(
col1 number GENERATED ALWAYS AS IDENTITY,
col2 varchar2(100)
)



alter table table_name ( MODIFY col1 generated always as identity
( start with 22 )
)

Error :- ORA-0175 invalid ALTER TABLE Option.

How to alter existing sequence create using "generated always as identity"
Connor McDonald
June 19, 2020 - 5:43 am UTC

That is not supported because we need to make sure we commence with a high water mark. Hence you use the following syntax

SQL> create table t
  2  (
  3  col1 number GENERATED ALWAYS AS IDENTITY,
  4  col2 varchar2(100)
  5  );

Table created.

SQL>
SQL> alter table t MODIFY col1 generated always as identity
  2  ( start with limit value )
  3  ;

Table altered.


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database