Øyvind Isene's Blog

EXP-00003

2011-12-29T18:18:00.000+01:00

There is a bug in the export utility on version 11.2.0.1 when running against a 11.2.0.2 (and possiby 11.2.0.1) 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

Wordfeud and regular expression in SQL

2011-12-28T17:51:00.003+01:00

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

Two free profilers for 10046 trace files

2011-11-30T20:15:00.010+01:00

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

OOW 2011 Day 5

2011-10-07T04:50:00.001+02:00

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:

OOW 2011 Day 4 and Blogger's meetup

2011-10-07T03:02:00.001+02:00

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

OOW 2011 Day 3

2011-10-05T16:05:00.001+02:00

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

OOW 2011 Day 2

2011-10-04T16:15:00.001+02:00

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 -

OOW 2011 Day 1

2011-10-03T15:48:00.001+02:00

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

OOW 2011 Day 0

2011-10-03T15:39:00.001+02:00

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,

Going to San Francisco...

2011-09-29T23:46:00.001+02:00

...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

Rebuild Oracle Text indexes for Portal

2011-07-13T15:42:00.001+02:00

The job portal.wwv_context.sync stopped working with the following error message:ORA-06510: PL/SQL: unhandled user-defined exceptionORA-06512: at "PORTAL.WWERR_API_EXCEPTION", line 71ORA-06512: at "PORTAL.WWV_CONTEXT", line 2530ORA-20000: Oracle Text error:DRG-10502: index WWSBR_CORNER_CTX_INDX does not existWe opened a Service Request at Oracle support but never figured out how the index

ORA-32012

2011-07-06T10:21:00.006+02:00

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

Hidden updates - another reason to trace

2011-06-03T18:32:00.008+02:00

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

VirtualBox on Fedora 15

2011-05-30T20:14:00.006+02:00

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:[virtualbox]name=Fedora $releasever - $basearch - VirtualBoxbaseurl=http://download.virtualbox.org/

Today

2011-05-29T18:54:00.003+02:00

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

Removing orphan DataPump jobs and filtering included tables with a query

2010-11-20T17:20:00.000+01:00

Say you define a DP job from the API (DBMS_DATAPUMP) you may end up with jobs with status NOT RUNNING until you get it right. Verify if you have such a job withselect job_name,state from user_datapump_jobs;Then you may try to remove it with:declare l_h number;begin l_h:=dbms_datapump.attach('YOUR_JOB'); dbms_datapump.stop_job(l_h,immediate=>1); commit;end;/If the query above still reports the

Slow performance with TDE and CHAR data type on 10.2.0.5

2010-11-16T19:16:00.011+01:00

In version 10.2.0.5 of the database there is a bug when using Transparent Data Encryption (TDE) on a column with the CHAR datatype. In the following test a table is created where the primary key is defined as CHAR(11) and then encrypted with NO SALT. A simple lookup on this PK works as expected by using the respective index on 10.2.0.4. But on 10.2.0.5 a trace on event 10053 shows that an

A few notes on Oracle VM

2010-10-20T11:28:00.014+02:00

This is an old post I kept as draft 8 months before I published it...I've been playing around with Oracle Virtual Manager (OVM) version 2.2 lately. This is collection of a few notes, not all directly related to OVM. The project started with building the server from parts I hoped would play along (they do). The CPU is an Intel i7-930, 12GB of RAM and two Western Digital 1.5TB disks (WD15EARS-

Permission denied when adding ASM disk

2010-10-10T16:45:00.007+02:00

This is a simple one, a reminder till next time I forget this, since stuff encountered after a Google-search led me astray... Just in case you try to add disks to an existing ASM disk group using syntax like this:alter diskgroup dataadd disk '/dev/xvdd1'add disk '/dev/xvde1';You may get error messages like these:ORA-15032: not all alterations performedORA-15031: disk specification '/dev/xvde1'

Error when using 11g export client on a 10g database

2010-06-29T19:29:00.000+02:00

The only reason to read this post is if you have googled for the error "ORA-00904: "POLTYP": invalid identifier". This error occurs if you try the old export command from an 11g client against a database on version 10g or lower. The export command runs a query against a table called EXU9RLS in the SYS schema. On 11g this table was expanded with the column POLTYP and the export command (exp)

Someone trying to outperform Oracle's cache

2010-05-08T19:04:00.007+02:00

The following code is from a migration project I'm working on. It is part of a package named PERFORMANCE and is run every 15 minutes: Declare test number; Cursor c_worklist Is select * from v_worklist where id = v_id; Begin FOR rec IN c_worklist LOOP test := 0; -- does nothing END LOOP; end;A nice little wtf-snippet. The purpose? According to "the vendor" the cursor that

Collatz conjecture in PL/SQL

2010-03-27T13:32:00.006+01:00

A simple implementation of Collatz conjecture in PL/SQL:create or replace type int_tab_typ is table of integer;/create or replace function collatz(p_n in integer) RETURN int_tab_typ PIPELINED as n integer;BEGIN if p_n < 1 or mod(p_n,1)>0 then RETURN ; end if; n:=p_n; while n > 1 loop pipe row (n); if mod(n,2)=1 then n:=3*n+1; else n:=n / 2; end if; end loop

Using trace to find wallet location

2010-03-06T14:58:00.001+01:00

Not talking about a lost wallet with money, but the wallet used for encryption in the Oracle database and with encrypted backup.A wallet is used for storing keys used to encrypt/decrypt data to/from the database, as with Transparent Data Encryption. The documentation on Advanced Security says that Oracle searches the parameter ENCRYPTION_WALLET_LOCATION in sqlnet.ora, or if not found it searches

Database triggers are evil

2010-01-29T22:16:00.001+01:00

If you have a lousy database model, you can get around it with triggers. If you need to hide some business logic from everybody else, you can do it with triggers. If you need a mechanism that bypass the consistency in the database, you can do it with triggers. They live a life on their own, seemingly autonomous from everything else and one easily forgets they exist. That is why I hate them. What

Changing character set in database

2010-01-25T19:53:00.000+01:00

If your database is using anything else than UTF8 as database character set you may consider to migrate from it. Oracle states in the Globalization Guide (10gR2):At the top of the list of character sets Oracle recommends for all new system deployment is the Unicode character set AL32UTF8.Depending on what characters you actually have in your database you have three options on how to do this:

RMAN Convert database and ORA-7445

2009-11-09T09:55:00.003+01:00

Using the CONVERT command in RMAN can be an efficient way to migrate a database from one OS to another, if they have the same endian.We migrated 9 databases on 10.2.0.4 from HP-UX (on Itanium) to AIX. The databases where distributed on three servers with not exactly the same hardware configuration. On one of the servers we encountered an error described in bug 888530 on Metalink or My Oracle

Manually add a host target to Grid Control

2009-10-11T14:25:00.013+02:00

Case: I wanted to reinstall the Enterprise Manager agent on a host. I went on to delete the host in the list of targets in EM. Then after a new installation and synchronization I somehow managed to push the list of targets from GC to the agent and thereby removing the host as a target in the local targets.xml file. Result: The monitored host did never show up in the target list of hosts even

AIX and large pages

2009-06-02T18:08:00.001+02:00

I'll probably need this in the not-so-distant future. Here is the link to Noon's write-up when I need it. His blog is recommended, by the way.

Passed the OCP exam

2009-03-08T12:39:00.014+01:00

Friday I passed the OCP exam (1Z0-043). In two months I have studied for, and taken three tests in order to achieve OCP certification. Before I started on this in January I had noe previous experience with Prometric testing or certification at all. But I knew I had read a post about it earlier and started looking up an old post by Chen Shapira about how she prepared for the OCA exam. I ordered

New job

2009-02-12T21:39:00.002+01:00

This year started off fine with a new job. I quit the company I joined just 25 months earlier, in itself a defeat. Though I gained much useful experience there, some signals convinced me to move on, and now I am quite happy that I did so. I joined Steria Norway to work as a senior system consultant, and so far it looks quite promising. The company is internationally focused and one is encouraged

Side effect

2008-10-26T06:29:00.005+01:00

A couple of months ago I had an incident on a live production system. The obvious error was to execute a DDL statement at 9 am that should have been performed during off-business hours. On the other hand I guess you cannot avoid any ad hoc change on a live system forever. That will require you to be aware of any problem and issue that may strike your system. Say you have an issue right now,

Connection Manager and ORA-12529

2008-08-27T22:33:00.001+02:00

A simple configuration with Oracle Connection Manager (CMAN) did not work, I constantly received ORA-12529 when trying to connect with sqlplus to a database through CMAN. After checking cman.ora over and over and browsing all the trace files I finaly dived into one and found a problem with host name resolving. Turned out that the host name defined in /etc/hosts on the database server was not

Multi-column function based index

2008-07-15T20:02:00.000+02:00

Today I was looking at an SQL statement with a slow response. The statement does a lookup on a person using 'upper(lastname) and upper(firstname)'. There is a functioned-based index (fbi) on the lastname column, but by checking the statistics it turned out to have a rather low selectivity and the CBO went for a full table scan. I don't know why it has not occurred to me before, but today it

Table partitioning to improve stats

2008-06-30T22:10:00.002+02:00

In a earlier post about legacy databases I mentioned that dirty data are more likely to enter the database on an earlier stage. One day while I was looking at the selectivity for a few columns I found the values a bit strange. After some digging I noticed that for most of the time the data was OK, even though a proper foreign key constraint was missing for the columns. But at some time many years

Another great tool: SQL Developer

2008-05-25T14:01:00.001+02:00

I like great tools. An efficient tool will help you do a piece of work faster than when you are on your own. If the tool is made by someone who is close to the material chances are the tool is efficient. The tool I use most is SQL Developer. It is easy to use and I promote it to any developer where I work, especially if they only need a quick access to the data dictionary, run some simple (or

Praise for Oracle BI Publisher

2008-05-13T21:25:00.000+02:00

I have been working on a small scale data warehouse project for a few months along with many other tasks. Following Kimball's ideas on dimensional modeling and by keeping it simple I had more or less an idea on how to proceed with the construction of the database, data model and how to create the reports (but only in SQL). One guy in management actually wanted to learn SQL and went ahead with

ORA-16047

2008-05-05T20:16:00.001+02:00

Just a short post on this error code. Happened last time I recreated a physical standby. I could not find more than the reference note on this error on Metalink (a note that is just a copy of the output from command oerr ora 16047):Cause: The DB_UNIQUE_NAME specified for the destination does not match the DB_UNIQUE_NAME at the destination.Action: Make sure the DB_UNIQUE_NAME specified in the

Create a standby database with RMAN

2008-04-29T21:07:00.001+02:00

After the incident with missing forced logging in primary database the logical standby had to be created again. Compared to what is normal these days the database is not huge, but the standby database remains on a server in a different city and the link between isn't exactly a digital super highway. Also I had to restrict the consumed bandwidth during transfer with secure copy (scp -l 2500,

Understanding a legacy database

2008-04-20T17:05:00.002+02:00

Any database that has gone into production is a legacy database according to Tom Kyte. I agree, but to me some databases give me associations to legacy more often than others. In a post at the OraStory blog Dom complains about companies that hire contractors for a maximum of one year, and due to the fact that it takes half a year to understand the business and the data, you hardly get to show

Data Guard and forced logging

2008-04-14T18:20:00.000+02:00

When you start with Oracle Data Guard you have three sources of useful information: The Data Guard Concepts and Administration manual, which is quite good, other peoples experience, and after some time your own. The manual does not include all the mistakes one can do, nor what many really don't use (like the data guard broker; not everybody would leave switchover, or worse failover to a robot).

First post...

2008-04-13T18:15:00.000+02:00

A Sunday morning is a perfect moment for starting your blog. Not sure where this is going, or if it's going. Here the other day I found a solution to a problem I thought others may have encountered. I figured that my own blog would be a place to write it down. At least I can find the solution again if I make the same mistake twice.This blog will concern mostly about stuff related to Oracle,