PLSQL: Using Datapump API with filter on multiple tables

The developers in one of the teams I work within wanted to export the tables in a schema with limited number of rows. This could of course be done by running  expdp command line.  But this time we needed to find the last x numbers of primary keys in one of the central tables and export only these rows. This opted for the use of the DBMS_DATAPUMP API. It took me some minutes to figure out how to put on a filter on multiple tables, but it was not really that hard. Here is what i did.

Continue Reading »

Why use Oracle Trace?

Oracle Trace is one of my favorite tools when working with performance. It’s not my only tool, but it’s a tool which have given me great success when working on performance related issues. In this small article I’ll try to explain why? I’ll start by telling a small story.

Continue Reading »

Nice blog about DBMS_METADATA from Alex Nuijten

This blog post has been laying in my TO_READ folder for a long time. Finally found some time to catch up, and I especially liked the little piece of PL/SQL for remapping the schema.

UKOUG Tech 16: Birmingham here i come!

tech16_speakerThe UKOUG Tech16 in Birmingham is getting close, and I really looking forward to this great event which I’ve been attending the last 3 years. In my experience there a lot of great presentations to attend, and a lot of interesting speakers and attendees to interact with.

This year I get the opportunity to give two presentations myself . The first one – “Mistakes developers will do – if the DBA don’t assist” (SuperSunday at 13:40), is about the horrors and trolls experienced from a decade in different (Java) development projects. The last one – “Sometimes is alright to be lazy – Oracle in Docker” (Monday at 8:50, Location: System 2), is about how you can use container technology such as Docker to run Oracle in different settings. Using docker is actually something that I’ve picked up being around developers, and it’s actually very cool. See you in Birmingham soon!

OOW16 – Are we failing or will performance rise?

img_1390The last session I attended at OpenWorld 2016 was “Thinking Clearly About Database Application Architecture”. A panel discussion with Toon Koppelaars, Cary Millsap, Gerald Venzl, Bryn Llewellyn and Connor Mcdonald. The panelists promotes that the business logic should be moved into the database, and implemented using PL/SQL. And yes – I do get the message, but I just don’t really agree with the rapping. I don’t think this is a one way street, and I think the message fades away because of the way this is presented. It’s a matter of expediency! Sometimes using ORM API’s like Hibernate, and running your business logic outside the database, can be a good solution. Nobody argues whether Ferrari is a very fast car, but this doesn’t mean that everybody have to ride a Ferrari. Sometimes a Fiat or Lada is more than good enough. My advice is: “Don’t let this become a silver bullet!” Continue Reading »

An Oracle DBA at JavaZone – a fish on land?

JavaZone 2016

Why in the world should an Oracle DBA spend two workdays at a Java conference like JavaZone. It doesn’t matter if the JavaZone is the third biggest Java conference in the world, serve great food all day long, have a concert at night with awesome bands, and – of course – you get a couple of free beers. It just doesn’t make sense. Or does it?

According to there are fish that survive on land. Sometimes I do feel a little lost in the crowd of developers, but I survive.

Continue Reading »

Books to read: “Mastering Oracle Trace Data, Second Edition” with Cary Millsap

The “Mastering Oracle Trace Data” from Cary Millsap is a great book about performance in general, and about how to work with trace data. The second edition have several new chapters adding valuable knowledge to both developers and DBAs. This is one of the books that you really should have in your bookshelf.

One of the things you just need to know about Oracle …

A couple of years ago I did ask my developers to start using the varchar2(char) option when creating new tables. In Norway we have some characters in the alphabet (‘Æ’, ‘Ø’, ‘Å’) that uses more than 1 byte of storage (when the database characterset is set to AL32UTF8). Using the default – varchar2(byte) – could then decrease the number of characters we actually end up storing in the database (when storing Norwegian text). Today I just read a tweet from Jonathan Lewis, directing me to one of his older blog posts – “Just in case”. I try to read every blog post from Jonathan, but sometimes it’s hard to keep up. This one I just happened to miss – at least until today. You better not miss it!

The article discusses why you shouldn’t increase your column width (of a varchar2) “just in case”. In my case – it thought me why it was a stupid idea to use varchar2(char) as a default approach. Many varchar2 columns in our databases doesn’t store Norwegian text, and would never contain these variable byte-length characters, hence should never be defined with varchar2(char). I should have thought: “Oracle must have made ‘byte’ the default for a reason”.