Category: SQL scripts

Transfer SQL Profiles from One database to other database.

05-Sep-2024 Admin No Comments

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…

Oracle, shell, SQL scripts

Creating never expiring DB user accounts in Oracle

14-Sep-202314-Sep-2023 Admin No Comments

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…

Oracle, SQL scripts

Convert multiple rows to single column

13-Sep-202313-Sep-2023 Admin No Comments

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,…

Oracle, SQL scripts

Find all users who have DML privileges

05-Sep-202305-Sep-2023 Admin No Comments

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…

Oracle, SQL scripts

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

11-Jun-202311-Jun-2023 Admin No Comments

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…

Oracle, SQL scripts

GSQ.sql

19-May-202325-May-2023 Admin No Comments

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…

Find_planinfo.sql

08-Apr-202308-Apr-2023 Admin No Comments

/******************************************** Author: Paresh Mehta Script Name : find_planinfo.sql********************************************/set lines 200set pages 500set echo offcol execs for 999,999,999col avg_etime for 999,999.999col avg_lio for 999,999,999.9col begin_interval_time for a30col node for 99999col plan_hash_value format 99999999999999col sql_profile format a30col intrvl format a29col component format a20alter session set nls_date_format=’DD-MON-RRRR:HH24:MI:SS’;–break on plan_hash_value,sql_profile on startup_time skip 1accept v_sql_id prompt ‘Enter…

Wait.sql

04-Apr-202313-Sep-2023 Admin No Comments

/********************************************Author: Paresh MehtaScript Name : waitt.sqlwaits order by sql start time********************************************/set feedback 1set pages 2000set lines 230column event format a45column machin format a12column modul format a10column prg format a10column osuser format a11column username format a10column sesinfo format a15column blockedby format a15alter session set nls_date_format=’DDMONRR:HH24:MI:SS’;select /*+ first_rows(100) */ a.sid || ‘,’ || a.serial# || ‘,@’ ||…

get_aix_vmstat.ksh

18-Nov-2010 Admin No Comments

#!/bin/ksh -x # First, we must set the environment . . . . ORACLE_SID=fdprod export ORACLE_SID ORACLE_HOME=/orastdbybin/fdprod/product/10.2 export ORACLE_HOME PATH=$ORACLE_HOME/bin:$PATH export PATH SERVER_NAME=`hostname` typeset -u SERVER_NAME export SERVER_NAME # sample every two minutes (120 seconds) . . . . SAMPLE_TIME=30 while true do vmstat ${SAMPLE_TIME} 2 > /tmp/msg$$ cat /tmp/msg$$|sed 1,7d | awk ‘{ printf(“%s…

Processes Parameter decision

17-Nov-2010 Admin No Comments

According to the query output from V$RESOURCE_LIMIT, the PROCESSES parameter limit is not yet reached. RESOURCE_NAME processes CURRENT_UTILIZATION 102 MAX_UTILIZATION 559 INITIAL_ALLOCATION 2000 LIMIT_VALUE 2000 The maximum value it ever reached was 559, while the limit is set to 2000.

Oracle, SQL scripts