— fsp.sql rem ———————————————————- rem Created by Navneet Upneja rem rem fsp.sql (Have to be executed as SYS User) rem rem Purpose: Flush Shared Pool just for 1 SQL ID in connected Instance rem analyzed rem rem ————————————————————- set define on define Enter_SQL_ID = ‘&1’; declare v_sql_id varchar2(20) := ‘&Enter_SQL_ID’; begin dbms_output.put_line(‘&1′); for i in… Continue reading Script to Flush Single SQL Statement from Shared Pool

We are having a database server where we have Oracle Database 11.2.0.2 being used for Oracle Business Intelligence. Every Night, we have an ETL job scheduled which runs for around 5 hours pulling database from various other Applications and inserting into the BI database. This server is actually having total of 3 databases. Server is… Continue reading Oracle Database 11.2.0.2 Swap Usage on RHEL 5.1

AIOUG – North India Chapter is delighted to bring a full day Oracle Cloud Event for the 1st time in Noida on 26th March 2016 at Oracle India Noida Office. It is going to be first of its kind event in North India where flavor of the days is going to be “Cloud”. So, If… Continue reading AIOUG – North India Chapter’s “Cloud Day”

ttitle off — ———————————————————————————————- — — Script: sec_detail.sql — Auther: Navneet Upneja — Version: V1.0 — — # set pages 200 set lines 200 col username format a30 COL ACCOUNT_STATUS FORMAT A16 HEADING ‘STATUS’ col user_access format a50 break on username SELECT USERNAME, account_status, listagg(b.granted_role,’ ,’) within group(order by username) user_Access FROM DBA_USERS ,dba_role_privs b… Continue reading Script to list All users with Account status and Roles assigned to them

Following script can be used to check the following information about tables and columns: 1. Table Last Analyzed date 2. Table Distinct Rows and Number of blocks 3. Columns density and Column Distinct Rows undefine tbl_name undefine owner SET VERIFY OFF SET ECHO OFF accept tbl_name prompt ‘Enter Table Name :’ accept owner prompt ‘Enter… Continue reading Column Information (Statistics)

column plan_hash_value format 999999999999999 SELECT sql_id, snap_id, plan_hash_value, SUM(disk_reads_total) disk_reads_total, SUM(executions_total) total_executions, SUM(elapsed_time_total) / ( 1000 * 1000 ) elapstime_Total, SUM(rows_processed_total) total_rows_processed FROM dba_hist_sqlstat WHERE sql_id = ‘&sql_id’ GROUP BY sql_id, snap_id, plan_hash_value ORDER BY snap_id; I personally use this script to check if SQL plan has changed for any sql id in recent past.

Accept sql_id prompt ‘Enter SQL ID:’ column name format a30 column category format a30 column status format a10 select a.name, a.status, a.CREATED, a.LAST_MODIFIED, a.CATEGORY, b.sql_id from DBA_SQL_PROFILES a, (select distinct sql_id,sql_profile from (select sql_id,sql_profile from DBA_HIST_SQLSTAT where sql_id =’&sql_id’ union select sql_id,sql_profile from v$sql where sql_id =’&sql_id’)) b where a.name=b.sql_profile; You can also modidfy the… Continue reading Script to see if There is SQL profile created for any particular SQL ID

I have written a document more of a troubleshooting one which I did to resolve one Performance issue related to wrong Access Path chosen by Optimizer which it needed to fetch around 5% of rows from 110M rows table. Here is the Link the paper..

We have been using DAC 10g in our ETL setup since few years now. It been serving all the purpose very well till now until few days back when suddenly we got a situation or demand where we want Oracle DAC 11g installed. Going through the Oracle’s documentation, upgrading Oracle 10G DAC repository to 11g… Continue reading Migrate Oracle Data Warehouse 10g (DAC) repository to Oracle DAC 11g