Shulou(Shulou.com)06/01 Report--

1. CONSTRAID ID 0 is cdb,1 is cdb$root, 2 is pdb seed,3 and above is pdb

two。 Self-increasing sequence

Before 12c, Oracle could only implement this function through sequence.

Sys@newtestCDB > create table test (id number generated always as identity, name varchar2 (20))

Table created.

Elapsed: 00:00:00.04

Sys@newtestCDB > insert into test (name) values ('smith')

1 row created.

Elapsed: 00:00:00.01

Sys@newtestCDB > insert into test (name) values ('smith3')

1 row created.

Elapsed: 00:00:00.01

Sys@newtestCDB > insert into test (name) values ('smith4')

1 row created.

Elapsed: 00:00:00.01

Sys@newtestCDB > select * from test

ID NAME 1 smith 2 smith3 3 smith4

Elapsed: 00:00:00.02

Sys@newtestCDB > update test set id=1 where id=2

Update test set id=1 where id=2

*

ERROR at line 1:

ORA-32796: cannot update a generated always identity column

Elapsed: 00:00:00.03

Sys@newtestCDB > insert into test (id,name) values (null,'smith4')

Insert into test (id,name) values (null,'smith4')

*

ERROR at line 1:

ORA-32795: cannot insert into a generated always identity column

Elapsed: 00:00:00.01

Sys@newtestCDB > insert into test (id,name) values (2remith4')

Insert into test (id,name) values (2MIT 4')

*

ERROR at line 1:

ORA-32795: cannot insert into a generated always identity column

Sys@newtestCDB > delete from test where id=3

1 row deleted.

Elapsed: 00:00:00.03

Sys@newtestCDB > insert into test (name) values ('smith5')

1 row created.

Elapsed: 00:00:00.01

Sys@newtestCDB > select * from test

ID NAME 2 smith3 4 smith5

Elapsed: 00:00:00.01

Conclusion:

GENERATED ALWAYS AS IDENTITY can insert without specifying this column

GENERATED ALWAYS AS IDENTITY cannot insert a null value in this column

GENERATED ALWAYS AS IDENTITY cannot specify specific values to insert

GENERATED ALWAYS AS IDENTITY cannot update this column with update

Sys@newtestCDB > create table test (id number generated by default as identity, name varchar2 (20))

Table created.

Elapsed: 00:00:00.03

Sys@newtestCDB > insert into test (name) values ('smith')

1 row created.

Elapsed: 00:00:00.01

Sys@newtestCDB > insert into test (name) values ('smith3')

1 row created.

Elapsed: 00:00:00.01

Sys@newtestCDB > insert into test (name) values ('smith4')

1 row created.

Elapsed: 00:00:00.01

Sys@newtestCDB > insert into test (id,name) values (null,'smith4')

Insert into test (id,name) values (null,'smith4')

*

ERROR at line 1:

ORA-01400: cannot insert NULL into ("SYS". "TEST". "ID")

Elapsed: 00:00:00.02

Sys@newtestCDB > insert into test (id,name) values (2remith3')

1 row created.

Elapsed: 00:00:00.01

Sys@newtestCDB > update test set id= NULL where id=2

Update test set id= NULL where id=2

*

ERROR at line 1:

ORA-01407: cannot update ("SYS". "TEST". "ID") to NULL

Conclusion:

GENERATED BY DEFAULT AS IDENTITY can insert without specifying this column

GENERATED BY DEFAULT AS IDENTITY cannot insert a null value in this column

GENERATED BY DEFAULT AS IDENTITY can specify specific values to insert

GENERATED BY DEFAULT AS IDENTITY can update the column using update, but not to NULL

Sys@newtestCDB > create table test (id number generated by default ON NULL as identity, name varchar2 (20))

Table created.

Elapsed: 00:00:00.03

Sys@newtestCDB > insert into test (id,name) values (null,'smith4')

1 row created.

Elapsed: 00:00:00.01

Conclusion:

GENERATED BY DEFAULT ON NULL AS IDENTITY can insert without specifying this column

In GENERATED BY DEFAULT ON NULL AS IDENTITY mode, you can specify specific values to insert.

GENERATED BY DEFAULT ON NULL AS IDENTITY can insert null values in this column

GENERATED BY DEFAULT ON NULL AS IDENTITY can update this column using update

Sys@newtestCDB > col tablename format A20

Sys@newtestCDB > col table_name format A20

Sys@newtestCDB > col sequence_name format A20

Sys@newtestCDB > SELECT a.name AS table_name

2 b.name AS sequence_name

3 FROM sys.idnseq$ c

4 JOIN obj$ an ON c.obj# = a.obj#

5 JOIN obj$ b ON c.seqobj# = b.obj#

6 where a.nameplate tests

TABLE_NAME SEQUENCE_NAME

TEST ISEQ$$_83962

Elapsed: 00:00:00.01

Sys@newtestCDB > create table test (id number generated by default as identity, name varchar2 (20))

Table created.

Elapsed: 00:00:00.03

Sys@newtestCDB > SELECT a.name AS table_name

2 b.name AS sequence_name

3 FROM sys.idnseq$ c

4 JOIN obj$ an ON c.obj# = a.obj#

5 JOIN obj$ b ON c.seqobj# = b.obj#

6 where a.nameplate tests

TABLE_NAME SEQUENCE_NAME

TEST ISEQ$$_83964

Elapsed: 00:00:00.01

Sys@newtestCDB > SELECT object_name, object_type FROM user_objects where object_name='ISEQ$$_83964'

OBJECT_NAMEOBJECT_TYPE

ISEQ$$_83964

SEQUENCE

Elapsed: 00:00:00.05

Sys@newtestCDB > drop table test

Table dropped.

Elapsed: 00:00:00.04

Sys@newtestCDB > SELECT object_name, object_type FROM user_objects where object_name='ISEQ$$_83964'

No rows selected

Elapsed: 00:00:00.00

Sys@newtestCDB > create table test (id number generated by default as identity, name varchar2 (20))

Table created.

Elapsed: 00:00:00.06

Sys@newtestCDB > SELECT a.name AS table_name

2 b.name AS sequence_name

3 FROM sys.idnseq$ c

4 JOIN obj$ an ON c.obj# = a.obj#

5 JOIN obj$ b ON c.seqobj# = b.obj#

6 where a.nameplate tests

TABLE_NAME SEQUENCE_NAME

TEST ISEQ$$_83966

Elapsed: 00:00:00.01

Sys@newtestCDB > drop SEQUENCE ISEQ$$_83966

Drop SEQUENCE ISEQ$$_83966

*

ERROR at line 1:

ORA-32794: cannot drop a system-generated sequence

Elapsed: 00:00:00.02

Conclusion:

Identity Columns is implemented based on sequence.

Sequence in GENERATED IDENTITY cannot be deleted alone

Delete the sequence table in GENERATED IDENTITY when it is deleted

This is a picture of 12.1, and there are new changes in 12.2.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.