Query 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, ...
Database
Insights and Experiences from Doyensys’ Database Administrators.
Script 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) ...
Script 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 , ...
Script 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’) ...
Query 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, ...
Query 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, ...
Script 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, ...
Script 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 ...
Script 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, ...
Query 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;
Query 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’;
Script 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 ...
Script 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 ...
Installing 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 ...
MIGRATING 19C DATABASE FROM LINUX TO WINDOWS.
INTRODUCTION The below steps showes us how to migrate a 19c database using Transportable Tablespace. MIGRATION FROM LINUX TO WINDOWS MIGRATION FROM LINUX TO WINDOWS BY TRANSPORTABLE TABLESPACE ...