Enhanced Partitioning Oracle Database 12C release 2 (12.2)

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

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

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)

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)

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