Tracking failed logon attempts
I am doing an audit and I need to be able to track all failed login (logon) attempts. Because the user never gets logged-on to Oracle The Oracle auditing utility…
Read MoreOracle GoldenGate Architecture
Oracle Goldengate supports the replication of data across various heterogeneous platforms. The Goldengate replication topology includes the capture and transfer of the extracted data from the source database, across to…
Read MoreQuery to find datafile used and free space
SELECT Substr(df.tablespace_name,1,20) “Tablespace Name”, Substr(df.file_name,1,40) “File Name”, Round(df.bytes/1024/1024,2) “Size (M)”, Round(e.used_bytes/1024/1024,2) “Used (M)”, Round(f.free_bytes/1024/1024,2) “Free (M)”, Rpad(‘ ‘|| Rpad (‘X’,Round(e.used_bytes*10/df.bytes,0), ‘X’),11,’-‘) “% Used” FROM DBA_DATA_FILES DF, (SELECT file_id, Sum(Decode(bytes,NULL,0,bytes)) used_bytes…
Read MoreFind the sql_text using ospid
Find top pids of oracle user $top -u oracle Enter the os pid and find the sql id select sid, sql_id from v$session s, v$process p where s.paddr = p.addr…
Read MoreQuery to find all executing sql
Query to find all executing sql select sql_text from v$sqlarea where users_executing > 0 / Query to find a particular users current sql Select sql_text from v$sqlarea where (address, hash_value)…
Read MoreQuery to find all constraints in a table
col type format a10 col cons_name format a30 select decode(constraint_type, ‘C’, ‘Check’, ‘O’, ‘R/O View’, ‘P’, ‘Primary’, ‘R’, ‘Foreign’, ‘U’, ‘Unique’, ‘V’, ‘Check view’) type , constraint_name cons_name , status…
Read MoreQuery to find the table with most physica i/o
select table_name,total_phys_io from (select owner ||’.’|| object_name as table_name, sum(value) as total_phys_io from v$segment_statistics where owner!=’SYS’ and object_type=’TABLE’ and statistic_name in (‘physica l reads’,’physical reads direct’, ‘physical writes’,’physical writes direct’)…
Read More