Author Archives: joda3008

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

Posted on by
Reply

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

Posted on by
Reply

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

Posted on by
Reply

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 →

Enhanced Partitioning Oracle Database 12C release 2 (12.2)

Posted on by
Reply

This article describes new options available in Oracle Database Release 12.2 for partitioning:

  • extensions for list partitioning
    • automatic list partitioned table
    • multi-column list partitioned table
  • read-only partitions and subpartitions
  • filtering maintenance operations
  • creating a table for exchange with a partitioned table
  • online converting non-partitioned table to a partitioned table
  • online split partition and subpartition

Continue reading →

Create drop database link in another schema Oracle

Posted on by
3

Unfortunately in Oracle you can’t create or drop database link in another schema. It’s very frustrating. Simple solution is to create anonymous block which will first create dummy function in another schema and then reuse it to create or drop an object(in our case database link).

Of course it can be reused to do other types of object as well.

Continue reading →

Install Oracle 12C Release 2 (12.2) on Fedora 25

Posted on by
1

This article presents installation of Oracle database 12C(12.2) on Fedora 25. Read following article to install Fedora 25 Linux: Install Fedora 25(for comfort set 4G memory for your virtual machine).

Software

Software for 12CR2 is available on OTN or edelivery

Database software

linuxx64_12201_database.zip

Continue reading →

Install Oracle 12C Release 2 (12.2) on Oracle Linux 7 (OEL7)

Posted on by
8

This article presents how to install Oracle 12C(12.2) Release 2 on Oracle Enterprise Linux 7 (OEL7).

Read following article how to install Oracle Enterprise Linux 7: Install Oracle Linux 7 (OEL7) (for comfort set 4G memory for your virtual machine before proceeding with Oracle software installation).

Software

Software for 12CR2 is available on OTN or edelivery

Database software

linuxx64_12201_database.zip

Continue reading →

Enhanced LISTAGG Oracle Database 12C release 2 (12.2)

Posted on by
Reply

In previous releases there was always problem with function LISTAGG. If list of concatenated expression exceeds maximum length supported by VARCHAR2 type, then ORA-01489 is raised.

In new version Oracle Database Release 12.2 addressed this issue by adding extra clause ON OVERFLOW TRUNCATE to hide this error. In my opinion it’s not full solution but in some cases can be useful.

LISTAGG ( [ALL] <measure_column> [,<delimiter>] 
 [ON OVERFLOW TRUNCATE [truncate_literal] [WITH | WITHOUT COUNT] | 
  ON OVERFLOW ERROR]) 
WITHIN GROUP (ORDER BY <oby_expression_list>)

I recommend to read following article as well:

String concatenation aggregation methods

Continue reading →