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 || ‘ […]
18c
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 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 […]
How to Connect to an Oracle Pluggable Database (PDB)
When connecting to a pluggable database, a valid service connection string that points to its service (previously configured and started) MUST always be provided, whether the connection is attempted locally or remotely: $ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 – Production on Fri Jul 30 17:32:15 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. […]
How To Find Creation Time of Oracle Pluggable Database (PDB)
Below query can be used to for PDB creation/cloning time. COLUMN DB_NAME FORMAT A10 COLUMN CON_ID FORMAT 999 COLUMN PDB_NAME FORMAT A15 COLUMN OPERATION FORMAT A16 COLUMN OP_TIMESTAMP FORMAT A10 COLUMN CLONED_FROM_PDB_NAME FORMAT A15 SELECT DB_NAME, CON_ID, PDB_NAME, OPERATION, OP_TIMESTAMP, CLONED_FROM_PDB_NAME FROM CDB_PDB_HISTORY WHERE CON_ID > 2 ORDER BY CON_ID; Here is a sample output […]
Dynamic Oracle Net Server Tracing
Dynamic tracing V/s Static Tracing Oracle Net tracing is controlled by sqlnet.ora parameters which are static for the lifetime of RDBMS server processes. Since there is a single sqlnet.ora file for an instance, trace settings apply to all server processes. This can pose difficulties in live production environments. Dynamic server tracing allows for tracing at […]
Oracle Database 18c New Feature – Memoptimized Rowstore
This post gives a summary of 18c new Memoptimized Rowstore feature. Feature Summary The memoptimized rowstore provides the capability of fast lookup of data for the tables that are mainly queried based on primary key columns. The memoptimized rowstore uses a memory area in the system global area (SGA) called the memoptimize pool that stores […]
Oracle Database 18c : How to Merge Partitions And Subpartitions Online
This post is a short note on merging partitions and subpartitions online. The below sample demonstration shows you how to merge partitions online while there are concurrent transactions going on. For the purpose of this post, we will create a sample table and load some sample data into it to perform the merge. 1. Create […]
Example of PDB Level Duplication in Oracle Database 18c
In the database versions before 18c, PDB could not be duplicated. Users could only duplicate CDBs. But starting with Oracle database 18c, users can now duplicate one PDB into another CDB. This post gives an example of how to do it. Example Environment Here is an example of how to duplicate PDB from one CDB […]
Oracle Database 18c new feature – Scalable Sequences
Starting 18.1 database, “Scalable Sequences” have been introduced. The ability to create Scalable Sequences has been added to improve the performance of data loading into tables having sequence values as keys. This feature provides the option to add instance and session offsets to significantly reduce the possibility of sequence and index block contention when loading […]