Sequence Gaps, Unusable Index Partitions, and a Shadowing Synonym

Gaps are expected behavior, not corruption. NEXTVAL is non-transactional and is never rolled back. With CACHE 20 on two RAC instances, each instance preallocates a block of 20 values; a node recycle, instance bounce, or ALTER SEQUENCE discards unused cached values. NOORDER lets each instance allocate independently, which is good for throughput but makes the gap pattern irregular. A failed transaction also consumes its value permanently.

Options and costs:

1. Keep CACHE 20 and accept gaps. This is the normal choice; a sequence is a surrogate key, not an invoice number.
2. Use NOCACHE to reduce the size of discarded ranges. It does not eliminate gaps, because rollback still burns values, and it adds allocation overhead.
3. Add ORDER to serialize allocation across RAC. Combined with NOCACHE this reduces gaps further but hurts scalability and still cannot guarantee contiguity during rollback.
4. If a gapless business number is mandatory, use a single-row counter table with SELECT FOR UPDATE and update it in the same transaction. This serializes writers and can become a bottleneck, but it provides rollback-safe numbering. Keep ORDER_SEQ as the surrogate primary key and add a separate business number column.

Do not ALTER SEQUENCE RESTART to close gaps in production; existing keys can collide. Verify cache settings in DBA_SEQUENCES and document the chosen trade-off.

An UNUSABLE index is ignored by the optimizer. It may still exist in the data dictionary, but it is not maintained and cannot be used for access, so queries fall back to full table scans or partition scans. Correct results plus a 90-second runtime is the classic symptom. Diagnose before rebuilding:

SELECT index_name, partitioning, status
FROM   user_indexes
WHERE  table_name = 'ORDERS';

SELECT index_name, partition_name, status
FROM   user_ind_partitions
WHERE  index_name = 'IDX_ORDERS_CUST';

Rebuild only the unusable partitions:

ALTER INDEX idx_orders_cust REBUILD PARTITION p2024_q1;

If the whole index is unusable, ALTER INDEX idx_orders_cust REBUILD; rebuilds it, which may exceed the window on a large table; consider ONLINE rebuild if the edition supports it, and watch for the extra space and undo. Note that DML against a table can cause a nonpartitioned unusable index to be maintained and become usable again, unless SKIP_UNUSABLE_INDEXES is set; do not assume an unusable index stays unusable after the next load. Rebuilding does not gather statistics, so run DBMS_STATS afterward. Finally, find out why the partitions were left unusable and add a post-load validation step.

For an unqualified name in a session whose current schema is RPT, Oracle resolves in this order: an object named ORDERS in RPT itself, then a private synonym in RPT, then a PUBLIC synonym. Because RPT has a private synonym ORDERS pointing to APP.ORDERS_ARCHIVE, the report reads the archive table. The PUBLIC synonym pointing to APP.ORDERS is never consulted. This is a common post-migration trap: a private synonym created for a one-off archive test silently shadows the public one, and application SQL that worked before now reads stale data.

Diagnose with:

SELECT owner, synonym_name, table_owner, table_name
FROM   all_synonyms
WHERE  synonym_name = 'ORDERS';

SELECT owner, object_name, object_type
FROM   all_objects
WHERE  object_name = 'ORDERS';

Fix by redefining the private synonym to point at the current table:

CREATE OR REPLACE SYNONYM rpt.orders FOR app.orders;

or drop it if the public synonym should govern. Qualifying APP.ORDERS in the report would also work but the team cannot change SQL this sprint. Check for a real table named ORDERS in RPT first; a schema object outranks both synonym types. Avoid creating public synonyms casually, because they affect every schema that lacks a private override.