CREATE TABLE "events"(
        "id" NUMBER(11,0),
        "room_id" NUMBER(11,0),
        "start" DATE,
        "end" DATE,
);

Please don't create object with lowercase names in quotes! This makes them case sensitive, so you always have to reference them with the quotes...

Also avoid using reserved words for object names. Events, start and end are all reserved! You can find which are reserved with the following query:

select * from v$reserved_words

Anyway, onto your problem.

If I've understood, if a booking spans months, you want a row for each. Then you sum these up as needed.

One way to do this is to generate a row for each boundary crossed. You can do this by combining connect by and months_between.

The following converts each date to the first of the month. It then calculates the difference between them:

months_between(trunc(end_dt, 'mm'), trunc(start_dt, 'mm'))

You want a row for each entry, plus the number of months crossed. So you need to add one to this.

You can then use the connect by level trick to create extra rows.

connect by prior start_dt = start_dt
and     level <= months_between(trunc(end_dt, 'mm'), trunc(start_dt, 'mm')) + 1
and     prior sys_guid() is not null

To understand how this works, read:

https://stewashton.wordpress.com/2016/02/09/chunking-tables-7-prior-sys_guid/

To get your out, you just need to do some date manipulation:

- If it's the first row for a booking, the start = start_date. Otherwise it's the 1st of the month for start_date + number of months elapsed.
- If it's the last row, the end = end_date. Otherwise it's the 1st of the start + number of months elapsed - one day.

Put it all together and you get:

CREATE TABLE event (
  id       NUMBER(11,0),
  room_id  NUMBER(11,0),
  start_dt DATE,
  end_dt   DATE
);

insert into event values (1, 1, date'2016-08-03', date'2016-08-30');
insert into event values (2, 1, date'2016-08-30', date'2016-09-02');
insert into event values (3, 1, date'2016-09-10', date'2016-09-20');
insert into event values (4, 1, date'2016-09-21', date'2016-10-01');
insert into event values (5, 1, date'2016-10-01', date'2017-01-01');

alter session set nls_date_format = 'dd-MON-yyyy';
select e.id, e.room_id,
       case 
         when level = 1 then start_dt else trunc(add_months(start_dt, level-1), 'mm') 
       end start_mth,
       case 
         when connect_by_isleaf = 1 then end_dt else trunc(add_months(start_dt, level), 'mm')-1
       end end_mth
from   event e
connect by prior start_dt = start_dt
and     level <= months_between(trunc(end_dt, 'mm'), trunc(start_dt, 'mm')) + 1
and     prior sys_guid() is not null
order   by 1, 3;

ID  ROOM_ID  START_MTH    END_MTH      
1   1        03-AUG-2016  30-AUG-2016  
2   1        30-AUG-2016  31-AUG-2016  
2   1        01-SEP-2016  02-SEP-2016  
3   1        10-SEP-2016  20-SEP-2016  
4   1        21-SEP-2016  30-SEP-2016  
4   1        01-OCT-2016  01-OCT-2016  
5   1        01-OCT-2016  31-OCT-2016  
5   1        01-NOV-2016  30-NOV-2016  
5   1        01-DEC-2016  31-DEC-2016  
5   1        01-JAN-2017  01-JAN-2017

You can then use these values to calculate your utilization.