Here I am at Denver Irrational
Airport (a pathetic follow-on to the beloved Stapleton of days
past) following a Rocky Mountain User Group training and
conference event that is clearly best-of-breed for North
America.
Among my personal highlights was being part of the Wednesday
keynote panel with Chen Shapiro, Alex Gorbachev, and Debra
Lilley, and managing, in the final half-hour of my
hour-and-a-half presentation to do Edition Based Redefinition
live in SQL*Plus.
Some people jump out of airplanes and trust their parachute.
Some people jump up in front of audiences and trust their
software. The later group likely suffers more catastrophic
failures but I was once again spared that fate.
Among those here and presenting, not mentioned previously, and
making this the event is was ... Mogens Norgaard, Cary
Millsap, Jeremiah Wilton, Graham Wood, Jose Senegacnik,
Sridhar Avantsa, and Robert Freeman (post-LDS job, now with
Oracle).
Non presenters included Angela, SQL*Developer product manager,
who I hope to lure into presenting at the next WWOUG meeting
and Dan Norris who insists that he doesn't sell Exadata but
helps those that do, and Paul Veilleux, formerly with Larry's
Pillar Data Systems returned to Oracle to also work with
Exadata.
Friday, January 29, 2010
Today is the last day of the IOUC
Leadership Conference and, following the pics, I have posted a
few comments from the event.
Mark Townsend
Willie Hardie
Margaret Lee
Morgan Stanley?
Extended support for
Database 9i (9.0 and 9.2) ends July 31, 2010
Extended support for
Database 10g (10gR1 and 10gR2) begins August 1, 2010
Larry Ellison is dismissive
of the entire buzz around the buzzword d'jour "Cloud" and
"Cloud Computing." So much for v12c.
Friday, January
01, 2010
From a private communication:
I have just confirmed that attempting to do GbE line rate TCP
writes on e1000s is sub-optimal.
Customers that are using Intel e1000 NICS (or e1000e in the
new Nelly) and are running the NFS client on ANY version of
RHEL on either AMD or Intel,
will only see a sequential TCP write range of 75-85MB/sec.
This is a GbE part. I don't know about 10GbE NFS client
performance yet (I do now have 2
Intel 10GbE parts).
Broadcom 5700 parts are fine. nVidia MCP55 (a builtin found in
AMD servers) are fine.
It is a corner case, but affects database performance for TCP
writes and e1000s are ubiquitous parts.
TCP read is line rate
TCP write is about 70% of line rate
UDP read is line rate
UDP write is line rate
Line rate is PAYLOAD, so this is about 105-108. At the filer,
this renders to about 113MB/sec
-- private communication from an Oracle Platform
Engineering at Red Hat Software
Wednesday, December 23, 2009
Sunrise Analemma at
the Tholos (Delphi)
I am just a few days from beginning my 61st analemma and we are pleased to report that
the only disappointment we face is the sad realization that we still can not tell whether Schrödinger's cat is ok.
To all of our many friends and acquaintances around the world: "Thank you." Lets do it again ... in in twenty-ten.
nav varsh ki
subhkamna
xin nian kuai le / xin nian hao
felice anno nuovo,
buon anno
sretna nova godina
gelukkig Nieuwjaar
happy new year
bliadhna mhath ur
head uut aastat
onnellista uutta
vuotta
bonne année
ein gutes neues Jahr
/ prost Neujahr
yeni yiliniz kutlu
olsun
hauoli makahiki hou
blwyddyn newydd dda
Monday,
November 30, 2009
Well things have calmed down a bit
so you can Click
Here to view a few pictures from B'ham that were taken
after the Speakers' event at
All Bar One and later at Thai Edge: both in Brindley Place.
There is no question but that UKOUG is the best Oracle uses
group in the world ... nor has there ever been much question
that the UKOUG conference is the best conference ... far
outshining Oracle's own OpenWorld from the standpoint of
serious DBAs and developers. This year UKOUG outdid itself and
this was, without a doubt, the best Oracle conference I have
ever attended.
Just to give you a sense of why I say that ... review this
list of those attending (and I mean no slight or disrespect to
anyone not listed below):
Alex Gorbachev
Bryn Llewellyn
Christian Antognini
Connor McDonald
David Kurtz
Debra Lilley
Graham Wood
Husnu Sensoy
James Morle
John King
Jonathan Lewis
Joze
Senegacnik
Julian Dyke
Larry Carpenter
Mogen Norgaard
Niall Litchfield
Pete Finnigan
Ronan Miles
Steven Feuerstein
Tanel Poder
Thomas Kyte
Tim Hall
Tuomas Pystynen
Wolfgang Breitling
Bryn Lewellyn
meets
ACE Director
Stanley who
travelled to
UKOUG
from Vietnam
And there wasn't a dull moment
with great parties, wonderful meet-the-speaker sessions, and
entertainment ranging from live opera with Matt Minter and
Lynn Boudreau of "Opera on the Run" to decent scotch
everywhere I went.
Wednesday,
November 25, 2009
Some may wish to juxtapose the following:
"If a nation expects to be ignorant and free, in a state of civilization, it expects what never was and never will be."
~Thomas Jefferson to Charles Yancey, 1816
"As Putin rears his head and comes into the air space of the United States of America, where
- where
do they go? It's Alaska. It's just right over the border."
~ Sarah Palin September 4, 2008
Others need only look up the definition of the word
"Rogue" in a
dictionary ... something Ms. Palin obviously has
not done or can not do for lack of working synapses.
The title of her book, it would seem, is both
accurate and precise.
Rogue used as a noun:
1: vagrant, tramp
2: a dishonest or worthless person : scoundrel
3: a mischievous person : scamp
4: a horse inclined to shirk or misbehave
5: an individual exhibiting a chance and usually
inferior biological variation
Rogue used as an intransitive verb:
to weed out inferior, diseased, or non-typical
individuals from a crop plant or a field Rogue used as an adjective:
1: resembling or suggesting a rogue elephant
especially in being isolated, aberrant, dangerous,
or uncontrollable
2: corrupt, dishonest <rogue cops>
3: of or being a nation whose leaders defy
international law or norms of international behavior
<rogue states>
Thursday,
November 12, 2009
Today I attended the second meeting of the Western Washington OUG. The new Oracle user group formed here
in the Puget Sound area. I presented one of my favorite topics: What Oracle includes in their software, for free, that they don't talk.
It was great to get back together with former students, my many local friends, and a tremendous and welcome surprise ...
my good friend Sridhar Avantsa (formerly with Pacific NW Bell and Amazon.com). A big thank you to everyone that made me feel so welcome.
A copy of my presentation slides can be found [Here] on the presentation's page. It will not, of course, include my
lecture notes ... I never have any ... nor the part that was live in SQL*Plus.
Again a very big thank you to everyone and best wishes with your new user group. I have no doubt that with leadership like
Jeremiah and Gary things will go well.
Monday, November 09, 2009
I am updating the page on the package SYS.STANDARD today and found the following comment
along with the DDL.
-- ROWID: this dreadful identifier is supposed to represent a datatype
-- outside of SQL and and a pseudo-column (function, to us) when inside
-- a sql statement. ADA data model doesn't allow for any
-- function X return X;
-- so we must special case this. Yuk. There's special-case code in ph2nre
-- which maps "rowid" to "rowid " if we're inside a SQL stmt.
function "ROWID " return ROWID; pragma builtin('ROWID ', 1, 209, 240);
-- this had better never be called.
Maybe it is just me but I can't think of a better way to get a DBA or Developer to try something than a comment like this.
Thursday, November 05, 2009
It is snowing in Helsinki but it
is amazingly comfortable here at OUGF where the finest Oracle
talent in Finland has gathered.
I was terribly distracted after that first sentence by Heli
and a call to Tiina and Bambi, and Rudolph and Winnie the Pooh
at the dinner table so I will
write more now that I am back in Seattle.
For now, though, here are some pictures from Saaga. The bear
balls were excellent. I'll leave the rest to your imagination.
Friday, October
16, 2009
Please remind me to write
something about OpenWorld here .... <g>
Friday, October 09, 2009
The ACE Briefing at the Oracle conference center has been extraordinarily valuable and were it that
what I am hearing was not covered by NDA I could write a small book: Perhaps if some of this is formally announced I will write about it. On
the other hand we are be briefed on middleware subjects of which I must confess there is less relevance so I opened a new SR and reported a bug
involving DBMS_REDEFINITION I will share with you.
The bug qualifies as an irritant and little more but it is an important irritant in that the result created using DBMS_REDEFINITION is different
from make the change in the traditional way. Here's the code that will allow you to see this yourself.
ALTER TABLE emp
ADD CONSTRAINT pk_emp
PRIMARY KEY (empno);
CREATE TABLE int_emp (
empno NUMBER(15),
name VARCHAR(100),
hiredate DATE);
-- the triggers
CREATE OR REPLACE TRIGGER bu1_hiredate
BEFORE UPDATE OF hiredate ON emp
FOR EACH ROW
BEGIN
:NEW.hiredate := SYSDATE;
END bu1_hiredate;
/
CREATE OR REPLACE TRIGGER bu2_hiredate
BEFORE UPDATE OF hiredate ON int_emp
FOR EACH ROW
BEGIN
:NEW.hiredate := SYSDATE+10;
END bu2_hiredate;
/
-- retrieve the trigger body source code
SELECT dbms_metadata.get_ddl('TRIGGER','BU1_HIREDATE') FROM dual;
DBMS_METADATA.GET_DDL('TRIGGER','BU1_HIREDATE')
-------------------------------------------------------------------
CREATE OR REPLACE TRIGGER "UWCLASS"."BU1_HIREDATE" BEFORE UPDATE OF hiredate ON EMP
FOR EACH ROW
BEGIN
:NEW.hiredate := SYSDATE+10;
END bu2_hiredate;
Monday, October
05, 2009
I
am pleased to share with friends and colleagues the
formation of the Western Washington Oracle Users Group.
Tonight the group got together at Oracle's offices for the
formation meeting, listened to an excellent presentation
by Oak Table Network member Jeremiah Wilton, and elected
an eight member Board of Directors.
It is going to be wonderful, again, having an active
Oracle community here in the Northwest. Thank you,
Jeremiah, for spearheading the efforts. I look forward to
presenting a paper worthy of this new group at the
November meeting.
Friday, September 18, 2009
I am getting ready for OpenWorld 2009 and, while going through the archives, came across an as
yet unposted picture taken at Palisade last year when some friends came to
visit. You never know what you may find when you start looking on your had drive.
Thursday, September
10, 2009
English language Library updates
are on schedule to be loaded by the end of the month. Then I go
back and work through some of the higher-level demos and start
bringing in the foreign language content. Tonight I found some
errors in the Oracle docs demos of CONNECT BY, fixed mine, and
emailed the issue to Oracle so they will get theirs fixed too.
Also enjoyed, again, having a President acting like one and
watching an opposition prove it is anything but a loyal
opposition. So in honor of Barack Obama's speech to the
Congress and the people of the United States we affirm that
....
s
s
s
x
We did not come here to fear the future
s
s
~ Barack Obama 9 September 2009
Monday, September 07, 2009
Time for another entry into the trust but verify chronicles.
I spent some time this weekend cleaning out old files and
found a usenet post from 1-May-2002 to
comp.databases.oracle.server posted by Pablo Sanchez and
referencing an item published to the Ixora News back in April
2002. The operative words in the posting were: "you're
incorrect Daniel" so I decided to see if I was, in fact, still
incorrect. Here's my quick test to determine if column
placement, at the beginning of a table affects performance.
I'll let you decide how important column placement is in
11.1.0.7.
CREATE TABLE test AS
SELECT * FROM dba_objects;
set timing on
DECLARE
CURSOR tcur IS
SELECT owner
FROM test;
x test.owner%TYPE;
BEGIN
FOR i IN 1 .. 100 LOOP
FOR trec IN tcur LOOP
x := trec.owner;
END LOOP;
END LOOP;
END;
/
DECLARE
CURSOR tcur IS
SELECT edition_name
FROM test;
x test.owner%TYPE;
BEGIN
FOR i IN 1 .. 100 LOOP
FOR trec IN tcur LOOP
x := trec.edition_name;
END LOOP;
END LOOP;
END;
/
First Column Access
Last Column Access
Elapsed: 00:00:16.37
Elapsed: 00:00:16.18
Elapsed: 00:00:16.29
Elapsed: 00:00:16.14
Elapsed: 00:00:16.31
Elapsed: 00:00:16.45
Elapsed: 00:00:16.36
Elapsed: 00:00:16.23
Wednesday, August 26, 2009
the dream
lives on
Monday, August 17, 2009
I'm begining
my preparations for OOW 2009 and in reminiscing about last
year it occured to me that I had not shared this video clip so I am doing it now.
If you are looking for any of these these attributes:
Zero Latency
Infinite Scalability
Zero Change
Zero Patching
Instant Operations
Subzero Defects
Lightspeed and Beyond
Anything, and everything, is possible in the Secret Virtual Office.
Sunday, August 16, 2009
Over the weekend I ran into another
one of my videos and thought I'd post the links: Here it is.
I also found the link to my previous Oracle Magazine interview and here it is as well.
Wednesday, August 12, 2009
It was an interesting day to be sure. Sometimes "interesting" is an arrow with flashing lights
that translates to "with extreme sarcasm." This wasn't one of those days.
We are getting closer to two very important events ... OpenWorld and the release of 11gR2. What can I say that is not
covered by NDA? Hmmm. Not much. <g>
I have another photo shoot coming up with Oracle Magazine which is enough information for you to figure out the rest and
I am also working on scheduling dates for another live video shoot at HQ.
Are you interested in an early preview of 11gR2 live in SQL*Plus? Well the class is done and ready to present
following OpenWorld so you shouldn't have to wait too much longer. Watch for information about the class on the home page.
Tuesday, August 11, 2009
Daw Aung San Suu Kyi
Out of sight ... not out of mind
Thursday, August 6, 2009
Today's chalenge:
Run the following SQL statment.
SELECT segment_name, tablespace_name
FROM dba_segments
WHERE segment_name IN ('OBJ$', 'COL$', 'IND$', 'TAB$');
Surprised by the result? Do
you know why it produce the result it does?
Wednesday, August 5, 2009
It seems our profession is
destined to be tortured by the promotion of ignorance and a
refusal to accept reality.
Tom Kyte spent years beating the drum of bind variables and
Tom and members of the Oak Table Network continue to promote
education over ignorance with respect to hit ratios, index
rebuilds, multiple block sizes, etc. What is it about the
NOLOGGING clause that people find so bloody hard to
understand?
The Oracle docs are perfectly clear ... perhaps no one
actually reads them.
Nothing about eliminating logging for normal DML statements. Nothing. Nada. Zip. Zilch.
Nyet. Get over it already. There are only two ways to avoid logging DML. The first is direct load operations. The second
is to never issue a COMMIT.
Saturday, August 1, 2009
The summer here in Seattle is hot
and so is Oracle. I am getting near the end of my 11gR2 beta
testing and while I still can't talk specifics I can talk
generalities. Conservatively speaking, something that does not
come naturally to me, if 11.1.0.6 could have been named
10.3.0.1 ... this is truly 11g. There are far fewer small
incremental changes and a number of very substantive
changes that are going to rock the database world in a way
that will put both Microsoft and IBM on notice that they are
not catching up anytime during the next two decades. IBM's
DB2/UDS is so marginalized it is hard to find it on anything
other than IBM's own hardware and operating systems and even
then IBM's hardware teams have stated quite openly that the
new hardware they are selling, especially z10's, are almost
all hosting Oracle. Microsoft, on the other hand, is about to
release the highly acclaimed Window 7 which is, it appears,
nothing more than Vista stripped of a lot of brain-dead
functionality that was tacked on in the hope of competing with
Apple's Mac. It seems, perhaps, it is just a dumbed down
version of Windows Server 2008 but we will know a lot more
when we are able to load the production release rather than
just late Betas.
Meanwhile, the new 11.2 library is just about done and I have
created a new "Oracle Database 11.2 New Features" class. I am
starting to set up dates to deliver the presentation, of
course live in SQL*Plus, following OpenWorld so if your
organization is interested give me a call or drop me an email.
Wednesday, July 15, 2009
I have been fielding a lot of
requests to put together a suite of videos of Oracle Database 11g
functionality. Videos that meet a number of criteria
including:
Professional production
quality (both video and audio)
Source code published here
at morganslibrary.org
11gR1 and R2 New Features
11gR1 and R2 Best Practices
Short ... high impact ...
targeted at specific functionality and indexed not just by
syntax but by how to address business and coding problems
Free
Well I didn't want to say anything without first discussing it
with our friends in Redwood Shores but can now announce that
we have their full support.
I will start filming soon and you will find the new videos
published on YouTube and elsewhere in Q4.
Thursday, June 25, 2009
Just back from a day-and-a-half at ODTUG Kaleidoscope in Monterey California where
I presented an updated paper on Oracle's Audit Vault technology and participated in an ACE Sundown
session with fellow ACEs Cary Millsap, Richard Foote, and Anjo Kolk.
Thank's ODTUG for a great conference, Thank's Oracle for the ACE Program and a special thanks to Justin, Vikki, and
Lillian for what they have created and how their efforts have benefited all of us in the international community.
Thanks Bryn for your time and a special thank you to Cary, Richard, and Anjo for the courage to be seen in public with me.
For those of you not fortunate enough to be able to join us in beautiful California please do so at OpenWorld in October and
watch for my new video ... shot at ODTUG with Willie Hardie's team. I will post a link to the video when it is release.
The YouTube Video, I am deeply embarrassed to say is undoctored.
Thursday, June 20, 2009
when will we ever learn?
Rest in peace Neda
Agha Soltan
Thursday, June 18, 2009
It's a 2 bug day!
My initial reaction when people "find a bug" in the Oracle database
is to wonder why they think their lack of reading the docs
is Oracle's bug ... well not today. One of them is a small
little thing involving timezones and DBMS_SCHEDULER and
not of tremendous consequence but the other, originally
found by John Awald in a complex environment I have
reproduced as a test case and submitted as an SR. Here it
is for your viewing pleasure.
Note: I have created this in several versions but, most
recently, not been able to replicate it with 11.1.0.7.
CREATE TABLE t
AS SELECT * FROM emp;
UPDATE t SET comm = 100 WHERE comm IS NULL;
UPDATE t SET comm = 100 WHERE comm = 0;
CREATE OR REPLACE VIEW v AS SELECT * FROM t;
CREATE OR REPLACE TRIGGER inoftr
INSTEAD OF INSERT ON v
FOR EACH ROW
BEGIN
dbms_output.put_line(:NEW.sal);
INSERT INTO t
(empno, sal)
VALUES
(:NEW.empno, :NEW.sal);
END;
/
SELECT empno, val * val FROM (
SELECT 2 empno, sal/SUM(comm) OVER (PARTITION BY
deptno) val
FROM t)
ORDER BY 1;
-- note the results from the
trigger created by the dbms_output.put_line
-- statement running the above SELECT statement as
an INSERT INTO
INSERT INTO v
(empno, sal)
SELECT empno, val * val FROM
(
SELECT 2 empno, sal/SUM(comm) OVER (PARTITION BY deptno) val
FROM t)
ORDER BY 1;
-- do the exact same thing but without the ORDER BY clause
INSERT INTO v
(empno, sal)
SELECT empno, val * val FROM
(
SELECT 2 empno, sal/SUM(comm) OVER (PARTITION BY deptno) val
FROM t);
Flying out in a few minutes for Seattle and will be home soon. Chile is a wonderful place and I will
expand these blog posts more when I get home and can unload my pics from my camera and collect pics from the others that attended.
Suffice it to say that while rebooking my return flight Delta Airlines again demonstrated their total committment to customer abuse. Next time, Delta, I fly LAN.
Note to the world ... is anybody paying attention? Click Here!
This is totally inexcusable. If DBAs were doctors they'd be guilty of malpractice.
Well it isn't so as I get ready to fly back to "civilization" I leave you with a
page of pics from Chile.
Tueday, April 14, 2009
I just had a tremendous thrill. Following Graham Wood's excellent database tuning
presentation. I co-led a presentation on the ACE Program with Lillian Buziak fresh from here debut as a
Polynesian dancer. Lillian did a great job representing Oracle and the program and we are all going
to see what we can do about getting Oracle to reimburse at least part of Lillian's expenses as she paid for the entire trip herself.
After our part we invited all of the ACEs in the room to come up to the stage and answer questions and we all had a
great time. I'm leaving now for La Serena in the Atacama and will write more soon.
Monday, April 13, 2009
CLOUG is a wonderful group and I predict good things for both Chilean users and the
greater Latin American community as they have clearly shown the way to doing it well.
The English language track, of which I was a part, had two excellent real-time translators that made it easy for
those of us presenting to do so seamlessly. I will get my slides, and some pictures, up shortly after I return home.
But, just to keep everyone from getting ahead of the story, here we are with Gloria Estefan.
Sunday, April 12, 2009
06:30am: Santiago Chile.
Finally! What a
wonderful site. Immigration is friendly and easy but be
sure to bring your hard-cash "blood money," $131 for US
Citizens. And they do want hard currency: Chilean Pesos
not accepted. I expected as much and brought three 50's.
Better to bring exact change. They really don't like
parting with the ones and fives they have but polite and
courteous all the while.
Did I mention my love affair with Delta Airlines in
Atlanta? Do you think my luggage, even though they swore
it had been rerouted and checked through arrived? Of
course not. Any sign of competence would ruin their
otherwise tarnished reputation.
Now at the Ritz Carlton. Beautiful hotel. Very nice room.
The bed is calling to me and I will submit just as soon as
I finish the Chivas.
13:28pm: My luggage arrived wrapped in plastic like
a mummified cocoon. First change of clothes in three days.
Thanks for very little Delta.
Saturday,
April 11, 2009
It is just before midnight and
Delta, not being able to get me on one of their direct flights
to Santiago has flown me to Guayaquil Ecuador where I
am changing airplanes to LAN. Delta, operating at its
nominal level of competence, gross incompetence, couldn't
give me a boarding pass for the LAN plane they were
putting me on so I had about one hour to get off the
plane, go through security, find LAN, get a boarding pass,
and get on the plane. The good news is that LAN is as
good, and as competent, as Delta is the exact opposite.
And just to make sure I trash Delta as much as they
deserve their planes are old and in derepair and their
food little more than edible.
Now let us contrast that with LAN. I am on what looks to
be a new clean plane. The seats are comfortable. The
dinner decent and they poured me enough Ballantine's
Scotch to sink a tumber, then offered more, and refused to
accept payment for it. Now this is an airline that will
get my repeat business. A few hours of precious sleep and
I arrive in Santiago around 5:00am. Thank you Delta. Wait
until you ask for a bailout from the Obama Administration.
I won't forget this when I send letters to my two Senators
(Murray and Cantwell) and my Congressman (Richert) and the
White House. Hope to see you in Chapter 7.
Friday,
April 10, 2009
It is 3:00am Saturday
morning and I am sitting in the Crowne Plaza Hotel in Atlanta Georgia. I am not supposed to be here.
I am supposed to be on an airplane to Santiago Chile to speak at CLOUG. But, it seems, a horrific thunderstorm,
beautiful from 7 miles up, and some tornados, put me on the ground hours after my plane departed. So what have I
learned from all this?
Never, ever, fly Delta Airlines to Atlanta. The "act of god" was beyond their
control. Their rudeness and general unhelpfulness were unforgiveable.
Never, ever, fly into Atlanta if you can possibly avoid it. Come to think of
it I have never had a good experience in this city. Too hot, too humid, and everyone seems to be suffering from
a lack of manners.
Never trust a Crowne Plaza confirmation number. They gave it to Oracle Travel
and then claimed it wasn't good (on the phone w/ Oracle Travel).
Tuesday, April 08, 2009
And, in answer to the inevitable
questions, yes I am really that tall, and really that young,
and that really is my hair, and no I didn't do the editing,
or focus the camera. And yes these are my words ... and I meant every single one of them.
Thank you Willie, it was an honor to be asked, and I look forward to doing another for you at ODTUG.
Tuesday, March 31, 2009
You should see the RAC cluster
he's got in the sail locker
Amazing. A lower latency cache
fusion interconnect and they'd have won.
Sunday, March 29, 2009
I have a nice little boat.
Larry Ellison has a slightly larger and faster boat.
Today I am on Larry's sled doing a podcast for Oracle. I will post the URL when I get it.
Friday,
March 26, 2009
I would like to thank Truls Bergersen, Frank Vikingstad, Alice Rossman, and the rest
of the fantastic group at Oracle User Group Norway in Oslo for what was not only one the best Oracle conferences I
have ever attended but a great oppotunity to explore a bit of their country as well.
It all started out at UKOUG in December when Mark Rittman of Rittman Mead asked me if I would like to be part of the
English language track at OUGN. He said two things that greatly interested me. First Norway as I had never been
there and I will pretty much go anywhere I've never been before. And, second, that the conference was being held on
a cruise ship. What could be better? A conference and a vacation. Both Mark and Julian Dyke couldn't make it and,
much to the Queen's embarrasement an American was there representing the United Kingdom.
Helen and I arrived in Oslo, via Copenhagen, on Sunday, March 8th, in a light snowstorm and after a good night's
sleep set out to explore Oslo. Had dinner with Truls and his lovely Brazilian wife, I mention her country of origin
only because she made the wickedest Caipirinas we've ever had. If you've never had
Caipirinhas before all you need is cane sugar, fresh limes, and a controlled substance the Brazilians call Cachaça. But back to my
story: Monday evening we went out to dinner at Lofoten
Restaurant in Oslo accompanied also by Danish ACE Director Sten Vesterli. I highly recommend the restaurant for fresh seafood. When you go to Norway, at least as an
American you are thinking sardines and herring. Reality strikes quickly. This is a city with world-class
restaurants and coffee so good it reminds me of Vienna.
Tuesday we left the hotel in the early morning and went to the cruise ship: The
Color Line'sM/V ColorMagic. I have a boat. Larry has a bigger boat. OUGN had a
ship. A ship complete with elevators that go up to the 15th floor.
The conference was held in the ColorMagic's conference facility with both English and Norwegian language tracks. We spent 22 hours making
our way to Kiel Germany, spent three or so hours there shopping, and then another 22 hours returning, at sunset under the Øresundsbroen,
the longest bridge in Europe.
Following the cruise Helen and I shopped, walked, and museumed, enjoying Norway's many National museums; the
Kon Tiki museum among them. The night before we left we had
what is, beyond question the best dinner of our lives and we've eaten reasonably well over the years. If you have
not yet visited Norway then, quite simply, you can justify the trip if you do nothing else other than have dinner at
Statholdergaarden, Oslo. Food better than this just does not exist on the planet earth.
At the Miracle 8th anniverary party ... Stanley lost a
food fight ... at RMOUG he took his revenge via this photo from the camera of Debra Lilley.
Next time Stanley ... next time.
Saturday, October 31, 2008
Richard Foote and I arrived
from Helsinki yesterday to attend Miracle A/S Denmark's
8th Anniversary Party. A huge thank you to Mogens for the
invitation and to both Mogens and Annette for their
hospitality. The party was, as Richard Foote has said:
SELECT 'Just a bit ' || RPAD('l', 70, 'o') || 'ng' FROM dual;
But the food was great, the supply of Miracle beer seemingly endless and the food
fight with Stanley a smashing victory. Though I did apologize to
Stanley later at RMOUG.
Thursday, October 29, 2008
Helsinki is a surprise in almost every respect and all of them positive.
The first surprise was that everyone I've met, everyone, speaks English like a native speaker. That means taxi drivers, people walking down
the street, people serving food in non-tourist restaurants. The city is small, clean, civilized, and by American standards devoid of motor
vehicles and parking places. It appears that everyone uses public transportation. The largest number of cars I've seen at an intersection
during the morning commute is six. Americans would be so envious if they didn't have their collective heads stuck in their BBQs while chanting
the mantra "we're number one."
But back to why I am here. Thanks to the kindness of Dian Thompson and Mary Dopart I was able to attend the EMEA President's Council in Munich
back in May where I had the pleasure of meeting OUGF President Heli Helskyaho. Heli invited me to present at
the Fall OUGF conference and I, with thanks to Justin and Victoria Lira of the ACE program was able to accept. Sometime later today I will
present my Oracle Gems session and earn my keep.
On the gastronomic side ... and any good trip must have one. I stayed at Helsinki Hotel Sokos and the hotel
restaurant had the best borscht I have ever had in my life. Good enough to be appetizer, salad, main course, and desert. Heli and her husband
took a small group of us out to dinner at a Lap restaurant: Saaga.
If you find yourself in Helsinki and have only a single chance to eat out ... this is the place.
The menu is a tream .. I had reindeer rump, Richard
ate the bear before it ate him, and we all drank our fill of Jaloviina brandy in our Kuksa mugs finishing the evening with the "cranberries
in an icy bowl." Thank you Heli for a real treat.
One night's decent sleep since OOW and I can talk a bit.
In some respects this was the best OOW and in some respects the worst. Certainly the new Exadata servers are
interesting and certainly there were a few good presentations out of the thousands: For example a few from
some of the Oakies and Aces and in the OTN Unconference. But for the most part the quality of the presentations has
not gotten better over the years so I spent most of my time meeting with old friends, making new friends, and
hanging out at Chevy's in Mogen Norgaard's virtual office: Click Here.
If you are paying attention and have a good memory you may be able to find the Oracle ClosedWorld conference in October of next year.
Also notable this year were the following (in no particular order):
Oracle Aces received new vests, black this time, and far more presentable outside of a Native American casino.
The Oracle Ace briefing contained the phrases "zero latency", "infinite scalability" and "zero
change." No one will ever accuse Oracle marketing of hyperbole. Mark Townsend's briefing was, as always, on target. Thank's Mark.
Justin Kestelyn and his new co-conspirator Lillian did a great job in every respect. Even without the new vests.
We missed Victoria and the chance to meet the new addition to her family: Congratulation!
The weather was near perfect
The on-site Beta test, before OOW, of the new Audit Vault was very impressive ... watch for the release soon
Watch for my picture, oh how I dread this, in the next issue of Oracle Magazine.
I am looking forward, a month from now to arriving in Helsinki Finland for
OUGF and then to
Miracle's 8th anniversary party in Ballerup.
Saturday, September 27, 2008
Just back from OOW 08 and almost found the
following in my in-basket from my friend and associate Caleb Small. Thought I'd share it with everyone:
Subject: RE: RAC 11g feedback
From: caleb@xxxxx.com
Date: Sat, September 27, 2008 10:23
To: KIRK.MCGOWAN@oraxxxx.com
Kirk:
Thanks for the explanation. Now that we know of this document, we can ask for updates on future assignments.
Are there any other internal documents you could suggest asking for regarding RAC best practices on 10/11g Linux?
We are often called upon to be experts and sometimes it does get frustrating finding all the information.
This assignment was a success; we just completed two 2-node production clusters on OEL5 and 11g, and added a
fifth node to an existing 10g cluster. It was a busy couple of weeks! I have some feedback/questions regarding
11g best practices:
DIRECT NFS CLIENT - we successfully configured DNFS
on both of the 11g clusters using NetApp storage. I think
our problem back in the lab relates to configuration of
our mount points and I will clarify next week. We did
discover one minor bug during RMAN backups. The following
message is repeated in the DB alert log. I suspect this is
related to bug #6477080 and we have an open SR on this
now. We are using oranfstab and it works fine for all
other files.
warning did not find dir
/u01/app/oracle/product/11.1.0/db_1/dbs
AUTOMATIC MEMORY MANAGEMENT -
setting max_memory and memory_target instead of max_sga,
sga_target and pga_target. We did use this feature, but it
appears that there is no way to lock the SGA into physical
memory. Using 10 Automatic Shared Memory Management
(max_sga) in combination with Linux 64 Huge Pages allows
us to lock the SGA in memory eliminating the risk of
swapping.
We also have an open SR regarding orphan memory pages
being left behind in /dev/shm when using AMM. I suspect it
may be related to one of the background maintenance jobs,
and am worried that it may be cumulative thus consuming
memory over time. I'll keep you posted.
ORACLE-VALIDATED.rpm - I am curious to hear you
opinion regarding using this utility for production
installations. We give it two thumbs down. It is awkward,
required access to Linux network which may not be possible
during initial install, especially when NIC drivers still
need to be built. It doesn’t really help that much, and
the kernel parameters it generates are not sutiable for
the production system.
DB CONSOLE - After getting over my initial
frustration learning new navigation, it seems to be much
better than 10g. It starts properly in a RAC environment,
it seems to be very responsive, and I like some of the new
features.
Thanks again for you help, Kirk.
Caleb.
On Fri, September 19, 2008 8:01 am,
KIRK.MCGOWAN@oraxxx.com wrote:
> Hey Caleb,
> That list of patches is constantly changing, and varies
by platform and version.
> Basically, it is a set of recommendations that are valid
at a point in time. Customers can get the list by filing
an SR and asking for the
> list.
>
> My team used to creat and maintain this list for the
customers we were engaged with. RAC Assurance team took
that list, and
> basically built an internal system to help maintain it,
and get my team out of the business of managing a
spreadsheet with a bunch
> of patches. That system is not externalized, and we do
not have a maillist where we email new recommendations. We
still are key
> contributors to the recommended patches, but
unfortunately, it is still painfully subjective as to
whether a patch should be on that
> list or not. In general, it is best not to patch unless
you really have no other alternative, but that depends on
the particular
> customer scenario.
>
> Long answer to your question.
> Short answer - you can get it for a customer at a point
in time - but need to file an SR asking for latest list of
recommended
> patches for Clusterware, rdbms/RAC, and ASM.
>
> K.
>
>
> -----Original Message-----
> From: caleb@xxxxx.com
> Sent: Friday, September 19, 2008 7:34 AM
> To: KIRK.MCGOWAN@oraxxx.com
> Subject: RAC Assurance Team documents?
>
>
> Hi Kirk:
>
> Hope you had a good long weekend on the boat, you sure
had the right weather this time! I'm busy back in
the mid-west again
> installing 11g RAC/App Server clusters. One of my
clients forwarded a document to me entitled:
>
> "RAC Assurance Team Recommended Patches"
>
> Creation date: April 2, 2008, Last Modified July 30,
2008
>
> Documents like this are invaluable to the work we do
both in the lab, and on client production systems. It is
possible for me get on
> the distribution list for such documents? Otherwise, I'm
not sure how to find them other than by hit-and-miss.
>
> Thanks
> Caleb
Monday, June 16, 2008
In a few minutes I will be
in the air on my way to ODTUG's Kaleidoscope 2008 in New
Orleans, Louisiana. My first time to an ODTUG event, my
first time to New Orleans, but no my first time into 90
degree weather with 90+% humidity: How can I possibly be
so lucky? Well at least I will be in good company as the
schedule includes Tom Kyte, Mogens Norgaard, Dr. Paul
Dorsey, Steven Feuerstein, Chris Ostrowski, Dan Norris,
Cary Millsap, Mark Rittman, John King, any many other
excellent presenters.
During Session 9 you
will find me in Napoleon B1 with Mogen Norgaard. My two
presentations will both be on Tuesday, June 17th. The
first is a Development DBA Experts Panel moderated by Dan
Norris of Piocon in Napoleon C3 (2:00pm) and the second in
Napoleon B1 with Oracle's Audit Vault Product Manager
Tammy Bednar (3:15pm).
I hope to meet many of
you there.
Saturday, May 2, 2008
With a smile on my face, joy in
my heart, and some really good Allagash beer I am heading to
Europe for two weeks. This morning I fly to Chicago, change
planes to Munich Germany, and then changes planes yet again
to Copenhagen where I will be delivering good beer and good
wishes to Mogens Norgaard on his very special day. The jet
lag should be palpable.
Depending on how I, or
perhaps we, feel during the following couple of days I hope
to further explore a concept discussed at RMOUG this year
with Mogens and Jonathan Lewis about the possible creation
of Miracle University.
After a few days of enjoying
Denmark: Helen and I will head for Prague to spend a week
totally out of touch with the world of ones and zeros and
then, on or about the 13th of the month, head to Munich
Germany for the EMEA President's Conference which Oracle has
asked me to attend.
I will be back at the U, and
in my office on the 17th hopefully with some Miracle beer.
Depending on how customs treats me I may just have to drink
it all before I board the plane. Could happen.
I'm heading for Collab '08 in Denver ... looking forware to seeing everyone there!
Monday, March 10, 2008
Is it time to move 180 degrees
around the circle away from the Microsoft PC revolution,
which has really become rather revolting when we look at
the consequences, and return to the time when mainframes
ruled the world of IT? I'm certainly not getting nostalgic
for punch cards and Fortran and certainly not having any
fond remembrances of COBOL.
But I think a good case can
be made for doing so. The average mainframe has a mean
time to failure of 90 years. Essentially no issues with
respect to viruses and other malware, no blue screen of
death, and a cost that dictates that those who have access
have a demonstrated ability to do better than just throw
untested code over the cubicle wall. And, in spite of the
jokes about RAC on mainframes, RAC on mainframes makes
tremendous sense.
Further, in a world where
the requirements for security, auditing, compliance,
governance, and stability are paramount, and considering
an environment that essentially eliminates the need for
virtualization complexity or concerns about performance
... why not Oracle on zLinux for the backbone?
So with that in mind I am
posting a link to Barry Perkins 2004 presentation on
Oracle products on zLinux for the community and I am
working on getting Barry to come to Seattle for the April
PSOUG meeting. [Click
Here]. Also of interest is the International
zSeries Oracle SIG [Click
Here].
Monday, December 24, 2007
Found a new gem hidden in the
newly released 11gR1 inside th DBMS_RESOURCE_MANAGER
built-in package. If you are not familiar with this
resource management definitely go to the Oracle docs and
the Library and check out the capabilities and the demos.
But here's what I just found: A new procedure named
CALIBRATE_IO.
-- Input arguments:
-- num_physical_disks - Approx, number of physical disk drives
-- max_latency - Maximum tolerable latency in milliseconds for
-- database-block-sized IO requests (max value 100)
--
-- Output arguments:
-- max_iops - Maximum number of I/O requests per second that can be
-- sustained. The I/O requests are randomly-distributed,
-- database-block-sized reads.
-- max_mbps - Maximum throughput of I/O that can be sustained,
-- expressed in megabytes per second. The I/O requests are
-- randomly-distributed, 1 megabyte reads.
-- actual_latency - Average latency of database-block-sized I/O
-- requests at "max_iops" rate, expressed in milliseconds.
SELECT pname, pval1
FROM aux_stats$
WHERE sname = 'SYSSTATS_MAIN';
So if you want to know what your i/o subsystem is actually doing ... here where you can find out.
Sunday, December 16, 2007
Welcome to the planet where
dinosaurs still roam freely.
No I don't mean birds that
descended from Cretaceous and Jurassic beasts. I am
referring to, in most cases, well meaning DBAs who learned
Oracle back in the version 5-7 days and were told
something by Oracle, or some book author, that was not
true then and just isn't true today either. Yes I am
referring to the mistaken belief that there is any diagnostic value in BCHR (Buffer
Cache Hit Ratio).
So in an attempt to add my
voice to the chorus of sanity on this I present two links
you should follow to two of the most widely respected
people in our profession. They are Jonathan
Lewis and Richard
Foote.
Friday, December 07, 2007
I just found out about a great new Oracle website with presentations
from OOW 2007. It has links to both Demo Ground and Partner Collateral.
Click Here
Wednesday, November 28, 2007
Hans Forbrich
at
OOW 2007
with
Arup Nanda at OOW2007
Anjo
Kolk & Mogens Norgaard at OOW2007
with Anjo, Mogens Norgaard at OOW2007
Pictures by Jared Still at the ACE Dinner
Sunday, November 11, 2007
I am sitting in the airport
in Seattle right now awaiting my flight to SFO for the
biggest Oracle party of 2007.
If you are trying to find me here's where you might want to look:
Saturday ... my hotel room recovering from a week of preparation.
Sunday ..... Moscone West
Monday ..... Moscone South
Tuesday .... all over the place
Wednesday .. Larry Carpenter's presentations on Data Guard
Thursday ... Moscone West and South
Friday ..... in my room recovering from five days at OOW
We have to be crazy to be doing this ... the same insanity over and over again ... I can't wait ... see you there.
Tuesday,
November 6, 2007
A few days ago I was trying to
answer a student question and stumbled on something I
couldn't answer. Here is my email exchange with Julian
Dyke of juliandyke.com in the UK.
I've stumbled trying to answer a student question and wonder if you
might know the answer. A thorough search of the docs has proven that
either the answer is not documented or I am not good at entering
search criteria.
I create a deferrable referential constraint such as this:
ALTER TABLE person
ADD CONSTRAINT fk_person_state_zip
FOREIGN KEY (per_state, per_zip)
REFERENCES state_zip (state, zip_code)
INITIALLY DEFERRED DEFERRABLE;
Look in the data dictionary
SELECT constraint_name, deferrable, deferred
FROM user_constraints
WHERE table_name = 'PERSON';
which is technically correct because my SET CONSTRAINTS only affects
my session and not others. But the question is where in Oracle is it
storing the fact that within my specific session I have altered the
behaviour of that constraint?
Thanks.
The
answer is that SET CONSTRAINTS ALL IMMEDIATE is
implemented as a transaction callback which is a
type of state object.
When you initially create a transaction you
allocate a ktxcb structure from a segmented array.
You can see these structures in X$KTCXB and once
the transaction has started in V$TRANSACTION. If
you do a state object dump (SYSTEMSTATE level 10),
you can see the transactions currently in progress
for each session (and process).
When you issue SET CONSTRAINTS ALL IMMEDIATE, a
transaction callback state object is created. This
is chained from the transaction object
Both transaction (ktcxb) and transaction callback
(ktccts?) are segmented arrays with names
"transaction" and "txncallback"
respectively. The amount of memory allocated to
these structures can be seen in V$SGASTAT (again
with names "transaction" and "txncallback"
respectively).
I can't find any X$ tables which externalise the
transaction callback structure. However, it is
easy to find in the system state dump.
Following is an extract from a level 10
SYSTEMSTATE dump which following the execution of
SET CONSTRAINTS ALL IMMEDIATE in a transaction:
I don't know what happens if with SET CONSTRAINTS
ALL DEFERRED yet.
Any use?
I will post if we learn more.
Thursday, October 25, 2007
Oracle has released both the Windows and 64bit Linux versions of
11g.
To download a personal copy of 11g go to Oracle's download page at OTN. [Click
Here]
Monday, October 22, 2007
Last week I was in Salt Lake City
at the UTOUG conference and this week, Thursday, I will be
at NoCOUG in Pleasanton, California doing what I enjoy ...
Oracle 11g in SQL*Plus without a safety net.
If you can make it there
you will see:
Oracle's new table
compression in action
Virtual table columns
(with constraints and indexes and the Result Cache)
Compound table triggers
and table triggers ordered using the FOLLOWS clause
11g's new Native
compilation
Some brand new PLW
warnings
and if time permits
Transaction Backout
Monday, October 15, 2007
I am at 37,000 ft flying to
Salt Lake City to provide two break-out sessions to the
Utah Oracle Users Group's Fall 2007 conference. I am
planning to present both sessions in 11.1.0.6: One for
DBAs ... one for developers.
While preparing for the conference I decided to clean up a
demo schema I use and found that among the flotsam and
jetsam was a flashback archive. I dropped the
table's undo from being archived:
ALTER TABLE servers NO FLASHBACK ARCHIVE;
then the archive:
DROP FLASHBACK ARCHIVE uw_archive;
and then saw something I had not seen before.
Two of the three tables created by Flashback Archive were
dropped but the third was not. Attempting to drop it
produced an ORA-55622 error which is not recognized by
metalink.
ERROR at line 1:
ORA-55622: DML, ALTER and CREATE UNIQUE INDEX operations are not allowed on table
"UWCLASS"."SYS_FBA_HIST_70439"
Well I proved "no DML" was true as I could not
update or delete rows. I also proved the ALTER statement
true in a few minutes. I could, however, rename it and did
so renaming it to ZZYZX to make tracing easier.
RENAME TABLE SYS_FBA_HIST_70439
TO ZZYZX;
All attempts to drop it after rename also failed
SQL> drop table zzyzx;
drop table zzyzx
*
ERROR at line 1:
ORA-55622: DML, ALTER and CREATE UNIQUE INDEX operations are not allowed on table "UWCLASS"."ZZYZX"
so it was time to get into the data dictionary.
I found nothing about flashback archives in dcore.bsq.
Nothing obvious anywhere else even after tracking down the
source code of every view I could find related to
Flashback Archive. Then I tried a 10053 level 12 trace:
Also a dead end.
Finally I decided there had to be something, even if
undocumented, that told Oracle this table was special so I
tore into obj$ and tab$ looking for anything that was
unique about this one table, owned by UWCLASS, and finally
found it:
SELECT object_id, object_name
FROM dba_objects
WHERE object_type = 'TABLE'
AND owner = 'UWCLASS';
So should a table created to support Flashback Archive
fail to drop when the table is converted to NO FLASHBACK
ARCHIVE the table can dropped ONLY, it appears, by doing
something remarkably dangers ... updating TAB$.
Here's what I did: The
object returned was the one I was trying to drop so I did
the dastardly deed:
UPDATE tab$
SET property = 536870912
WHERE obj# = 72403;
COMMIT;
The table then dropped
normally and no damage was done.
But please keep in mind
that any direct alteration of data dictionary tables in a
production database is not just irresponsible but will
render the database unsupported by Oracle. This should
only be done after opening an SR at metalink and under the
direction of Oracle support.
Wednesday, October 3, 2007
Well I've been kicking around
in 11g a lot more and discovering that it is even richer than
originally thought.
When
Beta testing I focus on a combination of testing some new
functionality while, at the same time, watching for old bugs
to make sure they have been fixed or not reintroduced and also
that legacy functionality has not been broken. The early 11g
Betas contained a bit of both, the last Beta was solid and the
production release looks to be ready for implementation in
development and testing environments immediately.
Needless to say there was a
lot of new functionality I never got a chance to touch
during the Beta and one of those was something I want to
talk about today ... the new INTERVAL PARTITIONING. It is
absolutely brilliant and here's what it does.
In 10g and before one had
to manually, or via a scheduled job, create new partitions
as days and weeks passed and we were approaching the point
where everything would have ended up in the LESS THAN
MAXVALUE partition. Interval partitions do away with that.
Oracle now dynamically creates new partitions when an
insert contains values beyond a point in time defined by
the existing interval partitions.
Here is a demo I've put on
the Partitions page that demonstrates this new capability:
Lets start by creating a
range partitioned table:
CREATE TABLE interval_part (
person_id NUMBER(5) NOT NULL,
first_name VARCHAR2(30),
last_name VARCHAR2(30))
PARTITION BY RANGE (person_id)
INTERVAL (100) STORE IN
(uwdata) (
PARTITION p1 VALUES LESS
THAN (101))
TABLESPACE uwclass;
Note that I didn't
create the usual LESS THAN MAXVALUE partition as it is
unnecessary.
First lets take a look at the segment Oracle created.
desc interval_part
SELECT table_name, tablespace_name, partitioned
FROM user_tables;
col high_value format a20
SELECT partition_name, tablespace_name, high_value
FROM user_tab_partitions;
And lets insert our
first row:
INSERT INTO interval_part
(person_id, first_name, last_name)
VALUES
(100, 'Dan', 'Morgan');
We can look and see that
it went into the first partition as it is the only one
that exists.
SELECT partition_name,
tablespace_name, high_value
FROM user_tab_partitions;
Let's now put in a second row only this time with a
person_id that exceeds the VALUES LESS THAN clause of the
existing partition.
INSERT INTO interval_part
(person_id, first_name, last_name)
VALUES
(101, 'Barack', 'Obama');
We look at the
partitions and note that Oracle has automatically created
a new partition to hold the record and set an appropriate
high_value.
SELECT partition_name, tablespace_name, high_value
FROM user_tab_partitions;
We now put in a
record that greatly exceeds the high value for both
of our existing partitions.
INSERT INTO interval_part
(person_id, first_name, last_name)
VALUES
(567, 'Anne', 'Sweet');
And again we look at
the partitions created and see that Oracle has done the
right thing and made our lives just a little bit less
complicated.
SELECT partition_name, tablespace_name, high_value
FROM user_tab_partitions;
Tuesday, September 25, 2007
Have you ever found yourself
reading a metalink note and found yourself staring at
something like this?
ORA-44310 - maximum number of services exceeded.
or
metalink note 194706.1 with ORA-26671 - maximum number of
STREAMS processes exceeded.
Well thanks to Mark Townsend we now have an answer to two
of these questions:
The limit is 115 services per database. Exceed that number
and you are toast.
On another note have you ever done this:
desc gv$parameter
and wondered about a parameter 4000 bytes long?
Well if so fear not. Another limit just discovered is that
the maximum number of bytes in a parameter is actually
around, sorry I couldn't get it more accurately 270 bytes.
So don't write STREAMS or DATA GUARD params that refer to
long paths or this will bite you too.
But merge these two items together and the number of
services changes substantially. You can have up to 115
services but, combined into a single init.ora parameter:
"service_names=xxx,yyy,zzz..."
They can not exceed 270 bytes. So in order ot have 115
services you can not use more than two bytes to name a
service.
If you find doc examples like this please forward them to me at my personal email address and I will try to get the docs changed to define them.
Sunday,
September 23, 2007
For what must be the tenth,
perhaps the hundredth, time this year I have responded to
the very same question. So I've decided to highlight it
here as it seems to be one that many have problems with.
This issue is how to query on a values list constructed in
the form:
x := '10,30';
SELECT *
FROM t
WHERE column IN x;
When Oracle sees this it
tries, quite naturally to match against the literal string
'10,30' as there is no instruction telling it that x is
intended to represent a comma delimited list.
The solution is to provide
Oracle with that missing information so that it knows what
you are thinking. And the way to tell it is to
demonstrated below.
Log on as the user scott
and try these on your database.
Does Not Work
Works
set
serveroutput on
DECLARE
i PLS_INTEGER;
InStr VARCHAR2(20) := '10,30';
BEGIN
SELECT COUNT(*)
INTO i
FROM emp
WHERE deptno IN (InStr);
dbms_output.put_line(i);
END;
/
CREATE OR REPLACE TYPE InStrTab IS TABLE OF VARCHAR2(20);
/
set serveroutput on
DECLARE
i PLS_INTEGER;
x InStrTab := InStrTab('10','30');
BEGIN
SELECT COUNT(*)
INTO i
FROM emp
WHERE deptno IN (
SELECT column_value
FROM TABLE(x));
dbms_output.put_line(i);
END;
/
The TABLE operator tells
Oracle that you wish for the comma delimited values in the
variable x to be treated as though they are a table. A
full demo of this capability can be found in the library
on the CONDITIONS page.
Tuesday,
September 04, 2007
Just got back into the office
after a week and a half in Alberta, Canada. Three
unforgettable days at
Chateau Lake Louise. Well somebody had to do it.
As a follow-up on PL/Scope,
and no I have not yet heard back from Oracle, the
recommendation in Oracle's docs is:
Each DATATYPE is a base type declared in package
STANDARD. In order to collect and view these identifiers,
package STANDARD must be compiled with
PLSCOPE_SETTINGS='IDENTIFIERS:ALL'.
If you do this you will
totally corrupt an 11g database. I am working on getting
an answer from Oracle about this but, for now, I can not
warn everyone strongly enough to ignore this
recommendation unless you are quite prepared to perform a
complete reinstallation.
Tuesday, August 21, 2007
It is a slow day in the lab so
not too much has happened. I'd be out painting the boat
except that this summer Seattle is truly living up to its
reputation and, yet again, it is raining. So here's what I
would have posted somewhere if I had had somewhere to post
it.
Oracle 11g contains a new
capability named PL/Scope that does for PL/SQL
what Cscope does for C. I have put together
a page in the Library showing its use based on a very
simple stored procedure that you can try. One the caution
I would make is that there is a SQL statement I have bold
faced because it does not work. I have sent an email to
Oracle asking someone to name the unnamed view that
contains the data type information (other than
all_arguments) but I haven't had a response yet.
So if you've downloaded 11g
and are starting to play with it take a look at PL/Scope.
It could be a very nice way to learn about code developed
by someone else before jumping into it and making an even
worse mess. <g>
Monday, August 20, 2007
In the Usenet group comp.databases.oracle.server, this morning, I found a post asking:
| In my
app, I've been using "OPEN some_cursor FOR
some_dyn_sql ;"
|
| Now I'm facing a limitation problem.... I have
to generate a VERY LARGE
| dynamic SQL. (bigger than "VARCHAR2(32767)")
|
| And "OPEN...FOR..." does not seem to
take CLOB.
|
| What other options do I have ?
... and I realized that while
Oracle has been sprinting along adding and extending the
database with new functionality ... a lot of legacy
functionality is essentially invisible.
The answer to the poster's
question is, in fact, functionality that has been in the
DBMS_SQL package since it was introduced in version 7.3.4.
While there is no question
but that Native Dynamic SQL was a substantial improvement
over the more complex syntax of DBMS_SQL it has never
duplicated, until 11g changed the rules, the raw power of
DBMS_SQL. So if you don't know the answer to the above
question ... go to the DBMS_SQL page in the library and
scroll down until you find "Executing CLOBS Demo
Tables." Create the two tables then create the "EXECUTE_PLSQL_BLOCK
procedure. After that you can run my demo data anonymous
block containing insert statements or put together yourown CLOB, however large, and test it out.
Morgan's Library Page Footer
This is site maintained by Dan Morgan. Last Updated:
