Thursday, December 29, 2011


There is a bug in the export utility on version when running against a (and possiby database that has tables without segments. Deferred segment creation was introduced as a smart feature in 11gR2, but this is causing error EXP-00003 when running the old export utility from lower versions. This is reported on MOS for pre-11g clients, but right now I could not find any reports that this is also a problem with the version when this feature was introduced.

This is a simple test to reproduce the error. Create a couple of tables in an 11gR2 database:

create table empty (id number, foo varchar2(2000) );
create table aaa (id number, foo varchar2(10));
insert into aaa values (42,'asdfas');
select table_name,segment_created
from user_tables ;

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

Then perform a simple export from a client:

exp oyise/oracle@orcl file=oyise.dmp log=oyise.log

Excerpt from the output / logfile:

. about to export OYISE's tables via Conventional Path ...
. . exporting table AAA 1 rows exported
. . exporting table EMPTY
EXP-00003: no storage definition found for segment(0, 0)

(For export to succeed with one table, create it with a name alphabetically before the EMPTY table).

Note, if the X,Y in "no storage definition found for segment(X,Y)" is non-zero, you have another issue that you probably find a solution for on Oracle Support, e.g. when segment owner <> table owner.

Solution: Upgrade client or allocate one extent for the table that fails:

alter table empty allocate extent;

You may consider set the parameter DEFERRED_SEGMENT_CREATION to FALSE in case more empty tables will be created.

Also this error does not happen on Data Pump export

Wednesday, December 28, 2011

Wordfeud and regular expression in SQL

Three family members invited me to play Wordfeud with them this Christmas. At the end of such a game losing badly to my sister-in-law and with only a few letters left I was thinking about how I could find candidate words using regular expression in SQL.

First I found a list of Norwegian words on the net. I should have taken the effort to get hold of the same word list as Wordfeud is using. The list was loaded into a table using SQL Developer.

Say you want to see if there is any word that start with N and uses only the letters C, F, S, and A. The following SQL may find it:

select word
from norwegians
where regexp_substr(word,'^n[cfsa]+$') is not null ;

For those not familiar with regular expressions, well, just Google or check out any good book on Perl. In short, the ^ and $ means the beginning and end of line (word in this case) respectively, [] is used to create a group of letters; [cfsa] means any letter of c, f, s, and a; + means one or more occurrences.

Now I only need to make a mobile interface to this, and create a function that calculate the score for each word based on Wordfeud's rules. Next Christmas, maybe. I do know there are apps ready to suggest this, but that is not half as fun :-)

Wednesday, November 30, 2011

Two free profilers for 10046 trace files

I'm a big fan of Method R, both the company and the way to optimize SQL (or any other process that can possibly be measured). The method is explained in details in their classic book Optimizing Oracle Performance. But it is hard to practice method R without a good profiler. In every installation of Oracle database the profiler tkprof is included and it does serve for some problems, but hides a lot of information from you.

When I have a tasks that runs for a few hours I enable extended Oracle trace from start to end, avoiding as best as possible anything outside the time interval for the task itself, meaning I coordinate the tracing with the end user. When I have the trace file ready I check it briefly to make sure I don't have some rubbish before or after the time window. Especially on Windows it is important to check that the tracefile has not already been written to by a previous session, may be it is the same on Unix, I haven't checked since I'm in Windows land for the moment. I use two free tools that create a profile in html-format:

Oracle Session Resource Profiler (OraSRP) by Egor Starostin and TVD$XTAT by Christian Antognini, the author of the book Troubleshooting Oracle Performance. In that book he describes his profiler that you can download from his site. Excellent book by the way.

I often use both profilers because I like to compare the results. They usually agree surprisingly well, the only difference is what is considered unaccounted for time. Whenever unaccounted for time is large I check the tracefile again to see if there is a long period of time spent waiting for the client at the end. Sometimes the missing time is located inside the session, and at one occasion when I was stuck I bought a license of mrls that parse the tracefile and makes it easy to track down where this happens.

In addition to create an overall profile for your session they have profiles for individual events, OraSRP is especially good at this. This is interesting because it adds another level. One of the tricks you encounter on the web is to increase the parameter DB_FILE_MULTIBLOCK_READ_COUNT. This parameter decides how much Oracle can try to fetch in each call when reading many blocks at a time, typically db file scattered read or direct path read usually when doing table scans as opposed to single key search in an index. Reading 128 at once must be better than 16 that is often the default, but this profile will in some cases show you that Oracle cannot read a maximum number of blocks for each read call. In one query I was working on the event db file scattered read contributed to 51% of the response time, but a profile on this event showed that the number of blocks pr read call was almost flat with a variation from 1 block pr call (7.1%), 128 blocks pr call (1.0%) to 73 blocks pr call (0.1%). The benefit of increasing the parameter was not as much as I hoped for, and the solution was found elsewhere.

One of the important points with profiling is to discover where not to waste time. You might suspect a full table scan to be the problem (because some people believe FTS is always bad), but large tables can be scanned really fast these days. With tracing and profiling you don't have to guess, you measure and draw a conclusion. There are so many things that can go wrong in a complex system, if you can measure and do simple math you are much more likely to reach your goal early and know that you actually have reached it than when you apply all those tricks and guesswork.

"Filter early" is important when optimizing, with profiling you filter out early where you don't want to apply the focus of your brain.

Friday, October 7, 2011

OOW 2011 Day 5

OOW has been great and I'm reaching the limit of how much information I can consume for a while. I'll probably spend some weeks or months reading and understanding presentations. Time to go home and practice what I've learned. I'm not even going to try to report dutifully on every session today. Just say that I've been to Real-World Performance Questions and Answers, Oracle Database Optimizer: Tips for Preventing Suboptimal Execution Plans, Looking Under the Hood at Oracle Clusterware 11g Release 2, and Under the Hood of Oracle Automatic Storage Management: Fault Tolerance. The first one was great fun, 3 of 4 had more than 20 years experience in this field. The youngest "only" 8. Greg Rahn revealed they have a "secret": they try to solve complex problems with simple solutions. Graham Woods advised people to ask Why? and understand what you are doing before you actually change anything in production. Simple guidelines, but since many are not following them it needs to be repeated.

I'm not going to say much about the two following, again useful content and I have to look again at the mysteries of Clusterware. Also Maria Colgan showed as she did yesterday she is an excellent speaker. She is invited to next year's OUGN conference, which according to Bryn is the world's best user group conference. Looking forward to it.

Back at home I've had many discussions regarding ASM, it is surprising what people can get religious about. One former coworker stated that Oracle has no right to implement a volume manager... I've never had any good arguments for migrating to ASM. If the DBAs at the site like it, I chime in with things I like, simplicity to name one. If not, because "any datafile should be verifiable with ls or dir", then I usually back off from any discussion. In the last presentation of the conference by Alex Gorbatchev from Pythian I learned quite a few reasons to go for ASM, like you have more integrity checks and efficiency if you use ASM. I'll probably stay away from heated discussion until I've read everyting from James Morle,, ASM-gurus like Alex; SAN- and Unix-admins don't give up easily.

This was first time at OOW. Was it worth it ? Absolutely. I got served more than I could eat, met a lot of smart people whose blog posts and tweets I've been reading for years; i.e. great networking opportunity, and had great fun. Learned a lot, now I know what other SSF-travelers are talking about.

Thanks to everybody who stopped for a talk or sent a smile in my general direction. Going home looking forward to see wife & jr.

OOW 2011 Day 4 and Blogger's meetup

Day four started well with Oracle Optimizer: Best Practices for Managing Optimizer Statistics presented by Maria Colgan. Then followed SQL Tuning Expert Roundtable with Jagan Athreya, Benoit Dageville, Tom Kyte and Graham Wood. These two presentations had a lot in common, correct statistics should be checked if plan is wrong, note correct, but not necessarily updated statistics. If an automatic routine for stats gathering cannot be found the metadata for the table and columns should be set manually and locked. More people talk about skews this day, it was mentioned here also, but I guess James Morle and Cary has more to say on that. The problem with histograms, bind variables and bind peeking is somewhat easier in 11g with adaptive cursor sharing, but not a 100% solution according to the panel. One 11g feature I'm going to look more into is extended statistics.

The third and last presentation for me was Powerful New Ways to Use Oracle Data Guard for Planned Maintenance with Larry Carpenter. This stuff requires a lot of consideration and planning. Downtime, complexity and risk. Finding a solution that lowers especially the last two is important. Another presentation I have to digest.

At 17:00 I went to Pythian's bloggers meetup. Met a lot of guys IRL that I've been following on Twitter and whose blogs I've been reading for years. Impressive event and very social. Pythian just ranked even higher.  Tim Hall won the prize for having collected most signatures, my collection in the picture. I was too busy talking to people, but got a fair share of signatures. Loved it all.

Wednesday, October 5, 2011

OOW 2011 Day 3

Went to five presentations, two of them stood out. Doug Burn spoke about SQL plan management in 11g and I learned some new tricks. The material and the content was quite good, also I like these comments that may not be in the slides, e.g. he quoted Jonathan Lewis who said that if you have a narrow problem you should apply a narrow solution, an advice against changing a system wide parameter just because you have one SQL statement gone astray.

The other presentation I liked a lot was about mining the AWR data with SQL, by Yury Velikanov from Pythian. The presentation was packed with useful examples and based on experience he had with customers. When he started almost all seats were taken, and then some people left, maybe it was over their head, but for me, I like these kind of presentations. Yes, it is cool with fancy colorful slides where rule number one is max six words pr slide, but I don't mind seeing code in the slides once in a while. I think that if you strive to understand the AWR repository and analyze it the way Yury did you will have a better understanding on how things are connected, in a way you cannot have when looking on nice graphs in EM. Also you see the limitations in AWR, most data are aggregated which creates other limitations; see Cary's presentation about skew. He made a point in the beginning that there are different concepts to use when attacking optimization and troubleshooting, he gave pointers to tools and methods. All in all a presentation that I have to download and study for a while.

Another great day.

Tuesday, October 4, 2011

OOW 2011 Day 2

Went to the keynote and five presentations. Keynote was a lot of show, but not much to bring home. Two presentations regarding database migration assistant for unicode, and Real Application Testing were relevant, but so uninspiring and lacked the extra stuff that it was like attending someone reading from the manual. Not much of a take away there either. Also went to a presentation on PL/SQL - Divide and Conquer, modularizing your code. I had hoped to hear more from Bryn Llewellyn, Oracle, but it was really Martin Büchi from Avaloq that had the whole show. Lots of usefull info, interesting real life case on how to structure enormous amount of code. Will remember this next time I get a chance to work with developers and have to look into refactoring of PL/SQL. It does seem to be something one can apply elsewhere in any large environment. Bryn is an excellent speaker, by the way. The two were clear that they did not want to sell consulting or anything else, just to share some goals and passion. I liked that, Bryn talks in way that could fool any DBA into write some code; wish he had a bigger part of it.

Next presentation was of course the highlight of the day. Cary Millsap talked about skew. I've heard some of this before, but again there was something new to learn and reconsider. A reminder of how skew occurs in many levels and groupings and is really the reason why he and most of us has a job. (Just that Method R gets the job done sooner, I think). Cary is the best presenter I can think of in the Oracle world, and made the room laugh many times at a potentially boring subject. Two of my friends heard him speak for the first time and was impressed. One of them considered to skip the remaining presentations for the day so the day would have a good ending. Couldn't have made a better compliment myself.

I finished of with a presentation regarding best practices for Oracle on Windows. I'm not much a fan of best practices, but this was a full packed check-list on what can go wrong on Windows; a unix/linux guy would suspect that there is enough to keep you busy. Very useful to use later since I have been sent to windows land on an assignment for 6-12 months.

I also met briefly Dan Norris from Oracle and Alex Gorbatchev from Pythian; two guys I've been following on Twitter for a while. Always great to see people IRL once in a while (MOW 2011 and Hotsos 2008 in that order). These guys remind me why I like working with this (the database), people outside, especially the open source java cool coding guys, wonder how can one have a life at all working with a comercial database that is anything than simple and inexpensive. Lots of smart people work with this and create an atmosphere beside the work - e.g. OOW has become a great event. Since they don't compete in my market it does not cost much to write this; but Method R and Pythian are two companies I admire; they create and share a lot with others, and appearantly have lots of fun at work and outside.

Now breakfast, no time to spell check. Love my new android tablet, by the way.

-Breakfast over, some spell checking done.
-Power tip for the Android tablet: Check the Wi-Fi disconnection policy, set it to disconnect when screen is turned off; battery lasts much longer.
-I remember that I met a smart guy on Rac Attack yesterday; Martin Nash. From UK, good for us we have such experts we can ask to come over to OUGN.

Monday, October 3, 2011

OOW 2011 Day 1

First day at OOW 2011 started with interesting presentations arranged by IOUG, very usefull stuff and I have to read the presentations again to get all the details regarding Oracle on VMware, and deploying APEX with security in mind.

Highlight of the day was OUGN's encounter for Norwegians at OOW 2011 and invited guests (invited VIP speakers for next years OUGN 2012 conference). This took place at The Norwegian Church Abroad. The church has a spectacular view over the city. Among the guests were Jonathan Lewis and Cary Millsap, two of my heroes in the Orace World. I had a long conversation with Cary regarding work, relationship between DBAs and developers, something we both have thought a lot about . I also tried to convince him to come next year and speak at OUGN 2012. Hopefully we can arrange for one of his one day classes a day before the conference.

OOW 2011 Day 0

This year when I was on my way to MOW 2011 in Denmark, a few months after I started at Keystep, my manager called me and asked if I wanted to go to Oracle Open World. That has not happened before in any other job I've had and of course I said yes, so here I am in San Francisco. Arrived late Friday, and spent Saturday adjusting to new timezone and walk around. First observations: really nice city, want to bring my family and see more. Also lots of nice people, with some exceptions from those driving car through the crossing of Lombart street and Hyde Street; they were pointing to the sky with wrong finger.

Thursday, September 29, 2011

Going to San Francisco...

...tomorrow to meet a lot of fellow Oracle nerds at Oracle Open World. Looking especially forward to Pythian's bloggers meetup on Wednesday, though my blog has been quite dormant for a while and I might not deserve it that much.  We are planning for next year's OUGN conference, and hopefully we'll have a chance to meet some of them we want to invite. All I know is that we'll have more of what has been a success so far. I might bring a cuddly toy to hand over to that guy who knows so much about partioning.... Until another time zone.

Wednesday, July 13, 2011

Rebuild Oracle Text indexes for Portal

The job portal.wwv_context.sync stopped working with the following error message:

ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "PORTAL.WWV_CONTEXT", line 2530
ORA-20000: Oracle Text error:
DRG-10502: index WWSBR_CORNER_CTX_INDX does not exist

We opened a Service Request at Oracle support but never figured out how the index disappeared, if that was the case and not a corruption somewhere. Support suggested that we executed the procedure given in Doc ID 340439.1; reinstall Oracle Text, then connect as the portal user from Portal server and execute the three scripts from middle tier home:


But the last script failed with:

SQL> @?/portal/admin/plsql/wws/ctxcrind.sql
Creating Context Preferences and Indexes...

PL/SQL procedure successfully completed.

ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "PORTAL.WWERR_API_EXCEPTION", line 164
ORA-06512: at "PORTAL.WWV_CONTEXT", line 2189
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "PORTAL.WWV_CONTEXT", line 2118
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "PORTAL.WWERR_API_EXCEPTION", line 164
ORA-06512: at "PORTAL.WWV_CONTEXT", line 2077
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "PORTAL.WWV_CONTEXT", line 1705
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-50857: oracle error in drvxtab.create_index_tables
ORA-00955: name is already used by an existing object
ORA-06512: at line 6

The Application Server Portal Configuration Guide section shows that the last script executes wwv_context.createindex and the error was isolated first to this procedure. The same section shows that wwv_context.createindex is equivalent to:

wwv_context.drop_prefs; /* Drop all Oracle Text preferences for the indexes, except Lexer preferences */
wwv_context.drop_invalid_indexes; /* Drop all invalid indexes */
wwv_context.create_prefs; /* Create all Oracle Text preferences,except Lexer preferences */
wwv_context.create_missing_indexes(l_indexes); /* Create missing indexes and record them in l_indexes */
wwv_context.touch_index(l_indexes); /* Mark all rows for created indexes as requiring synchronization */
wwv_context.sync; /* Synchronize indexes */
wwv_context.optimize; /* Optimize indexes */

By executing each procedure in steps the error was isolated to wwv_context.create_missing_indexes. All of the indexes in question are listed in and can be created one by one with procedure wwv_context.create_index.

To get around the error I executed the following procedure in stead of the script ctxcrind.sql, that is, after I executed the scripts inctxgrn.sql and ctxcrind.sql:



exec wwv_context.touch_index(WWV_CONTEXT.URL_TEXT_INDEX)

exec wwv_context.sync;
exec wwv_context.optimize;

We did not verified if it was really necessary to reinstall Oracle Text, you may try the procedure above before reinstall Oracle Text if you encounter the same error with wwv_context.sync.

Wednesday, July 6, 2011


You may get error ORA-32012 if you are on 11g, but have the compatible parameter set to pre-11g in the database you are cloning from, and the spfile for the source database is stored in ASM when you do an RMAN duplicate from active database.

To get around this error, I have found two workarounds:

1) Set the value for compatible to at least 11.1
2) Skip transferring of the spfile during the clone process.

Option 1 is a big change for the database (it affects the CBO among other things), but say you need a clone and are going to change this parameter anyway it is the simplest one since you don't have to create the parameterfile manually. Option 2 means you create the spfile before the cloning starts and remove the clause with spfile from the duplicate command.

An example of duplicate for option 1 is:

run {
set newname for datafile 1 to NEW ;
set newname for datafile 2 to NEW ;
set newname for datafile 3 to NEW ;
set newname for datafile 4 to NEW ;
set newname for datafile 5 to NEW ;
set newname for datafile 6 to NEW ;
set newname for tempfile 2 to NEW ;
duplicate target database to FOOTEST
from active database
set compatible=''
set diagnostic_dest='/u01/app/oracle'

An example for option 2:

run {
set newname for datafile 1 to NEW ;
set newname for datafile 2 to NEW ;
set newname for datafile 3 to NEW ;
set newname for datafile 4 to NEW ;
set newname for datafile 5 to NEW ;
set newname for datafile 6 to NEW ;
set newname for tempfile 2 to NEW ;
duplicate target database to WINTIDST
from active database;

It looks as the transfer of spfile from ASM to normal file system is perceived as a downgrade, though there is no change when compatible is 10.2.3 on both databases. The format of the spfile in ASM seems to be different when stored in ASM as explained in Database Administrator's Guide 11g. The change from HARD-enabled storage to normal is not supported then if you are below 11g. Makes sense when one thinks about it, but I did a few tests back and forth to understand the connection, and it looks as if this new format was introduced in 11g. Exactly what format is used for the spfile in ASM is not clear. The ASM installation in this case is on version and the database is on (with the compatible left on for some unknown reason). Maybe the new format is used when the database is on version 11g even though the compatible parameter is pre-11g, if so it looks like a bug when RMAN duplicate looks only at the compatible parameter and not on version of Oracle.

When RMAN creates the spfile it will be created in $ORACLE_HOME/dbs, I could not find any way of having it created in ASM directly.

Anyway, probably not a frequent error, but decided to write it up since I didn't find much when googling.

Friday, June 3, 2011

Hidden updates - another reason to trace

An insert or update statement that is taking a long time to complete is often working on the indexes belonging to the table rather than the table itself. If you look at the plan and finds nothing wrong, it is easy to forget that the indexes have to be updated as well.

The solution as always when something takes time is to enable trace. When updating the indexes the trace file will usually show waits of type db file sequential read, and usually one block at the time.

Full table scan normally causes db file scattered read with many blocks at the time. The number of blocks it fetches is limited, but not exclusively, by the parameter db_file_multiblock_read_count.

In other words, with lots of full table scans you expect db file scattered read but your GUI gives you db file sequential read. The trace file will show you what objects are causing waits and may remind you that the table has an index or two that you can drop and add later.

This command counts the number of waits of one type grouped by object_id (as in dba_objects):

grep "db file sequential read" FOOPROD_ora_6237.trc |awk '{ print $12}' |sort | uniq –c

You can find total elapsed time (in seconds) for a specific object_id (106836 in this example) with:

awk '/106836/ { ela += $8} END {print ela/1000000}' FOOPROD_ora_6237.trc

Repeat this for the object ids from the first list to find where most time is spent.

Then you can find the type and name of the object:

select object_type, object_name
from dba_objects
where object_id=106836;

Of course a decent profiler will do this for you. I wrote this post to remind myself that the trace file will tell me what the fancy GUI hides for me. And it is kind of cool that on a mature OS a string of simple commands allow me to make my own ad-hoc profiler.

Monday, May 30, 2011

VirtualBox on Fedora 15

This is what I did to install Oracle VM VirtualBox on a new Fedora 15 installation.

The documentation at the bottom of download page VirtualBox for Linux gives you the repos-file for yum on Fedora. Find the repos-file and save it to /etc/yum.repos.d/virtualbox.repo, it should look like this:

name=Fedora $releasever - $basearch - VirtualBox

Then you can search for VirtualBox:

[root@favela ~]# yum search VirtualBox
Loaded plugins: langpacks, presto, refresh-packagekit
=========================== N/S Matched: VirtualBox ============================
VirtualBox-4.0.x86_64 : Oracle VM VirtualBox

Name and summary matches only, use "search all" for everything.

To install:

yum install VirtualBox-4.0.x86_64

Users need to be included in the OS group vboxusers before they can run it:

usermod -a -G vboxusers oisene

VirtualBox is now ready to run from menu.

In fact this was not what I did first time, because I didn't read the fine manual to the end. I download it from here and installed the missing packages something like this:

yum install qt qt-x11 SDL
yum install kernel-headers-$(uname -r) kernel-devel-$(uname -r)
yum install binutils gcc patch glibc-headers glibc-devel
yum install dkms
rpm -ip /home/oisene/Downloads/VirtualBox-4.0-4.0.8_71778_fedora15-1.x86_64.rpm

Without the dkms package VirtualBox will run, but it is recommended to ensure VirtualBox host kernel modules will be update during next kernel update.

Sunday, May 29, 2011


I have made a few changes to my blog... think I want to blog again.

So far this year in a few words; new job at Keystep - smart move, if you try two years in the wrong company it is time to move on; met quite a few smart oracle folks at the yearly conference of Oracle User Group Norway - got elected as a board member; met more smart guys at Miracle Open World in Denmark the week after. And been thinking about moving back to Brazil quite a lot lately.