How to See When an Oracle Role Was Created

You can get the date and time a role was created from the CTIME column in SYS.USER$. You can see what I mean in the example below, which I tested in an Oracle 11.2.0.4 database. First I created a role between DATE_AND_TIME1 and DATE_AND_TIME2. As they were the same, to the nearest second, the role must have been created at 16:11:05 on 27th May 2016.

I then waited 5 seconds and queried the CTIME column in SYS.USER$ for BLAH. This showed 16:11:05 on 27th May 2016 i.e. the date and time when the role was created:

SQL> select to_char(sysdate,'DD-MON-YY HH24:MI:SS')
  2  date_and_time1
  3  from dual
  4  /

DATE_AND_TIME1
------------------
27-MAY-16 16:11:05

SQL> create role blah
  2  /

Role created.

SQL> select to_char(sysdate,'DD-MON-YY HH24:MI:SS')
  2  date_and_time2
  3  from dual
  4  /

DATE_AND_TIME2
------------------
27-MAY-16 16:11:05

SQL> exec dbms_lock.sleep(5);

PL/SQL procedure successfully completed.

SQL> select to_char(sysdate,'DD-MON-YY HH24:MI:SS')
  2  date_and_time3
  3  from dual
  4  /

DATE_AND_TIME3
------------------
27-MAY-16 16:11:10

SQL> select to_char(ctime,'DD-MON-YY HH24:MI:SS')
  2  role_created
  3  from sys.user$
  4  where name = 'BLAH'
  5  /

ROLE_CREATED
------------------
27-MAY-16 16:11:05

SQL>