Relational Databases

  Starting with Oracle Database 23ai, you can use column aliases directly in the   GROUP BY   and   HAVING   clauses. This functionality was not available in earlier versions of Oracle Database. Example in Oracle 21c: In Oracle 21c, attempting to use column aliases in the  GROUP BY  or  HAVING  clauses would result in an error: Oracle Database 21 c Enterprise Edition Release 21.0 .0 .0 .0 - Production SQL > SELECT LOWER (owner) AS Malek, SUM (bytes / 1024 / 1024 ) AS SIZE_MB 2 FROM dba_segments 3 GROUP BY Malek 4 HAVING SIZE_MB > 100 ; ERROR at line 4 : ORA -00904 : "SIZE_MB": invalid identifier To avoid the  invalid identifier  error in Oracle 21c, you must rewrite the query without using the alias in the  GROUP BY  and  HAVING  clauses: SELECT LOWER (owner) AS Malek, SUM (bytes / 1024 / 1024 ) AS SIZE_MB FROM dba_segments GROUP BY owner HAVING SUM (bytes / 1024...

  Tables utilizing the LONG data type often present challenges when transitioning to LOB data types. Oracle Database 23ai introduces a powerful enhancement to simplify this process. With this release, the   impdp   utility allows users to convert LONG data types to LOB during data import, making the migration more efficient and streamlined. Oracle has added a new clause to the TRANSFORM parameter, named LONG_TO_LOB. To perform the conversion during import, you simply set this parameter to Y (TRANSFORM=LONG_TO_LOB:Y). Below, I will demonstrate how to use this feature. Step 1: Create a Table with a LONG Datatype Column: SQL > create table tbl (id number, full_name long); Table created. SQL > insert into tbl values ( 1 , 'Vahid Yousefzadeh' ); 1 row created. SQL > commit ; Commit complete. Step 2: Export the Table SQL> create directory dir2025 as '/home/oracle/dump'; Directory created. [oracle@OEL9 ~]$ expdp directory=dir2025 dumpfile=tbl.dmp tables=...

  Oracle introduced the Boolean data type in SQL with version 23ai (although this data type has existed in PL/SQL previously). With this feature, you can define table columns as Boolean during table creation. SQL > CREATE TABLE jadval1 (id NUMBER, is_accept BOOLEAN ); Table created You can also use the keyword  BOOL  instead of  BOOLEAN : SQL > CREATE TABLE jadval1 (id NUMBER, is_accept BOOL); Table created You can set default values for Boolean columns: SQL > CREATE TABLE table1 (id NUMBER, is_accept BOOL DEFAULT FALSE ); Table created To populate the  is_accept  column, you can use the three keywords:  TRUE ,  FALSE , and  NULL : SQL > insert into table1 values ( 1 , true ); 1 row inserted SQL > insert into table1 values ( 2 , null ); 1 row inserted SQL > insert into table1 values ( 3 , false ); 1 row inserted SQL > select * from table1; ID IS_ACCEPT ---------- ---------- ...

  Question:   If a CDB contains multiple PDBs, how can we control the order in which these PDBs open? Answer: Before Oracle 23ai, there was no direct solution for managing the order of PDB openings. Executing the  startup  command provided no guarantee about the opening sequence of the PDBs, as they typically opened based on their container numbers. However, Oracle 23ai introduced a new feature allowing users to assign a  PRIORITY  to each PDB, which determines the order of operations such as opening, upgrading, or restoring PDBs. The general syntax for the command is as follows: ALTER PLUGGABLE DATABASE < PDB name > PRIORITY < value > PDB name  is mandatory. PRIORITY  can take a value between  1  and  4096 . A lower number indicates a higher priority. For example, a PDB with priority  1  will open before one with priority  2 . Example: Assigning Priorities SQL> show pdbs CON_ID CON_NAME ...