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

Advanced Index Compression Oracle Database 12C release 1 (12.1)

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

It improves compression ratio for multi column indexes that in previous releases were not good candidates for prefix compression. It reduces the size of all supported unique and non-unique indexes. It still providing efficient access to the indexes.

Notes about advanced index compression

  • it is not supported for bitmap indexes or index-organized tables.

  • it cannot be specified on a single column unique index.

  • it can be specified for whole index or just on index partition level
  • extended clause COMPRESS ADVANCED LOW
    COMPRESS ADVANCED LOW

Continue reading

Oracle Database 12.1.0.2 patch

New important patch 12.1.0.2 is available on Oracle Support.The patch brings a lot of new features:

  • Oracle Database In-Memory
  • Oracle Big Data SQL
  • Oracle JSON Document Store
  • Oracle REST Data Services
  • Improvements to Oracle Multitenant
  • Advanced Index Compression
  • Zone Maps
  • Approximate Count Distinct
  • Attribute Clustering
  • Full Database Caching
  • Rapid Home Provisioning

All details are here MOS Note:1905806.1. The patch is very important because of Oracle Database In-Memory. It can really drastically change your Oracle battles and games :p. Personally I consider this patch as one big NUKE blast !

As of tomorrow I’m planing to use  this version for new articles.

Have a fun :)

Tomasz

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

Security extension INHERIT [ANY] PRIVILEGES Oracle Database 12C release 1 (12.1)

This new extension in Oracle 12C closes security gap in previous release. When  a user (INVOKER) calls a PL/SQL code with pragma AUTHID CURRENT_USER of other user  then the code is executed with privileges of current user (INVOKER rights).

It’s very useful but can be dangerous if called code has got harm code. For example we can create simple function “get_me_dba_fnc” in schema SH with the pragma AUTHID CURRENT_USER and call it as user TOMASZ with high privileges like DBA and  by mistake grant DBA role to SH without noticing it.

role inherit

In Oracle 12C you can control inheritance of privileges by using following clauses

  • INHERIT PRIVILEGES
  • INHERIT ANY PRIVILEGES

Continue reading

View security extension BEQUEATH CURRENT_USER Oracle Database 12C release 1 (12.1)

This new feature in Oracle 12C changes behavior of calling functions within views. In previous releases all functions used within a view were called using rights of creator of the view (DEFINER rights) no matter if the functions used pragma AUTHID CURRENT_USER(INVOKER rights).

In Oracle 12C you can specify dedicated pragma  during view creation BEQUEATH CURRNT_USER so all functions that are using AUTHID CURRENT_USER will be called with INVOKER rights.

It’s worth to read article: Authid current_user, authid definer

Continue reading

Install Oracle 12C Release 1 (12.1) on Centos Linux 7

This article presents how to install Oracle 12C(12.1.0.1) Release 1 on Centos Linux 7.

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

Software

Software for 12CR1 is available on OTN or edelivery

Database software

linuxamd64_12c_database_1of2.zip 
linuxamd64_12c_database_2of2.zip

Continue reading

Install Oracle 11G Release 2 (11.2) on Centos Linux 7

This article presents how to install Oracle 11G on Centos Linux 7.

Read following article how to install Centos Linux 7: Install Centos Linux 7 (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.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.4

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

Continue reading

Install Centos Linux 7

This article presents how to install CentOS Enterprise Linux 7.

I assume you have already downloaded CentOS Enterprise Linux 7 64 bit(about 4 G) and you know how to use VirtualBox 64 bit(100M). Create virtual machine with default settings for RedHat Linux 64 bit. For start 1GB ram and 64G for disk is enough plus increase video memory to 128M and turn on acceleration 3D. Rest of options you can keep default.

Centos7_01

Continue reading