Relational Databases

  With the latest Oracle Database 19c Release Update (RU) 19.28, several key features previously available only in Oracle 23ai are now supported. This update introduces four notable capabilities that enhance developer productivity and simplify database management. DBMS_DEVELOPER PL/SQL Package The  DBMS_DEVELOPER  package offers a faster, more flexible way to retrieve metadata for database objects. Instead of XML, it returns metadata in JSON format, making integration with tools and services easier. It also allows adjusting the level of detail returned, improving performance and control. IF [NOT] EXISTS Syntax Support The  IF [NOT] EXISTS  clause is now supported in DDL operations for creating, altering, and dropping objects. This helps avoid errors when objects already exist or don’t exist, making scripts more reliable and easier to maintain. Schema Annotations With schema annotations, developers can attach structured metadata to database objects using simple n...

  When creating an object, if that object already exists, an “already exists” error will occur. Similarly, if you try to drop an object that does not exist, you’ll encounter a “does not exist” error. To prevent these two errors, you can use the   IF [NOT] EXISTS   clause. For example, if a table named  TB  already exists but you're not sure about it, you can use the  IF NOT EXISTS  clause in the  CREATE TABLE  statement to avoid a possible ORA-00955 error: SQL > create table tb (c1 number( 10 )); ORA -00955 : name is already used by an existing object SQL > create table if not exists tb (c1 number( 10 )); Table created SQL > create table if not exists tb (c1 number( 10 )); Table created This clause is also applicable to other DDL statements: SQL > drop sequence ss; ORA -02289 : sequence does not exist SQL > drop sequence if exists ss; Sequence dropped SQL > create user usef identified by ss; ORA -0...

  One of the improvements of the AutoUpgrade tool in version 21c is the automation of certain steps involved in upgrading a RAC database. In versions prior to Oracle 21c, the following steps had to be performed manually to upgrade a RAC database: Set the  CLUSTER_DATABASE  parameter to  FALSE . Stop all instances in the cluster. Start one of the instances to begin the upgrade process. Reset the  CLUSTER_DATABASE  parameter to  TRUE  after the upgrade is complete. Start all instances. Register the new version in the cluster. In version 21c, these steps are automatically handled by the AutoUpgrade tool (this feature was later backported to Oracle version 19.8 and above as well). The method for upgrading a database to version 21c using the AutoUpgrade tool has already been explained previously, so we will not repeat it here. We will only emphasize that before starting the upgrade process,  all instances must be in the open state . Zoom image wil...

  Up until Oracle version 11.2.0.2, there was still a limitation on renaming users. However, starting from that version, a hidden parameter called   _enable_rename_user   was introduced, which made it possible to rename users using a single command (although only in restricted session mode). In the example below, the user named  usef  will be renamed to  ali : SQL > alter session set “_enable_rename_user” = true ; Session altered. SQL > alter system enable restricted session; System altered. SQL > alter user usef rename to ali identified by a; User altered. SQL > alter system disable restricted session; System altered. Part of the trace file output related to the  ALTER USER RENAME  command: delete from user $ where user # = : 1 insert into user $( user #,name,password,ctime,ptime,datats#,tempts#,type#,defrole,resource$,ltime,exptime,astatus,lcount,defschclass,spare1,spare4,ext_username,spare2) values (: 1 ,: 2 ,...

  error_message_details   is another new parameter introduced in Oracle version 23ai, which allows displaying detailed information about data value-related errors. By setting this parameter to   ON , we can see the exact value that caused errors such as   ORA-00001: unique constraint violated   within the error message itself. For example, in the following scenario, enabling the  error_message_details  parameter helps identify the value that triggered the error: SQL > create table tbl1(id number primary key); Table created. SQL > variable B number; SQL > exec :B: = 2547 ; PL / SQL procedure successfully completed. SQL > insert into tbl1 values (:B); 1 row created. SQL > insert into tbl1 values (:B); ERROR at line 1 : 'ORA-00001: unique constraint (USEF.SYS_C008328) violated on table USEF.TBL1 columns (ID)' 'ORA-03301: (ORA-00001 details) row with column values (ID:2547) already exists' Help: https: / / docs.oracle.com ...

  ANY_VALUE   is a new function introduced in Oracle 21c, and it is also available in the later Release Updates of Oracle 19c (i.e., from 19.8 onward). In this article, we will get familiar with this function. The following query is executed in  pdb1  and is intended to determine how many datafiles each tablespace has: select t.ts #, t.name, count(*) "Tedad_DataFile" from v$datafile d, v$tablespace t where t.ts # = d.ts# group by t.ts #, t.name; As you can see, in the above query, both columns  ts#  and  name  are included in the  GROUP BY  clause. However, omitting the  name  column from the  GROUP BY  clause does not change the result, but Oracle does not allow this: ORA- 00979 : not a GROUP BY expression Prior to Oracle 21c, to work around this limitation, we could use aggregate functions like  MIN  or  MAX : select t.ts #, min(t.name), count(*) "Tedad_DataFile" from v$datafile d, v$t...