create table ABC_MASTER (Part_number varchar(40),Filename varchar(80));

insert into ABC_MASTER values (123, abc*cvbsmdm*cbdjdk(9)*asdre-01);
insert into ABC_MASTER values (124, qwe);

SQL> select part_number, regexp_substr(filename,'[^*]+',1,column_value) filename
  2  from ABC_MASTER,
  3       table(cast(multiset(select level from dual
  4                           connect by level <= regexp_count(filename,'\*')+1)
  5                  as sys.odciNumberList))
  6  order by 1, column_value
  7  /

PART_NUMBER                              FILENAME
---------------------------------------- ---------------
123                                      abc
123                                      cvbsmdm
123                                      cbdjdk(9)
123                                      asdre-01
124                                      qwe