Archive for the 'Oracle' Category

Oracle 12c Multitenant: impdp fails w/ ORA-31625 and ORA-01031 because of Database Vault

Things are different in Oracle Database 12c with multitenancy option. My most recent example:

I tried to import a schema (new name “NEWSCHEMA”) with datapump IMPDP and REMAP_SCHEMA into the same pluggable database it has been exported from with EXPDP immediately before (name “OLDSCHEMA”), running as SYSTEM. I’m doing things like that with DBA permissions, since my users have lots of grants and stuff in the schemas, and when a DBA does the export and import, all settes right. (See the details for commands and parfiles below.)

But IMPDP fails with
ORA-39083: Object type INDEX failed to create with error:
ORA-31625: Schema NEWSCHEMA is needed to import this object, but is unaccessible
ORA-01031: insufficient privileges

So what? I’m SYSTEM and thus, DBA, and the user NEWSCHEMA is there. And SYSTEM of course has the “IMPORT FULL DATABASE” privilege, it’s a DBA! So you may think.
Read more…

Oracle: Did my SQL get worse over time? (AWR query)

Sometimes, we get statements to look at, and are told “it’s getting worse and worse”. Since DBAs are well advised not to take anything for granted and only to believe what they see with own eyes, here comes a SQL on AWR to see Buffer Gets per Minute, over time.

 (extract (day from (s.END_INTERVAL_TIME-s.BEGIN_INTERVAL_TIME))*24*60)+
from dba_hist_sqlstat t, 
 dba_hist_snapshot s
where t.snap_id = s.snap_id
 and t.dbid = s.dbid
 and t.instance_number = s.instance_number
 and t.SQL_ID='vwxyz'
 and s.begin_interval_time between sysdate-90 and sysdate
order by t.SNAP_ID 

Pseudocode explanation:
Get all SQL ID’s from the historical SQL STAT view. Refer it to the snapshot details to get real-world date/time of the events. Since nobody knows how long the AWR snapshot interval was at the time of interest, make BUFFER GETS relative per minute by dividing each BUFFER GETS DELTA by the length of its interval.
Configure the SQL_ID and the interval to be reviewed in WHERE.

You can create a chart like that when exporting the result to the spreadsheet software of your choice:


Basically, this concept will also work with all other columns available in dba_hist_sqlstat, such as CPU consumption, Interconnect load, Disk IO etc.

“Everybody lies”, says Dr. House :)

PS: Please keep in mind, the system(s) to run this query on, will need Oracle’s Diagnostics Pack licensed on top of Enterprise Edition.

Martin Klier in Interview: Oracle Standard Edition

A while ago, interviewed me about Oracle’s Standard Edition and database system migrations. The material was published just recently, so I’d like to share it. Enjoy, and if there are questions, just let me know!


I have to add a corrigenda: SE RAC is not – at least not at the moment – limited to a number of nodes. It’s currently limited to CPU sockets, to be exact, 4 of them. So a four-node SE RAC is possible, if I did not completely misunderstand the licencing policies.

Disclaimer: Licensing and pricing here are my OPINIONS, and not a reliable source to make decisions or confront Oracle with. :) If you need some tailored licensing information, feel free to email for advice.


DOAG 2014 Presentation and Whitepaper online: Database I/O


my #DOAG2014 presentation and whitepaper are online now!

“Oracle Core für Einsteiger: Datenbank I/O”





Thank you all for attending!

Martin Klier

It’s #DOAG2014 time!

Hello World!

It’s time for all Oracle folks to congregate in Nuremberg for DOAG Konferenz 2014!


I’d love to meet and greet you there – maybe you are also interested in my talk for Database Rookies: “Oracle Core für Einsteiger: Database I/O”:

Hope to have a great week with you!
Martin Klier


Visited Germany’s first Spatial Database, Size 26 kiloStones

Last week, I had the chance to visit Bavaria’s (and so also Germany’s) oldest Spatial Database. It’s buried deep below Munich, and contains all the geo information about Bavaria in scale 1:5000 and some in 1:2500. It was introduced in 1808 and was in use until 1950. That’s also the current state of the data.

Each of the 26,000 official maps is painted in oil and mirror-invertedly on polished lime sand brick. Each “page” is 1m x 1m (3.2ft x 3.2ft) in size, 4-6cm (2-3in) thick and the weight of each stone “disk” is approximately 70kg (154lbs). That makes it 26 Kilostones in size, with a dump size of 1,820,000 kg or 3,968,000 lbs.

Read more…

Performance is rarely an accident (Deutsch)

Some time ago, I saw a great presentation of Cary Millsap: „Thinking clearly about performance”. It was obviously relevant for our internal developers, so he unhesistantly granted me permission to reproduce some of his ideas for us. Cary, thank you very much!

Here you can see, what I made out of the topic, mostly for visualization purposes.



Martin Klier: Performance is Rarely an Accident (pdf)

As I said, the intention was to show development teams, how beneficial it would be to think about performance at all, and that you’d need code instrumentation (=runtime meta information about application behavior) to get better.

I hope you enjoy the slide deck.
Martin Klier

Edit: Exchanged the basic version for Second Edition in Wiesau and Munich

Oracle 12c InMemory – don’t stop thinking about performance

Oracle has released its new database version that includes the famous in-memory column store. InMemory option  promises a big advantage for OLAP-like work loads by keeping table contents in a columnar in-memory structure. InMemory is not new, they did that for decades, but the interesting part is “columnar”. There’s much writing about that on the net and in the Oracle Concepts Guide, no need to reproduce that here.

But though the new feature is very young, we already can see a “you can stop using your brain, we have a new catch-them-all feature” thinking, at least such a marketing sound. But it’s quite easy to show that this is not real. As for many other features we got over the years, using Oracle InMemory still needs a concept, done by an architect knowing the ups and downs.

What I can see from playing with Oracle Inmemory is, that it’s only beneficial when all data you (might) have to query from is already in the columnar cache (Oracle term is “populated”). If not, query response times don’t improve much. Let me show you my test case.

Read more…

Oracle with In Memory Option available for Linux

As of today, the Oracle Database with InMemory Columnar Store is available at


According to the price list of July 10th, it will be sold as an option for Enterprise Edition (Exadata NOT mandatory), for US$ 23,000 per CPU (calculation like EE).

Hope it will do well, beta demos were extremely impressive.


Oracle Core für Einsteiger: Datenbank I/O at DOAG 2014

Proud to announce, that DOAG accepted my tech talk “Oracle Core für Einsteiger: Datenbank I/O” for DOAG Conference 2014 in Nuremberg. It’s my next contribution to DOAGs thread aiming at beginners and part-time database people.


The talk will be in German, here comes its abstract:

Oracle Core für Einsteiger: Datenbank I/O
Wir kennen verschiedene I/O-Typen, die die Datenbank benutzt um ihre Aufgaben zu erfüllen. Der Vortrag zeigt anschaulich wie sie sich unterscheiden, und erklärt, welche Vorteile die einzelnen Methoden bieten. Behandelt werden Zugriffe auf Tablespaces, Online/Archived Redo Logs und verschiedene Dateitypen, die mit der Instanz in Verbindung stehen. Eingeschlossen ist eine kurze Wiederholung der jeweiligen Funktion im RDBMS; ebenfalls enthalten ist ein “Crashkurs” zum Automatic Storage Management “ASM”.

Das Ziel des Vortrags ist, Einsteigern das Verständnis von Massenspeicheroperationen näher zu bringen und Hintergrundwissen für ein kompetentes Storage-Sizing zu vermitteln.

I’m also very happy to announce, that my business partner Benedikt Nahlovsky‘s presentation “Kostenlos aber effektiv: Performanceanalyse mit Statspack und SQL*” also was accepted. It’s all about doing professional performance analysis with STATSPACK and SQL* tools.

So Performing Databases is present with two sessions, we really appreciate it!

Looking forward to seeing you all at CCN East, Nuremberg Trade Fair, 18th-20th November 2014!

Stay safe
Martin Klier

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.