Flashback Data Archive 11g

This article presents new feature of 11g – Flashback Data Archive.

This functionality allows to save all transactions executed on a table for longer period than just UNDO_RETENTION parameter and UNDO tablespace. In 11g you can create dedicated space to keep changed data(DML operations, DDL operations) for longer specified retention period.

Historical records can be reviewed using standard flashback sql queries feature.

Continue reading

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

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

Check latest articles:

Read following article howto install Fedora 19 Linux: Install Fedora 19 (for comfort set 2G 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.3, 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.

Oracle software which I verified

release 11.2.0.3

p10404530_112030_Linux-x86-64_1of7.zip
p10404530_112030_Linux-x86-64_2of7.zip

release 11.2.0.4

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

Continue reading

Instance caging in Oracle 11g

Consolidation is very popular word today to save costs:
  • buy one huge server with a lot of CPU, memory, huge storage, fast network
  • put as many as possible databases on it
  • utilize it as efficient as possible
But consolidation brings problem how to share resources between databases:
  • CPU (this article is related only to this resource)
  • storage
  • memory
  • network

As default an Oracle database can see/use all CPUs on a host. In 11.2G there is new option called “Instance Caging” which enables to limit number of CPUs which can be seen/used by a database.

Continue reading

Table compression in Oracle 11g

This article presents new features related to table compression in Oracle 11g. It’s not presenting compression related to Exadata.

Compression option is extended in 11g and have following features:

  • requires compatibility 11.1 for new functionality
  • extended COMPRESS caluse: COMPRESS [BASIC | FOR OLTP]
  • BASIC – default, bulk-load operations from prior releases
  • FOR OLTP – it’s new used for OLTP + direct loads
  • BASIC and FOR OLTP are not supported for tables with more than 255 columns
  • only available for heap organized tables
  • compression can be different for each partition
  • COMPRESS [BASIC|FOR OLTP] is not used for BasicFile LOBs
  • new columns in views DBA/ALL/USER_TABLES and DBA/ALL/USER_TAB_PARTITIONS – COMPRESS, COMPRESS_FOR
  • columns can’t be dropped for compressed tables only set as unused

Continue reading

UTL_MATCH – string similarity/matching 11G

UTL_MATCH is very interesting package which implements quite advanced algorithms to match two strings. You can use them to quickly match name like First Names or Last Names.

Two basic algorithms are implemented here:

Edit Distance” – is a measure of Similarity between two strings, s1 and s2. The distance is the number of insertions, deletions or substitutions required to transform s1 to s2

Jaro-Winkler algorithm” is another way of calculating Edit distance between two strings. It is a String Comparator measure that gives values of partial agreement between two strings. The string comparator accounts for length of strings and partially accounts for typical human errors made in alphanumeric strings

Continue reading

PL/SQL Result Cache 11g

This article presents how result cache can be used together with PL/SQL.

it’s important to read following article

SQL Result Cache 11g

Let’s prepare simple function that is using result cache functionality

create or replace function sleep_fnc(id number)
return number
result_cache
is
begin
  dbms_lock.sleep(5);

  return 5;
end;
/

FUNCTION SLEEP_FNC compiled

Continue reading

SQL Result Cache 11g

Before reading this article I suggest to read article about deterministic functions

Deterministic functions

SQL Result cache is nice addition to deterministic functions.

It enables to:

  • cache the result of a query or query block for future reuse

  • cached results are available for all sessions unless they are staled

SQL Result cache are especially usefull for warhouses were users read many rows and return few rows. Returned rows are saved in SGA and can be reused if the same query is executed once again in the same or other session. Cached SQL results become stale when data in the objects being accessed by the query is modified.

NOTE: for RAC configuration each instance has got own private result cache and can’t be used by other instances. However invalidations work accross instances.

Continue reading