Partition outer join in Oracle (data densification)

Partition outer join is a method for “data-densification”. If you have sparse data it helps to easily duplicate data with new SQL syntax:

SELECT  
  select_expression
FROM    
  table_reference PARTITION BY (expr [,expr ]...)
RIGHT OUTER JOIN  
  table_reference

or

SELECT  
  select_expression
FROM    
  table_reference
LEFT OUTER JOIN  
  table_reference PARTITION BY (expr [,expr ]...)

Continue reading

DML Error Logging in Oracle Database 11G release 2 (11.2)

This article presents extension for standard DML operations (INSERT, UPDATE, DELETE, MERGE) .. LOG ERRORS INTO. It enables to execute successfully DML operation into target table regardless of errors during processing of rows. Informations about errors are loaded together with rows content into dedicated error table.

dml error log

Syntax

Here is general syntax for DML

INSERT/UPDATE/DELETE/MERGE ...
...
LOG ERRORS [INTO [schema_name.]table_name] [('simple_expression')] 
[REJECT LIMIT integer|UNLIMITED]

where

  • schema_name.table_name – is error table created with DBMS_ERRLOG package
  • simple_expression – is tag that can be applied to failed records. It’s stored in error table in column ORA_ERR_TAG$
  • REJECT LIMIT specifies maximum number of accepted errors before the statment fails and rollback all. Default value is 0 and maximum UNLIMITED

Continue reading

In-Database Archiving in Oracle Database 12C release 1 (12.1)

This article presents following new feature of Oracle Database 12C

In-Database Archiving – this option enables to mark records in a table as not active (called later archive records). As default archive records are not visible in Oracle sessions. The records can be later compressed or deleted.

The reason to add such option was to keep both versions of records (active and not active) in the same table instead of making backup(not active) on tape and delete them(not active)  from a table.

Continue reading

Install Oracle 11G Release 2 (11.2) on Suse Linux 11 (SLES11)

This article presents installation of Oracle database 11G Release 2 on Suse Linux Enterprise Server 11.

Read following article how to install Suse Linux Enterprise Server 11: Install Suse Linux Enterprise Server 11 SP3 64 bit(for comfort set 2G memory for your virtual machine).

During installation of Suse LES 11 user oracle and both group dba and oinstall are created. However I drop the user and groups and recreate them.

Software

Software for 11G R2 is available on OTN, edelivery or oracle support

Suse11 requires version Linux x86-64. In this presentation I’m using and always referring to version 11.2.0.4 downloaded from Oracle support page.

Binaries 11.2.0.4

p13390677_112040_Linux-x86-64_1of7.zip - database software  
p13390677_112040_Linux-x86-64_2of7.zip - database software

Requirements

Be sure you fulfill following:

  • SuSE Linux Enterprise Server (SLES) 11, which is Kernel 2.6.27.19-5 or newer.

Continue reading

Install Ubuntu 14

This article presents how to install Ubuntu 14.

I assume you have already downloaded Ubuntu 14 64 bit(about 1 G) and you know how to use VirtualBox 64 bit(100M). Create virtual machine with default settings for Linux Ubuntu 64 bit. 2GB ram (I set 4GB for future Oracle database installation) and 64G for disk is enough plus increase video memory to 128M and turn on acceleration 3D. Rest of options you can keep default.

Ubuntu_01

Continue reading

Duplicate records based on column value in Oracle

This article shows simple method to duplicate records based on value in a column.

For example following query returns 3 records

select 'a' letter, 2 num from dual
union all
select 'b', 4 from dual
union all
select 'c', 4 from dual

LETTER        NUM
------ ----------
a               2 
b               4 
c               4

to duplicate records based on column NUM you can do following transformation using CONNECT BY

with a as (
select 'a' letter, 2 num from dual
union all
select 'b', 4 from dual
union all
select 'c', 4 from dual
)
select a.*, b.column_value 
from a,
     table(cast(multiset(
                 select level from dual
                 connect by level <= num ) 
           as sys.odciNumberList)) b;

LETTER        NUM COLUMN_VALUE
------ ---------- ------------
a               2            1 
a               2            2 
b               4            1 
b               4            2 
b               4            3 
b               4            4 
c               4            1 
c               4            2 
c               4            3 
c               4            4

You can use above method CONNECT BY to transform complex string (comma separated string) in a column into multiple records and simple string(split based on comma)

select 'a,b,c' letter from dual
union all
select 'g,h' from dual
union all
select 'm,k,l' from dual

LETTER
------
a,b,c  
g,h    
m,k,l

and now simple

with a as (                         
select 'a,b,c' letter from dual
union all
select 'g,h' from dual
union all
select 'm,k,l' from dual
)
select a.*, b.column_value 
  from a,
     table(cast(multiset(
                  select regexp_substr(letter,'[^,]+',1,level) 
                    from dual
                  connect by level <= regexp_count(letter, ',')+1)
           as sys.odciVarchar2List)) b;

LETTER COLUMN_VALUE    
------ ----------------
a,b,c  a               
a,b,c  b               
a,b,c  c               
g,h    g               
g,h    h               
m,k,l  m               
m,k,l  k               
m,k,l  l

Have a fun :)
Tomasz

ORA-25158: Cannot specify RELY for foreign key if the associated primary key is NORELY

If you want to add RELY DISABLE constraint to child table that references a master table with primary key constraint with NORELY flag you can encounter following error:

ORA-25158: Cannot specify RELY for foreign key if the associated primary key is NORELY

Example:

create table master_tbl
(id number primary key);

select constraint_name, constraint_type, table_name, rely
from user_constraints
where table_name='MASTER_TBL';

CONSTRAINT_NAME     CONSTRAINT_TYPE TABLE_NAME    RELY
------------------- --------------- ------------- ----
SYS_C0012428928                   P MASTER_TBL        
create table child_tbl
(id number);

alter table child_tbl add constraint child_tbl_fk
foreign key (id) references master_tbl rely disable;

ORA-25158: Cannot specify RELY for foreign key 
           if the associated primary key is NORELY

First option is to modify primary key constraint of table master_tbl to RELY but there is another workaround – just add DISABLE RELY constraint in two steps to child_tbl.

alter table child_tbl add constraint child_tbl_fk
foreign key (id) references master_tbl disable;

alter table child_tbl
modify constraint child_tbl_fk rely;

select constraint_name, constraint_type, table_name, rely
from user_constraints
where table_name in ('MASTER_TBL', 'CHILD_TBL');

CONSTRAINT_NAME   CONSTRAINT_TYPE TABLE_NAME     RELY
----------------- --------------- -------------- ----
CHILD_TBL_FK      R               CHILD_TBL      RELY
SYS_C0012428928   P               MASTER_TBL

Have a fun :)

Tomasz

Compare objects in Oracle with DBMS_METADATA_DIFF – Oracle Database 11G release 2 (11.2)

This article presents new extension added into DBMS_METADATA and new package DBMS_METADATA_DIFF.

Both packages allow you to compare the metadata for two objects and show differences. Compare results can be presented as simple XML – SXML or SQL ALTER statements that can be used to make one object like the other.

Continue reading

Database Caching Mode Oracle Database 12C release 1 (12.1)

This feature is available starting with Oracle Database 12c Release 1 (12.1.0.2)

It enables to force caching of all segments into buffer cache automatically(when the segments are accessed). With this option turned on even FULL scans or NOCACHE LOBs are loaded into buffer cache. In previous releases it was not guaranteed that a given segments will be cached unless you specified KEEP buffer.

When to use it:

  • when you have enough memory to keep all segments in memory
  • when you are limited by I/O system and response time

Continue reading