Starting from Oracle 23ai, Oracle has added new enhancements for INSERT, UPDATE, and DELETE statements.  This helps developers to obtain values before and after statement execution. These values are valid only for UPDATE statements. INSERT statements do not report old values and DELETE statements do not report new values. Let us demontsrate the same with some examples. Oracle 19c: Let's create a table in Oracle 19c database. SQL> select banner from v$version; BANNER ..read more

Starting from Oracle 23ai, Oracle Database now supports IF EXISTS or IF NOT EXISTS clauses in DDL statements. This alerts you whether an error should be raised if an object exists or does not exist. Let us demontsrate the same with some examples. CREATE TABLE: Oracle 19c: Let's create a table in Oracle 19c database. SQL> select banner from v$version; BANNER ------------------------------------------------------------------------- Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production 16:15:17 SQL&g ..read more

Starting from Oracle 23ai, you can now query the DUAL table without FROM clause. Let us demontsrate the same with some examples. Let's query the the DUAL table in Oracle 19c database. SQL> select banner from v$version; BANNER ------------------------------------------------------------------------- Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production SQL> select sysdate from dual; SYSDATE --------- 10-NOV-24 SQL> select abs(-27) from dual;   ABS(-27) ----------         27 SQL&g ..read more

   Starting from Oracle 23ai, you can specify column aliases or positions in GROUP BY clause. You can also specify aliases for aggregate functions as well in HAVING clause. You can also  use column alias in GROUP BY CUBE, GROUP BY ROLLUP, and GROUP BY GROUPING SETS clauses.  Let us demontsrate the same with some examples. Let's create a table in 19c database. SQL> select banner from v$version; BANNER ------------------------------------------------------------------------- Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 ..read more

  Starting from Oracle 23ai, you can define columns as DEFAULT ON NULL for update operations, which was previously only possible for insert operations. The column which is specified as DEFAULT ON NULL which gets automatically updated with specified value when update operation sets a value to NULL. In older releases, you used to achieve this with the help of Triggers. Let us demontsrate the same with the help of examples. Example 1: DEFAULT ON NULL only for insert operations  SQL> select banner from v$version; BANNER ..read more

  This guide provides step-by-step instructions for setting the default UNDO tablespace in an Oracle Database.  This process ensures efficient transaction management and optimal performance by configuring the UNDO space according to your database requirements. Step1: Check existing UNDO configuration. Here, you can see that the existing default UNDO tablespace is UNDOTBS1. SQL> show parameter undo NAME                   TYPE        VALUE ---------------------- ----------- --------- temp_undo_enabled&nbs ..read more

Let's see some vector terms before understanding AI Vector Search feature. What is Vector ?  A vector is a set of numbers that represents the attributes of an object in the most optimized and organized way. An object can be a word, a sentence, a document, an image,  an audio, or a video. Data is first converted into numbers and then it is stored in the form of vectors to represent the object.  v = (v1, v2) What is Vectorization ? Vectorization is the process of converting data (Text/Images/Audios/Videos) in the form of vectors. What is Vector database ? A vector ..read more

Environment: Operating System: Windows 11 Pro Oracle Database: 23ai By default, when the Oracle Database service is started, the container database is started and opened automatically, but any non-default pluggable databases must be manually opened before use.  Default pluggable database FREEPDB1 will open automatically when the Oracle service starts and when the container database opens. You can stop/start Oracle database by below two methods: 1) Starting Up and Shutting Down Using Windows Services 2) Starting Up and Shutting Down Using ..read more

  Environment: Operating System: Windows 11 Pro Oracle Database: 23ai 1) Connecting Locally using OS Authentication: When you install Oracle Database Free, your Windows user is automatically added to the ORA_DBA operating system group, which grants you the SYSDBA privileges. Use the following commands to connect to the database. These commands connect you to the root container CDB$ROOT of the multitenant database (CDB) as database user SYS. C:\Windows\System32>set ORACLE_SID=FREE C:\Windows\System32>sqlplus / as sysdba 2)&nb ..read more

Environment: Operating System: Windows 11 Pro Oracle Database: 23ai Note: Please note below resource limitations before installing Oracle database 23ai. 1) Oracle Database Free CPU Limitations: Oracle Database Free limits itself automatically to two cores for processing.  2) Oracle Database Free Installation and Runtime Restrictions: Oracle Database Free restricts itself to only one installation per logical environment. The logical environment can either be a virtual host such as a VM or container, or a physical host. 3) Oracle Database Free User Data Limitations: The ..read more