SQL> select * from v$version;
BANNER
Have a Identity column created. Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production PL/SQL Release 12.1.0.1.0 - Production CORE 12.1.0.1.0 Production TNS for 64-bit Windows: Version 12.1.0.1.0 - Production NLSRTL Version 12.1.0.1.0 - Production SQL> @1;
TABLE_NAME COLUMN_NAME GENERATION IDENTITY_OPTIONS
-------------------- --------------- ---------- --------------------------------------------------
IDENTITY_TEST_1 ID ALWAYS START WITH: 1, INCREMENT BY: 1, MAX_VALUE: 9999999
999999999999999999999, MIN_VALUE: 1, CYCLE_FLAG: N
, CACHE_SIZE: 20, ORDER_FLAG: N
SQL> @1;
TABLE_NAME SEQUENCE_NAME
-------------------- ------------------------------
IDENTITY_TEST_1 ISEQ$$_92327
Trying to alter Cache value of sequence.
from SYSDBA
SQL> alter sequence ISEQ$$_92327 CACHE 1000;
alter sequence ISEQ$$_92327 CACHE 1000
*
ERROR at line 1:
ORA-02289: sequence does not exist
From Owner Schema
Error starting at line 4 in command:
alter sequence ISEQ$$_92327 CACHE 1000
Error report:
SQL Error: ORA-32793: cannot alter a system-generated sequence
Altering was not an option.
By Default oracle Set Cache Size to 20 for internal sequence created implicitly as part of identity column.
Now Suppose a Scenario were we need to upgrade Cache value of sequence to avoid any performance issue (row cache lock wait event!).
Deepak M.