Problem:You want to insert dates into a table and retrieve them.SolutionOracle, by default the uses the format DD-MON-YYYY to represent a date, whereDD represents a two-digit day.MON represents first three letters of the month, e.g., FEB.YYYY is a four-digit year, e.g., 1968Let us take a look at an example of ... Read More

Problem:You want to UNPIVOT results in Oracle.SolutionThe UNPIVOT clause is new for Oracle Database 11g and enables you to flip the columns into rows in the output from a query, and, at the same time, allow you to run an aggregation function on the data.Consider a table called customer which ... Read More

Problem:You want to PIVOT results in Oracle.SolutionThe PIVOT clause is new for Oracle Database 11g and enables you to flip the rows into columns in the output from a query, and, at the same time, allow you to run an aggregation function on the data.PIVOT is especially useful to look ... Read More

Problem:You want to monitor the usage of the temporary tablespace in Oracle.Solution:We can use the following query to find out the used and free space in a temporary tablespace.We will begin with identifying temporary table space names.ExampleSELECT tablespace_name FROM dba_tablespaces WHERE contents = 'TEMPORARY';OutputTEMPNext up we will use below SQL ... Read More

Problem:You want to find out the recent important waits in your database, as well as the users, SQL statements, and objects that are responsible for most of those waits.SolutionOracle provides us with V$ACTIVE_SESSION_HISTORY to get information about the most common wait events, and the SQL statements, database objects, and users ... Read More

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