Live SQL

sequence gaps by failed DDL

View All Scripts Login to Run Script

Script Name sequence gaps by failed DDL
Description based on a discussion on stackoverflow: http://stackoverflow.com/questions/34975559/why-do-oracles-sequences-skip-their-cached-values-when-attempting-to-re-create
Area SQL General
Contributor berx
Created Sunday January 24, 2016

Statement 1
```
drop sequence s
```
```
ORA-02289: sequence does not exist
```
More Details: https://docs.oracle.com/error-help/db/ora-02289

Statement 2

create sequence s cache 50

Sequence created.

Statement 3
```
select s.nextval from dual
```
```
 
NEXTVAL
1
```

Statement 4

create sequence s cache 50

ORA-00955: name is already used by an existing object

More Details: https://docs.oracle.com/error-help/db/ora-00955

Statement 5
```
select s.nextval from dual
```
```
 
NEXTVAL
51
```
Statement 6
```
drop sequence s1
```
```
ORA-02289: sequence does not exist
```
More Details: https://docs.oracle.com/error-help/db/ora-02289
Statement 7
```
drop sequence s2
```
```
ORA-02289: sequence does not exist
```
More Details: https://docs.oracle.com/error-help/db/ora-02289

Statement 8

create sequence s1 cache 50

Sequence created.

Statement 9

create sequence s2 cache 50

Sequence created.

Statement 10
```
select s1.nextval from dual
```
```
 
NEXTVAL
1
```
Statement 11
```
select s2.nextval from dual
```
```
 
NEXTVAL
1
```

Statement 12

create synonym S2 for S1

ORA-00955: name is already used by an existing object

More Details: https://docs.oracle.com/error-help/db/ora-00955

Statement 13
```
select s1.nextval from dual
```
```
 
NEXTVAL
2
```
Statement 14
```
select s2.nextval from dual
```
```
 
NEXTVAL
51
```

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database