Conditional uniqueness

A quick fly through the options for conditional uniqueness.

Requirement #1: I want uniqueness on a column but only under certain conditions.

For example, I have an active flag and I want to make sure there is only one active record for a particular attribute but there can be many inactive rows.

Initial setup:

create table t1
(col1      number       not null
,col2      varchar2(24) not null
,is_active number(1)    not null
,constraint pk_t1 primary key (col1)
,constraint  (more...)

Oracle XE 11g Win64 on Microsoft Azure cloud


(Or: "How to build your own Oracle Cloud in 25 minutes"... ! )

After downloading the recently released Oracle Express Edition for 64-bit Windows, I decided to test it out on a Windows Server 2008 R2 running in the Azure cloud (recently renamed from "Windows Azure" to "Microsoft Azure").



Here are the high-level steps needed to get this up and running:

  1. Sign up for Azure and login to the management portal.
  2. Create a new Virtual (more...)

Oracle Express Edition (XE) 11g for Windows 64 bit


As announced by Kris Rice (who is the product manager for SQL Developer, Apex Listener and Oracle XE), the Oracle Express Edition (XE) Database version 11g is now (finally!) available for 64-bit Windows.

It's been a looong wait; the 64-bit version for Windows was expected to be released around the same time as the 32-bit version, some 3 years ago.

Anyway, it's here now, which is great! Thanks Kris! :-)


Oracle Express Edition 11g (more...)

ODTUG Kscope 2014: Monday

Monday morning started with the opening keynote. At the same time the Dutch soccer team played Chile (which the Dutch won with 2-0). A small Dutch delegation went to watch the game, all dressed in orange, I was one of them.
During the keynote the location of Kscope 2015 was announced: Hollywood, Florida.
Lunch was special, there were lunch and learn. There were "reserved" tables were you could sit on and talk tech with ACE (more...)

ODTUG KScope14 Sunday APEX Symposium

The ODTUG Kscope conference always starts with a full day symposium. There are several specialized tracks going on, and I attended the APEX track (mainly).
The room was packed, I would estimate around a 175 delegates.
Joel Kallman started the day with an overview of the history of APEX, including a video of Steve Balmer being very enthousiastic about APEX. He also told a bit about the background and how some features evolved throughout the (more...)

MEMBER OF comparison of PL/SQL and SQL

In the Kscope14 sunday symposium today, Steven Feuerstein explained that MEMBER OF syntax was slow in SQL and fast in PL/SQL. I challenged him that perhaps it was missing indexes on the nested table? My mistake - I got the task of testing it and see if that was the case... So I tested and was surprised at the answer.

I'm creating a nested table type and a table with a column of that type (more...)

Verwendung von SQL Server Query Hints

In der SQL Server Welt betrachtet man Query Hints offenbar ähnlich argwöhnisch wie im Oracle Umfeld - als "best pratice" gilt: man sollte sie vermeiden und alle Entscheidungen dem Optimizer überlassen. Thomas Kejser sieht das ein wenig anders:
For a complete novice of SQL Server, I would agree. But to call avoiding query hints a “best practise” is taking it too far. For anyone with a little experience who knows what they are doing, I (more...)

Preview [video]: Apps HA Solutions for Planned & Unplanned DB Downtime w UCP

http://bit.ly/V0Dycf 

Literally speaking

Reading Scott Wesley's blog from a days ago, and he made a remark about being unable to concatenate strings when using the ANSI date construct.

The construct date '1900-01-01' is an example of a literal, in the same way as '01-01' is string literal and 1900 is a numeric literal. We even have use some more exotic numeric literals such as 1e3 and 3d .

Oracle is pretty generous with implicit conversions from strings (more...)

Rowsource-Statistiken nach Query-Abbruch

Ein nützlicher Hinweis von Randolf Geist im OTN-Forum:
one thing that is not so commonly known about the Rowsource Statistics is that *no matter how* the statement execution completed, the data will be available. So a long running query can be cancelled and the Rowsource Statistics will still be populated (but only after the statement execution stopped in some way). Usually the information then available is already good enough to tell why the execution takes (more...)

External Table Access

I left to chance where students would attempt to place their external files in a Linux or Unix implementation. As frequently occurs, they choose a location in their student user’s home directory. Any attempt to read an external table based on a file in this type of directory fails because it’s not accessible by the Oracle user. You can’t simply chown the directory and files in the directory.

The failure returns the following result:

SELECT  (more...)

Oracle APEX 5 EA2 ready for consumption

After last night's teaser I thought I'd try apexea.oracle.com again to see if EA2 was ready, and it is!

and it looks darn sexy

Confirmed in Twitter by some big-wig APEX dude at Oracle
I've had bit of a play and it it's very impressive - plenty (more...)

My sessions at JavaOne and OOW 2014

APEX 5 EA 2 is almost upon us

KScope14 is just days away from kicking off and hours after I realised the live stream of Patrick Wolf's APEX 5.0 Page Designer presentation was actually at a reasonable Perth hour (9:30pm), I found out I wasn't going to be home to watch it - darn it!

Luckily in this age of the internet there are other reasons to get excited. Tonight while I continued to write my Prezi on how APEX 5 will (more...)

Is your database secure? Are you sure? Are you *really* sure?

Credit for finding this bug is given to Daniel Ekberg – https://www.linkedin.com/profile/view?id=10435009. He got the official credit given by Oracle Corp for helping out with security related bugs in the January 2014 CPU. He found the bug and had the tenacity to track down and prove that it was a bug and not just a flaw in the logging mechanism where this first was indicated to occur.

Today is the day when (more...)

The empire strikes back!

About 3 years ago SAP started to create a new database engine, SAP HANA, with a pure In-Memory concept. SAP aggressively move it's new database to it's software stack. The database was not used as cache but for running Enterprise Application satisfying both OLTP and OLAP demands on the same database.  

As oracle announced the new Oracle 12c in late 2012 there the 'c' was for cloud based computing, means the simplification of creating (more...)

9th Planboard DBA Symposium

A couple of months ago, Nienke Gijsen invited me to speak about materialized views at the upcoming Planboard DBA Symposium. Because I had the pleasure of presenting before, I knew the conference is always well organized and a pleasure to visit. So of course I accepted the invitation. We agreed I'd talk on "just" incremental refreshes of materialized views using materialized view logs and about my

Discuss Your Modernization Challenges with Oracle at Kscope14

With Kscope14 only a week away, I wanted to share a unique opportunity with my readers. This year Oracle Product Manager, Michael Ferrante, is going to be answering questions about modernizing with Oracle Forms at the AuraPlayer booth (#414). If you are concerned about the future of your Oracle Forms investment or if you are unsure how to update, modernize or go mobile, then you can start discussing these challenges directly with Oracle.

Michael will be available to (more...)

Autonomes Logging mit postgres

Im Rahmen von ETL-Operationen ergibt sich immer wieder die Situation, dass man eine Log-Message in ein ETL-Log schreiben möchte, ohne dadurch die eigentliche Transaktion per commit festzuschreiben, denn der Umfang der Transaktionen sollte nicht durch die Log-Einträge bestimmt werden. Da die Log-Nachricht im Fall eines Rollbacks der Transaktion ebenfalls zurückgerollt werden würde, kann man sich allerdings auch nicht auf den Abschluss der eigentlichen ETL-Operation verlassen. Zur Lösung dieses Problems bietet Oracle das Pragma Autonomous Transaction (more...)

NULL-Werte in Indizes

Jonathan Lewis hat in seinem Blog zuletzt zwei Artikel veröffentlicht, die sich mit der Speicherung von NULL-Werten in Indizes beschäftigen:
  • Bitmap Nulls: liefert ein kompaktes Beispiel für die Möglichkeit der Verwendung von Bitmap-Indizes bei der Einschränkung über ein IS NULL Prädikat: bekanntlich enthält ein B*Tree-Index keine Einträge für Datensätze, bei denen alle indizierten Spalten einen NULL-Wert enthalten - während im Bitmap Index ausnahmslos jeder Datensatz indiziert wird. Somit kann eine Bedingung column IS NULL im (more...)