In Oracle, if you want to view an execution plan for a query, you use the DBMS_XPLAN.DISPLAY_CURSOR() procedure. NOTE: The execution plan is totally different than an explain plan; don’t confuse these two. The easiest way to get an execution… Read More
Using DBMS_UTILITY.EXPAND_SQL_TEXT
If you have a query that contains views, or a query where the columns are not all qualified, the DBMS_UTILITY.EXPAND_SQL_TEXT function might be useful. The DBMS_UTILITY.EXPAND_SQL_TEXT function shows you what your query looks like without the views, and with all… Read More
How to Get Row Source Statistics In Oracle
One great way to get time analysis information for a query in Oracle is to use row source statistics. Not everyone has the tuning and diagnostic pack, meaning not everyone can generate SQL Monitor reports. Row source statistics serves as… Read More
How to Create a 10046 Trace File in Oracle
This article will show you how to create a 10046 trace file in Oracle. There are multiple different ways of creating a SQL trace file, including doing an alter session set sql_trace = true but if you want the most… Read More
Using PRAGMA AUTONOMOUS_TRANSACTION in a Function
Here’s a quick example of how to use the AUTONOMOUS_TRANSACTION pragma in a function: If you use the AUTONOMOUS_TRANSACTION pragma in Pl/SQL, it creates a separate transaction from the transaction you are currently working in. Because we included it in… Read More
Easiest Way to Wrap Pl/SQL Code
In this article, I’ve included the easiest possible way to wrap any piece of existing Pl/SQL code, using an anonymous Pl/SQL block. If you’ve stumbled on this page wondering “what does it mean to wrap Pl/SQL?” just know that it… Read More
ORA-12954: The request exceeds the maximum allowed database size of 12 GB
Today, I was gathering statistics on a table when I received ORA-12954: The request exceeds the maximum allowed database size of 12 GB. This threw me for a complete loop, because of a few things: I had a USERS tablespace,… Read More
Where did the DBA_PARAMETERS View Go in Oracle?
If you’re searching for DBA_PARAMETERS or ALL_PARAMETERS or USER_PARAMETERS…it’s because (like me) you’ve forgotten that the name is DBA_ARGUMENTS, not DBA_PARAMETERS. There are 3 steps you can follow to prevent this from happening in the future: 1.) Go clear your… Read More
How To Create A SQL Monitor Report
First of all, you want to ensure that you have the tuning and diagnostic pack for your database–if you do not have the tuning and diagnostic pack, Oracle does not authorize you to create SQL Monitor Reports. Important Information About… Read More
How to Fix “ORA-01489: Result of String Concatenation is Too Long” When Using Listagg
If you’re using the LISTAGG() function in Oracle, and you’ve encountered the error ORA-01489: result of string concatenation is too long it’s because you’re trying to create a comma-delimited list that is too long for a VARCHAR2. In Oracle, a… Read More