Current SQL’s running on Instance

Current SQL’s running on Instance This below query useful to find out current running SQL statements of instance, These SQL’s can be either DML or DDL statements or even Select queries. This query useful to track whether that specific SQL is still running or not. set linesize 400 set pagesize 100 col oracle_usrename for a10…

Estimate tablespaces usage before database refresh

 Estimate tablespaces usage before database refresh We have been facing missing tablespaces/inadequate space in tablespaces issue while performing database refresh. The import jobs are failed or suspended due to tablespaces issue it leads delay In database refresh. we have developed the scripts(tablespaces_usage.sh & compare_files.pl)  to work around the issue. These scripts are generate a report…

CPU Usage Queries

CPU usage Queries Historical CPU Usage From the below query we can modify the END_INTERVAL_TIME as required by adjusting from where clause. set linesize 200 set pagesize 120 col module for a60 SELECT   mymodule “Module”, SUM (cpu_time) “CPU Time”, SUM (wait_time) “Wait Time”,          SUM (cpu_time) + SUM (wait_time) “Total Time”     FROM (SELECT a.module…

Scripts for Locks and Blocking sessions

Scripts for Object Locks and Blocking Sessions. Blocking Session using v$lock SELECT    l1.sid || ‘ is blocking ‘ || l2.sid blocking_sessions FROM    v$lock l1, v$lock l2 WHERE    l1.block = 1 AND    l2.request > 0 AND    l1.id1 = l2.id1 AND    l1.id2 = l2.id2; Blocking session with More information SELECT s1.username…

Advanced Table Compression with Oracle Database 11g for OLTP

Advanced Compression with Oracle Database 11g for OLTP Oracle introduced Table Compression from version 9i, to compress data for bulk loaded. Its been introduced from 11gR1 with OLTP Table Compression that allows data to be compressed for all DML’s(Inserts/Updates/Deletes), OLTP Table Compression reduces the associated compression overhead of write operations making it suitable for transactional…

How to Shrink Datafiles – Adjusting HWM

Shrink Datafiles – Adjusting HWM This below example is derived from “Shrinking datafiles” scripts from asktom.oracle.com Scenario:- Lets suppose the datafile size is 100m with autoexted upto maxsize 32gb. 1) Created a table 2) Inserted 50millions of rows with commit — Data file size extended from 100mb to 20gb. 3) performed so many DML’s —…

Interview with Kellyn Pot’Vin

Kellyn Pot’Vin – Oracle ACE & Expert     A talented and accomplished multi-platform database administrator with twelve years of extensive experience in Oracle and SQL Server, numerous years experience in MySQL, Sybase and other database platforms, database group management and technical project management. A proven ability to implement large-scale database servers for enterprise level…

Interview with Y. Emre Baransel

Y. Emre Baransel – Oracle ACE & Expert in High Availability Yunus Emre Baransel is an Oracle Certified Professional with more than 7+ years of experience and much specialized in High Availability. Oracle ACE Member of the TROUG management. Blogger at emrebaransel.blogspot.com, turkceoracle.com Contributed to Oracle RMAN 11g book, published by Oracle Press. Speaked at Oracle…