Top 5 Oracle Database Features 2019
As we look forward to what the Oracle 19c release has in store for us later this year, here is a look back at a few recent Oracle database enhancements that you may have missed. Many significant improvements have been made in the Oracle 12.2 release and the subsequent 18c update that are worth a closer look in 2019.
#5 Direct Integration with Microsoft Active Directory
Starting in version 18c, Oracle now provides a simple and easy way to directly integrate with Active Directory using the Centrally Managed Users (CMU) feature. Prior to 18c, many companies avoided integrating database security with Active Directory due to the complexity and difficulty of configuring Oracle Enterprise User Security and also installing Oracle Internet Directory. For environments that don’t require the additional functionality of Oracle Internet Directory, the CMU integration allows organizations a simple way to allow centralized authentication and to manage users and roles for multiple databases. The CMU configuration simply requires a service account in Active Directory that is used by the Oracle Database to login and query the user and group information each time a user connects to the Oracle database. Because the setup process has been streamlined considerably, it would be an ideal proof of concept project to consider in 2019 that would not require a significant amount of effort to test in your environment.
#4 Schema Only Accounts
One of the most common security issues in Oracle databases surrounds managing the passwords for schemas that own application objects in the database. Any malicious user or hacker that obtains the password to the application schemas have full access to steal, modify, or remove sensitive data. Often times it can also be inconvenient to change application schema passwords on a regular basis without downtime, which can result in compliance issues when it comes time for a security audit. Now in Oracle 18c there is a solution to this problem called schema only accounts. Schema only accounts do not have a password assigned to them and do not allow direct logins to the database. Access is controlled via a proxy user or by granting the appropriate access to other accounts to interact with the schema objects. There are a few limitations, most notable being that schema only accounts cannot be used to connect through database links. Using schema only accounts is a simple and effective way to close one of the largest security risks in most Oracle databases.
#3 Private Temporary Tables
Developers with experience coding for other database platforms, especially SQL Server, are often puzzled by the way Oracle has implemented temporary tables in the past. Temporary tables are a quick way to work with data that is only needed within a specific block of code or only during your current session. Traditionally, Oracle had implemented what they refer to as Global Temporary Tables (GTTs) that are defined and created in the data dictionary, much in the same way as permanent tables, while only the data contained within a GTT is actually temporary. In version 18c, Oracle has introduced Private Temporary Tables whose behavior is more similar to other RDBMS software. Private Temporary Tables are designed to be created and dropped on the fly to store data needed only for an individual transaction or session and the table creation is performed in memory. The data in a private temporary table is only visible within the session that created it and the table is automatically dropped at the end of the transaction or session.
#2 Partitioning Enhancements
Although Partitioning has been an option in Oracle for quite a long time, it has never been easier to implement and as feature rich as it is now in 2019. While partitioning has offered many wonderful capabilities in the past, it could often become an administrative burden on DBAs to manage and perform maintenance operations on large amounts of data. Throughout the life cycle of Oracle 12.1, 12.2, and into 18c, Oracle has made great strides in expanding the flexibility and ease of use for implementing and maintaining partitioning in the database. A wide range of activities can now be performed online with zero downtime including converting a traditional table to partitioned, moving partitions, splitting partitions, and merging partitions. There are several new partitioning methods including interval-reference partitions, multi-column list partitions, and auto list partitions. You can now mark partitions as read-only and there is improved functionality for performing partition maintenance as well as maintaining indexes during maintenance activities. If you haven’t considered partitioning before or felt limited by the partitioning options in the past, you should give it another chance in 2019.
#1 In-Memory Database
And finally we come to one of the most significant performance features Oracle has ever released, the In-Memory Database option could be a game changer for your system. You simply allocate a portion of memory to be used and Oracle creates an in-memory column store that will be accessed by analytical queries, providing orders of magnitude performance improvements (up to 100x improvement!). This feature is ideally designed for analytic workloads, but in can also provide benefits in mixed workload systems by reducing the overhead associated with maintaining numerous reporting indexes and materialized views against your OLTP tables. Database In-Memory is very simple to implement and does not require any code changes to your existing application. You only need to specify the amount of RAM to use for the in-memory area and identify which objects to populate in the column store, and Oracle has even added functionality to automate the placement of objects into the column store for you. Some other new enhancements in Oracle 12.2 and 18c include the ability to resize the in-memory area online, a fast start population of the column store during a restart, use on Active Data Guard, performance improvements with join groups and expressions, dynamically performing parallel scans if idle CPU is available, and a heat map to automatically manage the contents of the column store. Oracle also provides a Database In-Memory Advisor to help evaluate which database objects would benefit from using Database In-Memory. This would be a great place to begin to kick off an evaluation project to decide if this option is right for your system.
About the Author
Doug Floyd is a Senior Oracle Consultant at Indy Data Partners with 18 years of experience administering and supporting Oracle Databases.
Ready to start your project, or have questions? Contact us!