Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table test  (id number, 
name varchar2(10));



insert into test (id, name)
     select 1, 'A' from dual union all
     select 2, 'B' from dual union all
     select 3, 'B' from dual union all
     select 4, 'C' from dual union all
     select 5, 'A' from dual union all
     select 6, 'B' from dual union all
     select 7, 'B' from dual union all
     select 8, 'B' from dual union all
     select 9, 'B' from dual union all
     select 10, 'X' from dual
10 rows affected

select min(id), max(id)
from (select t.*,
             row_number() over (order by id) as seqnum,
             row_number() over (partition by name order by id) as seqnum_1
      from test t
     ) t
where name = 'B'
group by (seqnum - seqnum_1)
order by min(id) desc
fetch first 1 row only;


MIN(ID)
MAX(ID)


6
9


    select t.*
    from (select t.*,
                 dense_rank() over (order by nonbs_after asc) as grp
          from (select t.*, 
                       sum(case when name <> 'B' then 1 else 0 end) over (order by id desc) as nonbs_after
                from test t
               ) t
          where name = 'B'
         ) t
    where grp = 1;


ID
NAME
NONBS_AFTER
GRP


9
B
1
1

8
B
1
1

7
B
1
1

6
B
1
1