In Oracle Database 18C type initialization is simplified with qualified expressions for Record type and Associative Array Type. It can help simplify coding.
rec_var := (field_name => some_value, .., field_name => some_value)
rec_var := (1 => some_value, .., 10 => some_value)
New type of temporary tables appeared in 18C called Private Temporary Tables. They are temporary database objects that are dropped at the end of a transaction or session. Private temporary tables are stored in memory and each one is visible only to the session that created it.
CREATE PRIVATE TEMPORARY TABLE .... ON COMMIT DROP DEFINITION
CREATE PRIVATE TEMPORARY TABLE .... ON COMMIT PRESERVE DEFINITION
|This creates a private temporary table that is transaction specific. All data in the table is lost, and the table is dropped at the end of transaction.
|This creates a private temporary table that is session specific. All data in the table is lost, and the table is dropped at the end of the session that created the table.
New type of schema can be created in 18C where user has no password.
CREATE USER username NO AUTHENTICATION;
Sequence is commonly known oracle object that is used to generate numbers in specified orders. In Oracle 18C new extra words can be defined for sequence creation:
- SCALE | NOSCALE(default)
- EXTEND | NOEXTEND(default)
In latest version of database 18C instead of killing user session you can cancel its currently running or opened SQL statement using the ALTER SYSTEM CANCEL SQL statement. Continue reading
Following article shows how to catch all ORA- statements in sqlplus using clause
SET ERRORLOGGING ON
General rule for creating indexes in Oracle is following:
- Bitmap indexes – always index nulls
- B*Tree cluster indexes – always indexes nulls
- B*Tree indexes – can’t index data if all indexed columns are NULL
The last one can cause some performance issues but there is trick to index all NULL columns also for B*Tree indexes.
This article presents how to install Oracle 18C on Oracle Enterprise Linux 7 (OEL7).
Read following article how to install Oracle Enterprise Linux 7: Install Oracle Linux 7 (OEL7) (set 8G memory for your virtual machine before proceeding with Oracle software installation).
In Oracle 12C it’s possible to assign a role to a code procedure/function/package
GRANT role_name TO PROCEDURE|FUNCTION|PACKAGE code_name;
This kind of grant can be executed only by following user:
- user with GRANT ANY ROLE privilege
- user who own code and was granted the role with ADMIN OPTION
- user who own code and was granted the role with DELEGATE OPTION
Top three options are commonly know. New option is DELEGATE OPTION. It enables to grant a role to procedure|function|package and nothing more.
Once the role is granted to a code the role is always enabled no matter if the code is created with PRAGMA AUTHID_USER or PRAGMA DEFINER and no matter if owner of the code calls it or other user.
This article focus on two hints:
USE_CONCAT - The
USE_CONCAT hint forces combined
OR conditions in the
WHERE clause of a query to be transformed into a compound query using the
ALL set operator. Generally, this transformation occurs only if the cost of the query using the concatenations is cheaper than the cost without them.
NO_EXPAND - The
NO_EXPAND hint prevents the cost-based optimizer from considering
OR-expansion for queries having
OR conditions or
IN-lists in the
WHERE clause. Usually, the optimizer considers using
OR expansion and uses this method if it decides that the cost is lower than not using it.
NO_EXPAND can be forced by alter session for all statements
ALTER SESSION SET "_no_or_expansion" = TRUE;