Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • secure crt settings Linux/Unix
  • grep multuple patterns Linux/Unix
  • Example of How To Resize the Online Redo Logfiles Note:1035935.6 Oracle
  • Find nth max and min. Oracle
  • Set Role explaination. Oracle
  • How to Make Trace Files Created by Oracle Readable by All Users ? Oracle
  • For Search and replace unix command. Linux/Unix
  • SAN Linux/Unix
  • 751131.1 New Article Error 2819 While Requesting a Systemstate Dump Oracle
  • move_arch_files.ksh Linux/Unix
  • OPENING A STANDBY DATABASE IN READ-ONLY MODE Oracle
  • Virtual Indexes in Oracle Oracle
  • SQLPLUS COPY command Precautions. Oracle
  • Looping for remote servers and find its database from oratab file. Linux/Unix
  • configUOCIOTTO.ora Oracle

Author: Admin

load SPM baseline from cursor cache

Posted on 05-Jun-2025 By Admin No Comments on 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);

Oracle, SQL scripts

Drop all SPM baselines for SQL handle

Posted on 05-Jun-202505-Jun-2025 By Admin No Comments on Drop all SPM baselines for SQL handle

declaremyplan pls_integer;beginmyplan:=DBMS_SPM.drop_sql_plan_baseline (sql_handle => ‘&sql_handle’);end;/

Oracle, SQL scripts

Load SPM baseline from AWR

Posted on 05-Jun-2025 By Admin No Comments on Load SPM baseline from AWR

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= ‘ ||…

Read More “Load SPM baseline from AWR” »

Oracle, SQL scripts

Drop specific SQL plan baseline – spm

Posted on 05-Jun-202505-Jun-2025 By Admin No Comments on Drop specific SQL plan baseline – spm

set serveroutput onvar res numberexec :res :=DBMS_SPM.DROP_SQL_PLAN_BASELINE (‘&original_sql_handle’,’&original_plan_name’);exec dbms_output.put_line(‘Number of plans dropped: ‘ || :res);

Oracle, SQL scripts

findinfo.sql (SQL for getting CPU and Active session info)

Posted on 27-May-2025 By Admin No Comments on findinfo.sql (SQL for getting CPU and Active session info)

set lines 120 pages 200col dd format a20col inst_id format 99col metric_name format a30col value format 99.99 select x.dd, x.inst_id, x.metric_name, x.value “%CPU”, y.sessfrom(select to_char(sysdate, ‘DD-MON-RRRR:HH24:MI’) DD, inst_id, metric_name, valuefrom gv$sysmetricwhere metric_name like ‘Host CPU Utilization%’ and group_id=2 ) x,(select inst_id, count(1) sess from gv$sessionwhere status = ‘ACTIVE’ and osuser != ‘oracle’group by inst_id) ywhere…

Read More “findinfo.sql (SQL for getting CPU and Active session info)” »

Oracle, SQL scripts

SQL Tracker by SID sqltrackerbysid.sql

Posted on 22-Apr-202522-Apr-2025 By Admin No Comments on SQL Tracker by SID sqltrackerbysid.sql

col SAMPLE_TIME format a25col program format a20col SQL_EXEC_START format a25col machine format a15set lines 140 pages 200alter session set nls_date_format=’DD-MON-RRRR:HH24:MI:SS’; select SAMPLE_TIME, SESSION_ID, SESSION_SERIAL#, USER_ID, sql_ID, Machine, program, SQL_EXEC_STARTfrom gv$active_session_historywhere session_id = &v_session_idand sample_time > sysdate – 1/24order by sample_time/

Oracle, SQL scripts

How to connect to Oracle Database with Wallet with Python.

Posted on 21-Mar-2025 By Admin No Comments on How to connect to Oracle Database with Wallet with Python.

To connect Python to Oracle Database using an Oracle Wallet, you can use the cx_Oracle or oracledb library with Oracle Client libraries (like Oracle Instant Client). Oracle Wallet simplifies secure connections by storing credentials and SSL certificates. ✅ Steps to Connect Python to Oracle using Oracle Wallet Step 1: Install Required Python Package Step 2:…

Read More “How to connect to Oracle Database with Wallet with Python.” »

Oracle, Python/PySpark

JSON/XML Types in Oracle

Posted on 18-Mar-202518-Mar-2025 By Admin No Comments on JSON/XML Types in Oracle

Handling JSON and XML Data in Oracle Oracle provides built-in support for JSON and XML data storage, querying, and manipulation. Below are methods to handle both data types efficiently. 1. Handling JSON Data in Oracle A. Storing JSON Data Example: Creating a Table with JSON Column B. Inserting JSON Data C. Querying JSON Data 1….

Read More “JSON/XML Types in Oracle” »

Oracle

CPU Core related projections

Posted on 12-Mar-202512-Mar-2025 By Admin No Comments on CPU Core related projections

Rule of Thumb for Estimating Concurrent DB Connections on a 24-Core Machine (Single Thread Each) The number of concurrent database connections a 24-core machine can handle depends on various factors, including: Hypothetical Calculation Based on CPU A single-threaded, single-core system can typically handle 2-10 concurrent active queries (depending on execution time and parallelism). For a…

Read More “CPU Core related projections” »

AWS, Azure, Linux/Unix, Oracle

Exadata Basics

Posted on 10-Dec-202410-Dec-2024 By Admin No Comments on Exadata Basics

In an Oracle Exadata environment, cell node storage disks are presented to compute nodes as ASM disks, and this mapping occurs through the Oracle ASM (Automatic Storage Management) and Exadata Storage Server software (CellCLI). Here’s a detailed explanation of how the cell node storage disks are mapped to the compute nodes: 1. Cell Node Storage:…

Read More “Exadata Basics” »

Oracle

Posts pagination

1 2 … 56 Next

Categories

  • AWS (2)
  • Azure (1)
  • Linux/Unix (149)
  • Oracle (392)
  • PHP/MYSQL/Wordpress (10)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (17)
  • rman-dataguard (26)
  • shell (149)
  • SQL scripts (341)
  • Uncategorized (0)
  • Videos (0)

Recent Posts

  • load SPM baseline from cursor cache05-Jun-2025
  • Drop all SPM baselines for SQL handle05-Jun-2025
  • Load SPM baseline from AWR05-Jun-2025
  • Drop specific SQL plan baseline – spm05-Jun-2025
  • findinfo.sql (SQL for getting CPU and Active session info)27-May-2025
  • SQL Tracker by SID sqltrackerbysid.sql22-Apr-2025
  • How to connect to Oracle Database with Wallet with Python.21-Mar-2025
  • JSON/XML Types in Oracle18-Mar-2025
  • CPU Core related projections12-Mar-2025
  • Exadata Basics10-Dec-2024

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • How do I get Oracle to automatically start when my server boots up? Oracle
  • longtx.sql with the flag whether session is blocking any DML locks or not. Oracle
  • Implementation of key based authentications Linux/Unix
  • All About oracle password and security from metalink Oracle
  • Good Site for Oracle Internals Oracle
  • remove archfiles only when it is applied to DR rm_archfiles.sh Linux/Unix
  • Find all users who have DML privileges Oracle
  • oracle 11g RAC on vmware Oracle

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme