Query to find the dbms_jobs running
To find the job details set pages 1000 lines 1000 col schema_user for a20 col this_date for a20 col next_date for a20 col what for a80 col instance for 999999999…
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 MoreScript to check duplicate objects in SYS and SYSTEM
Please use the below query to check the same. column object_name format a30 select object_name, object_type from dba_objects where object_name||object_type in (select object_name||object_type from dba_objects where owner = ‘SYS’) and…
Read MoreInstalling sqlt
Download sqlt_10g_11g_12c_18c_19c_5th_June_2020.zip set db environment cd /home/oracle unzip sqlt_10g_11g_12c_18c_19c_5th_June_2020.zip cd sqlt/install sqlplus / as sysdba sql> start sqlcreate.sql Enter password for SQLTXPLAIN Enter Tablespace name Enter Temp tablespace name…
Read More