I am working in Oracle version 12cR2 and in SQL Server 2014, I have two problems with IDENTITY columns because I want to have the same behavior in both managers, the first problem is that in SQL Server when I insert a record sending a value for the IDENTITY column, for example 20, and in my next insert I do not send a value for the column I get a 21 record, in Oracle in the first record if I get 20 but when I perform the second insert I get record 1, the way to create the IDENTITY columns is as follows:
SQL server:
ID INT IDENTITY (1,1) NOT NULL
ORACLE:
ID NUMBER (10) GENERATED BY DEFAULT AS IDENTITY
In both managers the ID field is the primary key, the second case of error in Oracle is the following: when inserting a record without sending a value for the IDENTITY column I get record 1, if in my next insert I sent the value, for this example 2, if it is inserted, but when performing a third insert without sending a value for the column I get the error:
ORA-00001: single restriction violated
If I try to register again, it is already inserted without problem, to what I understand is that the SG has the number it goes in cached and always advances by 1 when an insert attempt is made.
Is there any external configuration to avoid these two problems in oracle? I hope you can help me, thanks