The mess that is fast-refresh join-only Materialized Views

Every now and then you come across a feature or a combination of features which has turned into such a dog’s dinner that you wonder how many people can possibly be using it.

This week – fast fresh materialized views.
I would have thought that this was a very commonly used feature.

This is quite a long article so I will do a top-level TOC first

  1. Why am I looking at a fast-refresh, on-commit, join-only (more...)

Privilege Analysis mit 12c

Eine kleine persönliche Beichte, die regelmäßige Leser vielleicht nicht allzu sehr überrascht: Performance-Fragen interessieren mich deutlich mehr als solche der Security. Und noch ein Geständnis: ich habe gelegentlich Applikationen ausgeliefert, deren Berechtigungen größer waren als erforderlich - weil es mir zu mühsam und zu öde war, mich bis ins Detail mit der Privilegienvergabe zu beschäftigen. Für Entwickler, die unter ähnlichen Charakterschwächen leiden, gibt es in 12c jetzt ein großartiges neues Feature: die Privilege Analysis, bei (more...)

Datapump Bug: Scheduler Jobs Not Imported in 11.2

Just the other day I hit the unpublished Bug 16445132: DBMS_SCHEDULER PROBLEM WITH DATABASE MIGRATION VIA EXPDP/IMPDP. It affects Scheduler Jobs that were created to end with a corresponding Scheduler Window. Here’s an excerpt of a Data Pump Logfile: ORA-39083: Object type PROCOBJ failed to create with error: ORA-27469: STOP_ON_WINDOW_EXIT is not a valid job […]

Instanz-Crash bei Latch-Analyse

Stefan Koehler analysiert bei SAP on Oracle eine Situation, in der das manuelle Setzen und Freigeben von Latches über Oradebug zu einem Instanz-Crash führt. Relevanter als das Ergebnis ist für mich die methodische Vorgehensweise bei der Analyse - sollte ich mich mal wieder mit Latches beschäftigen wollen, dann wäre es nützlich, diesen Artikel noch mal intensiver anzuschauen.

Chrome Device Mode and Mobile Emulation

Recently a colleague suggested I take a fresh look at the mobile emulation button in Chrome's developer tools.

Apparently this was upgraded earlier in the year and it's a shame I didn't have this a year ago!

Chrome Developer Tools Mobile Emulation
Some immediate advantages I see for APEX development:




  • Specify the device you'd like to emulate from a decent range of contemporary devices
  • This viewport can then be easily scaled to suit whatever monitor (more...)

Querying a Object Type

I demonstrated a number of SQL approaches to reading object types in Appendix B of the Oracle Database 12c PL/SQL Programming book. For example, the easiest one to construct and return the results from a TO_STRING member function uses the TREAT function:

SELECT TREAT(base_t() AS base_t).to_string() AS "Text"
FROM   dual;

However, it seems that I could have provided one more. Here’s an example of how you can (more...)

Slides for my german presentation #DOAG2014 “The Dirty Dozen – ADF Migration to 12c in the IKB” available

I have presented last week on DOAG 2014 in Nuremberg. For all non DOAG members – you can find the german presentation on slideshare too.

doag2014


Filed under: Uncategorized

Zone Maps in 12c

Nur eine knappe Sammlung von Links zum Thema: Zone Maps sind eine neue Errungenschaft in 12c und stellen eine Index-artige Struktur dar, die es erlaubt, eine Filterung von Blöcken anhand der Definition von Wertebereichen durchzuführen. Dazu sind mir zuletzt mehrere Artikel von Richard Foote und ein Beitrag im Data Warehouse Insider Blog begegnet:

Baselines und Parsing

Jonathan Lewis weist darauf hin, dass Baselines keine große Hilfe sind, wenn es darum geht, den Aufwand beim Parsen komplexer Join-Operationen zu verringern. In seinem Beispiel führt ein Join von acht Tabellen zur Prüfung von 5040 Join-Reihenfolgen - wobei die Einstellung von _optimizer_max_permutations=2000 offenbar ignoriert wird - und das ändert sich auch dann nicht, wenn eine SQL Plan Baseline für die Query erzeugt wird, weil der Optimizer zunächst versucht, einen besseren Plan zu erzeugen, ehe (more...)

Finding Direct Indexes

If you’re not using Toad DBA Suite, it’s sometimes hard to find solutions. Somebody wanted to know how to find indexes that aren’t that aren’t indirect. Indirect indexes are those created for a primary key because a primary key column or set of columns are both not null and uniquely constrained. You can’t drop a unique index for a primary key without dropping the primary key constraint that indirectly created it.

The following query (more...)

Date Ranges in Data Warehouses using Oracle 12c

When you load data with an “effective date” into a Data Warehouse, what happens when the new data is the same as yesterday’s? Do you add a new record with the new “effective date” or do you update a date range in the existing record? At Open World last month, I got both answers: Tim Gorman […]

ODTUG from afar

I would like to extend my apologies to those people who persevered just now with our attempt to bring to you my presentation on APEX 5 Page Designer thanks to ODTUG.

Due to the massive time zone differences, the appropriate time on my end (6am) means I connect from home - where I'm limited to an ADSL connection. My location is even a bit of a dead zone for my smartphone's 4G plan which is (more...)

ANSI Join Syntax und Prädikatanordnung

Die ANSI-Syntax zur Formulierung von Joins finde ich grundsätzlich deutlich lesbarer als Oracles traditionelle Schreibweise und würde sie vermutlich regelmäßig einsetzen, wenn sie nicht so viele Bugs hervorrufen würde. Einer davon ist gestern in einem OTN-Thread aufgetaucht. Dort hatte sich der Fragesteller darüber gewundert, dass zwei Varianten eines Outer-Joins unterschiedliche Ergebnisse lieferten, je nachdem, ob die ON-Prädikate direkt bei den zugehörigen FROM-Klauseln erschienen, oder getrennt von diesen in einem gemeinsamen abschließenden Block. Mein erster Gedanke (more...)

ADF Archtitecture Practice – JDeveloper Silent Install of Extensions

This article describes how to standardize your JDeveloper Installation as requested in Planning & Getting Started – Team, Skills and Development Environments. It is part of the ADF Architecture Practice lessons and describes the installation with examples for windows.


Version 12.1.x

There is a tool ojwebupdate to handle this. It is fully supported but it’s not documented at the moment, documentation is planned for 12.1.4. But you can get a lot (more...)

SQL Plan Directive in 12c

Mir ist gerade aufgefallen, dass ich bisher nichts über die in 12c eingeführten SQL Plan Directives geschrieben habe, die mir erstmals im White Paper Optimizer with Oracle Database 12c von Maria Colgan begegnet sind. In der Dokumentation wird die SQL Plan Directive folgendermaßen definiert:
A SQL plan directive is additional information and instructions that the optimizer can use to generate a more optimal plan. For example, a SQL plan directive can instruct the optimizer to (more...)

My DOAG session re:Server-side JavaScript

#DOAG Wed 19/11 17:00 rm HongKong Server-side #JavaScript (#NodeJS) progrm#OracleDB using #nashorn & Avatar.js --#db12c @OracleDBDev #java

Will post shortly ablog re: JavaScript Stored Procedures. 

See you at #DOAG2014 “The Dirty Dozen – ADF Migration to 12c in the IKB” room Kopenhagen on 11/19/2014 at 10:00 am

doag2014

The presentation is ready, time for travelling now.

I will speak over our experiences with following

  • provide user software
  • install servers
  • iterative test migrations
  • migration plannig
  • real migration
  • and the problems and pitfall’s we find on our way

If you speak german and you are interested in some of this, you should come and attend!


Filed under: Uncategorized

Thoth Gateway 1.4.0 beta release 1 available

As mentioned in my previous post about the Thoth Gateway, a mod_plsql and Apex Listener alternative that runs on IIS, the goal for the next release of the gateway (tentatively called version 1.4.0) is to change the Oracle Data Provider for .NET (ODP.NET) from the "Unmanaged" to the "Managed" driver. By using the Managed (pure .NET) driver, the installation of the Thoth Gateway becomes even simpler, because it removes the need (more...)

Statement-Level Atomicity

So important yet often ignored or misunderstood! No, not me, but “statement level atomicity”. Whenever you call the Oracle database to change data, the result will be all or nothing: Oracle will either do everything you asked it to, or nothing at all. This is true for SQL and PL/SQL. What the documentation says From […]

rdbms-subsetter

I've never had a tool I really liked that would extract a chunk of a large production database for testing purposes while respecting the database's foreign keys. This past week I finally got to write one: rdbms-subsetter.

rdbms-subsetter postgresql://user:passwd@host/source_db postgresql://user:passwd@host/excerpted_db 0.001

Getting it to respect referential integrity "upward" - guaranteeing every needed parent record would be included for each child row - took less than a day. Trying to get it to also guarantee (more...)