seq

-- 1. Check for unusable indexes
SET SERVEROUTPUT ON
DECLARE
  cnt NUMBER;
BEGIN
  FOR idx IN (SELECT index_name, status 
              FROM user_indexes 
              WHERE table_name = 'SEQUENCE') LOOP
    IF idx.status = 'UNUSABLE' THEN
      dbms_output.put_line('Rebuilding index: ' || idx.index_name);
      EXECUTE IMMEDIATE 'ALTER INDEX ' || idx.index_name || ' REBUILD';
    END IF;
  END LOOP;
END;
/
-- 2. Safely truncate table
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE SEQUENCE';
  dbms_output.put_line('Table SEQUENCE truncated successfully.');
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('TRUNCATE failed: ' || SQLERRM);
    dbms_output.put_line('Trying DELETE instead...');
    EXECUTE IMMEDIATE 'DELETE FROM SEQUENCE';
    dbms_output.put_line('All rows deleted from SEQUENCE.');
END;
/
-- 3. Optional: check row count
SELECT COUNT(*) AS ROWS_LEFT FROM SEQUENCE;