Problem:You’d like to identify the blocking and the blocked sessions in your database.SolutionWhen we see an enqueue wait event in an Oracle database, the chances are that there is some thing locking or holding up some sessions from executing their SQL statements. When a session waits on an “enqueue” wait ... Read More

Problem:You want to use GROUPING() with ROLLUP, CUBE and SETS functions.SolutionThe GROUPING() function accepts a column and returns 0 or 1. This function returns 1 when the column value is null and returns 0 when the column value is non null. However, GROUPING() is used only in queries that use ... Read More

Problem:You want to gather extended explain plan statistics for a specific query.SolutionWe can use the GATHER_PLAN_STATISTICS hint. This hint when placed within a query at runtime, will generate extended runtime statistics. It basically have two steps.Execute the query with the gather_plan_statistics hint.Use dbms_xplan.display_cursor to display the results.ExampleSELECT /*+ gather_plan_statistics */ ... Read More

Problem:You have a performance issue with a queryhaving JOIN conditions on multiple tables, and you wish to override the join type by placing the appropriate hint in the query.Solution:There are three possible types of joins: nested loops, hash, and sort merge.Nested Loops Join Hint:  To invoke a nested loops join, ... Read More

Problem:You have a performance issue with a queryhaving JOIN conditions on multiple tables, and the Oracle optimizer is not choosing the join order you wanted.Solution:Oracle has two hints the ORDERED hint, and the LEADING hint that can be used to influence the join order used within a query.ORDERED HintYou are ... Read More

Problem:You wanted to know the difference between Union ALL and Insert ALL to generate small amounts of data.Solution:I have been recently generating small amounts of data for testing some functionality and came across a couple of options in Oracle. Union All and Insert All are two options commonly used for ... Read More

Problem:You need to determine if any given year is a leap year.Solution:There are numerous ways of solving this problem.These include calculations to determine whether February 29 exists in the year, or whether March 1 is the 61st or 62nd day of the year.ExampleSELECT to_number(to_char(sysdate, 'YYYY')) Year,   CASE     ... Read More

Problem:You want to find out some indroductory information about the database.Solution:Every Oracle programmer/ DBA has at one point or another during their career inherited a database someone else have already set up.You needs to find out some introductory information about the database to learn more about it.Idenitfy the host details ... Read More

You want to use Oracle aggregate function XMLAGG for string aggregation.?Solution:ExampleSELECT class_id,   rtrim(xmlagg(xmlelement(x, first_name   || ' '   || last_name , ', ') ORDER BY first_name).extract('//text()').getstringval(), ', ') AS "names" FROM students GROUP BY class_id;OutputCL_MATH         ANDERSON DANIEL, MARTINEZ CHRISTOPHER, TAYLOR PAUL, THOMAS MARK, WILSON ... Read More

Problem Statement:You want to perform string concatenation as a comma delimited text in oracle.Solution:Oracle has few methods to perform string aggregation. The most common usuage you popularly find on internet is to convert multiple rows to a single row with a delimiter.Starting Oracle version 11.2, a new built-in function is ... Read More