dropping tablespaces and queues – not happy companions


SQL> drop tablespace MY_TSPACE including contents;
drop tablespace MY_TSPACE including contents
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-24005: Inappropriate utilities used to perform DDL on AQ table MY_SCHEMA.MY_QUEUE_TABLE

You would think that if you ask to drop a tablespace, then you’re pretty confident that you want all the stuff inside it to disappear :-(

You can workaround the issue by running

exec dbms_aqadm.drop_queue_table('MY_SCHEMA.MY_QUEUE_TABLE',force=>true)

on (more...)

db file parallel read faster on Linux than HP-UX?

I am still working on comparing performance between an HP-UX blade and a Linux virtual machine and I have a strange result.  I tried to come up with a simple example that would do a lot of single block I/O.  The test runs faster on my Linux system than my HP-UX system and I’m not sure why.  All of the parameters are the same, except the ones that contain the system name and filesystem names. (more...)

Simple test of DB server CPU speed

I’m trying to compare two types of database servers and it looks like one has a faster CPU than the other.  But, the benchmark I have used runs a complicated variety of SQL so it is hard to really pin down the CPU performance.  So, I made up a simple query that eats up a lot of CPU and does not need to read from disk.

First I created a small table with five rows:


In Search of Standing Desk

I came across Evodesk Standing Desk Review

Could not resist the temptation but reach out to @TreadmillDesker
You bark loud but how is ThermoDesk ELITE better EVODESK other than motor? $477=1333-886 is a lot for a motor. Let’s see pic.

Here is the respond I got back.
Thanks for the question! lab test of the Evo’s base concluded: slower speed, louder motors, and instability at taller heights.

Notice the respond was very (more...)

DDL_LOCK_TIMEOUT to sneak in change on active system

I need to change a view and an index on an active production system.  I’m concerned that the change will fail with a “ORA-00054: resource busy” error because I’m changing things that are in use.  I engaged in a twitter conversation with @FranckPachot and @DBoriented and they gave me the idea of using DDL_LOCK_TIMEOUT with a short timeout to sneak in my changes on our production system.  Really, I’m more worried about backing out the changes since (more...)

Google Search Appliance GSA Version 7.4 Released

Wanted to drop a quick note here that Google released the latest version of the Google Search Appliance software last week. That brings the most current version up to 7.4.0.G.72 and officially end of life’s the 7.0 version of the appliance software.

New features from the support site posting:

  • Seamless Integration with Microsoft: we’re releasing our SharePoint and Active Directory 4.0 connectors out of beta. These connectors provide improved (more...)

temporary undo in 12c

This feature seems a no-brainer once you’re on 12c. After all, why would you want your global temporary tables to be hammering away at your redo logs.  With that in mind, my initial tinkering with the feature had me getting ready for a “blog rant” because it did not seem to work.  Let’s see how you might end up unimpressed. 

Here’s the standard usage of undo (as per 11.2 and below).


PLAN_HASH_VALUE calculation different HP-UX and Linux?

I’m trying to compare how a query runs on two different systems.  One runs on HP-UX Itanium and one runs on 64 bit x86 Linux.  Same query, same plan, different hash value.


SQL_ID 0kkhhb2w93cx0
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,exts
ize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL,
:13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16,
spare1=DECODE(:17,0,NULL,:17),scanhint=:18, bitmapranges=:19 where
ts#=:1 and file#=:2 and block#=:3

Plan hash value: 1283625304

| Id  | Operation             | Name           | Rows  | Bytes  (more...)

NUMBER data type…what harm can it do ?

There’s a somewhat sour discussion going on based attached to the video at https://www.youtube.com/watch?v=jZW-uLb52xk

Whether you agree or disagree with the video or the comments, or (sadly) the animosity in them, it does lead to an interesting bit of investigation when it comes to data types with arbitrary precision, which was stumbled upon by a friend at work.

Let’s start with a simple comparison between two dates. In this case, I’ve just (more...)

OGh APEX World 2015

DSC_9284-001 (800x533)

iAdvise Diamond Sponser APEX World 2015

After 5 successful earlier editions of the APEX World day, they chose a different venue for the 2015 edition. The ship SS Rotterdam was the setting for the annual APEX World event. For us, it was not only the location that was special, we were also official Diamond Sponsor of APEX World 2015! And a great day it was! Not only to meet a lot of nice people from (more...)

Avoiding the COMMIT bomb!

I’m an Oracle dinosaur, so I like using SQL Plus.  Its simple, fast, comes with every version and installation and platform, and I’m very familiar with it.  (And who knows, it might still be at the forefront of the Oracle development teams!  http://www.slideshare.net/hillbillyToad/sqlcl-overview-a-new-command-line-interface-for-oracle-database )

But there is one important thing I always take care of when I’m using SQL Plus, and it’s easiest to explain with an example.

You start (more...)

Oaktable World Las Vegas April 15, 2015 at Collaborate



Wednesday April 15 at Collaborate 2015 Las Vegas room Mandalay K
For more information see Pythian’s Blog post.

Screen Shot 2015-04-08 at 10.03.53 AM


Screen Shot 2015-04-08 at 10.06.48 AM



What is Oaktable World ? Oaktable World is a day organized by members of the Oaktable network. The Oaktable network is a network of Oracle database tuning geeks. Among the members are Tanel Poder, Jonathan Lewis, Cary Millsap etc.  Oaktable network was created by Mogens Nørgaard back in 2001 or so and Mogens started (more...)

ADF 12.2.1 New Features

Oracle has just updated their ADF Statement of Direction, announcing that ADF 12.2.1 will be out in 2015. Here is what they are promising for this release:

The Next Version of Oracle ADF

Oracle is planning to deliver the next version of Oracle ADF – 12.2.1 – as part of the next release of Oracle Fusion Middleware 12.2.1 in 2015. Some of the focus areas for this version include:

(OT) an idea for Easter (and well…every day)

I don’t travel as frequently as some people do, but with OpenWorld and various Oracle conferences each year, I definitely see my fair share of the inside of a plane and a hotel room.  To pass the time on flights, I try to read, but when you’re reading on a work-based trip, it’s a case of reading a page or two here and there, rather than a true extended session of relaxed reading.

The (more...)

Edition based redefinition – an apology

In April 2008 (wow, does time fly!) I used the following picture in my "11g features for Developers" presentation at the Australian Oracle User Group conference.


I think the picture is from the movie "Indiana Jones and the Last Crusade"… where they sought the Holy Grail.

I used the picture because I said that Edition Based Redefinition (EBR) was the Holy Grail of 24/7 Oracle based applications.  (Like most Oracle presentations at the (more...)

Observer effect

In physics, one important limitation of any experiment is the fact that an act of observation inevitably interferes with the observed process (“observer effect”). Same thing is true about databases. It is a well known fact that, for example, turning on tracing can significantly slow down the process for which it’s enabled. But there exist even nastier forms of this effect: for example, when you try to trace a SQL statement using nested loop batching mechanism, (more...)

sar -d on Linux

I started using sar -d to look at disk performance on a Linux system this week and had to look up what some of the returned numbers meant.  I’ve used sar -d on HP Unix but the format is different.

Here is an edited output from a Linux VM that we are copying files to:

$ sar -d 30 1
Linux 2.6.32-504.3.3.el6.x86_64 (myhostname)  04/01/2015      _x86_64_        (4 CPU)

05:26:55 PM        (more...)

In-memory – can you REALLY drop those indexes ?

Its not a hard sell to convince anyone that a whopping huge great chunk of compressed column-based memory is going to assist with those analytic-style queries.  The In-memory option seems a natural fit, especially for those people where the bottleneck in their infrastructure is the capacity to consume data from storage.

What perhaps a more interesting area of investigation is the claim that In-memory will also be of benefit in OLTP systems, where (to (more...)

Enterprise Manager is not able to connect to the database instance

Solution :

check the sysman and dbsnmp account status, If expired, please activate
SQL> select username,account_status from dba_users where username =’SYSMAN';

—————————— ——————————–

SQL> select username,account_status from dba_users where username =’DBSNMP';

—————————— ——————————–


So sysman account has been expired.  Please activate with old password and also Please check if this account has been locked, please unlock this account.


SQL> alter user sysman identified by Admin123;


Try Oracle 12c VM with Delphix download

 photo by Jose Maria Cuellar (CC 2.0)

Thanks to Leighton Nelson who pointed out  that :

Oracle has a pre-installed Linux VM with 12c

Delphix as well has a pre-installed   trial version 

Download both of these and start them up in Virtualbox and you can start virtualizing your PDBs.
The Oracle pre-installed VM has a few eccentricities that have to be addressed before using it. There is no IP address and there (more...)