# Oracle Sequences Quick Reference

## Create Sequence

```sql
CREATE SEQUENCE sequence_name
    [START WITH n]           -- First value (default 1)
    [INCREMENT BY n]         -- Step (default 1, negative for descending)
    [MINVALUE n | NOMINVALUE] -- Minimum (default 1 or -10^27)
    [MAXVALUE n | NOMAXVALUE] -- Maximum (default 10^27 or -1)
    [CYCLE | NOCYCLE]        -- Restart at min/max (default NOCYCLE)
    [CACHE n | NOCACHE]      -- Pre-allocate values (default 20)
    [ORDER | NOORDER];       -- Guarantee order in RAC (default NOORDER)
```

## Common Examples

```sql
-- Simple auto-increment
CREATE SEQUENCE emp_seq;

-- With specific settings
CREATE SEQUENCE order_seq
    START WITH 1000
    INCREMENT BY 1
    NOCYCLE
    CACHE 100;

-- Descending sequence
CREATE SEQUENCE countdown_seq
    START WITH 100
    INCREMENT BY -1
    MINVALUE 1
    MAXVALUE 100;

-- Cycling sequence
CREATE SEQUENCE week_seq
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 7
    CYCLE;
```

## Using Sequences

```sql
-- Get next value
SELECT emp_seq.NEXTVAL FROM DUAL;

-- Get current value (must call NEXTVAL first in session)
SELECT emp_seq.CURRVAL FROM DUAL;

-- In INSERT
INSERT INTO employees (id, name) VALUES (emp_seq.NEXTVAL, 'John');

-- In INSERT with RETURNING
INSERT INTO employees (id, name) VALUES (emp_seq.NEXTVAL, 'John')
RETURNING id INTO v_new_id;

-- Multiple NEXTVAL in one statement
INSERT INTO employees (id, name, code)
VALUES (emp_seq.NEXTVAL, 'John', 'EMP' || emp_seq.CURRVAL);

-- In PL/SQL
DECLARE
    v_id NUMBER;
BEGIN
    v_id := emp_seq.NEXTVAL;
    INSERT INTO employees (id, name) VALUES (v_id, 'John');
END;
/
```

## Modify Sequence

```sql
-- Change increment
ALTER SEQUENCE emp_seq INCREMENT BY 10;

-- Change cache
ALTER SEQUENCE emp_seq CACHE 200;

-- Change max value
ALTER SEQUENCE emp_seq MAXVALUE 1000000;

-- Enable cycling
ALTER SEQUENCE emp_seq CYCLE;

-- Cannot change START WITH - must recreate
```

## Reset Sequence

```sql
-- Method 1: Alter increment (temporary)
-- If current is 100 and you want to reset to 1:
ALTER SEQUENCE emp_seq INCREMENT BY -99;
SELECT emp_seq.NEXTVAL FROM DUAL;  -- Now at 1
ALTER SEQUENCE emp_seq INCREMENT BY 1;

-- Method 2: 12c+ RESTART
ALTER SEQUENCE emp_seq RESTART START WITH 1;

-- Method 3: Drop and recreate
DROP SEQUENCE emp_seq;
CREATE SEQUENCE emp_seq START WITH 1;

-- Set to specific value (12c+)
ALTER SEQUENCE emp_seq RESTART START WITH 500;
```

## Sequence Metadata

```sql
-- View sequence info
SELECT sequence_name, min_value, max_value, increment_by,
       cycle_flag, order_flag, cache_size, last_number
FROM user_sequences
WHERE sequence_name = 'EMP_SEQ';

-- All sequences
SELECT * FROM user_sequences;

-- Current value (approximate, doesn't consume)
SELECT last_number FROM user_sequences WHERE sequence_name = 'EMP_SEQ';
```

## Identity Columns (12c+)

```sql
-- Auto-generate ID (alternative to sequence)
CREATE TABLE employees (
    id NUMBER GENERATED ALWAYS AS IDENTITY,
    name VARCHAR2(100)
);

-- Can override with GENERATED BY DEFAULT
CREATE TABLE employees (
    id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    name VARCHAR2(100)
);

-- With options
CREATE TABLE employees (
    id NUMBER GENERATED ALWAYS AS IDENTITY (
        START WITH 100
        INCREMENT BY 1
        NOCACHE
    ),
    name VARCHAR2(100)
);

-- Insert with identity
INSERT INTO employees (name) VALUES ('John');  -- ID auto-generated

-- Override (only with BY DEFAULT)
INSERT INTO employees (id, name) VALUES (999, 'Admin');  -- BY DEFAULT only
```

## Sequence vs Identity

| Feature | Sequence | Identity |
|---------|----------|----------|
| Reusable across tables | Yes | No |
| Gap-free possible | No | No |
| Can be reset | Yes | Yes (12c+) |
| DEFAULT value | Manual | Automatic |
| Performance | Same | Same |

## Performance Considerations

```sql
-- Large CACHE for high-volume inserts
CREATE SEQUENCE bulk_seq CACHE 1000;

-- NOCACHE for audit/compliance (gaps visible)
CREATE SEQUENCE audit_seq NOCACHE;

-- ORDER for RAC when sequence order matters
CREATE SEQUENCE ordered_seq ORDER CACHE 20;
```

## Common Patterns

### Gap Detection (Audit)

```sql
-- Find gaps in sequence usage
SELECT
    id + 1 AS gap_start,
    next_id - 1 AS gap_end
FROM (
    SELECT id, LEAD(id) OVER (ORDER BY id) AS next_id
    FROM employees
)
WHERE next_id - id > 1;
```

### Sequence for Multiple Tables

```sql
-- Master sequence for all IDs
CREATE SEQUENCE global_id_seq START WITH 1;

INSERT INTO customers (id, name) VALUES (global_id_seq.NEXTVAL, 'Customer');
INSERT INTO orders (id, customer_id) VALUES (global_id_seq.NEXTVAL, 1);
INSERT INTO products (id, name) VALUES (global_id_seq.NEXTVAL, 'Product');
```

## Drop Sequence

```sql
DROP SEQUENCE sequence_name;

-- With cascade (removes from dependent objects)
DROP SEQUENCE sequence_name CASCADE;
```
