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 a per-process level and can be enabled ad hoc on any running server process. This is useful for tracing individual server-side processes … [Read more...] about Dynamic Oracle Net Server Tracing
18c
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 the hash indexes of the tables when enabled for fast lookup. Fast lookup is enabled by a hash index structure in the memoptimize pool that provides … [Read more...] about Oracle Database 18c New Feature – Memoptimized Rowstore
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 A table Let's create a sample table "onlinemergepartition" as shown below. --- Creating a table CREATE TABLE onlinemergepartition ( pid number, salesdate date, price … [Read more...] about Oracle Database 18c : How to Merge Partitions And Subpartitions Online
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 to another CDB. Let's say that we have the following CDB on both source site and dest site: Source: hostname: test001.tst.com CDB: ORC181U01 PDB: pdb01 Dest: hostname: … [Read more...] about Example of PDB Level Duplication in Oracle Database 18c
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 data across RAC instances and multiple loading processes within single instances. The new business benefit is that it further … [Read more...] about Oracle Database 18c new feature – Scalable Sequences
How To Cancel A SQL Query In Oracle Database 18c
The idea of this post is to explain the steps to cancel a query in Oracle Database 18c. In this version, you have the option to cancel just the query instead of terminating the session. The following clauses are required in an "ALTER SYSTEM CANCEL" SQL statement: SID – Session ID SERIAL – Session serial number Here is an example: 1. Find SID and SERIAL# Let us first find the SID and SERIAL# for the query to be cancelled. ##### Session 1 ##### SQL> select sid, serial# from … [Read more...] about How To Cancel A SQL Query In Oracle Database 18c