02 June, 2024

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.

26 May, 2024

Testing Open a PDB as a Hybrid Read Only PDB in 23ai

 Oracle 23ai now allows the DBA to open a PDB in Hybrid Read Only mode.  This mode allows Common Users (e.g. SYS or SYSTEM or others defined as Common Users from the Root CDB) to access a PDB in Read-Write mode while local (i.e. non-Common Users) can access the PDB only in Read-Only mode.

This facilitates live maintenance (e.g. patching or changes to the database / schema) being executed by a DBA or Common User while "normal" local users (eg. Application Accounts) can still query the database.

This is a quick demo :


SQL> -- open the PDB as "normal" Read Write
SQL> connect / as sysdba
Connected.
SQL> -- Version 23ai Free Edition
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> alter pluggable database freepdb1 close;

Pluggable database altered.

SQL> alter pluggable database freepdb1 open;

Pluggable database altered.

SQL>
SQL> -- first demo a normal user in the PDB
SQL> connect hemant/hemant@freepdb1
Connected.
SQL> create table x_test (id number , data varchar2(15));

Table created.

SQL> insert into x_test values (1,'First');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from hemant.x_test order by 1;

        ID DATA
---------- ---------------
         1 First

SQL>
SQL>
SQL> -- now close and open the PDB in Hybrid Read Only mode
SQL> connect / as sysdba
Connected.
SQL> alter pluggable database freepdb1 close;

Pluggable database altered.

SQL> alter pluggable database freepdb1 open hybrid read only ;

Pluggable database altered.

SQL> -- test that SYSTEM (a Common User) can manipulate data -- e.g. INSERT
SQL> connect system/manager@freepdb1
Connected.
SQL> insert into hemant.x_test values(2,'System');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from hemant.x_test order by 1;

        ID DATA
---------- ---------------
         1 First
         2 System

SQL> -- even grant DBA to hemant
SQL> grant dba to hemant;

Grant succeeded.

SQL>
SQL> -- test hemant a non-common user
SQL> -- see if the user can execute INSERT and SELECT
SQL> connect hemant/hemant@freepdb1
Connected.
SQL> insert into hemant.x_test values(3,'Third');
insert into hemant.x_test values(3,'Third')
                   *
ERROR at line 1:
ORA-16000: Attempting to modify database or pluggable database that is open for
read-only access.
Help: https://docs.oracle.com/error-help/db/ora-16000/


SQL> commit;

Commit complete.

SQL> select * from hemant.x_test order by 1;

        ID DATA
---------- ---------------
         1 First
         2 System

SQL> -- so SELECT works, but not INSERT
SQL>
SQL>
SQL> -- reopen PDB as normal "Read Write"
SQL> connect / as sysdba
Connected.
SQL> alter pluggable database freepdb1 close;

Pluggable database altered.

SQL> alter pluggable database freepdb1 open;

Pluggable database altered.

SQL> connect hemant/hemant@freepdb1
Connected.
SQL> insert into hemant.x_test values(4,'Fourth');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from hemant.x_test order by 1;

        ID DATA
---------- ---------------
         1 First
         2 System
         4 Fourth

SQL>


Thus, when the PDB was first opened in "normal" mode (ie the default OPEN mode is OPEN READ WRITE), the local user  "HEMANT" could execute DDL and DML (create the table and Insert).  

When it was reopened in Hybrid Read Only Mode, the user could not make changes (insert the row with ID=3) but could still query the data (even if the user has been granted "DBA").  However,  the Common User "SYSTEM" was allowed to insert the row with ID=2, DATA='SYSTEM'.

Finally, reopening the PDB in "normal" OPEN READ WRITE mode, the user "HEMANT" could again insert a row (ID=4)

21 May, 2024

Testing RENAME LOB (Segment) in 23ai

Another new feature of 23ai is the ability to rename a LOB (Segment) in-place without having to use the MOVE clause.

A quick demo :


SQL> -- Version 23ai Free Edition
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>
SQL> DROP TABLE my_lob_objects purge;

Table dropped.

SQL>
SQL> -- create the table with a LOB, column name "c",  lob segment name also "c"
SQL> CREATE TABLE my_lob_objects (object_id NUMBER primary key, c CLOB)
  2        lob (c) STORE AS SECUREFILE c
  3        ( TABLESPACE users
  4          DISABLE STORAGE IN ROW
  5          NOCACHE LOGGING
  6          RETENTION AUTO
  7          COMPRESS
  8        );

Table created.

SQL>
SQL> -- query the data dictionary
SQL> select table_name, column_name, segment_name, tablespace_name from user_lobs;

TABLE_NAME       COLUMN_NAME      SEGMENT_NAME         TABLESPACE_NAME
---------------- ---------------- -------------------- ----------------
MY_LOB_OBJECTS   C                C                    USERS

SQL>
SQL> -- insert three rows
SQL> insert into my_lob_objects values (1, dbms_random.string('X',100));

1 row created.

SQL> insert into my_lob_objects values (2, dbms_random.string('X',100));

1 row created.

SQL> insert into my_lob_objects values (3, dbms_random.string('X',100));

1 row created.

SQL>
SQL> -- verify the column name when querying the table
SQL> select * from my_lob_objects;

 OBJECT_ID C
---------- --------------------------------------------------------------------------------
         1 IBGOGKA9QKK56O746IJL3C56ZK9LEO0G1W4LWBN11T8EWCFTTLUW9TPIVQAU8BPSGPQ2ZV57BS0ZPK0S
         2 7K04DVVYDQB1URIQ1OQ2458M8ZOURHWW50XIZDMVGAZH6XVN2KKN4PIGKPY5CSVIQ9KU45LHZPJB33AA
         3 2G5194Z7TSR3XG0K698G587AOZOJ8VN6KFCTCH3074TNCOWCSMOPRJLRGTLIZMDD73XAY4KDD14IW4MG

SQL>
SQL> -- now rename the column
SQL> alter table my_lob_objects rename column c to clob_col;

Table altered.

SQL>
SQL> -- query the data dictionary
SQL> select table_name, column_name, segment_name, tablespace_name from user_lobs;

TABLE_NAME       COLUMN_NAME      SEGMENT_NAME         TABLESPACE_NAME
---------------- ---------------- -------------------- ----------------
MY_LOB_OBJECTS   CLOB_COL         C                    USERS

SQL>
SQL> -- now rename the lob segment
SQL> alter table my_lob_objects rename lob(clob_col) c to my_lob_objects_clob;

Table altered.

SQL>
SQL> -- query the data dictionary
SQL> select table_name, column_name, segment_name, tablespace_name from user_lobs;

TABLE_NAME       COLUMN_NAME      SEGMENT_NAME         TABLESPACE_NAME
---------------- ---------------- -------------------- ----------------
MY_LOB_OBJECTS   CLOB_COL         MY_LOB_OBJECTS_CLOB  USERS

SQL>
SQL> -- verify the column name when querying the table
SQL> select * from my_lob_objects;

 OBJECT_ID CLOB_COL
---------- --------------------------------------------------------------------------------
         1 IBGOGKA9QKK56O746IJL3C56ZK9LEO0G1W4LWBN11T8EWCFTTLUW9TPIVQAU8BPSGPQ2ZV57BS0ZPK0S
         2 7K04DVVYDQB1URIQ1OQ2458M8ZOURHWW50XIZDMVGAZH6XVN2KKN4PIGKPY5CSVIQ9KU45LHZPJB33AA
         3 2G5194Z7TSR3XG0K698G587AOZOJ8VN6KFCTCH3074TNCOWCSMOPRJLRGTLIZMDD73XAY4KDD14IW4MG

SQL>
SQL> -- identify the segment
SQL> select tablespace_name, segment_name, segment_type, bytes/1024 Size_KB
  2  from user_segments
  3  where segment_name = 'MY_LOB_OBJECTS_CLOB'
  4  /

TABLESPACE_NAME  SEGMENT_NAME         SEGMENT_TYPE         SIZE_KB
---------------- -------------------- ------------------ ---------
USERS            MY_LOB_OBJECTS_CLOB  LOBSEGMENT              2304

SQL>



First I create a Table where the Column and LOB (Segment) are both called "C".  In recent versions, SECUREFILE is the default and recommended for LOBs (e.g. with the COMPRESS, DEDUPLICATION and ENCRYPTION advantages).

Then I insert 3 rows.

I then rename the column "C" to "CLOB_COL".

Next, I rename the LOB (Segment) to "MY_LOB_OBJECTS_CLOB".  I include the Table Name because the LOB segment is an independent segment that I might query in USER_SEGMENTS (where Table Name) is not available.  This RENAME LOB clause is new in 23ai and does not require the use of MOVE LOB.


I then verify the new Segment Name for the LOB as well.

Yes, the 2,304KB "size" seems excessive but this will make sense (with the COMPRESS attribute) when the LOB grows much much larger as new rows with long Character-Strings are inserted.