Search

Top 60 Oracle Blogs

Recent comments

February 2011

HTML5 heading for an early grave?

I was very dismayed when I read this yesterday.

A number of news services have done the rounds since, including this today on Slashdot.

I think it’s all a bit depressing really. So we have another 3-4 years to wait for the final spec to be sorted. Then of course we have to wait for the spec to be implemented (badly) by all the necessary browsers. Of course they are already supporting various bits of it, but their implementations will no doubt change along the way and cause untold numbers of apps to break with them. Then we have to wait for everyone to upgrade their browsers. So what is the real date that HTML5 is go? 2020?

All of a sudden native apps seem kinda appealing and Flash doesn’t seem quite as evil as it used to be. :(

Cheers

Tim…

ORA-29913

External tables as a substitute of loading tables through SQL*Loader have become more popular over the last couple of years – which means questions about Oracle error ORA-29913 have also become more popular. So what do you do about something like this:


SQL> select * from extern;
select * from extern
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04063: unable to open log file EXTERN_17063.log
OS error No such file or directory
ORA-06512: at "SYS.ORACLE_LOADER", line 19

The commonest reason for ORA-29913 is simply that Oracle can’t open the operating system files it’s supposed to use. This may be the input data files or the various output files (the “bad”, “discard” and “log” files) that it needs to produce. The reason for failure may be that the directory doesn’t exist, or that the Oracle executable doesn’t have permission to access the directory appropriately, or that the file itself doesn’t have the necessary permissions.

In the example above it looks as if Oracle can’t write to the required log file which, as the “OS error” line suggests, may be a problem with the (unspecified) directory or with the named file (EXTERN_17063.log) in that directory.

So, a few quick checks when you see this error when trying to work with external tables:

    Check your ‘create directory … ‘ statements
    Check that the directories have appropriate permission for Oracle to read and write to them
    Check that there isn’t a problem with existing file permissions

I have a piece of framework code for external tables, and its most important feature is catering for all the output files (that I am aware of), and separating the directories for the inputs and the outputs.

define m_filename = 'd11g_ora_1820.trc'
define m_file_dir = 'C:\oracle\diag\rdbms\d11g\d11g\trace'
define m_log_dir  = 'c:\working'

drop table ext;

create or replace directory ext_tab as '&m_file_dir';
create or replace directory log_dir as '&m_log_dir';

create table ext(text_line varchar(4000))
organization external
(
	type oracle_loader
	default directory ext_tab
	access parameters (
		records delimited by newline
		discardfile	log_dir:'read_trace_dis.txt'
		badfile		log_dir:'read_trace_bad.txt'
		logfile		log_dir:'read_trace_log.txt'
		characterset us7ascii
		fields
			rtrim
			reject rows with all null fields
		(
			text_line	(1:4000)	char
		)
	)
	location ('&m_filename')
)	reject limit unlimited
;

As it stands this has been set up to define a standard trace file on my laptop as an external table; it’s a framework I use sometimes on customer sites where I have lots of database privileges but don’t have any rights to see the server: sometimes you really need to see the trace files. (A nice little feature that’s appeared in 11g, by the way, is that you don’t need to work out names for trace files any more, you just need to get at column v$process.tracefile)

A couple of footnotes:

  • If you don’t specifiy a “log” directory as I have then all the output files go into the “default” directory which, for many people, is likely to be the directory where you had your datafiles. (You could have different directories for the “bad” and “discard” files, of course.)
  • The various log files are appended to (not replaced) every time you query the external table – and it’s very easy to forget this and let them become very large (which means that you could get ORA-29913 because the size of an output file exceeds the O/S limit, or has filled the device).
  • If you don’t specify names for the output files then they will contain the name of the external table and the process ID of the session’s shadow process. The presence of the proces ID does reduce the risk of a single output file getting too big, of course. (I haven’t checked what this does with shared servers, but the typical use of external tables means you probably shouldn’t be querying them through shared servers)
  • The default names of the output files varies with version – until 11g I think it was always {table_name}_{process_id}.{type}, but my 11g sandbox seems to introduce another number in the middle of the name to give:  {table_name}_3168_{process_id}.{type}

Got to stop there, the plane’s about to push back – and it’s “all electronics off”. Spelling and grammar may be corrected this evening.

Searching on Google just got a lot safer...

If you use Chrome anyway. If you haven't tried Chrome - give it a whirl, I was a diehard Firefox user until I tried out Chrome.

There is an extension that can block sites on Google Search. It is called the "Personal Blocklist". Within seconds of installing it - the annoying sites that use whatever technique they can to get to the top of a Google Search were blocked (by me, you just click on "block " in the search result and they disappear!)
You can opt to show the blocked results at anytime - so if you wanted to see them - they are easily there, but hidden.
Very cool - very very cool. There was at least one or two sites I blocked immediately - including the annoying ones that pretend to show answers but then want you to log in to read them (sometimes with a paywall).

UnConference - Jonathan Lewis

Media: 
See video

 How to become an Oracle expert.

Furies of Calderon…

I was getting a bit of Dresden Files withdrawal, so I decided to give Jim Butcher‘s other series (Codex Alera) a go, starting with Furies of Calderon.

It’s very different to the Dresden Files style. For a start, it’s not in first-person, so you don’t feel like you are in the head of any specific character, but you get a more rounded view of the other characters. Second, there is no magic, although I guess you could call the furies a type of magic. Third, the books are big. Speaking as a born-again-reader, I’m still a bit daunted by large books.

Despite the obvious differences, I really enjoyed it. I will no doubt start on the second book in the series some time today.

Cheers

Tim…

Calculating Overlapping Hours for Labor Transactions

February 15, 2011 There is an interesting problem in the ERP platform that I support.  When production floor workers begin working on a particular job, the employee “clocks into” the job in the computer system.  When production floor workers stop working on a particular job, the employee “clock out of” the job in the computer [...]

It’s Conference Season!

My favorite mode of life is being busy doing something that I enjoy and that I know, beyond a doubt, is the Right Thing to be doing. Any hour I get to spend in that zone is a precious gift.

I’ve been in that zone nearly continuously for the past three weeks. I’ve been doing two of my favorite things: lots of consulting work (helping, earning, and learning), and lots of software development work (which helps me help, earn, and learn even faster).

I’m looking forward to the next four weeks, too, because another Right Thing that I love to do is talk with people about software performance, and three of my favorite events where I can do that are coming right up:

Product Support vs Operational Support

Sometimes I get questions as to whether Pythian is one of the competitors battling with Oracle for MySQL support. The answer lies in the distinction of product support and operational support.

At Pythian, we are laser focused on supporting applications and data infrastructure using Oracle, MySQL and Microsoft SQL Server products. A vast majority of our Oracle customers (there are few customers who have very old 7.x and 8.x products running without vendor support) have Oracle maintenance subscriptions that include product updates and product support. Product support entitles the customer to open support requests when the product doesn’t perform according to the specifications (bug reports) as well as fill in enhancement requests. It also covers deployment blue-prints and deployment guidelines in the official vendor documentation and support database.

What you can’t expect from product support are answers to questions like these:

  • How do I architect my infrastructure?
  • How much CPU do I need to run this database
  • How do I setup my backups?
  • How do I tune that SQL statement?
  • What I need to monitor in my environment to keep it healthy and avoid service outages?

Of course you cannot expect product support to login to your systems and help monitor them, recover a corrupted database or resolve performance issues etc.

Oracle customers usually have clear understanding of the differences between product support and operations support and consulting that Pythian provides. Even then, every now and again we hear rare statements like “I’m not renewing our Oracle product support because we now have you, Pythian, supporting our databases.” Hearing that, we’re catching our breath for few seconds and then patiently explain that this is inadvisable and the product support is totally different from what Pythian does.

Because of its open-source nature, MySQL database customers have somewhat less incentive to sign up for product support relying on public community releases and the ability to patch the product themselves but even then there is a clear distinction between product support and operational support.

All that was a long prelude to answering the question — “Is Pythian Competing with Oracle and other vendors for MySQL product support”? The answer is NO — Pythian provides plan, deploy, manage services — we analyze, design, implement and maintain the infrastructure. We are working with the vendor providing product support (or as part of the community at large when it comes to the open-source community MySQL releases).

CMOS battery replaced…

My main desktop machine has been playing up a bit recently. When powered down it was sometimes forgetting the date and time settings. I figured it was the CMOS battery, but I couldn’t be bothered to open the case and change it. Anyway, after redoing the CMOS settings far to many times I finally opened it up to see what kind of battery it took. To my delight it was a “CR2032″, which rang a bell as I had bought one recently to put into some kitchen scales. A quick trip down stairs and I “found” a new battery, popped it in and Robert’s your Father’s Brother. :)

I would just like to point out that I still believe this event was a sign that I should waste money on some new hardware… :)

Cheers

Tim…