Einzelne Query aus dem Shared Pool löschen

Eigentlich war ich mir ziemlich sicher, dass ich hier irgendwo einen Link auf Kerry Osbornes Erläuterungen zum Löschen einer einzelnen Query aus dem Shared Pool untergebracht hätte - aber anscheinend ist das nicht der Fall. Daher hole ich zunächst dieses Versäumnis nach:

In-Memory Performance

Gerade habe ich noch mal überprüft, dass es Fälle gibt, in denen ich Artikel aus dem Oracle Scratchpad von Jonathan Lewis hier nicht verlinke. Das geschieht aber selten und gewöhnlich nur aus Gründen der Trägheit. Allerdings ist in der Tat nicht jeder Artikel von gleicher Bedeutung, manchmal sind die Themen auch relativ abgelegen und betreffen seltene Ausnahmefälle. Der aktuelle Artikel In-memory DB jedoch befasst sich mit einer recht grundsätzlichen Frage: wieso ist eine (column-store) In-Memory (more...)

Oracle Sample Schemas auf GitHub

Wieder nur eine kurze Notiz, aber eine mit einem aus meiner Sicht recht bedeutsamen Inhalt: wie man von Christopher Jones erfährt gibt es Oracles Beispiel-Schemata seit kurzem als Repository auf GitHub:
This repository contains a copy of the Oracle Database sample schemas that are installed with Oracle Database Enterprise Edition 12c. These schemas are used in Oracle documentation to show SQL language concepts. The schemas themselves are documented in Oracle Database Sample Schemas, 12c Release (more...)

MERGE and invisible columns = invisible documentation?

Oracle 12c introduced "invisible columns" to help us add columns to tables without breaking existing applications. The documentation explains how they work with SELECT and INSERT, but not MERGE. Here's what happened when I tried MERGE.

SQLCl – LDAP anyone?

since  we released our first preview of SDSQL, we've made  a lot of changes to it and enhanced a lot of things too in there so it would be more useable.  One specific one was the use of LDAP which some customers on SQLDeveloper are using in their organisations as a standard and our first release precluded them from working with this.

Well, to add this, we wanted a way that we could specify the (more...)

Blog update!

Hi everyone!

This blog hasn’t seen any updates since over a year now and I do apologize for that. However, the reason for that is simple. Last year I’ve decided to take one a new project. But before I was going to tell everybody about it I wanted to make sure that I also really got the time to do so. In the meantime I got the confidence that the project will succeed and that (more...)

Apex multilingual applications and build options

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates. Just a quick reminder for those who are devoloping Apex multilingual applications: Build options' state is propagated to translated applications at the time of seeding, so you need to be careful when changing the state of a build option in the primary language because you may end up

A PL/pgSQL Function

Somebody wanted to know how to write a basic PostgreSQL PL/pgSQL function that returned a full name whether or not the middle name was provided. That’s pretty simple. There are principally two ways to write that type of concatenation function. One uses formal parameter names and the other uses positional values in lieu of the formal parameter names.

Since you’ll probably test the two approaches, I’ve also provided a conditional drop statement for the full_name (more...)

New Year, New Name, Same Mission

Happy New Year! Looks like it will be another busy one. So, let’s get going! Yes, I just changed the name of my blog from “Oracle Data Warrior” to “The Data Warrior.” Why? Well, for one, my company name is Data Warrior LLC, so it seemed to make sense. Plus over the last few years […]

Größe des Initial Extents für Partitionierte Tabellen

Dom Brooks weist in seinem Blog auf zwei Punkte hin, von denen mir (wie ihm) der erste komplett entgangen war:
  • die Default-Größe des Initial Extents für partitionierte Tabelle ist seit auf 8MB erhöht worden, davor betrug sie 64KB. Das kann im Fall geringfügig gefüllter Partitionen zur Verschwendung von Speicherplatz führen, wobei solche Partitionen natürlich unter Umständen auch ein Anlass sein könnten, über die Partitionierungsstrategie nachzudenken.
  • der zweite Punkt war mir klar: (more...)

Partition gotchas

Two minor partition gotchas on partition extent sizing and on the number of partitions in an interval partitioned.

First – old news – note that initial extent size for partitioned tables changed in
Somehow this passed me by and I was late to the large extent party – duh!
Influenced by parameter _partition_large_extents, the default was changed to 8MB rather than 64K for autoallocated ASSM extent sizing.

Pre 11.2. (more...)

Moving from Procedural to Set-Based Thinking

Most people who are new to working with databases tend to think procedurally. That is: I have a table that I need to update, so:

  • Find the first row
  • Update the relevant columns with the relevant values
  • Find the next row
  • Update the relevant columns with the relevant values
  • Find the last row
  • Update the relevant columns with the relevant values

People who are familiar with databases and set-based thinking would simply do:

STEW_COMPARE_SYNC: Introducing the package

After many blog posts about comparing and synchronizing tables, I have united all the techniques I presented in one place. The STEW_COMPARE_SYNC package generates SQL for
  • Comparing tables, views and queries, both local and remote.
  • Synchronizing, or applying changes to target tables from either source tables or "Change Data Capture" input.

NLS_LANG für Oracle unter Windows

Vor ein paar Jahren habe ich hier eine ausgesprochen knappe Notiz untergebracht, um mich daran zu erinnern, den Registry-Schlüssel NLS_LANG unter Windows auf GERMAN_GERMANY.WE8PC850 zu setzen, um eine korrekten Darstellung von Umlauten in sqlplus zu erhalten. Dass man den Sachverhalt auch umfassend analysieren kann, hat mir jetzt Franck Pachot gezeigt, der das unterschiedliche Verhalten von WE8MSWIN1252 und WE8PC850 detailliert beschreibt: dabei ist WE8MSWIN1252 die default-Einstellung in der Registry und für GUI-Tools durchaus geeignet. Sqlplus allerdings (more...)

PL/SQL Fall Through?

Somebody wants to know how you can write a PL/SQL solution that mimics the fall through of a switch statement because PL/SQL doesn’t support a switch statement. It’s a question that I found interesting because there wasn’t a need for it when I figured out what he wanted to accomplish. Essentially, he wanted to know how to implement a nested loop where the first loop runs in ascending order and the nested loop runs in (more...)

SQL Brainteasers: Guess the Chocolate Bar

Some more SQL clues, this time to chocolate bars.

select iso_code
from   countries
where  capital_city = 'Taipei'
and    9 = roman;

select *
from   planets
where  position = 4;

select characters
from   thundercats
where  age = 'prepubescent';

select *
from   ships
where  crew = 'mutinous';

select *
from   cities
where  that_is = 'Eboracum';

select min(volume)
from   speech;

When you’ve got them, put your answers in the comments!

Compare and Sync without Primary Keys

I have written a lot about comparing and synchronizing tables. My examples always had both primary keys and non-key columns, so I could do updates along with inserts and deletes. What about the other tables? Here's a technique that works for them.

Convert to SQL Server?

I’m always amazed at the questions that pop up for me. For example, how do you convert an Oracle script that creates my Video Store model to a Microsoft SQL Server script. It’s not very hard but there’s one big caveat, and that’s the fact that system_user is a reserved word. That means you can’t create the Access Control List (ACL) table with a system_user name. The alternative, would be to convert the system_user table (more...)

Sync tables: generate MERGE using Unique constraint

In my post “Compare and sync tables: Generating the code“, I use the primary key constraint on the target table. A reader called “Bal” asked how to use a unique constraint instead. Test data I’m going to create a target table with a unique constraint, but without any NOT NULL constraints on the columns. UK1 […]

Parallel Query, OR Expansion und überflüssige Buffer Sorts

In den letzten Tagen hat Randolf Geist zwei Artikel zum Auftreten von überflüssigen Buffer Sort Operationen bei der Ausführung paralleler Queries veröffentlicht: