Read the first part of this blog post here

Modifying the Identity parameters

We can modify the Table structure and its related Sequence parameters. In this case Oracle® modifies the Sequence definition but it obviously maintains the same name, because the Sequence is bound to the Table’s Object Id.

Now we’ll see that if we change the START WITH parameter, Oracle® recreates (resets) the Sequence (we can’t change the START WITH parameter of a traditional Sequence, as a matter of fact).
But if we change some other parameters, Oracle® doesn’t recreate the Sequence and keeps its current values.
After every change, we will see the output from the DBMS_METADATA.GET_DDL function and the USER_SEQUENCES view.
We can use the Table T from the previous example. So, first of all, we have to find the system-generated Sequence name:

CONRAD@orcl> SELECT COLUMN_NAME, DATA_DEFAULT
  2  FROM USER_TAB_COLUMNS
  3  WHERE TABLE_NAME = 'T' AND IDENTITY_COLUMN = 'YES';

COLUMN_NAME   DATA_DEFAULT
-----------   -------- ----------------------
ID            "CONRAD"."ISEQ$$_95196".nextval

CONRAD@orcl>

The last generated value, in the previous example, was 510. So, the next available value in the cache is 520. In case we lose all the cached values, we will get 700:

CONRAD@orcl> SELECT ISEQ$$_95196.NEXTVAL FROM DUAL;

   NEXTVAL
----------
       520

CONRAD@orcl>

Let’s see the current Sequence settings (remember, from the previous example, that we have specified our custom, non-default, increment and starting values):

MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 10
START WITH 500 CACHE 20 NOORDER  NOCYCLE  NOT NULL ENABLE

SEQUENCE_NAME   MIN_VALUE                      MAX_VALUE   INCREMENT_BY   CYCLE_FLAG
-------------   ---------   ----------------------------   ------------   ----------
ISEQ$$_95196            1   9999999999999999999999999999             10   N

ORDER_FLAG   CACHE_SIZE   LAST_NUMBER   PARTITION_COUNT   SESSION_FLAG   KEEP_VALUE
----------   ----------   -----------   ---------------   ------------   ----------
N                    20           700                     N              N

Now, for example, let’s modify the increment (INCREMENT BY) and caching (CACHE) parameters:

CONRAD@orcl> ALTER TABLE T
  2  MODIFY (ID NUMBER      GENERATED ALWAYS AS IDENTITY
  3                         INCREMENT BY 50
  4                         NOCACHE);

Table altered.

CONRAD@orcl>

MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 50
START WITH 500 NOCACHE  NOORDER  NOCYCLE  NOT NULL ENABLE

SEQUENCE_NAME   MIN_VALUE                      MAX_VALUE   INCREMENT_BY   CYCLE_FLAG
-------------   ---------   ----------------------------   ------------   ----------
ISEQ$$_95196            1   9999999999999999999999999999             50   N

ORDER_FLAG   CACHE_SIZE   LAST_NUMBER   PARTITION_COUNT   SESSION_FLAG   KEEP_VALUE
----------   ----------   -----------   ---------------   ------------   ----------
N                     0           570                     N              N

The last generated value was 520. But now we don’t have cached values, so the next value to be generated – with the new increment of 50 – is 570, as we can see:

CONRAD@orcl> SELECT ISEQ$$_95196.NEXTVAL FROM DUAL;

   NEXTVAL
----------
       570

CONRAD@orcl>

So Oracle® has not reset the Sequence, but it has used the new INCREMENT value to calculate the next to be generated.

MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 50
START WITH 500 NOCACHE  NOORDER  NOCYCLE  NOT NULL ENABLE

SEQUENCE_NAME   MIN_VALUE                      MAX_VALUE   INCREMENT_BY   CYCLE_FLAG
-------------   ---------   ----------------------------   ------------   ----------
ISEQ$$_95196            1   9999999999999999999999999999             50   N

ORDER_FLAG   CACHE_SIZE   LAST_NUMBER   PARTITION_COUNT   SESSION_FLAG   KEEP_VALUE
----------   ----------   -----------   ---------------   ------------   ----------
N                     0           620                     N              N

Now, let’s modify the starting value (START WITH) only:

CONRAD@orcl> ALTER TABLE T
  2  MODIFY (ID NUMBER      GENERATED ALWAYS AS IDENTITY
  3                         START WITH 100);

Table altered.

CONRAD@orcl>

MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1
START WITH 100 CACHE 20 NOORDER  NOCYCLE  NOT NULL ENABLE

SEQUENCE_NAME   MIN_VALUE                      MAX_VALUE   INCREMENT_BY   CYCLE_FLAG
-------------   ---------   ----------------------------   ------------   ----------
ISEQ$$_95196            1   9999999999999999999999999999              1   N

ORDER_FLAG   CACHE_SIZE   LAST_NUMBER   PARTITION_COUNT   SESSION_FLAG   KEEP_VALUE
----------   ----------   -----------   ---------------   ------------   ----------
N                    20           100                     N              N

As you can see, Oracle® has reset the current parameters (INCREMENT BY and CACHE) to the default ones. And then, with a new starting value of 100, the next value to be generated has to be 100.

CONRAD@orcl> SELECT ISEQ$$_95196.NEXTVAL FROM DUAL;

   NEXTVAL
----------
       100

CONRAD@orcl>

After the first number generation (100), thanks to the caching of the next 20 values, the next to be generated (in the case of loss of all the cached ones) is 120.

MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1
START WITH 100 CACHE 20 NOORDER  NOCYCLE  NOT NULL ENABLE

SEQUENCE_NAME   MIN_VALUE                      MAX_VALUE   INCREMENT_BY   CYCLE_FLAG
-------------   ---------   ----------------------------   ------------   ----------
ISEQ$$_95196            1   9999999999999999999999999999              1   N

ORDER_FLAG   CACHE_SIZE   LAST_NUMBER   PARTITION_COUNT   SESSION_FLAG   KEEP_VALUE
----------   ----------   -----------   ---------------   ------------   ----------
N                    20           120                     N              N

You have seen that by changing the START WITH parameter, Oracle® recreates the Sequence from scratch. In fact, you can’t modify the starting value in a manually created Sequence, at least not with the ALTER SEQUENCE START WITH command:

CONRAD@orcl> CREATE SEQUENCE SEQ_T;

Sequence created.

CONRAD@orcl> ALTER SEQUENCE SEQ_T START WITH 4;
ALTER SEQUENCE SEQ_T START WITH 4
                     *
ERROR at line 1:
ORA-02283: cannot alter starting sequence number

CONRAD@orcl> DROP SEQUENCE SEQ_T;

Sequence dropped.

CONRAD@orcl>

Note: you can change the next value to be generated with a little work-around, without recreating the Sequence, playing with the INCREMENT BY setting (the START WITH setting remains unchanged, anyway). Keep in mind that there’s no CREATE OR REPLACE SEQUENCE command, so should you need to recreate the Sequence you have to drop it, recreate it and re-grant the needed privileges to the users.

“By Default” clause

By default (the ALWAYS clause), as previously explained, we can’t insert a value into a column created as Identity:

CONRAD@orcl> INSERT INTO T (ID, TNAME) VALUES (19, 'VALUE 3');
INSERT INTO T (ID, TNAME) VALUES (19, 'VALUE 3')
               *
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column

CONRAD@orcl>

If we need to insert such “free” values, independently of the Identity mechanism, we have to specify the BY DEFAULT clause, instead of ALWAYS.

CONRAD@orcl> ALTER TABLE T
  2  MODIFY (ID NUMBER      GENERATED BY DEFAULT AS IDENTITY);

Table altered.

CONRAD@orcl>

This command has not altered any Sequence parameter. They are the same as before the DDL command.

MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1
START WITH 100 CACHE 20 NOORDER  NOCYCLE  NOT NULL ENABLE

SEQUENCE_NAME   MIN_VALUE                      MAX_VALUE   INCREMENT_BY   CYCLE_FLAG
-------------   ---------   ----------------------------   ------------   ----------
ISEQ$$_95196            1   9999999999999999999999999999              1   N

ORDER_FLAG   CACHE_SIZE   LAST_NUMBER   PARTITION_COUNT   SESSION_FLAG   KEEP_VALUE
----------   ----------   -----------   ---------------   ------------   ----------
N                    20           120                     N              N

And you can recognize Identity columns with BY DEFAULT property set:

CONRAD@orcl> SELECT TABLE_NAME, COLUMN_NAME, GENERATION_TYPE
  2  FROM USER_TAB_IDENTITY_COLS
  3  ORDER BY TABLE_NAME;

TABLE_NAME   COLUMN_NAME   GENERATION
----------   -----------   ----------
T            ID            BY DEFAULT
T4           ID4           ALWAYS

CONRAD@orcl>

So, now, I can specify my Identity column or not:

CONRAD@orcl> INSERT INTO T (TNAME) VALUES ('VALUE 3');

1 row created.

CONRAD@orcl> INSERT INTO T (ID, TNAME) VALUES (19, 'VALUE 4');

1 row created.

CONRAD@orcl> SELECT * FROM T ORDER BY ID;

        ID   TNAME
----------   -----------------
        19   VALUE 4
       120   VALUE 3
       500   VALUE 1
       510   VALUE 2

CONRAD@orcl> COMMIT;

Commit complete.

CONRAD@orcl>

But I can’t supply a NULL for the Identity column:

CONRAD@orcl> INSERT INTO T (ID, TNAME) VALUES (NULL, 'VALUE 5');
INSERT INTO T (ID, TNAME) VALUES (NULL, 'VALUE 5')
                                  *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("CONRAD"."T"."ID")

CONRAD@orcl>

The real problem with the possibility of inserting both system generated values and user-supplied ones, is that in this way we may have conflicts, because we may insert the same value more than once: with the auto-increment Sequence and manually. Obviously, this doesn’t happen if you define the Identity column as a Primary Key, or you put a Unique constraint on it (in both cases you will have a unique constraint violation).
But you may take advantage of this capability, for example, to get back a lost value.

“By Default on NULL” clause

If we want to have the possibility of specifying a NULL for the Identity column (in the previous example we got an error), we need the BY DEFAULT ON NULL clause.
But – pay attention – this doesn’t mean that you can insert a NULL. Absolutely not! Remember that Identity columns are – by definition – NOT NULL constrained.
So, if you specify a NULL, instead of getting the ORA-01400 error, your insert will succeed; but Oracle® will generate the Sequence NEXTVAL for you, and the NULL will be ignored.
So, for example, these two commands are equivalent:

INSERT INTO T (ID, TNAME) VALUES (NULL, 'VALUE X');

INSERT INTO T (TNAME) VALUES ('VALUE X');

Let’s try this:

CONRAD@orcl> ALTER TABLE T
  2  MODIFY (ID NUMBER      GENERATED BY DEFAULT ON NULL AS IDENTITY);

Table altered.

CONRAD@orcl>

We can recognize Identity columns with BY DEFAULT ON NULL property:

CONRAD@orcl> SELECT COLUMN_ID, COLUMN_NAME, DATA_DEFAULT,
  2         IDENTITY_COLUMN, DEFAULT_ON_NULL
  3  FROM USER_TAB_COLUMNS
  4  WHERE TABLE_NAME = 'T'
  5  ORDER BY COLUMN_ID;

 COLUMN_ID   COLUMN_NAME   DATA_DEFAULT                       IDE   DEF
----------   -----------   ------------                       ---   ---
         1   ID            "CONRAD"."ISEQ$$_95196".nextval    YES   YES
         2   TNAME                                            NO    NO

CONRAD@orcl>

If we try to insert a NULL, as I previously stated, we get:

CONRAD@orcl> INSERT INTO T (ID, TNAME) VALUES (NULL, 'VALUE 5');

1 row created.

CONRAD@orcl> SELECT * FROM T ORDER BY ID;

        ID   TNAME
----------   --------------------------
        19   VALUE 4
       120   VALUE 3
       121   VALUE 5
       500   VALUE 1
       510   VALUE 2

CONRAD@orcl> COMMIT;

Commit complete.

CONRAD@orcl>

Dropping a Table with an Identity column

When we drop a Table with an Identity column, the system-generated Sequence will be dropped too, but in the only case you specify the PURGE option. Otherwise it will remain until you purge the Recycle Bin.
Let’s try with our Table T and its Sequence ISEQ$$_95196:

CONRAD@orcl> SELECT SEQUENCE_NAME
  2  FROM USER_SEQUENCES
  3  WHERE SEQUENCE_NAME = 'ISEQ$$_95196';

SEQUENCE_NAME
--------------------
ISEQ$$_95196

CONRAD@orcl> DROP TABLE T;

Table dropped.

CONRAD@orcl> SELECT SEQUENCE_NAME
  2  FROM USER_SEQUENCES
  3  WHERE SEQUENCE_NAME = 'ISEQ$$_95196';

SEQUENCE_NAME
--------------------
ISEQ$$_95196

CONRAD@orcl> PURGE RECYCLEBIN;

Recyclebin purged.

CONRAD@orcl> SELECT SEQUENCE_NAME
  2  FROM USER_SEQUENCES
  3  WHERE SEQUENCE_NAME = 'ISEQ$$_95196';

no rows selected

CONRAD@orcl>

Additional restrictions

The Datatype for an Identity column must be Numeric:

CONRAD@orcl> CREATE TABLE T
  2  (ID        VARCHAR2(11)   GENERATED ALWAYS AS IDENTITY);
(ID        VARCHAR2(11)   GENERATED ALWAYS AS IDENTITY)
                                                      *
ERROR at line 2:
ORA-30675: identity column must be a numeric type

CONRAD@orcl>

As I previously mentioned, we can’t define more than one Identity column per Table:

CONRAD@orcl> CREATE TABLE T
  2  (ID1       NUMBER         GENERATED ALWAYS AS IDENTITY,
  3   ID2       NUMBER         GENERATED ALWAYS AS IDENTITY);
 ID2       NUMBER         GENERATED ALWAYS AS IDENTITY)
                                                      *
ERROR at line 3:
ORA-30669: table can have only one identity column

CONRAD@orcl>

As any other Sequence, all the values generated are not rollbackable. If you open a Transaction, generate some values, and then issue a ROLLBACK command, those values are lost:

CONRAD@orcl> CREATE TABLE T
  2  (ID        NUMBER      GENERATED ALWAYS AS IDENTITY,
  3   TNAME     VARCHAR2(128));

Table created.

CONRAD@orcl> INSERT INTO T (TNAME) VALUES ('VALUE 1');

1 row created.

CONRAD@orcl> INSERT INTO T (TNAME) VALUES ('VALUE 2');

1 row created.

CONRAD@orcl> SELECT * FROM T ORDER BY ID;

        ID   TNAME
----------   ---------------------------
         1   VALUE 1
         2   VALUE 2

CONRAD@orcl> ROLLBACK;

Rollback complete.

CONRAD@orcl> INSERT INTO T (TNAME) VALUES ('VALUE 1');

1 row created.

CONRAD@orcl> SELECT * FROM T ORDER BY ID;

ID   TNAME
----------   ---------------------------
         3   VALUE 1

CONRAD@orcl> COMMIT;

Commit complete.

CONRAD@orcl>

If we create a Table with a CTAS (Create Table … As Select …) command from a Table containing an Identity column, the new Table will not inherit the Identity column properties, but only the NUMBER datatype and the NOT NULL constraint:

CONRAD@orcl> CREATE TABLE T_NEW
  2  AS
  3  SELECT ID, TNAME
  4  FROM T;

Table created.

CONRAD@orcl> SELECT DBMS_METADATA.GET_DDL('TABLE', 'T') FROM DUAL;

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

  CREATE TABLE "CONRAD"."T"
   (    "ID" NUMBER GENERATED ALWAYS AS IDENTITY
                    MINVALUE 1 MAXVALUE 9999999999999999999999999999
                    INCREMENT BY 1 START WITH 1 CACHE 20
                    NOORDER  NOCYCLE  NOT NULL ENABLE,
        "TNAME" VARCHAR2(128)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 4194304 NEXT 4194304 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "CONRAD_TAB"

CONRAD@orcl> SELECT DBMS_METADATA.GET_DDL('TABLE', 'T_NEW') FROM DUAL;

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

  CREATE TABLE "CONRAD"."T_NEW"
   (    "ID" NUMBER NOT NULL ENABLE,
        "TNAME" VARCHAR2(128)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 4194304 NEXT 4194304 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "CONRAD_TAB"

CONRAD@orcl> DROP TABLE T PURGE;

Table dropped.

CONRAD@orcl> DROP TABLE T_NEW PURGE;

Table dropped.

CONRAD@orcl>

We can’t modify a Table’s column from NUMBER to Identity (even if the Table is empty):

CONRAD@orcl> CREATE TABLE T
  2  (ID      NUMBER,
  3   TNAME   VARCHAR2(128));

Table created.

CONRAD@orcl> ALTER TABLE T
  2  MODIFY (ID      NUMBER      GENERATED AS IDENTITY);
MODIFY (ID      NUMBER      GENERATED AS IDENTITY)
        *
ERROR at line 2:
ORA-30673: column to be modified is not an identity column

CONRAD@orcl> DROP TABLE T PURGE;

Table dropped.

CONRAD@orcl>

Another thing to note is that a TRUNCATE TABLE command doesn’t reset the Identity column starting value. If you have a little experience with Microsoft® SQL Server®, for example, you know that after such a command you’ll get the Identity column reset too.

CONRAD@orcl> CREATE TABLE T
  2  (ID        NUMBER(1)      GENERATED AS IDENTITY,
  3   TNAME     VARCHAR2(128));

Table created.

CONRAD@orcl> INSERT INTO T (TNAME) VALUES ('VALUE 1');

1 row created.

CONRAD@orcl> SELECT * FROM T ORDER BY ID;

        ID     TNAME
----------     --------------------
         1     VALUE 1

CONRAD@orcl> TRUNCATE TABLE T;

Table truncated.

CONRAD@orcl> INSERT INTO T (TNAME) VALUES ('VALUE 1');

1 row created.

CONRAD@orcl> SELECT * FROM T ORDER BY ID;

        ID     TNAME
----------     --------------------
         2     VALUE 1

CONRAD@orcl> DROP TABLE T PURGE;

Table dropped.

CONRAD@orcl>

What about errors?

Does any error cause a loss of the next Sequence value? Obviously not… not, of course, if Oracle® detects it before the statement execution. Let’s see a couple of examples:

CONRAD@orcl> CREATE TABLE T
  2  (ID      NUMBER   GENERATED ALWAYS AS IDENTITY,
  3   TNAME   VARCHAR2(128));

Table created.

CONRAD@orcl> INSERT INTO T (TNAME) VALUES ('VALUE 1');

1 row created.

CONRAD@orcl> INSERT INTO T (ID, TNAME) VALUES (19, 'VALUE 2');
INSERT INTO T (ID, TNAME) VALUES (19, 'VALUE 2')
               *
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column

CONRAD@orcl> INSERT INTO T (ID, TNAME) VALUES (19, 'VALUE 2');
INSERT INTO T (ID, TNAME) VALUES (19, 'VALUE 2')
               *
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column

CONRAD@orcl> INSERT INTO T (TNAME) VALUES ('VALUE 2');

1 row created.

CONRAD@orcl> SELECT * FROM T ORDER BY ID;

        ID     TNAME
----------     --------------------
         1     VALUE 1
         2     VALUE 2

CONRAD@orcl>

We get two consecutive errors but we preserve the next Sequence value (2). Oracle® throws the exception before the statement execution (the Sequence value hasn’t been generated yet).

But what happens if I get an error about the datatype precision?
For example, let’s create an Identity column with precision “1” (the maximum allowed value will be “9”) and define the Sequence with a starting value of “9” so that the second INSERT statement will fail.

CONRAD@orcl> DROP TABLE T PURGE;

Table dropped.

CONRAD@orcl> CREATE TABLE T
  2  (ID        NUMBER(1)      GENERATED AS IDENTITY
  3                            START WITH 9,
  4   TNAME     VARCHAR2(128));

Table created.

CONRAD@orcl> INSERT INTO T (TNAME) VALUES ('VALUE X');

1 row created.

CONRAD@orcl> INSERT INTO T (TNAME) VALUES ('VALUE X');
INSERT INTO T (TNAME) VALUES ('VALUE X')
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

CONRAD@orcl> INSERT INTO T (TNAME) VALUES ('VALUE X');
INSERT INTO T (TNAME) VALUES ('VALUE X')
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

CONRAD@orcl> SELECT * FROM T ORDER BY ID;

        ID     TNAME
----------     --------------------
         9     VALUE X

CONRAD@orcl> SELECT COLUMN_NAME, DATA_DEFAULT
  2  FROM USER_TAB_COLUMNS
  3  WHERE TABLE_NAME = 'T' AND IDENTITY_COLUMN = 'YES';

COLUMN_NAME     DATA_DEFAULT
-----------     --------------------------------------------------------
ID              "CONRAD"."ISEQ$$_95329".nextval

CONRAD@orcl> SELECT "CONRAD"."ISEQ$$_95329".NEXTVAL FROM DUAL;

   NEXTVAL
----------
        12

CONRAD@orcl>

This time we have lost two values (10 and 11), because the Sequence values have been generated before getting the error about the precision.

CONRAD@orcl> DROP TABLE T PURGE;

Table dropped.

CONRAD@orcl>

Download the first and second parts of this blog post here