Oracle #DB12c New – MAX_STRING_SIZE

In pre-12c database releases, VARCHAR2, NVARCHAR2 data types were limited to 4000 bytes and RAW data type was limited to 2000 bytes. If there is a need to store more data in VARCHAR2 column, you can in 12c after setting the parameter MAX_STRING_SIZE to EXTENDED. With setting MAX_STRING_SIZE to EXTENDED, you can store up to […]

Smart Scan enhancements in Oracle Exadata 12c-inline LOBs

As part of the 12c release of the database and cellos Oracle introduced the ability to perform Smart Scans on inline LOBs. The enhancement is certainly for Secure Files only. And as always, if you would like to read up about Secure Files you can head over to Tim Hall’s website for the details.

To demonstrate the new behaviour I have used an 11.2.0.3 BP 22 database on Exadata 11.2.3. (more...)

Temp Table Transformation Cardinality Estimates – 1

Having published recently two notes about the Temp Table Transformation highlighting the heuristics based decision and other weaknesses, for example regarding the projection of columns, it's time to publish some more notes about it.The transformation can also have significant impact on cardinality estimates, both join and single table cardinality.Looking at the difference in the join cardinality estimates of following simple example:

create table t1
as
select
rownum as id
, mod(rownum, 10) (more...)

Writing Tips : A URL is for life, not just for Christmas!

writingI’m currently doing yet another pass through my whole website correcting broken external links. This is nothing to do with my bad management. This is to do with people changing their URLs and not putting the correct redirects in place. I’ve just done a scan of my website and there are over 800 broken links to Oracle documentation!

There seem to be several ways people deal with URL changes on their website.

Datapump TTS quick tip

I was trying to transport a large number of tablespace datafiles from one database to another, and impdp kept crashing with “PL/SQL: numeric or value error: character string buffer too small.”

It turns out there is a limit on what you can put on a single line in a parameter file.  Hence if you have something like:

transport_datafiles=/long/path/file1,/long/path/file2,/long/path/file3,....

then you might run into trouble. It’s easily fixed however – just put the entries (more...)

Tracing Single SQL in Oracle

Many times, while doing SQL tuning, we want to trace (event 10046) single SQL in database. Instead of going for module level tracing or session level using DBMS_MONITOR, we can simply use below alter system command to trace specific SQL

Example: I have a table T1 and index T_I_TABLE_NAME on that table.
I am running following SQL and I want to trace on this SQL

select * from T1 where table_name = 'SINGLE_PRODUCT_GROUPS';

I (more...)

direct path read behavior in Oracle 11.2

Prior to 11g, whenever optimizer goes for full table scan, Oracle used to show “db file scattered read” as wait event. But starting from 11g, a full table scan can show (depending on certain conditions) “direct path read” wait event.

db file scattered read – happens when blocks for a table is read from datafile into buffer cache in SGA

direct path read – happens when blocks for a table is read from datafile into (more...)

Recover Oracle Undo Tablespace without Backup

Woke up with an issue regarding a Oracle 10.2.0 database on Linux complaining about an Undo file on startup.


sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.3.0 - Production on Fri May 22 20:11:07 2015

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup pfile='init.ora'
ORACLE instance started.

Total System Global Area 2801795072 bytes
Fixed Size                  2075504 bytes
Variable Size            1275069584 bytes
(more...)

From Product X to SQL Developer

I recently worked at a company that used "Product X" for all of it SQL and PL/SQL activities.  There’s no real need to reveal what "Product X" is, because this isn’t a post about whether as a product it was good, bad or somewhere in between.  "Product X" met the needs of the Oracle developers in the company, and that’s all that matters.  There was just one issue – for Product X, (more...)

EM Express versus EM Cloud Control

Recently I was asked the question “What is the real difference between EM Cloud Control [NOTE: I’ll refer to this as EM12c through the rest of this post] and EM Database Express in 12c?” It was (for me) a pretty easy question to answer, but I wanted to provide the questioner with a link to the place in the Enterprise Manager documentation where it covers that in detail. Somewhat to my surprise, I wasn’t (more...)

Friday Philosophy – Why I Volunteer for User Groups

I’ve just noticed a new page about me popping up on the UKOUG web site – It’s in the section about volunteer case studies, alongside people like Joel Goodman, Simon Haslam, Carl Dudley, Jason Arneil, Brendan Tierney and others who have been stupid good enough to give time and effort to the UKOUG.
{You can get to the page by going to the UKOUG home page (www.ukoug.org) and clicking the Membership (more...)

APEX 5.0 Rollout

apexLast month there was a frenzy of activity when APEX 5.0 was released. I had been having a dabble with the Early Adopter for a while, but I felt the need to do a local install.

The only slight issue I had was with static files and that was down to me not RTFMing properly. :) Patrick Wolf wrote about this issue recently here.

Having not had any problems while I was (more...)

Writing Tips : How do I publicise my writing?

writingThis is quite a touchy subject for me and I’m maybe not the best person to ask.

On the one hand, I feel you should grow an audience in an organic manner. If your content is good, they will find you.

On the other hand, I’ve been doing this for so long it is relatively easy for me to get heard. If I was starting today and producing the same type of content, would you have (more...)

Understanding SQL

From time to time someone publishes a query on the OTN database forum and asks how to make it go faster, and you look at it and think it’s a nice example to explain a couple of principles because it’s short, easy to understand, obvious what sort of things might be wrong, and easy to fix. Then, after you’ve made a couple of suggestions and explained a couple of ideas the provider simply fades into (more...)

Guaranteed Method of Boosting your Oracle Skills

I can tell you how to be a better Oracle DBA, Developer, Designer, Architect – whatever your flavour of role or aspect of profession, if Oracle tech is part of your working world I can hand you the key to improvement. And it is easy.

I am totally assured(*) I can do this. It will work for every single one of you reading this post (except for you two, you know who you are). And (more...)

UKOUG Systems Event 2015

ukougI have a bone to pick with UKOUG. I’m coming from Birmingham and I’m a lazy git, so why the heck am I on in the first slot? :) It was a 04:30 start to the day, for a train journey starting at 05:50. I could have got a later train, but it would have been busier and more importantly, more expensive… After putting the latest of my writing tips posts live and lying in the (more...)

Managing Oracle Database 12c with Enterprise Manager – Part XIV


We are discussing the management of Oracle Database 12c in Oracle Enterprise Manager 12c. In our previous blog post on this topic, we discussed the new Enterprise Manager Database Express 12c, which is the replacement for the earlier Database Control (used in previous versions of the Oracle database such as 11g and 10g, to manage a single database).
One of the menu options in Database Express 12c is the Database Performance Hub. This gives (more...)

Writing Tips : Should I write off-topic posts?

writingI’m a Jedi master at writing off-topic posts! This blog started life as an Oracle blog, but now I just write reviews of Twilight and 50 Shades of Grey. :)

The simple answer is you should do what you are happy with. I had been writing on my website for 5+ years before I started this blog. When I started the blog, I thought it would be technical. It quickly became apparent that the Oracle content was better (more...)

Managing Oracle Database 12c with Enterprise Manager – Part XIII – Database Express 12c


We are discussing the management of Oracle Database 12c in Oracle Enterprise Manager 12c. In our previous blog post on this topic, we briefly looked at the changes for Metering and Chargeback. We will now look at the new Database Express 12c.
Oracle Database 12c introduces “Enterprise Manager Database Express 12c” instead of Enterprise Manager Database Control that was available with previous versions of the database, such as 11g and 10g. The important (more...)

Part1 Configuration Evodesk

Installation was not bad.

Evodesk is the 2nd company I am aware of for using recesss nuts for desktop installation.

This makes much more sense than using wood screws.

However, pondering if Evodeskk will be the first company to used recess nuts for all components and get rid of wood screws.

Don’t you think the legs make  great towel rack?

install1

install2