Oracle ETRM
The Oracle E-Business Suite Electronic Technical Reference Manual (eTRM) is a repository of database design and dependency information for Oracle E-Business Suite. Earlier it was available in online in the…
Read More5 different ways to find TOP N queries
Objective: Let us write five different queries to list the top 4 highest paid employees from EMP table. Methods: 1) Using ROWNUM 2) Using ROW_NUMBER () 3) Using RANK() 4)…
Read MoreLife At Doyensys – Velmurugan R
Employees are the backbone of an organisation. With the right people, a company can not only achieve its goals but also surpass them. We want to thank our employees and…
Read MoreQuery to generate AWR report
set lines 500; set pages 500; set long 1000000; SELECT X.SQL_ID, X.CPU_TIME, X.EXECUTIONS, T.SQL_TEXT FROM DBA_HIST_SQLTEXT T, ( SELECT S.SQL_ID SQL_ID, SUM(S.CPU_TIME_DELTA/1000000) CPU_TIME, SUM(S.EXECUTIONS_DELTA) EXECUTIONS FROM DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT P…
Read MoreScript to generate statspack report
set lines 300; set pages 500; set long 1000000; select A.hash_value, A.text_subset, A.module, trunc((B.cpu_time-A.cpu_time)/1000) “CPU_TIME(ms)”, B.executions-A.executions executions, trunc(decode(B.executions-A.executions, 0, 0, (B.cpu_time-A.cpu_time)/(B.executions-A.executions))/1000) “CPU_TIME_PER_EXEC(ms)” from STATS$SQL_SUMMARY A, STATS$SQL_SUMMARY B where A.hash_value =…
Read MoreScript to find Session_longops query for datapump
select x.job_name,b.state,b.job_mode,b.degree , x.owner_name,z.sql_text, p.message , p.totalwork, p.sofar , round((p.sofar/p.totalwork)*100,2) done , p.time_remaining from dba_datapump_jobs b left join dba_datapump_sessions x on (x.job_name = b.job_name) left join v$session y on (y.saddr…
Read MoreScript to Identify the Datapump Progress
set lines 150 pages 100 numwidth 7 col program for a38 col username for a10 col spid for a7 select to_char(sysdate,’YYYY-MM-DD HH24:MI:SS’) “DATE”, s.program, s.sid, s.status, s.username, d.job_name, p.spid, s.serial#,…
Read MoreQuery to check snapshot refresh are successfully completed and refresh for materialized views.
Please use the below query. SELECT DISTINCT(TRUNC(last_refresh)) FROM dba_snapshot_refresh_times; select owner, mview_name, last_refresh_type, last_refresh_date from dba_mviews;
Read MoreQuery to check whether database has any externally authenticated SSL users
Please use the below query. SELECT name FROM sys.user$ WHERE ext_username IS NOT NULL AND password = ‘GLOBAL’;
Read MoreScript to check for corruption in the database dictionary
Set verify off Set space 0 Set line 120 Set heading off Set feedback off Set pages 1000 Spool analyze.sql SELECT ‘Analyze cluster “‘||cluster_name||'” validate structure cascade;’ FROM dba_clusters WHERE…
Read More