Sequence Wraparound, a Shadowing Synonym and an Unused Index

The sequence was created with CYCLE and MAXVALUE 500000 - the same ceiling as the number of migrated rows. Once it reached 500,000 it wrapped to 1 and began regenerating keys that the migration had already inserted, so the primary key rejected them. NOCACHE is irrelevant here; NOCYCLE is the missing clause.

Diagnosis:

SELECT sequence_name, min_value, max_value, increment_by, cycle_flag,
       order_flag, cache_size, last_number
FROM user_sequences WHERE sequence_name = 'TICKET_SEQ';
SELECT MAX(ticket_id) FROM tickets;

LAST_NUMBER shows the next value that would be handed out (including the cached block), so it is an upper bound, not a precise high-water mark. Compare it with MAX(ticket_id) to confirm the wrap.

Remediation:

CREATE SEQUENCE ticket_seq_new START WITH 600001 INCREMENT BY 1
  NOCYCLE CACHE 1000;
-- repoint the default or the insert, then drop the old sequence after cutover

Size MAXVALUE well above projected growth (for NUMBER, 10^27 is available). Explain to the team that gaps are normal and unavoidable: NEXTVAL is non-transactional, numbers are consumed by rolled-back inserts, and CACHE means a restart discards unused values. A sequence is not a gap-free counter and not a concurrency lock; if gap-free numbering is a business requirement, it needs a different design.

Name resolution for an unqualified table reference checks, in order: objects in the current schema, then private synonyms in the current schema, then PUBLIC synonyms. RPT therefore resolves TICKETS to its own private synonym, which points at RPT.TICKETS_OLD, and never reaches the public synonym that APP relies on. The 11% shortfall is stale snapshot data, not a privileges problem - and it is dangerous precisely because nothing errors.

Proof:

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

SELECT object_name, object_type, status, last_ddl_time
FROM all_objects
WHERE object_name IN ('TICKETS','TICKETS_OLD');

Run the first query connected as RPT and again as APP; the TABLE_OWNER/TABLE_NAME pair will differ. SELECT CURRENT_USER FROM dual confirms which schema context you are testing.

Fix: drop or repoint the private synonym so it matches the public one:

DROP SYNONYM rpt.tickets;
CREATE SYNONYM rpt.tickets FOR app.tickets;  -- or rely on the public synonym

Then verify grants: a synonym is only a name, so RPT still needs SELECT on APP.TICKETS. Also confirm that no other RPT code depends on TICKETS_OLD before dropping it; query ALL_DEPENDENCIES to find compiled objects that reference the old target.

Two separate problems. First, TRUNC(created_date) wraps the column in a function, so the optimizer cannot match the indexed column to a plain range; the index on created_date would need a function-based index on TRUNC(created_date) to be usable. Second, the index is composite with STATUS as the leading column and the query supplies no STATUS predicate. Oracle cannot do an efficient range scan on the second column alone; an index skip scan is possible but is usually only chosen when the leading column has very few distinct values and statistics support it.

Preferred rewrite - half-open range, no function on the column:

SELECT ticket_id FROM tickets
WHERE created_date >= DATE '2024-03-11'
  AND created_date <  DATE '2024-03-12';

This is sargable and is safe across time components; BETWEEN date AND date would silently drop rows with a non-midnight time.

Options if the predicate must stay as TRUNC:

• Add CREATE INDEX idx_tix_trunc_created ON tickets(TRUNC(created_date)); and verify with the query rewritten to match the index expression exactly.
• Add STATUS to the predicate when the report allows it, making the existing composite index useful.
• Check USER_INDEXES.status first: an index left UNUSABLE by a partition or load operation is skipped without warning.

Confirm with EXPLAIN PLAN FOR ... and SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL,NULL,'BASIC +PREDICATE')) rather than guessing from timing alone.