SELECT
   h.sample_time,
   u.username,
   h.program,
   h.module,
   s.sql_text,
   H.SQL_EXEC_START,
   H.WAIT_TIME
FROM
   DBA_HIST_ACTIVE_SESS_HISTORY h,
   DBA_USERS u,
   DBA_HIST_SQLTEXT s
WHERE  
          h.user_id=u.user_id
   AND h.sql_id = s.sql_iD
   AND u.USERNAME = 'username'
   AND TO_DATE (TO_CHAR(h.sample_time , 'DD/MM/YYYY') , 'DD/MM/YYYY') = TO_DATE (TO_CHAR(SYSDATE, 'DD/MM/YYYY') , 'DD/MM/YYYY') - 1
ORDER BY h.sample_time desc

SELECT 
SQL_EXEC_ID,
TO_CHAR(SQL_EXEC_START,'YYYY-MM-DD HH24:MI:SS') SQL_START,
TO_CHAR(MIN(SAMPLE_TIME),'YYYY-MM-DD HH24:MI:SS') FIRST_SAMPLE,
TO_CHAR(MAX(SAMPLE_TIME),'YYYY-MM-DD HH24:MI:SS') LAST_SAMPLE,
MAX(SAMPLE_TIME)-MIN(SAMPLE_TIME) ELAPSED_SECONDS,
S.SQL_TEXT
FROM DBA_HIST_ACTIVE_SESS_HISTORY H,
DBA_USERS U,
V$SQL S
WHERE  H.USER_ID=U.USER_ID
   AND H.SQL_ID = S.SQL_ID
   AND U.USERNAME = 'USERNAME'
   --AND H.SQL_ID = '---'

GROUP BY SQL_EXEC_ID,SQL_EXEC_START,S.SQL_TEXT
ORDER BY SQL_EXEC_START DESC ,SQL_EXEC_ID;