Finding threshold value of the sequence

 

select * from 

  (

    select

        seq.sequence_owner

      , seq.sequence_name

      , ( select application_name from 

          ( select ord, application_id, application_name from 

            ( select 1 ord, app.application_id, app.application_name

              from apps.fnd_application_vl app

              where app.product_code = substr(seq.sequence_name, 1, instr(seq.sequence_name,'_')-1)

              union

              select 2 ord, app.application_id, app.application_name

              from apps.fnd_oracle_userid fou, apps.fnd_product_installations fpi, apps.fnd_application_vl app

              where fou.oracle_username = seq.sequence_owner 

                and fpi.oracle_id = fou.oracle_id

                and app.application_id = fpi.application_id

            ) order by ord, application_id

          ) where rownum = 1

        ) application_name

      , seq.min_value

      , seq.max_value

      , seq.cache_size

      , seq.last_number

      , round(((seq.last_number-seq.min_value)/(seq.max_value-seq.min_value))*100) "% Range"

    from dba_sequences seq

    where seq.cycle_flag = 'N'

      and seq.max_value > 0

  ) 

where "% Range" >= 10 /* active threshold % */

  and max_value < 2147483648 /* limited range */

  and cache_size > 1000 /* large cache size */

order by cache_size desc;