Batch modify sequence cache

Time:2021-8-30
--Batch modify sequence cache
SELECT 'alter sequence ' || SEQUENCE_OWNER || '.' || SEQUENCE_NAME || ' cache 1000;' INC_SEQ_CMD
  FROM DBA_SEQUENCES
 WHERE LAST_NUMBER > 100000
   AND CACHE_SIZE < 200
   AND SEQUENCE_OWNER IN
       (SELECT USERNAME
          FROM DBA_USERS
         WHERE ACCOUNT_STATUS = 'OPEN'
           AND USERNAME NOT IN
               ('SYS', 'SYSTEM', 'ZABBIX', 'DBSNMP', 'MGMT_VIEW', 'SYSMAN')) 
   AND EXISTS (SELECT 1
          FROM V$DATABASE
         WHERE OPEN_MODE = 'READ WRITE'
           AND DATABASE_ROLE = 'PRIMARY');