{"id":7344,"date":"2020-01-02T11:46:45","date_gmt":"2020-01-02T11:46:45","guid":{"rendered":"https:\/\/rax-stg-wp-aws.com\/blogs\/new-features-in-oracle-database-18c\/"},"modified":"2026-11-03T19:28:37","modified_gmt":"2026-11-03T19:28:37","slug":"new-features-in-oracle-database-18c","status":"publish","type":"blogs","link":"https:\/\/www.rackspace.com\/en-gb\/blogs\/new-features-in-oracle-database-18c\/","title":{"rendered":"New features in Oracle Database 18c"},"content":{"rendered":"<p><em><strong>This blog post discusses the new interesting features introduced in Oracle&nbsp;Database 18c that make the life of DBA a little easier.<\/strong><\/em><\/p>\n<p>\nOracle Database 18c introduces the following new features:<\/p>\n<ul>\n<li>&nbsp;SQL cancelling<\/li>\n<li>&nbsp;Password file location<\/li>\n<li>&nbsp;Read-only Oracle Home<\/li>\n<li>&nbsp;Private temporary tables<\/li>\n<\/ul>\n<p><strong>&nbsp;SQL cancelling&nbsp;<\/strong><\/p>\n<p>Oracle Database 18c introduces a new command that database administrators (DBAs) can use&nbsp;to cancel blocking, or overly expensive, statements without needing to kill the&nbsp;original session. In earlier releases, DBAs used the <em>alter session kill<\/em>&nbsp;command&nbsp;to terminate the session. The new <em>alter system cancel<\/em>&nbsp;SQL command cancels an&nbsp;SQL statement in a session, providing an alternative to killing a rogue session&nbsp;in the Oracle Database 18c.<\/p>\n<p>Use the following syntax for the `alter system cancel` SQL statement:<\/p>\n<p><em>&nbsp; &nbsp; ALTER SYSTEM CANCEL SQL &#8216;SID, SERIAL#[, @INST_ID][, SQL_ID]&#8217;;<\/em><\/p>\n<p>If you omit the <strong>INST\\_ID<\/strong>, the system defaults to the current instance. If&nbsp;you omit the <strong>SQL\\_ID<\/strong>, the system assumes that the SQL should run in the&nbsp;specified session. Following are some variations:<\/p>\n<p><em>&#8211; Current SQL in session on this instance.<\/em><\/p>\n<p><em>&nbsp; &nbsp; &nbsp; &nbsp; ALTER SYSTEM CANCEL SQL &#8216;723, 45325&#8217;;<\/em><\/p>\n<p><em>&#8211; Current SQL in session on instance with **INST\\_ID** = 1.<\/em><\/p>\n<p><em>&nbsp; &nbsp; &nbsp; &nbsp; ALTER SYSTEM CANCEL SQL &#8216;723, 45325, @1&#8217;;<\/em><\/p>\n<p><em>&#8211; Specified SQL in session on this instance.<\/em><\/p>\n<p><em>&nbsp; &nbsp; &nbsp; &nbsp; ALTER SYSTEM CANCEL SQL &#8216;723, 45325, 84djy3b3xgvq&#8217;;<\/em><\/p>\n<p><em>&#8211; Specified SQL in session on instance with **INST\\_ID** = 1.<\/em><\/p>\n<p><em>&nbsp; &nbsp; &nbsp; &nbsp; ALTER SYSTEM CANCEL SQL &#8216;723, 45325, @1, 84djy3b3xgvq&#8217;;<\/em><\/p>\n<p><strong>&nbsp;<\/strong><\/p>\n<p><strong>Password file location<\/strong><\/p>\n<p>The default location for the Oracle Database 18c password file is now <strong>ORACLE\\_BASE<\/strong>&nbsp;instead of <strong>ORACLE\\_HOME\/dbs<\/strong>. This change enables you to use&nbsp;Oracle Home as a static repository for database files.<\/p>\n<p>The new location simplifies database migrations because <strong>ORACLE\\_BASE<\/strong>&nbsp;is&nbsp;common to every installation&#8217;s <strong>ORACLE\\_HOME<\/strong>. Migrating a database from one <strong>ORACLE\\_HOME<\/strong>&nbsp;to another does not require moving the password file:<\/p>\n<p>&nbsp; &nbsp; <em>orapwd file=\u2019$ORACLE_BASE\/orapwtest18c\u2019 password=oracle force=y format=12<\/em><\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26393-body-Picture1_16.png\" alt=\"18c Pic 1\" \/><\/p>\n<p>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.&nbsp;<\/p>\n<p>Use the following steps to enable the read-only<br \/>\nOracle Home:<\/p>\n<p>1. Go to the bin directory:<\/p>\n<p>&nbsp; &nbsp;<em> &nbsp; &nbsp; $ cd $ORACLE_HOME\/bin<\/em><\/p>\n<p>2. Run the script to enable read-only Oracle home:<\/p>\n<p><em>&nbsp; &nbsp; &nbsp; &nbsp; $ .\/roohctl -enable<\/em><\/p>\n<p><strong>Private temporary tables<\/strong><\/p>\n<p>Oracle Database 18c introduces the concept of a private temporary table &#8212; a&nbsp;memory-based temporary table that is dropped at the end of the session or&nbsp;transaction depending on the setup. In the previous releases, you can create a&nbsp;Global Temporary Table (GTT), which is a permanent metadata object that holds&nbsp;rows in temporary segments on a transaction-specific or session-specific basis.&nbsp;<\/p>\n<p>Normally, you don&#8217;t create and drop GTTs on the fly. With the introduction of&nbsp;private temporary tables, Oracle has a similar option available in other engines,&nbsp;where the table object itself is temporary, not just the data.<\/p>\n<p>The <em>PRIVATE_TEMP_TABLE_PREFIX<\/em>&nbsp;initialization parameter, which defaults to&nbsp;<strong><em>ORA$PTT<\/em><\/strong>, defines the prefix that you must use in the name when creating the&nbsp;private temporary table. If you try to create a private temporary table without&nbsp;using the correct prefix in the name, an error results.<\/p>\n<p>The default &#8220;`ON COMMIT DROP DEFINITION&#8221;` clause drops the table at the end of the transaction or at the end of the session.<\/p>\n<p><strong>Use the following syntax to create a private temporary table:<\/strong><\/p>\n<p><em>&nbsp; &nbsp; CREATE PRIVATE TEMPORARY TABLE ora$ptt_my_temp1 (<br \/>\n&nbsp; &nbsp; &nbsp; id NUMBER,<br \/>\n&nbsp; &nbsp; &nbsp; description &nbsp;VARCHAR2(20)<br \/>\n&nbsp; &nbsp; )<br \/>\n&nbsp; &nbsp; ON COMMIT DROP DEFINITION;<\/em><br \/>\n&nbsp;<\/p>\n<p><strong>Conclusion<\/strong><\/p>\n<p>We recommend that you take advantage of these new features, such as using SQL&nbsp;canceling to clear any identified sessions from the database. The read-only&nbsp;<strong>ORACLE\\_HOME<\/strong>&nbsp;feature enables you to share the software across&nbsp;different deployments easily. With the introduction of private temporary tables, you&nbsp;can quickly create a temporary table without having to keep track of it,&nbsp;because Oracle automatically drops it at the end of the transaction. Depending&nbsp;on what situation you need to handle, you can use any of these features&nbsp;to save time.<\/p>\n<p><strong>Optimize your environment with expert administration, management, and configuration<\/strong><\/p>\n<p><a href=\"https:\/\/www.rackspace.com\/application-management\/managed-services\">Rackspace&#8217;s Application services<\/a>&nbsp;<strong>(RAS)<\/strong>&nbsp;experts provide the following [<a href=\"https:\/\/www.rackspace.com\/application-management\/professional-services\">professional<\/a>&nbsp;and&nbsp;<a href=\"https:\/\/www.rackspace.com\/application-management\/managed-services\">managed services<\/a>&nbsp;for a broad portfolio of applications:<\/p>\n<ul>\n<li><a href=\"https:\/\/www.rackspace.com\/ecommerce-digital-experience\">eCommerce and Digital Experience platforms<\/a><\/li>\n<li><a href=\"https:\/\/www.rackspace.com\/erp\">Enterprise Resource Planning (ERP)<\/a><\/li>\n<li><a href=\"https:\/\/www.rackspace.com\/business-intelligence\">Business Intelligence<\/a><\/li>\n<li><a href=\"https:\/\/www.rackspace.com\/dba-services\">Databases<\/a><\/li>\n<li><a href=\"https:\/\/www.rackspace.com\/email-hosting\">Email Hosting and Productivity<\/a><\/li>\n<\/ul>\n<p><strong>We deliver:<\/strong><\/p>\n<ul>\n<li><strong>Unbiased expertise<\/strong>: We simplify and guide your modernization journey,&nbsp;focusing on the capabilities that deliver immediate value.<\/li>\n<li><strong>Fanatical Experience\u00ae<\/strong>: We combine a *Process first. Technology second\u00ae&nbsp;approach with dedicated technical support to provide comprehensive solutions.<\/li>\n<li><strong>Unrivaled portfolio<\/strong>: We apply extensive cloud experience to help you&nbsp;choose and deploy the right technology on the right cloud.&nbsp;<\/li>\n<li>&#8211; <strong>Agile delivery<\/strong>: We meet you where you are in your journey and align&nbsp;our success with yours.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>New features in Oracle Database 18c<\/p>\n","protected":false},"author":2,"featured_media":7346,"menu_order":0,"comment_status":"open","ping_status":"open","template":"","format":"standard","meta":{"content-type":"","nid":"26393","content_type":"blog","created_by":"10130","authored_on":"2023-12-18T11:46:45+00:00","field_article_industries":"","tag_category":"Technical","field_blog_author_text":"by Rackspace Technology Staff","seo_description":"Dive into the new features introduced in Oracle Database 18c that aid DBA activities. Understand changes like SQL cancelling, read-only Oracle Home, and more.\r\n","field_featured_image":"","insights_image":"\/wp-content\/uploads\/2026\/blog_technical_images\/26393-insights_image-oracle-technical-blog-1-insights.jpg","field_partners":"","rxt_platforms":"Oracle","product":"","publisheddate":"2020-01-02T11:46:45","rxt_solutions":"Databases, Data Management, Data Modernization","opengraph_tag":"","x_tag":"","teaser_flag":"Technical Blog","seo_titile":"Oracle Database 18c Capabilities | Rackspace Technology","field_topics":"","field_slug":"new-features-in-oracle-version-18c","footnotes":""},"categories":[178],"tags":[204],"class_list":["post-7344","blogs","type-blogs","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","tag-technical"],"_links":{"self":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/7344","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs"}],"about":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/types\/blogs"}],"author":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/comments?post=7344"}],"version-history":[{"count":3,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/7344\/revisions"}],"predecessor-version":[{"id":9409,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/7344\/revisions\/9409"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/media\/7346"}],"wp:attachment":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/media?parent=7344"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/categories?post=7344"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/tags?post=7344"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}