--这个
WITH t1 AS
 (SELECT NAME, '#' || OTHERS OTHERS FROM temp)
SELECT DISTINCT NAME,
                substr(t1.others,
                       instr(t1.others, '#', 1, LEVEL) + 1,
                       decode(instr(t1.others, '#', 1, LEVEL + 1),
                              0,
                              length(OTHERS) + 1,
                              instr(t1.others, '#', 1, LEVEL + 1)) - instr(t1.others, '#', 1, LEVEL) - 1) s
  FROM dual, t1
CONNECT BY LEVEL <=
           (SELECT length(OTHERS) - length(REPLACE(OTHERS, '#')) FROM t1 b WHERE b.name = t1.name)
ORDER BY NAME;

------解决方案--------------------
SQL> select * from ta;
  
NAME OTHERS
---- ----------
小A  yyy#mmm#dd
小B  zzz#nnn#ss
  
SQL>  
SQL> select distinct name,
 2         substr('#' || others || '#',
 3                instr('#' || others || '#'  , '#', 1, level) +1,
 4                instr('#' || others || '#'  , '#', 1, level + 1) -
 5                instr('#' || others || '#'  , '#', 1, level)-1) others
 6    from ta
 7  connect by level <= (length(others)-length(replace(others,'#')))/length('#') +1
 8  order by name;
  
NAME OTHERS
---- ------------------------
小A  dd
小A  mmm
小A  yyy
小B  nnn
小B  ss
小B  zzz
  
6 rows selected