APPROX_COUNT_DISTINCT – fast count distinct values Oracle Database 12C release 1 (12.1)

APPROX_COUNT_DISTINCT is new function available since version 12.1.0.2. It returns approximate number of rows that contain distinct values of expr.

APPROX_COUNT_DISTINCT(expr)

It’s alternative to the COUNT (DISTINCT expr) function, which returns the exact number of rows that contain distinct values of expr.

For processing large amounts of data it’s significantly faster than COUNT, with negligible deviation from the exact result.

Continue reading

ORA-12528: TNS:listener: all appropriate instances are blocking new connections

If your database was started in nomount mode you can encounter following error

SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 16 12:39:28 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter user-name: sys@ora12c as sysdba
Enter password:
ERROR:
ORA-12528: TNS:listener: all appropriate instances 
are blocking new connections

To solve this problem you need to make static registration of your service in listener.ora.

Settings before solving the error

tnsnames.ora

ORA12C =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = laptop)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORA12C)
    )
  )

listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:\app\oracle\product\12.1.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\app\oracle\product\12.1.0\dbhome_1\bin\oraclr12.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = laptop)(PORT = 1521))
    )
  )

New settings

listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:\app\oracle\product\12.1.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\app\oracle\product\12.1.0\dbhome_1\bin\oraclr12.dll")
    )
    (SID_DESC =
      (GLOBAL_DBNAME = ORA12C)
      (ORACLE_HOME = D:\app\oracle\product\12.1.0\dbhome_1)
      (SID_NAME = ORA12C)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = laptop)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = D:\app\oracle\product\12.1.0\dbhome_1\log

You need to restart listener to make it effective. Once it’s done you should be able to connect to your database which is working in nomount.

SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 16 12:50:15 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter user-name: sys@ora12c as sysdba
Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit
Production With the Partitioning, OLAP, Advanced Analytics 
and Real Application Testing options

SQL>

Have a fun :)

Tomasz

Data Redaction Oracle Database 12C release 1 (12.1)

Data Redaction: Overview

This new feature enables to easily prevent the display of sensitive data to end-users by performing redaction in each application. It’s very simple to implement and very efficient. Data are modified on the-fly right before results are returned to applications.

  • on-the-fly redaction based on username, ip address, application context and other factors
  • transparent, consistent enforcement in the database
  • no measurable impact on production workloads
  • appropriate for call centers, decision support systems, and systems with PII, PHI,and PCI data

Data redaction is automatically turned off for following operations:

  • backup and restore
  • import and export
  • patching and upgrades
  • replication
  • sys connections are always exempt from redaction policies

Data Redaction 1

Continue reading

ORA-01000: maximum open cursors exceeded

ORA-01000: maximum open cursors exceeded – It’s typical error encountered in Oracle database. It happens when too much cursors are opened on an oracle instance.

Maximum number of opened cursors for an instance is limited by parameter

open_cursors

usually for small databases it’s about 300 and can be very huge for bigger ones. To increase this parameter it requires to bounce database instance.

To find out who opened many cursors on instance use following query

SELECT SID, count(*) ses_cursors, sum(count(*)) OVER() total_cursors
FROM v$open_cursor
GROUP BY SID
ORDER BY 2 DESC;

       SID ses_cursors total_cursors 
---------- ----------- -------------
       572         115           837 
      2835          95           837 
...
      1987           1           837 
      1417           1           837 

 41 rows selected

then you can investigate a given session why so many cursors are opened.

Have a fun :)

Tomasz

Temporal Validity in Oracle Database 12C release 1 (12.1)

Temporal Validity is very interesting feature in Oracle 12C that provides ability to scan effectively Gantt data:

  • adds (one or more) “time dimension” to a table by using current columns or using columns automatically created by database
  • enable using simple SQL syntax to filter the columns to access only active data using Oracle flashback technology

Following picture shows five records Rec1 … Rec5 that are active only in a period of time (represented in the picture as |————|). Usually the most complex part for programmers is to filter active records in specific dedicated time:

  • for Filter1 – valid records are: Rec1, Rec2, Rec3, Rec5
  • for Filter2 – valid records are: Rec1, Rec2, Rec4, Rec5
  • for Filter3 – valid records are: Rec1, Rec4, Rec5

temporal_validity_1

Typical example could be Employee table with two “time dimension” columns :

  • hire_start_date – when employee started to work
  • hire_end_data – when employee ended work

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

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