SQL> with test as
  2    (select 'Cecilia (x)' col from dual)
  3  select
  4    substr(col,
  5           instr(col, '(') + 1,
  6           instr(col, ')') - instr(col, '(') - 1
  7          ) oldfashioned,
  8    substr(regexp_substr(col, '\(\w+\)'),
  9           2,
 10           length(regexp_substr(col, '\(.*?\)')) - 2
 11          ) regexp_10g,
 12    regexp_substr(col, '\((\w+)\)', 1, 1, null, 1) regexp_11g
 13  from test;

OLDFASHIONED    REGEXP_10G      REGEXP_11G
--------------- --------------- ---------------
x               x               x

SQL> with test as
  2    (select 'xxx.yyy.aaa.bbb' col from dual)
  3  select
  4    regexp_substr(col, '\w+$') result
  5  from test;

RESULT
----------
bbb

SQL>