Cannot Drop a System-Generated Sequence

cannot drop a system-generated sequence

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

Does Oracle table drop also trigger dropping all its sequences?

If the sequence is genuinely 'part' of the table definition (in which case it is visibly linked in USER_TAB_IDENTITY_COLS) then it will be dropped. Otherwise it is an independent object and will remain.

Sequence increment is not working

I cannot reproduce this at all. Please run the following code and show us what is displayed.

CREATE SEQUENCE TestSeq START WITH 1 INCREMENT BY 1  NOCACHE NOCYCLE;

select testseq.nextval from dual
/

select testseq.nextval from dual
/

select testseq.nextval from dual
/

Cannot drop disconnected oracle user

The other session(s) are connected to another instance of your cluster.

Add inst_id to your gv$session query, like this:

select sid,serial#,inst_id 
from gv$session 
where username = 'user_i_want_to_drop';

Then, include the inst_id in the alter system kill session command, like this:

alter system kill session '<sid>,<serial#>,@<inst_id>' immediate;

E.g.,

alter system kill session '15,1891,@1' immediate;

How do I reset a sequence in Oracle?

Here is a good procedure for resetting any sequence to 0 from Oracle guru Tom Kyte. Great discussion on the pros and cons in the links below too.

tkyte@TKYTE901.US.ORACLE.COM> 
create or replace
procedure reset_seq( p_seq_name in varchar2 )
is
    l_val number;
begin
    execute immediate
    'select ' || p_seq_name || '.nextval from dual' INTO l_val;

    execute immediate
    'alter sequence ' || p_seq_name || ' increment by -' || l_val || 
                                                          ' minvalue 0';

    execute immediate
    'select ' || p_seq_name || '.nextval from dual' INTO l_val;

    execute immediate
    'alter sequence ' || p_seq_name || ' increment by 1 minvalue 0';
end;
/

From this page: Dynamic SQL to reset sequence value

Another good discussion is also here: How to reset sequences?

---

Related Topics