To Monitor DB behaviour using SQL queries the DB user should have the SELECT ANY DICTIONARY privilege if you‘re not using a system account. Keep in mind that selecting from some views like AWR views (DBA_HIST_xxx) requires the Enterprise Edition with Diagnostics Pack rsp. Tuning Pack license.

To learn how to select several states of the DB by SQL you may use the free analysis tool „Panorama for Oracle“. Setting the environment variable PANORAMA_LOG_SQL=true before starting the app will log all SQL statements to the console that are executed while using the browser GUI of this app.