create table test (sub_id number, name varchar2(10), main_id number);

insert into test values (1,'abc',1);
insert into test values (2,'abc',1);
insert into test values (3,'abc',1);
insert into test values (4,'efg',2);
insert into test values (5,'efg',2);
insert into test values (6,'ijk',3);
insert into test values (7,'lmn',3);
insert into test values (8,'opq',3);
insert into test values (9,'rst',4);


SQL> select * from test;

    SUB_ID NAME          MAIN_ID
---------- ---------- ----------
         1 abc                 1
         2 abc                 1
         3 abc                 1
         4 efg                 2
         5 efg                 2
         6 ijk                 3
         7 lmn                 3
         8 opq                 3
         9 rst                 4

9 rows selected.

  1  select sub_id, main_id, name from
  2  (
  3  select sub_id, main_id, name, count(*) over(partition by main_id) count_main_id,
  4  count(*) over(partition by main_id, name) count_main_name_id
  5  from test
  6  )
  7* where count_main_id = count_main_name_id
SQL> /

    SUB_ID    MAIN_ID NAME
---------- ---------- ----------
         1          1 abc
         2          1 abc
         3          1 abc
         4          2 efg
         5          2 efg
         9          4 rst

6 rows selected.


  1  select * from test
  2  where main_id in (
  3  select main_id from test
  4  group by main_id
  5  having count(distinct name) = 1
  6* )
SQL> /

    SUB_ID NAME          MAIN_ID
---------- ---------- ----------
         1 abc                 1
         2 abc                 1
         3 abc                 1
         4 efg                 2
         5 efg                 2
         9 rst                 4

6 rows selected.

select * from test
  2  where main_id in (
  3  select main_id from test
  4  group by main_id
  5  having count(distinct nvl(name,'NULL')) = 1
     and count(main_id) >1
  6* )