Oracle Database parameter MAX_STRING_SIZE controls the maximum size of VARCHAR2, NVARCHAR2, and RAW data types in SQL. From Oracle Database 12c onwards, table column(s) length can be modified to greater than 4000 characters when this parameter value is set to EXTENDED. Allowed values for this parameter are: STANDARD: Default value. Means that the length limits […]
Oracle 19c
Connecting PDB in Oracle 12c and 19c
To connect as sys or other elevated local users, use the same methods as below, but use ‘as sysdba’ wherever necessary. Method 1 alter session set container = pdbName; Method 2 For other methods, you need to know the service-name for the PDB: – Get the service name for the PDB: SELECT name || ‘ […]
Establish Connection in CDB and PDB
When it comes to Multi-tenant architecture, one question that comes to our mind is how to connect to container database and pluggable database. Today we will see all possible ways to connect to the container database. Obviously, we connect database using service name for the non-local connection. When we create CDB, a service is created […]
How to Use Startup, Shutdown, and Alter Database commands in Oracle 12c
Before 12c there are only a few commands available for startup and shutdown but if we follow that we end up starting and stopping only cdb. The commands in 12c to start and shutdown the CDB and PDB are a little different than the previous version. Below list of commands were used in earlier versions […]
Auto Space Management for Flashback Logs in the Fast Recovery Area – Oracle 19c New Feature
Starting with Oracle Database Release 19c, the management of space in the fast recovery area is simplified. Oracle Database monitors flashback logs in the fast recovery area and automatically deletes flashback logs that are beyond the retention period. When the retention target is reduced, flashback logs that are beyond the retention period are deleted immediately. […]
Oracle 19c New Feature – SQL_DIAGNOSE_AND_REPAIR (New Automatic Diagnostics and Repair Function)
In Oracle Database 19c, a new function SQL_DIAGNOSE_AND_REPAIR is introduced to diagnose a given SQL statement for a given SQL for the given problem type. This function creates an incident, populates incident metadata with required information like SQL ID, SQL text, compilation environment, and so on. It also creates a diagnostic task, executes it, and […]
Oracle 19c New Feature – Real-Time Statistics
Oracle Database 12c introduced online statistics gathering for CREATE TABLE AS SELECT statements and direct-path inserts. Oracle Database 19c introduces real-time statistics, which extend online support to conventional DML statements. Because statistics can go stale between DBMS_STATS jobs, real-time statistics helps the optimizer generate more optimal plans. Checking for real-time statistics gathering 1. Generate explain […]
How to Enable or Disable Veritas ODM for Oracle database 12.1.0.2, 18c and 19c
This post describes the procedures for Enable/Disable Oracle Disk Manager driver for Oracle Database 12.1.0.2, 18c, and 19c. We assume the Veritas ODM driver is already installed, mounted, and available. Follow the steps outlined below to enable/disable ODM for the Oracle database (note that with different versions of the Solaris OS, the path may change […]
ORA-65010: maximum number of pluggable databases created
If you try to create 4 or more PDB in a container then you might get below error. SQL> create pluggable database ADMIN USER pdb_admin identified by *** DEFAULT TABLESPACE USERS; Enter value for p: TEST old 1: create pluggable database &p ADMIN USER pdb_admin identified by *** DEFAULT TABLESPACE USERS new 1: create pluggable […]
Oracle Database 19c: RMAN-06012: channel: d1 not allocated
The Problem We were trying to run a RMAN script (running the duplicate database) from a Oracle 12c environment onto a 19c environment. The script worked perfectly fine on the 12c databases but was giving below error when run on a 19c database: RUN { allocate auxiliary channel d1 device type disk maxpiecesize 20G; allocate […]