CODEWITHSUNDEEP
sqloracle-databaseoracle12c
IllegalKniegal
IllegalKniegal

Reputation: 83

cannot drop a system-generated sequence

I have a system-generated sequence in my Oracle 12c database that I cannot seem to get rid of. Calling "drop sequence" on it gets this error:

Error code: ORA-32794
Description: cannot drop a system-generated sequence
Cause: An attempt was made to drop a system-generated sequence.
Action: A system-generated sequence, such as one created for an identity column, cannot be dropped.

The sequence was used to generate values for a table's Id column, and the table no longer exists. Usually, I can create and drop such tables and the sequence drops with it. I do not know the statements by which the table was dropped in this instance, but I would really like to remove the stray sequence.

Upvotes: 8

Views: 18201

Answers (2)

Philippe
Philippe

Reputation: 1823

You can drop a table and purge the system-generated sequence with a single drop command by using the purge keyword

drop table mytable purge cascade constraints

Upvotes: 1

William Robertson
William Robertson

Reputation: 16001

You should not need to drop an identity column sequence explicitly, as it is associated with the table, and dropping the table with the purge option (or dropping normally and emptying the recycle bin) will also drop the sequence.

create table test_tbl (id  number generated as identity);

Table created.

select * from user_tab_identity_cols;

TABLE_NAME                     COLUMN_NAME GENERATION_TYPE SEQUENCE_NAME IDENTITY_OPTIONS
------------------------------ ----------- --------------- ------------- --------------------------------------------------------------------------------
TEST_TBL                       ID          ALWAYS          ISEQ$$_124811 START WITH: 1, INCREMENT BY: 1, MAX_VALUE: 9999999999999999999999999999, MIN_VAL

Dropping the table leaves the table and its sequence in the recycle bin:

drop table test_tbl;

Table dropped.

select * from user_tab_identity_cols;

TABLE_NAME                     COLUMN_NAME GENERATION_TYPE SEQUENCE_NAME IDENTITY_OPTIONS
------------------------------ ----------- --------------- ------------- --------------------------------------------------------------------------------
BIN$gQ8wv//QTjK+mAGxdffxgQ==$0 ID          ALWAYS          ISEQ$$_124811 START WITH: 1, INCREMENT BY: 1, MAX_VALUE: 9999999999999999999999999999, MIN_VAL

Emptying the recycle bin clears it:

purge recyclebin;

Recyclebin purged.

select * from user_tab_identity_cols;

no rows selected

select * from user_sequences where sequence_name like 'ISEQ$$%';

no rows selected

Upvotes: 34

Related Questions