If, like me, you have suffered from ORA-29902 Error in executing ODCIIndexStart() routine errors where Spatial indexes are involved, the following might help you fix it. The error involved in the following has been extracted from a log file for a system which doesn’t use Spatial or Locator itself, but calls out to a separate database which does have Locator installed. This latter database was created using Transportable Tablespaces, exported from 10.

I have been looking at this far too long, and I’m stumped. I resolved a similar problem yesterday on another server. That was down to the ORACLE_HOME setting in listener.ora having a ‘1’ in it rather than a ‘2’. Took ages to spot that. Anyway, here the stuff you’ll need to know to sort this for me, or suggest stuff. It’s a question on Oracle L seeing as there is a lot of evidence to post.

This post is from 2013 when Internet Explorer was [still] a thing. I rather suspect nobody uses IE any more! Are you forced to use Internet Explorer at work? Are you, like me, forced to use an old, insecure, broken version of IE at work, because it’s the Government Standard version? And are you, like me, unable to upload evidence files to My Oracle Support? You need to go to Tools, then Internet Options.

Tired of trying to drop empty datafiles from a tablespace, which you know is empty? Keep getting errors telling you the data file isn’t empty? Getting frustrated with the whole thing? Me too. This link has the reason and solution.

Oh hum. An 11.2.0.3 Enterprise Edition production database has statspack taking a regular snapshot under the control of a dbms_scheduler job. For no apparent reason, the snapshot started failing with ORA-01400 Cannot insert NULL into PERFSTAT.STATS$SYSTEM_EVENT.EVENT. This was an interesting one to fix. The following is the investigative process, in brief. Test the snapshot process with a manual one - same error. Google and My Oracle Support aka MOS, were no help whatsoever.

Recently, I saw a mention of an interview question for SQL developers. It was something along the lines of: There is a table with a sex column. It has been discovered that the values are swapped around and need to be corrected. How would you swap all ‘M’ values to ‘F’ and all ‘F’ values to ‘M’, while leaving the other values untouched, in one single SQL statement and without requiring the use of any temporary variables.

http://jarneil.wordpress.com/2013/04/23/recovering-from-rm-rf-on-a-datafile/ has all you need to know to help avert a total disaster. Tanel Poder, who knows these things, advises that the file should be quiesced or made read only before attempting the recovery.

Ever see this error? I have, just today. An interesting one to debug. I got there in the end though. The database is running on a two node VERITAS cluster. To protect the innocent, I shall refer to these as node_04 and node_05, for that is similar to their real names! The database is not RAC, it runs on one node or the other, but never both. There is one instance and one database.

A quickie! How do you set the correct Oracle environment in scripts? Do you hard code? You’d better not. I’ve lost count of the times I’ve ended up with, for example, a 10g database running with bits of the 9i software hanging around. It leads to monumental problems that can be hard to track down. Moral: Do not hard code Oracle environment details. This is what I usually do: 1 2 3 4 5 6 7 8 #!

In the old days of exp/imp doing a Transportable Tablespace export/import was relatively simple - unless you had Spatial data, in which case, it wasn’t. Then we got hold of expdp/impdp and it became “different”. It now seems that in order to do a Transportable Tablespace import with impdp, you don’t tell it to do one! Confused? In the old days, you told both exp and imp which tablespaces you were transporting using the TRANSPORT_TABLESPACE and TABLESPACES parameters to exp, and the same with imp.

Accessing a web server or an email server, directly from within a database, used to be quite simple. However, it all stops working at 11g. Why is that and what can be done to fix it? Introduction Prior to Oracle 11g, any user in the database wishing to use the various network packages - UTL_HTTP, UTL_SMTP, UTL_TCP, UTL_MAIL etc, and their predecessors, only requires to be granted EXECUTE privileges on the appropriate package(s).

Oracle has raised an alert in the alert.log and created a trace file as well, for a failed DBMS_SCHEDULER job with a strange name which doesn’t appear in DBA_SCHEDULER_JOBS or DBA_SCHEDULER_PROGRAMS - what’s going on? An extract from the alert log and/or the trace file mentioned in the alert log shows something like: 1 2 3 *** SERVICE NAME:(SYS.USERS) ... *** MODULE NAME:(DBMS_SCHEDULER) ... *** ACTION NAME:(ORA$AT_SA_SPC_SY_nnn) ... Where ’nnn’ in the action name is a number.

This post is all about cloning an 11g database from one server to another using an RMAN active database clone. This is not being done for Standby Database purposes, only to duplicate an existing database onto another server. The physical structure on both servers is the same, some path names have been changed. Source Structure Database SID: msmdppr /srv/msmdp/oradata/msmdppr/ /srv/msmdp/flashback_area/msmdppr/ Everything hangs off /srv/msmdp/oradata/msmdppr and there are data, index, temp, undo, redo, ctrl, dbs, diag directories there.