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
Commenti recenti