Identity Columns and Data Pump

Identity columns were introduced in Oracle 12c to automatically create sequence numbers for a column – usually for the primary key. Interesting question: What is the behavior of identity columns if the table is exported and imported using Data Pump?

Since Oracle 12c it is possible to use a sequence as the default value for a table column. This is very useful to define a surrogate key as the primary key for a table like this:

CREATE TABLE beers
(id      NUMBER DEFAULT beer_seq.NEXTVAL
,name    VARCHAR2(40)
,brewery VARCHAR2(40)
,CONSTRAINT beers_pk PRIMARY KEY(id)
);

I never understood in the past why this is not allowed and wished to have this feature since Oracle 7. In previous releases of Oracle, we either had to insert the surrogate keys directly in the application, or with a DML trigger on each table. Now, the sequence can be used directly in the table definition. But we still have to create the sequence with an additional CREATE SEQUENCE statement.

Identity Columns in Oracle 12c

An even simpler way to define a surrogate key is the usage of an identity column in Oracle 12c. Identity columns can be defined directly in the CREATE TABLE statement, without creating a sequence manually.

CREATE TABLE beers
(id      NUMBER GENERATED ALWAYS AS IDENTITY
,name    VARCHAR2(40)
,brewery VARCHAR2(40)
,CONSTRAINT beers_pk PRIMARY KEY(id)
);

In this case, a sequence is created automatically in the background, and it is used as the default for the identity column:

SELECT column_name, data_default
  FROM user_tab_columns
 WHERE table_name = 'BEERS'
ORDER BY column_id;

COLUMN_NAME DATA_DEFAULT
------------ ------------------------------
ID          "DANI"."ISEQ$$_123212".nextval
NAME
BREWERY

The name of the sequence, in this example ISEQ$$_123212, is derived from the internal object id of the table (column OBJ# in table SYS.OBJ$) and cannot be renamed to a more meaningful name:

RENAME ISEQ$$_123212 TO beer_seq;

ERROR at line 1:
ORA-32799: cannot rename a system-generated sequence

Is this a problem? For most of us it seems to be one, because we cannot associate the sequence with the corresponding table. That’s true – but who cares? Has this ever been an issue for NOT NULL constraints? Although it is possible to define a NOT NULL constraint with a specific name, this is rarely used. And since INTERVAL partitioning is available, it is more and more common that partition names have system-generated names. In a few years, nobody cares about the name of a sequence anymore.

But there is a potential issue with system-generated sequences: What happens if a table with an identity column is exported and imported to another database or schema? The imported table will have a different object id. How does Oracle handle this to make sure that the correct sequence is used for the imported table? An attendee of an Oracle 12c new features training asked me this question, and because I never thought about this before, I tested it with Data Pump.

• The short answer is: It works
• The long answer: See the following use case

Exporting and Importing Identity Columns

I created the demo table above in a schema DANI and inserted some rows (because I’m currently in ski holidays, I use some local craft beers of the ski region for this example).

INSERT INTO beers (name, brewery) VALUES ('Kirchlibräu', 'Arosabräu');
INSERT INTO beers (name, brewery) VALUES ('1800m IPA', 'Arosabräu');
INSERT INTO beers (name, brewery) VALUES ('Schanfigger Häx', 'Arosabräu');
COMMIT;

SELECT * FROM beers;

ID NAME           BREWERY
-- -------------------- ----------------------------------------
 1 Kirchlibräu    Arosabräu
 2 1800m IPA      Arosabräu
 3 Schanfigger Häx Arosabräu

Now, let’s export the table with Data Pump:

[oracle@bigdatalite Blog]$ expdp system/welcome1 dumpfile=beers.dmp tables=dani.beers

Export: Release 12.1.0.2.0 - Production on Sun Feb 14 16:38:29 2016

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** dumpfile=beers.dmp tables=dani.beers 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
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
. . exported "DANI"."BEERS" 5.992 KB 3 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
 /u01/app/oracle/admin/orcl/dpdump/beers.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Sun Feb 14 16:38:45 2016 elapsed 0 00:00:15

The next step is to import the table into a different schema, in this case to schema SCOTT:

[oracle@bigdatalite Blog]$ impdp system/welcome1 dumpfile=beers.dmp remap_schema=dani:scott

Import: Release 12.1.0.2.0 - Production on Sun Feb 14 16:40:07 2016

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** dumpfile=beers.dmp remap_schema=dani:scott 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."BEERS" 5.992 KB 3 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 "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Sun Feb 14 16:40:24 2016 elapsed 0 00:00:16

A copy of the table is now available in schema SCOTT. The table and its content were imported, but not the system-generated sequence. A new sequences is created during the import and assigned to the default value of the identity column. As you can see in the following query, the sequence has a different name as the one on the original table.

SELECT column_name, data_default
  FROM user_tab_columns
 WHERE table_name = 'BEERS'
ORDER BY column_id;

COLUMN_NAME DATA_DEFAULT
------------ ------------------------------
ID          “SCOTT"."ISEQ$$_123217".nextval
NAME
BREWERY

Finally, I insert some more rows into the new table in schema SCOTT (this time, the beers of my own microbrewery). A query on the table shows that the new sequence starts with the next available number and can therefore be used as the primary key generator for the new table.

INSERT INTO beers (name, brewery) VALUES ('Glatthopfen', 'Monsterbräu');
INSERT INTO beers (name, brewery) VALUES ('Weisses Einhorn', 'Monsterbräu');
INSERT INTO beers (name, brewery) VALUES ('Schwarze Kobra', 'Monsterbräu');
INSERT INTO beers (name, brewery) VALUES ('Lazariter', 'Monsterbräu');
COMMIT;

SELECT * FROM beers;

ID NAME            BREWERY
-- -------------------- ----------------------------------------
 1 Kirchlibräu     Arosabräu
 2 1800m IPA       Arosabräu
 3 Schanfigger Häx  Arosabräu
 4 Glatthopfen     Monsterbräu
 5 Weisses Einhorn Monsterbräu
 6 Schwarze Kobra  Monsterbräu
 7 Lazariter       Monsterbräu

Remark for old exp/imp Utility

With the old-fashioned exp/imp utility (aka “Original Export/Import”), this would not work. For our test case, the table in schema SCOTT would use the original sequence in schema DANI. If the table is imported into another database, an ORA-02289 (sequence does not exist) occurs. Because exp and imp are not supported anymore since Oracle 11g, these utilities do not support new features such as identity columns.

Conclusion

With identity columns, we don’t have to care about creating sequences anymore. An system-generated sequence is created in the background and assigned as default value for the identity column. Although the sequence name is derived from the internal object id of the table, identity columns can be exported and imported with Data Pump without problems.