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