What To Do at OOW15 (Social & Serious)?

I’m going to OOW15 this year, my first Oracle Open World in 11 years I think. And despite the Prom Queen rejecting all my offerings, I will actually be sneaking in a presentation – which I am very happy about.

The European Oracle User Group (EOUG) get a few slots and two are being used on Sunday 25th, 13:30 – 15:15 for “More Than Another 12 on Oracle Database 12c” – 12 European experts (more...)

Oracle Database Developer Choice Awards : Spread the Love!

You’ve probably already heard about the Oracle Database Developer Choice Awards, Steven Feuerstein wrote about them here, but this is just a quick reminder to get your votes in.

This year there are five categories to vote for:

I am always a little nervous about “popularity contests”, but I would suggest you look through the finalists for each category and vote for someone if (more...)

How the log writer and foreground processes work together on commit.

(warning: this is a rather detailed technical post on the internal working of the Oracle database’s commit interactions between the committing foreground processes and the log writer)

After the Trivadis Performance days I was chatting to Jonathan Lewis. I presented my Profiling the log writer and database writer presentation, in which I state the foreground (user/server) process looks at the commit SCN in order to determine if its logbuffer contents are written to disk by (more...)

You’re Not Allowed

"Have you ever heard a blindfolded octopus unwrap a cellophane-covered bathtub?"
Norton Juster, The Phantom Tollbooth 

Recently a thread in an Oracle forum I frequent asked the following question:

“… is there a way to restrict the login to database only from the application which we have made.”

That, of course, is not an easy question to answer as several possibilities emerge with none of them foolproof. Let’s look at those options.

The (more...)

Oracle Midlands Presentation

Ever wonder why your SQL execution plan has changed? Wondering why your boss is shouting something about “traders can’t trade” or “shoppers can’t spend” or “well that’s the Airport shut!” ?

When you write SQL, Oracle runs it through the Optimiser to determine the fastest way to access the data. This access path sometimes changes without anyone noticing, sometimes with sub-optimal results. I’m giving a presentation at an OracleMidlands even (more...)

Master Notes

MoS has a number of “Master Note” documents which pop up from time to time while I’m checking for known problems or solutions; they get extended from time to time (and, conversely, link to some articles which are clearly no longer relevant to current versions). This is just a tidied up list of a few of the master notes that I’ve jotted down over time. The Data Dictionary overview appeared in my daily “Hot Topics” (more...)

Delphix Express installation

A couple of blog post ago I announced that there is now a free version of Delphix.  Here is a little more information about the installation of Delphix Express.

Delphix Express

Delphix Express is a free version of Delphix limited to 25 GB of managed storage and 1 vCPU. Delphix Express is set up for small projects and not optimized for high throughput nor performance testing.


Landshark is a set of 2 virtual machines (more...)

Oracle 12c RAC on Oracle Linux 6 and 7 using NFS

linux-tuxFollowing on from the last post, I’ve brought my NFS RAC stuff up to date also.

I noticed I had not done a RAC install using NFS on Oracle Linux 6, so I threw that in for good measure too. :)

Just as a little (more...)

Saving XML documents from an Oracle database

A colleague of mine asked about the several ways of saving XML content, I knew,…

PDB Saved state –

Prior to version, whenever container database was restarted,the pluggable databases within the container database remained in MOUNT state. Startup trigger were written to open the database in READ-WRITE/READ-ONLY mode.Starting from, this can be done with PDB save state feature

SQL> show pdbs

---------- ----------------- ---------- ----------

Lets create a new PDB


Lock by reference

As you may know, not indexing foreign key can have a huge impact on lock behavior and hurt database scalability (Not only with Oracle).

But there is also some rules of thumbs (coming from Asktom) when non indexing the foreign key is considered as reasonable.

So, when do you NOT necessary need to index a foreign key ?

In general when the following conditions are met:

o you do NOT delete from the parent table. (especially with (more...)

Come and Enjoy – OakTable World 2015

Yep, its free, its cool and a lot of fun. During Oracle OpenWorld 2015, the…

The Art of Being a Successful DBA- Paranoid DBA Best Practices

Ever look at a screen’s output and get that puckered feeling in the pit of your stomach? If you have been working in this profession for any amount of time, you know the feeling I’m talking about. The feeling that makes you think you would rather be living in Montana making woodcarvings at a roadside stand than being a DBA. I’ll be taking a somewhat lighthearted look at the perils of our profession and discuss (more...)

latch: cache buffers chains and rollback

I had an interesting encounter with latch: cbc contention early this week. During my oncall I received page for Load of 206.81 exceeded threshold of 150. After I logged into server , the server load average was continously increasing and all the top PIDs were of oracle processes. After logging into database, I saw multiple sessions waiting on latch: cache buffer chains wait event

load average: 258.52, 244.27, 226.15

select username,sql_id,event,count(*)  (more...)

Identifying database link usage

As part of ongoing security reviews I wanted to determine if all database links on production systems were in use. That is not very easy to do and this article is a listing of some of the options I have considered to get that information and how it is now possible from 11GR2 onwards.

The first option was to look and see if auditing can be used. The manual states “You can audit statements that (more...)

Result Cache 2

Following on from my earlier posting of problems with temporary table and the PL/SQL result cache (a combination which the manuals warn you against) here’s another problem – again, to a large degree, self-inflicted.

Imagine you have a complex report involving a large number of financial transactions with a need to include calculations about current exchange rates. Unfortunately the rules about calculating the appropriate exchange rate for any transaction are complex and you find you (more...)

ORA-23515: materialized views and/or their indices exist in the tablespace

When I am trying to drop the tablespace, I am getting below error

ORA-23515: materialized views and/or their indices exist in the tablespace

Solution :

Step 1: Find the materialized views and/or their indices

SQL> set head off
SQL> set newpage none
SQL> set pagesize 9999
SQL> spool drop_materialized_view.sql

SQL>select ‘drop materialized view ‘||owner||’.’||name||’ PRESERVE TABLE;’ from dba_registered_snapshots where name in (select table_name from dba_tables where tablespace_name =’XXXXXXX’);

SQL>spool off

Step 2:  Drop (more...)

flashback database point in time recovery where is my relocated datafile available in 12c

Its new location

Documentaion says

“A flashback operation does not relocate a moved data file to its previous location. If you move a data file online from one location to another and later flash back the database to a point in time before the move, then the data file remains in the new location, but the contents of the data file are changed to the contents at the time specified in the flashback.”

Let (more...)

OEL/RHEL 7 – firewalld

How cool is getting Linux. Hope soon to not look like a Windows. If they put a butterfly or shiny window in the “start menu” I’m out of the game. The “new” firewalld is cool and dynamic, but my servers are square, boring and static. Maybe if I’m using RHEL/OEL 7 in my laptop it […]

East Coast Oracle Conference 2015

East Coast Oracle Conference was fun and filled with very good sessions and speakers. I had the opportunity to attend several wonderful sessions and present two sessions. Data Pump: Tricks, Tips and Techniques Getting Started with Oracle Multitenant Administration The presentations are uploaded to East Coast Oracle Conference site and you may download from there. […]