Private Temporary Tables Oracle Database 18C

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 

or

CREATE PRIVATE TEMPORARY TABLE .... ON COMMIT PRESERVE DEFINITION
DROP 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.
PRESERVE DEFINITION 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.

Continue reading

Code Based Access Control for Definer’s Rights and Invoker’s Rights Oracle Database 12C release 2 (12.2)

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:

  • SYS
  • 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.

Continue reading

NO_EXPAND USE_CONCAT hints

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 UNION 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;

Continue reading

Install Fedora 26

This article presents how to install Fedora 26 – Live Workstation version.

I assume you have already downloaded Fedora 26 64 bit Live Workstation(about 1,3 G) and you know how to use VirtualBox 64 bit(100M). Create virtual machine with default settings for Linux Fedora 64 bit. 2CPU, 2GB ram and 64G(includes extra space for Oracle installations) for disk is enough plus increase video memory to 128M. Rest of options you can keep default.

Continue reading