#!/usr/bin/bash
ORACLE_HOME=/u02/app/oracle/product/19.0.0.0/dbhome_1
for v_inst in `ps -ef| grep pmon | awk '{print $8}' | grep -iv asm | grep -iv apx | awk -F"_" '{print $3}'`
do
echo "Instance name = $v_inst"
export ORACLE_SID=$v_inst
# Fetch the list of PDBs (excluding the Seed PDB)
# We use -S (silent) to keep the output clean for the loop
PDB_LIST=$(sqlplus -S / as sysdba <<EOF
SET HEAD OFF FEEDBACK OFF PAGES 0
SELECT name FROM v\$pdbs WHERE name != 'PDB\$SEED';
EXIT;
EOF
)
echo "----------------------------------------------------"
echo "Iterating through PDBs for SID: $ORACLE_SID"
echo "----------------------------------------------------"
for PDB in $PDB_LIST
do
echo "Checking PDB: $PDB"
sqlplus -S / as sysdba <<EOF
set lines 120 pages 200
ALTER SESSION SET CONTAINER = $PDB;
SHOW CON_NAME;
EXIT;
EOF
echo "----------------------------------------------------"
done
done
Category: SQL scripts
runon_allcdbs_find_pdbs.sql
#!/usr/bin/bash
ORACLE_BASE=/u02/app/oracle
ORACLE_HOME=/u02/app/oracle/product/19.0.0.0/dbhome_1
LOGNAME=runon_all_localdb_$$.log
for v_inst in `ps -ef| grep pmon | awk '{print $8}' | grep -iv asm | grep -iv apx | awk -F"_" '{print $3}'`
do
echo "Instance name = $v_inst"
export ORACLE_SID=$v_inst
$ORACLE_HOME/bin/sqlplus "/ as sysdba" << EOF >> ${LOGNAME}
show user
set echo off
set numwidth 9
set linesize 132
set pagesize 300
set feedback off
set verify off
set time off
set timing off
--select * from v\$instance;
@/u02/scripts/paresh/find_pdbs.sql
--select PROPERTY_VALUE from database_properties where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
EOF
done
cat ${LOGNAME}
rm ${LOGNAME}
Running PDB on single node in RAC
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…
find_arc.sql
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…
pvm_pre_change.sql
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…
track_autoupgrade_copy_progress.sql
This script will be useful for tracking progress of GBS copied to Target system. Need to execute it on Target system.
refre.sql for multitenant
This will provide prompt with session details.
tracksqltime.sql
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:
Trace a SQL session from another session using ORADEBUG
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” »
load SPM baseline from cursor cache
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);