Oracle 12C Identity Columns implementation Oracle self-growth column

In previous versions of Oracle 12C, if you wanted to achieve column growth, you would need to implement a sequence + trigger, and then 12C ORACLE introduced the Identity columns new feature, enabling the column to grow, and Mysql,sql server-like functionality.
Using syntax
ORACLE 12C IDENTITY
Generated ALWAYS as identity mode test
C:\users\ffcheng>sqlplus chf/xifenfei@pdb

Sql*plus:release 12.1.0.2.0 Production on Tuesday March 10 14:34:46 2015

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Last successful logon time: Friday December 19 2014 21:00:26 +08:00

Connect to:
Oracle Database 12c Enterprise Edition release 12.1.0.2.0-64bit Production
With the partitioning, OLAP, Advanced Analytics and real application testing opt
Ions

Xff_pdb@chf> select * from V$version;

BANNER con_id
-------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition release 12. 0
1.0.2.0-64bit Production

Pl/sql Release 12.1.0.2.0-production 0
CORE 12.1.0.2.0 Production 0
TNS for 64-bit windows:version 12.1.0.2.0-produ 0
Ction

Nlsrtl Version 12.1.0.2.0-production 0

Xff_pdb@chf> CREATE TABLE T_xifenfei (ID number generated ALWAYS as Identity,na
Me varchar2 (100));

Table has been created.

Xff_pdb@chf> select Object_name,object_type from User_objects;

object_name object_type
--------------- -----------------------
iseq$$_91982 SEQUENCE
T_xifenfei TABLE

Xff_pdb@chf> Set Long 10000
Xff_pdb@chf> Select Dbms_metadata.get_ddl (' TABLE ', ' T_xifenfei ') from DUAL;

Dbms_metadata. GET_DDL (' TABLE ', ' T_xifenfei ')
--------------------------------------------------------------------------------
CREATE TABLE "CHF". " T_xifenfei "
("ID" number generated ALWAYS as IDENTITY MinValue 1 MAXVALUE
9999999999999999999999999999 INCREMENT by 1-START with 1
CACHE Noorder nocycle not NULL ENABLE,
"NAME" VARCHAR2 (100)
) SEGMENT Creation DEFERRED
PCTFREE pctused Initrans 1 Maxtrans 255
Nocompress LOGGING
Tablespace "InMemory"

Xff_pdb@chf> INSERT into T_xifenfei VALUES (1, ' WWW.XIFNEFEI.COM ');
INSERT into T_xifenfei VALUES (1, ' WWW.XIFNEFEI.COM ')
*
Line 1th Error:
ORA-32795: Cannot insert to always build identity column


Xff_pdb@chf> INSERT into T_xifenfei (name) VALUES (' WWW.XIFNEFEI.COM ');

1 lines have been created.

Xff_pdb@chf> INSERT into T_xifenfei (name) VALUES (' WWW.orasos.COM ');

1 lines have been created.

Xff_pdb@chf> commit;

Submit completed.

Xff_pdb@chf> col name for A30
Xff_pdb@chf> select * from T_xifenfei;

ID NAME
---------- ------------------------------
1 WWW.XIFNEFEI.COM
2 WWW.orasos.COM

xff_pdb@chf> Update T_xifenfei set id=3 where id=2;
Update T_xifenfei set id=3 where id=2
*
Line 1th Error:
ORA-32796: Unable to update the Always build identity column

Xff_pdb@chf> Delete from T_xifenfei where id=1;

1 rows have been deleted.

Xff_pdb@chf> commit;

Submit completed.

Xff_pdb@chf> select Iseq$$_91982.nextval from dual;

Nextval
----------
3

Xff_pdb@chf> INSERT into T_xifenfei (name) VALUES (' WWW.ORASOS.COM ');

1 lines have been created.

Xff_pdb@chf> commit;

Submit completed.

Xff_pdb@chf> select * from T_xifenfei;

ID NAME
---------- ------------------------------
2 WWW.orasos.COM
4 WWW.ORASOS.COM

Xff_pdb@chf> ALTER session SET EVENTS? TRACE NAME Context FOREVER, Level 1
2 '

The session has changed.

Xff_pdb@chf> Select value from V$diag_info where Name= ' Default Trace File '

VALUE
--------------------------------------------------------------------------------

D:\APP\FFCHENG\diag\rdbms\xff\xff\trace\xff_ora_10628.trc

Xff_pdb@chf> INSERT into T_xifenfei (name) VALUES (' WWW.111cn.net ');

1 lines have been created.

Xff_pdb@chf> COMMIT;

Submit completed.

--Trace trace file
Parsing in CURSOR #688719640 len=55 dep=0 uid=103 oct=2 lid=103 tim=15129490112 hv=961646460
Ad=ƍff05d11a18 ' SQLID=དUZYJHWP33VW '
INSERT into T_xifenfei (name) VALUES (' WWW.111cn.net ')
End of STMT
PARSE #688719640: c=15600,e=18909,p=0,cr=44,cu=0,mis=1,r=0,dep=0,og=1,plh=2541165129,tim=15129490112
EXEC #688719640: c=0,e=347,p=0,cr=1,cu=5,mis=0,r=1,dep=0,og=1,plh=2541165129,tim=15129490731
STAT #688719640 id=1 cnt=0 pid=0 pos=1 obj=0 op= ' LOAD TABLE conventional T_xifenfei (cr=1 pr=0-pw=0 time=296 us) '
STAT #688719640 cnt=1 pid=1 pos=1 obj=91983 op= ' SEQUENCE iseq$$_91982 (cr=0 pr=0 pw=0 time=89 us) '
Wait #688719640: nam= ' sql*net message to client ' Ela= 2 driver id=1413697536 #bytes =1 p3=0 obj#=-1 tim=15129490971

Xff_pdb@chf> ALTER TABLE T_XIFENFEI2 Modify (ID number generated ALWAYS as Identi
TY minvalue 1 MAXVALUE 99999999999999 INCREMENT by 1 START with 1 CACHE 100);

The table has changed.

xff_pdb@chf> drop sequence iseq$$_91982;
Drop sequence iseq$$_91982
*
Line 1th Error:
ORA-32794: Unable to delete system-generated sequence

xff_pdb@chf> drop table T_xifenfei;

The table has been deleted.

Xff_pdb@chf> Select Object_name,object_type from user_objects where object_name=
' iseq$$_91982 '

object_name object_type
-------------------- -----------------------
iseq$$_91982 SEQUENCE

Xff_pdb@chf> Select Object_name,object_type from user_objects where object_name=
' iseq$$_91982 '

object_name object_type
-------------------- -----------------------
iseq$$_91982 SEQUENCE

Xff_pdb@chf> Purge table T_xifenfei;

The table has been cleared.

Xff_pdb@chf> Select Object_name,object_type from user_objects where object_name=
' iseq$$_91982 '

No rows selected
Here comes a few conclusions:
1. Generated ALWAYS as IDENTITY column cannot manually specify values and modify this value
2. The essence of generated IDENTITY is also achieved through sequence
3. Sequence cannot be deleted individually in generated IDENTITY
4. Generated IDENTITY in the table deletion, if there is a recycle Bin, the sequence is still stored, if the table is completely deleted, then sequence also deleted
5. The sequence in generated IDENTITY can be queried by a SELECT statement
6. Modify the sequence correlation value of generated IDENTITY by using the Alert table statement

Generated by DEFAULT as identity mode test
Xff_pdb@chf> CREATE TABLE T_xifenfei2 (ID number generated by DEFAULT as Identi
Ty,name varchar2) tablespace users;

Table has been created.

xff_pdb@chf> INSERT INTO T_XIFENFEI2 values (1, ' www.111cn.net ');

1 lines have been created.

xff_pdb@chf> INSERT into T_XIFENFEI2 (name) VALUES (' www.orasos.com ');

1 lines have been created.

Xff_pdb@chf> col name for A20
Xff_pdb@chf> select * from T_xifenfei2;

ID NAME
---------- --------------------
1 www.111cn.net
1 www.orasos.com

xff_pdb@chf> INSERT INTO T_XIFENFEI2 values (null, ' www.111cn.net ');
INSERT into T_XIFENFEI2 values (null, ' www.111cn.net ')
*
Line 1th Error:
ORA-01400: Cannot insert NULL ("CHF".) T_xifenfei2 "." ID ")


Xff_pdb@chf> desc T_XIFENFEI2
is the name empty? Type
----------------------------------------- -------- ---------------------------

ID not NULL number
NAME VARCHAR2 (100)
Come to the conclusion that:
1. Generated by DEFAULT as identity method cannot insert null values in this column
2. Generated by default as identity method can specify specific value inserts

Generated by DEFAULT on NULL as identity method test
Xff_pdb@chf> CREATE TABLE T_xifenfei3 (ID number generated by DEFAULT on null A
S identity,name varchar2) tablespace users;

Table has been created.

Xff_pdb@chf> desc T_XIFENFEI3
is the name empty? Type
----------------------------------------- -------- ----------------------------

ID not NULL number
NAME VARCHAR2 (100)

xff_pdb@chf> INSERT INTO T_XIFENFEI3 values (null, ' www.111cn.net ');

1 lines have been created.

Xff_pdb@chf> select * from T_xifenfei3;

ID NAME
---------- --------------------
1 www.111cn.net
Test conclusion: A null value can be queried on a column of generated by default on NULL as identity, except that the default is converted to the corresponding sequence value

Traditional self-growing column implementation method
Xff_pdb@chf> CREATE TABLE T_xifenfei4 (ID number,name varchar2 (MB)) tablespace
Users

Table has been created.

xff_pdb@chf> Create sequence Xff_sequence
2 Increment by 1
3 MinValue 1
4 Nomaxvalue
5 Start with 1
6 Cache 20
7 order;

The sequence has been created.

Xff_pdb@chf> Create or Replace trigger xifenfei_id
2 before insert on T_XIFENFEI4
3 for each row
4 begin
5 Select Xff_sequence.nextval into:new.id from dual;
6 end;
7/

Trigger has been created

xff_pdb@chf> INSERT into T_XIFENFEI4 (name) VALUES (' www.111cn.net ');

1 lines have been created.

Xff_pdb@chf> select * from T_xifenfei4;

ID NAME
---------- --------------------
1 www.111cn.net

Contact: Mobile Phone (13429648788) QQ (107644445)
Link:http://www.xifenfei.com/5755.html
Author: Xi-FEI