I need to rename the sequences of my tables. There are a lot of tables and they are complex and droping anything would not be prefered. Is there a way to rename them?

I tried:

ALTER SEQUENCE ISEQ$$_149698 RENAME TO NEW_SEQUENCE;
RENAME ISEQ$$_149698 to NEW_SEQUENCE;

The first option throws the following error:

SQL Error [2286] [42000]: ORA-02286: no options specified for ALTER SEQUENCE

The second:

SQL Error [32799] [99999]: ORA-32799: cannot rename a system-generated sequence

CodePudding user response：

You cannot rename a sequence generated for an identity column. (As other users have pointed out, and as the error message implies.) For that reason, I recommend you use a sequence default value instead of an identity column.

For example:

--Create the sequence and a table to use it.
create sequence my_sequence;
create table my_table(a number default my_sequence.nextval, b number);

--Rename the sequence to whatever you want.
rename my_sequence to my_sequence2;

However, there are a few disadvantages of the default approach:

1. This feature is not available until 12.1. (Although identity columns are also a new feature.)
2. You have to create the sequences yourself (obviously).
3. You need to remember to also grant the sequence to any users who will insert rows to the table:

grant insert, select on my_table to test_user;
grant select on my_sequence to test_user;

4. If you rename a default sequence you must also modify the default value to point to the new sequence.

--Afate a sequence rename, this INSERT fails with: ORA-02289: sequence does not exist
insert into my_table(b) values(1);

--You must first modify the table:
alter table my_table modify a number default my_sequence2.nextval;

--Now this will run:
insert into my_table(b) values(1);

Despite the disadvantages of using the sequence default, I still prefer that approach over identity columns because I want all of my objects to have the exact same name in every environment.

CodePudding user response：

rename old_seq to new_sequence;