Oracle SecureFile LOBs in 11G – Oracle Database 11G release 2 (11.2)

This article describes new feature of 11g SecureFile LOB.

In new version 11g Oracle decided to create new type large objects (LOB) called Secure Files. It offers many new benefits versus old version of LOB called in 11g release BasicFile:

  • performance
  • intelligent compression
  • transparent decryption
  • easier management and development

Continue reading

Install Oracle 11G Release 2 (11.2) on Fedora 22

This article presents how to install Oracle 11G on Fedora 22.

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

Installation software is available on OTN version 11.2.0.1 or metalink 11.2.0.4. In this installation I’m presenting installation for 11.2.0.4 but for previous version 11.2.0.X it shouldn’t be different.

Check article for new release Install Oracle 12C Release 1 (12.1) on Fedora 22

Oracle software that was verified

release 11.2.0.4

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

Continue reading

Install and configure Apex 5.0.X embedded PL/SQL

This article presents how to install and configure Apex for version 5.0.X for Oracle 11G

Prepare software to installation

Download installation package from Oracle site and unzip.

Download apex_5.0.X.zip to directory /tmp and unzip it from
http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html

cd /tmp
unzip <downloaded software>

After unzip is completed a new directory will be created /tmp/apex so go to this directory and log into database as SYSDBA. Always use SYSDBA account for running all scripts.

cd /tmp/apex
sqlplus / as sysdba

Pre-installation steps

It’s recommended to do backup of the database and disable the Oracle XMLDB HTTP server by setting the HTTP port to 0.

EXEC DBMS_XDB.SETHTTPPORT(0);

Continue reading

Install Oracle 11G Release 2 (11.2) on Fedora 21

This article presents how to install Oracle 11G on Fedora 21.

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

Installation software is available on OTN version 11.2.0.1 or metalink 11.2.0.4. In this installation I’m presenting installation for 11.2.0.4 but for previous version 11.2.0.X it shouldn’t be different.

Check article for new release Install Oracle 12C Release 1 (12.1) on Fedora 21

Oracle software that was verified

release 11.2.0.4

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

Continue reading

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

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

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

Deferred segment creation on demand – Oracle Database 11G release 2 (11.2)

In Oracle 11G Release 2 segment allocation has been changed. When new table is created, the table segment creation is postponed till first row insert.

This functionality is enabled by default with new parameter DEFERRED_SEGMENT_CREATION set as default to TRUE

DEFERRED_SEGMENT_CREATION = TRUE | FALSE

This new feature allows to save initially a lot of space for newly created objects especially when you have thousands of partitions. However you need to remeber about careful space planing.

Continue reading