Thursday, July 12, 2018

Identity column Vs sequence default


Have discussed about Oracle 12c identity column and defaulting a sequence at column level feature in past, this blog post is about what is the difference between those two features.

In the identity column the sequence is locked to the table, it can only be used for the table for identity population, Oracle provided this feature to provide an easy migration path from other database, that have this concept.

In the sequence default to a column, we have two distinct objects – the table and sequence. We could drop a table, and the sequence will stay and vice-versa.

However during export/import – tables having identity column all objects will come back if we export just the tables. Since that sequence is closely tied up with that table. For table having sequence.nextval default to a column during the export/import of that table the sequence doesn’t come back, until we export/import them explicitly.

Here is a quick demo of it, export two tables T1 (having identity column) and T2 (having sequence.nextval defaulted) from the DEMO schema.

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\admin>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

Now import them into DEMO2 schema.

C:\Users\admin>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

Connect to the DEMO2 schema and do some dml(s) on these imported objects.

C:\Users\admin>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> insert into t2(x,y,z) values(1,2,3);

1 row created.

demo2@ORA12C> select * from t2;

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


We are able to insert data into T1 but not into T2, thought the table T2 exists in that schema. Looking at the ddl for the object T2, we could see that the sequence from DEMO schema got defaulted.


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"

So for the above insert statement on T2 to get succeed we need to grant “select” privilege on the sequence “S” from DEMO schema to DEMO2 schema.

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> 


No comments:

Post a Comment