Skip to Main Content
  • Questions
  • Need some Oracle Request syntaxe to extract informations

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Badr Eddine.

Asked: May 21, 2018 - 2:44 pm UTC

Last updated: May 28, 2018 - 3:07 am UTC

Version: 12c Release 2

Viewed 1000+ times

You Asked

hy all, it's my first discussion that i post . please help me:

i work on a solution of supervision of oracle database, and i need two request syntaxe to extract informations:
The first: the requset to show the most Oracle query consume cpu time and the user correspond in 1 week or 1 day.
The second: the request to show the users who are failed over than 3 times in connction to Oracle.

I have a little knowledge of oracle ( iknow just the basics). i don't know where i could find informations (tables or vue v$ ...) .
thanks a lot for guiding me. i will be thankfull : )
Best regards : )

and Connor said...

You really want to be looking at using AWR and understanding the depth of details available to you there. It can report on WAY WAY more than just bad sql.

https://docs.oracle.com/en/database/oracle/oracle-database/18/tgdba/gathering-database-statistics.html#GUID-EB7B34CD-6F4B-4010-9A8A-E1C7B8F2F851

But if you want a quick query to show you potentially expensive SQL, you can run:

select sql_text, buffer_gets,  executions, sql_id, elapsed_time
from v$sqlstats
where buffer_gets > 1000000
 or executions > 10000
 or disk_reads > 100000
order by 2


and adjust the numbers accordingly for your database.

For tracking failed connections, I'd recommend auditing.

SQL> audit session;

Audit succeeded.

SQL> conn scott/tiger
Connected.

SQL> conn scott/bad
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.


Then just query DBA_AUDIT_TRAIL, for example

select * from dba_audit_trail
where action_name = 'LOGON'


and examine the RETURN_CODE column for success or failure.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.