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.

Advertisements

3 thoughts on “Identity Columns and Data Pump

  1. Even using new expdp/impdp, if the table was using user created sequence, we still get ORA_02289 error because it’s still trying to use the original sequence in a schema that doesn’t exist in a new database.
    Do you have any ideas on how to fix that?

    Like

    • A user created sequence is not connected to the table that uses it. If you do an export/import with Data Pump, you have to export and import the sequences, too. This can be defined either with the INCLUDE command or with a full schema export/import.

      Liked by 1 person

      • No, full export/import does not work either. The problem is that remap does not work for the sequence that is used for default value of a column. During the import the reference in the table remains as “DANI”.”beer_seq”.NEXTVAL, even if we tried to remap to schema SCOTT.

        Identity columns has also problems:
        1. If the identity field is not the first one, the import is not correct, sequence starts from 1, for example.
        2. Existing column cannot be modified to identity.
        3. Only one identity column can be used in a table.

        Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s