Reverse engineer and discover foreign keys with SQL Developer Data Modeler

Originally posted on HeliFromFinland:
You can easily document your database with Data Modeler: just reverse engineer the database with File-> Import -> Data Dictionary. But what if the database has no foreign keys? What’s the point of documenting then? Just tables that has nothing to do with each other…. Well, Data Modeler can help with…

first_rows und first_rows_n

Jonathan Lewis weist in seinem Blog darauf hin, dass die Optimizer-Modi first_rows und first_rows_n deutlich weniger gemein haben, als man vielleicht annehmen könnte. Während first_rows seit langem nur noch aus Gründen der Abwärtskompatibiltät unterstützt wird und diverse Heuristiken benutzt, um die Optionen des Optimizers einzuschränken, arbeiten die first_rows_n-Varianten strikt kostenbasiert, ohne dem Optimizer massiv in die Arbeit zu pfuschen. Tatsächlich gibt es allerdings einen Fall, in dem first_rows_n sich so verhält wie first_rows, nämlich die (more...)

Perth 2014 Professional Learning Event

There's a little over a week left to this year's major Oracle user group event in Perth, so attention all locals (or even east coasters) - if you're interested in picking up new skills & ideas - you haven't got much time left to register!

This year it's called a "Professional Learning Event" instead of a conference and the program reflects it - plenty of hands on workshops mixed in with normal sessions. Considering it's (more...)

Temp Table Transformation

Randolf Geist erläutert in seinem Blog das Verhalten der Temp Table Transformation, die hauptsächlich (aber nicht ausschließlich) dann anzutreffen ist, wenn eine CTE im Plan mehrfach referenziert wird und mindestens ein (Filter- oder Join-) Prädikat enthält, und weist insbesondere darauf hin, dass diese Transformation nicht kostenbasiert erfolgt, sondern automatisch. In Fällen, in denen eine zusätzliche Bedingung jenseits des CTE-Kontexts eine dramatische Reduzierung der Ergebnismenge hervorrufen würde, ist diese Materialisierung demnach ausgesprochen kontraproduktiv. Darüber hinaus (more...)

Histogramme, Cardinality und der Optimizer Mode first_rows_100

Eine interessante Antwort auf die Frage nach unplausiblen Cardinalities, die sich trotz frequency histogram ergeben, hat Jonathan Lewis in einem OTN-Forums-Thread gegeben. Der vom Fragesteller gelieferte handliche Testfall zeigte sehr deutlich, dass in seinem System mit 11.2.0.4 für einfache Queries sehr merkwürde Cardinalities ermittelt wurden, obwohl das vorhandene Histogramm die tatsächliche Verteilung exakt abbildete. Diverse Beiträger konnten nur feststellen, dass das Verhalten in ihren Systemen (zwischen 11.2.0.1 und 12. (more...)

ODTUG Webinar: APEX 5 Page Designer

I'll be presenting an online webinar for ODTUG not long after the Perth conference.

It will be the same session I did for AUSOUG back in July, which is lucky since it's 6am for us Perth locals. It's a close look at the Oracle APEX 5 Page Designer, and what it means to you as an APEX developer.

Check the webinar registration page for details on the abstract and help with the time zone (more...)

Dead Code Kills

Your applications contain 20-30% dead code that is never executed. Not just commented-out code that programmers have left in “just in case”, but routines never called, branches never executed and screens never displayed.

The cost of software maintenance depends on the size of the code base, but systems with dead code are more expensive to maintain. This is because every time a developer comes across a dead piece of code, he becomes unsure: “I wonder (more...)

Next/Previous buttons from Interactive Report results

What could be simpler than a set of "Next" and "Previous" buttons?

What could be simpler than a set of “Next” and “Previous” buttons?

I love Interactive Reports, they make it easy to deliver a lot of power to users for very little development effort. However, with that power comes some complexity for building certain features not available in the base Apex toolset.

I had an IR with a fairly costly and complex query behind it, linked to another screen to view the details for a record. The (more...)

ADF Archtitecture Practice – JDeveloper Silent Patch

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


Version 12.1.x

In this version  the Oracle OPatch is delivered with the silent installation of JDeveloper.

All what you need is the patch file for your platform (e.g. p19591087_121300_Generic.zip for JDEVADF SECURITY (more...)

Oracle 12c Multitenant – Inmemory (pre-basics)

A couple of very, very basic observations on getting going with 12c Inmemory in a multitenant database.

1. When trying to set inmemory_size within a PDB when inmemory_size is 0 in the CDB
ORA-02096: specified initialization parameter is not modifiable with this option

SQL> alter session set container = cdb$root;
Session altered.

SQL> select value from v$parameter where name = 'inmemory_size';
VALUE
--------------------------------------------------------------------------------
0

SQL> alter session set container = orcl;
Session altered.

SQL> alter  (more...)

Index Reorganisation

Franck Pachot beantwortet in seinem Blog eine Frage, die mir gelegentlich durch den Kopf gegangen ist, ohne dass ich mich ernsthaft um ihre Beantwortung gekümmert hätte: welche Form der Reorganisation eines Index ist unter welchen Voraussetzungen die passendste? Dazu liefert er einen einfachen Testfall, zu dem er die relevanten Statistiken zum Index und zur Ressourcen-Nutzung im Rahmen der Reorganisation beisteuert. Die Ergebnisse lauten ungefähr:
  • COALESCE: eine Online-Operation, die die leaf blocks defragmentiert, aber nicht wieder (more...)

Check Constraints

Oracle Database 12c introduces a SEARCH_CONDITION_VC column to the CDB_, DBA_, ALL_, and USER_CONSTRAINTS views. The SEARCH_CONDITION_VC column is a VARCHAR2 data type equivalent to the search condition in the LONG data type SEARCH_CONDITION column. Unfortunately, Oracle Database 11g and earlier versions requires you to convert the LONG data type to a VARCHAR2 for the equivalent behavior. This post provides you with a function to help you do that in (more...)

ORA-20104: create_collection_from_query ParseErr:ORA-00918: column ambiguously defined

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.   If you are wondering why you are getting this error message after following the example in the documentation describing procedure CREATE_COLLECTION_FROM_QUERYB2 in the APEX_COLLECTION API, the quick answer is that the sample code is flawed. ORA-20104: create_collection_from_queryb2

Advanced Index Compression in 12c

Leider schaffe ich es derzeit nicht, mir eigene Gedanken zu machen, so dass ich mich darauf beschränken muss, anderer Leute Ergebnisse zu reproduzieren. Einer der Autoren, bei denen ich das recht gerne mache, ist Richard Foote, der inzwischen wieder sehr regelmäßig neue Artikel schreibt. Zwei davon betreffen die in 12c eingeführte Advanced Index Compression:

SQL Brainteasers: Guess the Country

Same concept, different theme. All the SQL statements below are clues to the English spelling of countries.

Can you get them all?

As always, put your answers in the comments!

select *
from   german_affirmative
join   cooking_vessel;

select *
from   nuts
where  anaphylaxis = 'sexually transmitted';

select *
from   olde_english_the
join   males;

select *
from   elements
where  atomic_number = 47;

select *
from   programming_languages
where  able_to = 'influence PL/SQL';

select *
from   places
 (more...)

Mastering Oracle ADF Bindings: Advanced Techniques

My Oracle OpenWorld presentation “Mastering Oracle ADF Bindings: Advanced Techniques” is now available for download from the OpenWorld web site. In this presentation, I talk about

  • Reading the Bindings tab
  • Bindings in the ADF lifecycle
  • Working programmatically with bindings
  • Getting the binding from a component
  • Working with dynamic bindings
  • Creating a binding programmatically

If you are interested in creating bindings programmatically, you should read Eugene Fedorenko’s blog post on this. He also makes (more...)

Detailsinformationen zu den system statistics

Franck Pachot liefert im dbi services Blog ein nettes Skript zur Analyse der Systemstatistiken, auf denen das Costing des Optimizers basiert. Dabei ergänzt er zu den Basisinformationen aus (SYS.)AUX_STATS$ diverse Angaben zu ihrer Berechnung, die man sich ohne Skript mehr oder minder mühsam zusammensuchen muss.

Oracle 12c: Temporal Validity, multiple on one table – Part Deux

One of the most wonderful things of Oracle Open World are the Demo-Grounds. When you want to learn more about a certain feature, this is the place to go to. The actual developers and product managers are there to answer your questions and more!

In a previous blog I had written about Temporal Validity in Oracle 12c and whether it would be possible to have multiple validity periods on one table. You can read that (more...)

PL/SQL Challenge Roundtable

Do you write PL/SQL? Me too!

Trouble is, sometimes it's hard to decide how to structure your packages - particularly in an APEX project. Over at the PL/SQL Challenge website run by Steven Feuerstein and friends there is a page dedicated to roundtable discussions.

I submitted my question not so long ago and hope to get some interesting responses. Why don't you give the site a visit and contribute? The discussions usually last for about (more...)

Making it Easy to do the Right Thing

I was just in Stockholm visiting a customer, and I noticed a new feature in my Volvo rental car: You could set the cruise control to a speed limit.

Speed_limit(apologies for the blurry picture, it was taken by my Narrative shirt pocket camera)

You drive the car as you would normally do, but when you approach the set speed, the car does not go any faster when you press the accelerator further. Unless you kick (more...)