Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • upload.html PHP/MYSQL/Wordpress
  • Error Handling in Proc Oracle
  • to see when crontab is changed. Linux/Unix
  • setting prompt display with .profile Linux/Unix
  • How does one overcome the Unix 2 Gig file limit? Linux/Unix
  • Rename Oracle Instance Name Oracle
  • How to stop OCSSD Daemon Oracle
  • How to Use DBMS_STATS to Move Statistics to a Different Database Oracle
  • Reading parameter file and printing Linux/Unix
  • Order by with ROWNUM Oracle
  • Some useful Unix Commands Linux/Unix
  • Find Stale DR Physical Standby Oracle
  • Add new columns in dataframe Python/PySpark
  • set_env_dba Linux/Unix
  • Adding or Dropping Online Redo Log Files When Physical Standby in place Oracle

Category: Oracle

Transfer SQL Profiles from One database to other database.

Posted on 05-Sep-2024 By Admin No Comments on Transfer SQL Profiles from One database to other database.

In this guide, I’ll walk you through moving SQL profiles using a staging table. SQL profiles help databases optimize query execution plans. Step#1 Create the Staging Table First, create a staging table to hold SQL profiles that need to be transferred. SQL> exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF(table_name => ‘SQL_STG_TAB’, schema_name => ‘SYS’); This will create a table SQL_STG_TAB…

Read More “Transfer SQL Profiles from One database to other database.” »

Oracle, shell, SQL scripts

Load testing on Oracle 19C RAC with HammerDB

Posted on 18-Jan-202418-Jan-2024 By Admin No Comments on Load testing on Oracle 19C RAC with HammerDB

When dealing with Oracle 19c Real Application Clusters (RAC), the process of capturing a workload from a source Oracle 19c RAC database and replaying it on a target Oracle 19c RAC database using HammerDB involves some additional considerations. Below are the revised steps: Capture and Replay Workload from Source to Target Oracle 19c RAC Download…

Read More “Load testing on Oracle 19C RAC with HammerDB” »

Oracle

Creating never expiring DB user accounts in Oracle

Posted on 14-Sep-202314-Sep-2023 By Admin No Comments on Creating never expiring DB user accounts in Oracle

DB Users are required to reset the password periodically for better security. For some service accounts,  we need to make sure the password does not expire and does not impact the application. For this purpose, we need to update that user’s Profile. Each Profile has multiple security settings that controls Password_life_time, password_reuse_time, password_reuse_max  settings. These…

Read More “Creating never expiring DB user accounts in Oracle” »

Oracle, SQL scripts

Convert multiple rows to single column

Posted on 13-Sep-202313-Sep-2023 By Admin No Comments on Convert multiple rows to single column

Converting rows to single column: create table emp ( empno number, empname varchar2(100), deptno number); insert into emp values( 1, ’emp1′, 10); insert into emp values( 2, ’emp2′, 20); insert into emp values( 3, ’emp3′, 30); insert into emp values( 4, ’emp4′, 10); insert into emp values( 5, ‘paresh’, 30); insert into emp values( 6,…

Read More “Convert multiple rows to single column” »

Oracle, SQL scripts

Find all users who have DML privileges

Posted on 05-Sep-202305-Sep-2023 By Admin No Comments on Find all users who have DML privileges

Run following query as sysdba. alter session set nls_date_format=’DD-MON-RRRR:HH24:MI:SS’; alter session set nls_date_format=’DD-MON-RRRR:HH24:MI:SS’; select username, created, account_status /* Users who granted role with DML privs */ from dba_users where oracle_maintained = ‘N’ and username in (select grantee from dba_role_privs where granted_role in (select role from dba_roles where role in (select GRANTEE from dba_tab_privs where privilege…

Read More “Find all users who have DML privileges” »

Oracle, SQL scripts

Oracle GoldenGate lag monitoring shell script

Posted on 12-Jun-202318-Jun-2023 By Admin No Comments on Oracle GoldenGate lag monitoring shell script

Following script will be useful for tracking Oracle GoldenGate replicat process and related lag. This script will generate output on standard screen.  You can redirect it using file redirection to logfile or other locations.  while true do <goldengate home directory>/ggsci <<EOF > test.out info all exit EOF lag=`cat test.out | grep TESTREPLICAT | awk ‘{print…

Read More “Oracle GoldenGate lag monitoring shell script” »

Linux/Unix, Oracle, shell

Find which sessions is accessing object that prevent your session to have exclusive locks in Oracle

Posted on 11-Jun-202311-Jun-2023 By Admin No Comments on Find which sessions is accessing object that prevent your session to have exclusive locks in Oracle
Find which sessions is accessing object that prevent your session to have exclusive locks in Oracle

If you want exclusive lock on any table, you need to make sure that no other session is  accessing it. This happens when you want to run DDL or any ALTER statements, that internally needs exclusive lock. Following script will to find the blocker session and provide killing statement for clearing locks.  col owner format…

Read More “Find which sessions is accessing object that prevent your session to have exclusive locks in Oracle” »

Oracle, SQL scripts

ENQ: KO – FAST OBJECT CHECKPOINT tips

Posted on 25-May-202325-May-2023 By Admin No Comments on ENQ: KO – FAST OBJECT CHECKPOINT tips

On reporting queries, there are too many “enq: KO – fast object checkpoint” wait event. All reporting queries are doing full table scans concurrently. Oracle uses direct path reads instead of db file scattered reads. And direct path reads require a checkpoint “_serial_direct_read”=NEVER Parameter Session Value Instance Value ————————————————– —————————— —————————— _serial_direct_read auto auto

Oracle

GSQ.sql

Posted on 19-May-202325-May-2023 By Admin No Comments on GSQ.sql

This SQL will be used to identify SQL details of given Session. You need to pass SID and INSTANCE_ID as INPUT. This query will provide session details, SQL_ID, SQL_EXEC_START time and other transaction level details. set echo off feedback off verify offcol Transaction_id format a20col username format a8col days format 99.99col hours1 format 999999col TX_HRS…

Read More “GSQ.sql” »

Oracle, SQL scripts

DBA_HIST_SQLSTAT contents

Posted on 10-May-202310-May-2023 By Admin No Comments on DBA_HIST_SQLSTAT contents

Query DBA_HIST_WR_CONTROL to see the interval, retention and the TOPNSQL setting.  For TOPNSQL a setting of DEFAULT translates to: 30 where statistics_level is typical 100 where statistics_level is all Simply because this one SQL appears to consume 7% of the load it may not be in the top SQLs in these categories: Elapsed Time CPU…

Read More “DBA_HIST_SQLSTAT contents” »

Oracle

Posts pagination

Previous 1 2 3 … 40 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
  • DBMS_UTILITY.ANALYZE_SCHEMA Oracle
  • remove archfiles only when it is applied to DR rm_archfiles.sh Linux/Unix
  • This is from Temi Oracle
  • runsql_once.ksh Linux/Unix
  • Renaming the column name Oracle
  • crontab syntax Linux/Unix
  • To see mem usage and CPU usage system wide. Linux/Unix
  • Add new columns in dataframe Python/PySpark

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme