Oracle Scratchpad

OC Appendix

Addenda and Errata for Oracle Core Appendix

Back to Index


 n/a Charles Hooper has given me a number of details to chase up in his recent review of the book, but there’s one point he made that’s worth a quick little addendum here in the Appendix. It relates to a throwaway comment I made about event 10120.One of the little jobs I do when a new release of Oracle comes out is to browse the list of trace events which. Historically, were carried with the error numbers 10,000 to 10,999, although recently many more have appeared scattered across the range 32,000 to 65,000. If you have a Unix-based version of Oracle you can read these error numbers and their meaning (plus other details) in the file $ORACLE_HOME/rdbms/mesg/oraus.msg. If you’re running Oracle on Windows, though, or if you want to start with a smaller summary, you can taken advantage of the pl/sql function sqlerrm().Here’s a little SQL script I use from time to time:
set linesize 180
set trimspool on
set serveroutput on size 1000000 format wrapped

	m_ct		number(5) := 0;
	m_message	varchar2(511);
	for i in 10000..10999 loop
--	for i in 1..65535 loop
		m_message := sqlerrm(-i);
		if m_message not like 'ORA-_____: Message _____ not found;%' then
--		if lower(m_message) like 'ora-%event%' then
			m_ct := m_ct + 1;
		end if;
	end loop;
	dbms_output.put_line('Messages reported: ' || m_ct);

There are two strategies shown in the single piece of code. As it stands it reports any messages in the range 10000-10999 (if the number doesn’t appear in the oraus.msg file the value returned by sqlerrm(-nnnnn) reads: “ORA-NNNNN: Message NNNNN not found; product=RDBMS; facility=ORA”). If you switch the comment markers on the loop and test lines then it reports any messages containing the word “event”. I also use the second version of the code if I’m looking for events (or error messages) relating to a particular topic – there are, for example, about 135 messages relating to the word “materialize”.It’s not a method for getting perfect information about what’s in the file – but it’s a quick and easy starting check.



p.230  First paragraph, second sentence: “The pnext_buf_kcrfa is …” should be “The pnext_buf_kcrfa_cln is …”


 p.240  Section Log Files – the example is missing the semi-colon:

alter system dump logfile 'C:\ORACLE\ORADATA\D10G\REDO01.LOG';

Back to Index


  1. Dear mister Lewis,

    Thank you very much for bringing out this book.
    I have a small comment about the query on page 241 which can be improved so no modification is necessary any more.
    Basically the start time and end time can be determind by:
    counting the months between the start of 1988 and the start of the current month and convert this to seconds each month having 31 days (so times 31, times the amount of seconds in a day) and added to this the number of seconds between the start of the month and the current time.


    Tycho Schoenmaker

    select (floor(months_between ((sysdate-10/(24*60)), to_date ('1988-01-01','yyyy-mm-dd') )))*31 *24*60*60
          +  ((sysdate -10/(24*60))- to_date(to_char((sysdate-10/(24*60)), 'yyyy-mm')||'-01','YYYY-MM-DD'))*24*60*60 start_time,
         (floor(months_between ((sysdate-5/(24*60)), to_date ('1988-01-01','yyyy-mm-dd') )))*31 *24*60*60
          +  ((sysdate -5/(24*60))- to_date(to_char((sysdate-5/(24*60)), 'yyyy-mm')||'-01','YYYY-MM-DD'))*24*60*60 end_time
    from dual

    Comment by Tycho Schoenmaker — November 25, 2012 @ 10:43 am BST Nov 25,2012 | Reply

  2. Jonathan,
    Well I developed the opposite query years ago to be able to change the stamp (%t) in rman pieces into a real timestamps and seeing it shift when switching to a new month. So there was nobody to suggest it I found it myself.

    Comment by Tycho Schoenmaker — December 15, 2012 @ 4:46 pm BST Dec 15,2012 | Reply

RSS feed for comments on this post.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme Blog at


Get every new post delivered to your Inbox.

Join 3,507 other followers