Configuring a PDB to Listen on a Specific RAC Node in Oracle Database In Oracle Real Application Clusters (RAC) environments, you may need to restrict a Pluggable Database (PDB) to run on specific nodes. Here’s how to configure PDB MYPDB to listen only on node1: Step-by-Step Configuration 1. Configure the PDB State on Node 1…
Monitoring Oracle Archive Logs: A Quick Guide As an Oracle DBA, keeping a close eye on your archived redo logs is crucial for maintaining database health and ensuring smooth recovery operations. Today, I’ll share a simple yet powerful script that helps you monitor your archive logs effectively. The Power of v$archived_log The v$archived_log view is…
Automating Oracle Invalid Object Recompilation: A Practical Script As Oracle DBAs, we frequently encounter situations where database objects become invalid after patches, upgrades, or schema changes. Manually recompiling these objects can be time-consuming, especially in large databases. Today I’ll share a practical script that automates the generation of recompilation commands for all invalid objects in…
This script will be useful for tracking progress of GBS copied to Target system. Need to execute it on Target system.
This will provide prompt with session details.
This PL/SQL block identifies recently active SQL queries from a specific application server and provides details on their performance and execution plans. Here is a breakdown of what the script does:
Find the SPID from Oracle’s SID using below SQL. REM **** This is used to get SPID from SID.col username format a30col machine format a20col program format a40accept _sid prompt ‘Enter Oracle Session ID ->’select a.sid, b.pid, b.spid, a.username, a.program,a.machinefrom v$session a,V$process bwhere a.paddr = b.addrand a.sid = &_sid/ Use ORADEBUG command as below. oradebug…
Read More “Trace a SQL session from another session using ORADEBUG” »
set serveroutput onaccept v_sqlid prompt ‘SQLID:’accept v_phv prompt ‘PHV:’ var pvm numberbegin:pvm := dbms_spm.load_plans_from_cursor_cache(sql_id=>’&v_sqlid’, plan_hash_value=>&v_phv, fixed =>’NO’, enabled=>’YES’);end;/ EXEC dbms_output.put_line(‘Number of plans loaded: ‘ || :pvm);
declaremyplan pls_integer;beginmyplan:=DBMS_SPM.drop_sql_plan_baseline (sql_handle => ‘&sql_handle’);end;/
accept v_sqlid prompt ‘Enter sqlid ‘accept v_phv prompt ‘Enter PHV ‘set serveroutput on DECLAREv_first_snapid number;v_last_snapid number;v_sqlset varchar2(1000);loaded_plans number; BEGINselect max(ss.snap_id)-3, max(ss.snap_id)into v_first_snapid, v_last_snapidfrom DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SSwhere sql_id = ‘&v_sqlid’and plan_hash_value = ‘&v_phv’and ss.snap_id = S.snap_idand ss.instance_number = S.instance_numberand executions_delta > 0 ; loaded_plans := dbms_spm.load_plans_from_awr( begin_snap=>v_first_snapid,end_snap=>v_last_snapid,basic_filter=>q’# sql_id=’&v_sqlid’ and plan_hash_value=’&v_phv’ #’ ); dbms_output.put_line(‘loaded-plans= ‘ ||…
