How important is “up to date” to you ?

I wrote a post a while back showing how one of the options with an Oracle Text index is to keep that index perfectly updated with its underlying table.  (Long termers with Oracle may know that this is a relatively recent addition to Text indexes, which historically were updated with calls to CTXSYS.CTX_DDL.SYNC_INDEX, either on demand or via the scheduler).

I also said that there is “no such thing as a free (more...)

ORACLE_MAINTAINED Objects Don’t Export

What a disaster this is with 12c full database export and ORACLE_MAINTAINED objects.

Somehow, oracle thinks MDINH is an ORACLE_MAINTAINED user and user MDINH does not get exported.


Why Can an Object Not Be Exported? Expdp of SYSTEM User’s Table Returns ORA-39166 or ORA-31655 (Doc ID 2114233.1)

There are certain system generated schemas that are not exportable using exp or expdp because they contain Oracle-managed data and metadata. SYS, MDSYS, and ORDSYS are some (more...)

Synthetic Full Backup for Oracle

Synthetic full backup is a method for combining incremental backup into a full backup. It is not a new thing, many backup utilities can do that with file system backups. It is also well described:

However, there is only one backup suite that can do that with Oracle RDBMS: Commvault 11.

How is it done?  Well, Commvault is a very well documented tool, the documentation is here:



I recently discovered about column ORACLE_MAINTAINED added to DBA views and got excited over nothing.

Denotes whether the role was created, and is maintained, by Oracle-supplied scripts (such as catalog.sql or catproc.sql).
A role for which this column has the value Y must not be changed in any way except by running an Oracle-supplied script.

It looks like ORACLE_MAINTAINED is only valid for CDB and seems as if Oracle is really wanting all (more...)

Copy Data Management with Delphix on Exadata

I’ve been busy reading and testing everything I can with Delphix, whenever I get a chance.  I’m incredibly fascinated by copy data management and the idea of doing this with Exadata is nothing new, as Oracle has it’s own version with sparse copy.  The main challenge is that Exadata’s version of this is kind of clunky and really doesn’t have the management user interface that Delphix offers.


There (more...)

Oaktable World 2016 Sept 19 & 20 is on !!

Having take a new job at Amazon just two weeks ago and moving to Seattle (!)  I didn’t have the time nor was it practical to set up Oaktable World  this year. Luckily Kelly Pot’vin has taken over the mantel !

Get the info straight from the horses mouth at her blog.

The following content has been graciously supplied from Kellyn’s blog:

Oak Table World is FREE to the PUBLIC!  We don’t require an (more...)

EM13c: Performance Tuning Access to Read Only Administrators

About six months ago, when I was in the OUGN Spring Conference, someone asked me how they can let developers access to performance tuning pages on Enterprise Manager Cloud Control 13c. He said that they granted full administrator privileges to the developers so they could access the performance page but it’s not acceptable. He also mentioned that they didn’t need to grant full administration privilege to the developers’ EM users while they were using (more...)

Little things worth knowing: using Remote Diagnostic Agent more effectively with profiles

RDA, or the Remote Diagnostics Agent, has been around for a little while. Over the time, and with the growing number of Oracle’s acquisitions it has become, shall we say, a little more difficult to handle. It appears to me as if every one of them will have its diagnostics handled by RDA making it hard to focus on something specific, like for example the database.

I won’t go into very detail of the Remote (more...)

Friday Philosophy: Be A Hero – OR Be The Best

There is a crisis! The database is not responding, the apps can’t work and the business is suffering. Management are doing what management are there for – panicking and demanding “Someone Do Something!!!”.

Step forward a DBA who logs into the server, checks the alert logs, spots what is wrong and fixes it. The database starts processing requests, the applications are all working fine and the business is back on track. What a hero!

How to Import Data (to Oracle RDBMS) from XML

I wrote a blog post about how to import data from a XML file to Oracle about 7 years ago. I demonstrated how we can use DBMS_XMLSTORE package to parse XML data. Yesterday, I replied a question from a blog reader about that post. He had problem with running my sample script, I did troubleshoot and find out the problem with his script, and while testing the sample script, I noticed that I can write (more...)

Accuracy Matters

angry-1300616_640One of the annoying things about our industry is the volume of acronyms and very specific terminology used on a day-to-day basis. If you know the lingo you are fine. If not, life can be quite problematic.

In some cases, this is just a smoke screen to hide a lack of true understanding, say enough buzzwords you read in a Gartner report and people will eventually stop asking you to define them, but in other cases (more...)

Grid Infrastructure 12c installation fails because of 255 in the subnet ID

I was doing another GI cluster installation last month when I got really weird error.

While was running on the first node I got the following error:

2016/07/01 15:02:10 CLSRSC-343: Successfully started Oracle Clusterware stack
2016/07/01 15:02:23 CLSRSC-180: An error occurred while executing the command '/ocw/grid/bin/oifcfg setif -global eth0/ eth1/' (error code 1)
2016/07/01 15:02:24 CLSRSC-287: FirstNode configuration failed
Died at /ocw/grid/crs/install/  (more...)

Will the Real Revolutionary in Data Virtualization Please, Stand Up

While chatting on slack the other day, one of my peers asked if I’d seen that ESG Global had done a write up on Veritas Velocity.  Velocity is a product that won’t be available until the end of 2016 and is considered “Alpha”.  I was surprised that anyone allowed to participate in Alpha was able to publish a description on the product, but that’s not my call to make.


What (more...)

Building “Ready to Deploy” Oracle VM Templates

I’ve been playing a bit lately with Oracle VM on a few different platforms (ODA, Exalytics, Private Cloud Appliance) and was tasked with creating a good Oracle VM template image for Oracle Linux 7. Oracle makes templates available on eDelivery ( that present you with VM configuration questions on first boot, but those VM templates have a few limitations that make them hard to use:

  1. The templates have a minimal Linux installation, and are (more...)

SUBSTR Versus LIKE in Oracle 11.2

I was reading an old SQL tuning book which was printed in 2002. It said that a where clause with like could often use an index whereas a similar clause using substr could not. I wondered if this might still be the case in an Oracle database. To find out, I created a table:

SQL> conn andrew/reid
SQL> create table tab1 as
  2  select table_name from dba_tables
  3  (more...)

Basicfile LOBs 4

At the end of the previous installment we saw that a single big batch delete would (apparently) attach all the “reusable” chunks into a single freepool, and asked the questions:

  • Why would the Oracle developer think that this use of one freepool is a good idea ?
  • Why might it be a bad idea ?
  • What happens when we start inserting more data ?

(Okay, I’ll admit it, the third question is a clue about the answer (more...)

G-Cloud – Moving Your Oracle System to Amazon Web Services

G-Cloud was awarded a few weeks ago and we're glad to say that clckwrk were selected as a supplier in the areas of Infrastructure as a Service and Specialist Services.

The services we have on G-Cloud are specifically curated for implementing, moving and managing Oracle systems to the cloud. This is a quick guide for how to combine those services to migrate to the cloud. For public sector users of Oracle technology and applications this is the set (more...)

Columns Affected by Extended Data Type

I am not going to post how to convert to extended data type since there are many blogs on that already.

Just a reminder, there’s no going back; hence have backup and possibly minimize changes during testing to be able to restore (which is ideal and may not be feasible).

Before reverting to MAX_STRING_SIZE=STANDARD, columns affected by extended data type need to be identified.

From Oracle documentation, MAX_STRING_SIZE controls the maximum size of VARCHAR2, NVARCHAR2, (more...)

Database Triggers : Use at your own risk!

error-24842_640I wrote a Tweet about triggers yesterday that seemed to get a strong reaction, so I thought I would clarify some things. 🙂

Guns don’t kill people, people do (or is that rappers). In the same way, triggers are not inherently evil, but their misuse is. Toon Koppelaars, of Helsinki Declaration fame, wrote some posts about triggers, which amongst other things highlights the problem of stigmatising something because it is being misused. With all that (more...)

Review of Book

My new book has been reviewed by popular Oracle ACE Director Gokhan Atil, please see for the detailed review. Thanks Gokhan.