There seems to be nothing readymade equivalent to Microsoft’s SQL Profiler that allows us to monitor SQL statements executing at the database. There are a lot of sites that talk about using v$session view, using a complex SQL query, for example this one. All I wanted to do was look at the exact query sent from my application. v$sql has a huge amount of all kinds of SQL even internal to Oracle system databases, hence some filtering is needed. I found that the following simple query worked well for me:

select sql_text from v$sql
where UPPER(sql_text) like '%CSV_%'
order by last_load_time desc

Replace the like condition with the name of some table that you know. In case all your tables have a particular prefix, you could use that prefix and get every query that was related to your application. More complex tracing can be done using SQL_TRACE and enabling some profiling.