Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

In Oracle 12c , Can we alter sequence generated implicitly as part of identity column.

Deepak MahtoNov 19 2014 — edited Nov 19 2014

Hi Folks,

Please consider below,

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!).

Is it possible to alter underlying sequence if Yes how?

If NO then any workaround or Expert thought on same?

Thanks

Deepak M.

This post has been answered by Dom Brooks on Nov 19 2014
Jump to Answer

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 17 2014
Added on Nov 19 2014
4 comments
14,115 views