New features in Oracle Database 18c
by Rackspace Technology Staff
This blog post discusses the new interesting features introduced in Oracle Database 18c that make the life of DBA a little easier.
Oracle Database 18c introduces the following new features:
- SQL cancelling
- Password file location
- Read-only Oracle Home
- Private temporary tables
Oracle Database 18c introduces a new command that database administrators (DBAs) can use to cancel blocking, or overly expensive, statements without needing to kill the original session. In earlier releases, DBAs used the alter session kill command to terminate the session. The new alter system cancel SQL command cancels an SQL statement in a session, providing an alternative to killing a rogue session in the Oracle Database 18c.
Use the following syntax for the `alter system cancel` SQL statement:
ALTER SYSTEM CANCEL SQL 'SID, SERIAL#[, @INST_ID][, SQL_ID]';
If you omit the INST\_ID, the system defaults to the current instance. If you omit the SQL\_ID, the system assumes that the SQL should run in the specified session. Following are some variations:
- Current SQL in session on this instance.
ALTER SYSTEM CANCEL SQL '723, 45325';
- Current SQL in session on instance with **INST\_ID** = 1.
ALTER SYSTEM CANCEL SQL '723, 45325, @1';
- Specified SQL in session on this instance.
ALTER SYSTEM CANCEL SQL '723, 45325, 84djy3b3xgvq';
- Specified SQL in session on instance with **INST\_ID** = 1.
ALTER SYSTEM CANCEL SQL '723, 45325, @1, 84djy3b3xgvq';
Password file location
The default location for the Oracle Database 18c password file is now ORACLE\_BASE instead of ORACLE\_HOME/dbs. This change enables you to use Oracle Home as a static repository for database files.
The new location simplifies database migrations because ORACLE\_BASE is common to every installation's ORACLE\_HOME. Migrating a database from one ORACLE\_HOME to another does not require moving the password file:
orapwd file=’$ORACLE_BASE/orapwtest18c’ password=oracle force=y format=12
The new password file location enables you to mount one shared read-only Oracle Home on different systems, making deployment for complex architectures more reliable and simpler. In other words, a read-only Oracle Home separates the software from the database configuration information and log files, which enables you to share the software across different deployments easily. A read-only Oracle Home also simplifies version control and standardization.
Use the following steps to enable the read-only
1. Go to the bin directory:
$ cd $ORACLE_HOME/bin
2. Run the script to enable read-only Oracle home:
$ ./roohctl -enable
Private temporary tables
Oracle Database 18c introduces the concept of a private temporary table --- a memory-based temporary table that is dropped at the end of the session or transaction depending on the setup. In the previous releases, you can create a Global Temporary Table (GTT), which is a permanent metadata object that holds rows in temporary segments on a transaction-specific or session-specific basis.
Normally, you don't create and drop GTTs on the fly. With the introduction of private temporary tables, Oracle has a similar option available in other engines, where the table object itself is temporary, not just the data.
The PRIVATE_TEMP_TABLE_PREFIX initialization parameter, which defaults to ORA$PTT, defines the prefix that you must use in the name when creating the private temporary table. If you try to create a private temporary table without using the correct prefix in the name, an error results.
The default "`ON COMMIT DROP DEFINITION"` clause drops the table at the end of the transaction or at the end of the session.
Use the following syntax to create a private temporary table:
CREATE PRIVATE TEMPORARY TABLE ora$ptt_my_temp1 (
ON COMMIT DROP DEFINITION;
We recommend that you take advantage of these new features, such as using SQL canceling to clear any identified sessions from the database. The read-only ORACLE\_HOME feature enables you to share the software across different deployments easily. With the introduction of private temporary tables, you can quickly create a temporary table without having to keep track of it, because Oracle automatically drops it at the end of the transaction. Depending on what situation you need to handle, you can use any of these features to save time.
Optimize your environment with expert administration, management, and configuration
- eCommerce and Digital Experience platforms
- Enterprise Resource Planning (ERP)
- Business Intelligence
- Email Hosting and Productivity
- Unbiased expertise: We simplify and guide your modernization journey, focusing on the capabilities that deliver immediate value.
- Fanatical Experience®: We combine a *Process first. Technology second® approach with dedicated technical support to provide comprehensive solutions.
- Unrivaled portfolio: We apply extensive cloud experience to help you choose and deploy the right technology on the right cloud.
- - Agile delivery: We meet you where you are in your journey and align our success with yours.
Rackspace Response to Microsoft February 2024 Patch Tuesday Vulnerability
February 19th, 2024
Take GitHub Webhooks Event Processing to the Next Level
January 19th, 2024
Choosing the Right Multicloud Model
January 5th, 2024
Rackspace Response to November 2023 Microsoft Patch Tuesday Security Advisory
November 21st, 2023