You have likely already read about these features. Although they are quite helpful, they are only available to Oracle 23ai users—or at least they were until a few days ago, when anyone using Oracle 19c who updated to Release Update 19.28 could use them.
Now, let’s check out what they are, how to use them, and, most importantly, when they can be useful to us.
IF [NOT] EXISTS
The existence of the specified object will determine whether the CREATE, ALTER, and DROP commands succeed or fail.
For instance, we will get the following error while using CREATE if the object already exists:
SQL> CREATE TABLE test_table (
id NUMBER
);
CREATE TABLE test_table (
*
ERROR at line 1:
ORA-00955: name is already used by an existing objectNow it is possible to add IF NOT EXISTS to avoid these errors when the object already exists.
SQL> CREATE TABLE IF NOT EXISTS test_table (
id NUMBER
);
Table created.However, if the object not exists in the case of DROP, we will get the following error.
SQL> DROP TABLE test_table;
*
ERROR at line 1:
ORA-00942: table or view does not existThese errors go away if we add IF EXISTS.
SQL> DROP TABLE IF EXISTS test_table;
Table dropped.To find out more about this feature, considerations, limitations, and numerous examples, read this article written by the legendary Tim Hall.
SQL Diagnostic Report
A lot of information must be collected to analyze the performance of a query, including the values of the bind variables, the execution plans and their history, the structure of the tables, and their indexes.
We can continue to use the tools that Oracle provides to gather this data, such as SQLTXPLAIN and SQL Tuning Health-Check Script (SQLHC), but wouldn’t it be easier if it came standard?
Indeed, it does: welcome to the DBMS_SQLDIAG package’s REPORT_SQL function.
All we need to know is the SQL_ID. Oracle will output a compressed HTML file with the following name pattern in the directory we designate:
SQLR_{{SQL_ID}}_{{YYYYMMDDHH24MI}}.zip
DECLARE
report CLOB;
BEGIN
report := DBMS_SQLDIAG.report_sql(
sql_id => 'g4d8ysyx34vaz',
directory => 'DATA_PUMP_DIR',
level => 'ALL');
END;
/
$ ls -ltA /u01/app/oracle/19.0.0/db_2/rdbms/log/
total 56
-rw-r--r-- 1 oracle asmadmin 25472 Jul 29 12:15 SQLR_g4d8ysyx34vaz_202507291215.zip
-rw-r----- 1 oracle asmadmin 4098 Jul 28 22:01 qopatch_log.log
drwxr-x--- 3 oracle oinstall 291 Jul 28 22:01 opatch
-rw-r----- 1 oracle asmadmin 1137 Jul 28 03:16 db1928_1_ora_13505.trc
-rw-r----- 1 oracle asmadmin 851 Jul 28 03:16 db1928_1_ora_13484.trc
-rw-r----- 1 oracle asmadmin 1137 Jul 27 15:49 db1928_1_ora_16106.trc
-rw-r----- 1 oracle asmadmin 161 Jul 27 15:43 dp.log
-rw-r----- 1 oracle asmadmin 992 Jul 27 15:38 db1928_1_ora_4332.trcYou can retrieve the contents of the HTML file without creating the compressed file if you have trouble accessing the directory on the database server:
VAR report CLOB;
BEGIN
:report := DBMS_SQLDIAG.report_sql(
sql_id => 'g4d8ysyx34vaz',
level => 'BASIC');
END;
/
SET feedback off
SET trimspool on
SET trim on
SET pagesize 0
SET linesize 32767
SET long 1000000
SET longchunksize 1000000
SPOOL diag_g4d8ysyx34vaz.html
PRINT :report
SPOOL OFFIf you want to check out the contents of the file, here is an example created using the HR sample schema for the following query, with as much detail as possible (level => ‘ALL’).
SELECT dep.department_name,
COUNT(*) emp_count,
MAX(salary) max_salary
FROM employees emp,
departments dep
WHERE emp.department_id = :deptno
AND emp.department_id = dep.department_id
GROUP BY department_name;Check out this article by Ulrike Schwinn for more information on this feature.
Install RU 19.28 now to begin taking advantage of these awesome new features!
