Video on the Maximum Availability Protection Mode in Oracle DataGuard (with RAC)

 I've published a new video demonstrating Maximum Availability Protection Mode in Oracle DataGuard.

Testing updated ORA-942 Error Message in 23ai

 Oracle 23ai now has a long-requested fix to the ORA-942 "table or view does not exist" error message.  The error message would not print out *which* table or view was missing -- this was particularly troublesome with very long SQL statements and multiple tables and views being referenced.

A demo of the fixed behaviour in 23ai  (followed later by the same code in 19c 19.22) 

SQL> select banner from v$version;

BANNER
---------------------------------------------------------------------------------------------------------------------------------
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free

SQL>
SQL> -- create the two tables
SQL> drop table employees;
drop table employees
           *
ERROR at line 1:
ORA-00942: table or view "HEMANT"."EMPLOYEES" does not exist
Help: https://docs.oracle.com/error-help/db/ora-00942/


SQL> drop table departments;

Table dropped.

SQL> create table departments (dept_id number(12) primary key, dept_name varchar2(30));

Table created.

SQL> create table employees (emp_id number(12) primary key, emp_name varchar2(54), dept_id number(12));

Table created.

SQL>
SQL> -- insert data
SQL> insert into departments values (1,'Human Resources');

1 row created.

SQL> insert into employees values (1,'Somebody',1);

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> -- test query
SQL> select d.dept_id, d.dept_name, e.emp_name
  2  from departments d, employees e
  3  where e.dept_id = d.dept_id
  4  order by d.dept_id,e.emp_name
  5  /

   DEPT_ID DEPT_NAME                      EMP_NAME
---------- ------------------------------ ------------------------------------------------------
         1 Human Resources                Somebody

SQL>
SQL> -- drop a table
SQL> drop table employees;

Table dropped.

SQL>
SQL> -- RETEST query
SQL> select d.dept_id, d.dept_name, e.emp_name
  2  from departments d, employees e
  3  where e.dept_id = d.dept_id
  4  order by d.dept_id,e.emp_name
  5  /
from departments d, employees e
                    *
ERROR at line 2:
ORA-00942: table or view "HEMANT"."EMPLOYEES" does not exist
Help: https://docs.oracle.com/error-help/db/ora-00942/


SQL>

So, now the error message text for ORA-942 includes the name of the missing <schema>.<table_or_view>

Here is the same code in 19c (19.22) 

SQL> --select banner from v$version;
SQL> -- use BANNER_FULL in 19c to get RU level -- 19.22
SQL> select banner_full from v$version;

BANNER_FULL
------------------------------------------------------------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0


SQL>
SQL> -- create the two tables
SQL> drop table employees;
drop table employees
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> drop table departments;

Table dropped.

SQL> create table departments (dept_id number(12) primary key, dept_name varchar2(30));

Table created.

SQL> create table employees (emp_id number(12) primary key, emp_name varchar2(54), dept_id number(12));

Table created.

SQL>
SQL> -- insert data
SQL> insert into departments values (1,'Human Resources');

1 row created.

SQL> insert into employees values (1,'Somebody',1);

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> -- test query
SQL> select d.dept_id, d.dept_name, e.emp_name
  2  from departments d, employees e
  3  where e.dept_id = d.dept_id
  4  order by d.dept_id,e.emp_name
  5  /

   DEPT_ID DEPT_NAME                      EMP_NAME
---------- ------------------------------ ------------------------------------------------------
         1 Human Resources                Somebody

SQL>
SQL> -- drop a table
SQL> drop table employees;

Table dropped.

SQL>
SQL> -- RETEST query
SQL> select d.dept_id, d.dept_name, e.emp_name
  2  from departments d, employees e
  3  where e.dept_id = d.dept_id
  4  order by d.dept_id,e.emp_name
  5  /
from departments d, employees e
                    *
ERROR at line 2:
ORA-00942: table or view does not exist


SQL>

So, upto 19c, ORA-942 would simply say "table or view does not exist".  In my example using sqlplus and a very short from list, the "*" does indicate where the error is -- but with other tools / clients and long FROM or sub-queries with FROM clauses, it may well be difficult to identify the missing table or view unless you trace the execution or run a test query against each table or view.