Oracle SQL query to get the max logins per hour from am_session table

I think you can just aggregate:

select to_char(create_tme, 'YYYY-MM-DD HH24') as yyyymmddhh, COUNT(*)
from am_session
group by to_char(create_tme, 'YYYY-MM-DD HH24')
order by count(*) desc
fetch first 1 row only;

If you want the hour independently of the date, then:

select to_char(create_tme, 'HH24') as yyyymmddhh, COUNT(*)
from am_session
group by to_char(create_tme, 'HH24')
order by count(*) desc
fetch first 1 row only;

You can do as below -

select 
to_char(create_time, 'YYYY-MM-DD') as create_date, 
extract(hour from create_time) as Hour,
Count(*)
from am_session
group by 
to_char(create_time, 'YYYY-MM-DD'),
extract(hour from create_time)
order by 1, 2 desc;

Note : This will not provide you count for hours where there was no login.