Feed aggregator

keeping-eye-on-sessions

Ayyappa Yelburgi - Sun, 2007-12-23 04:01
--Long operationsselect sid,username usern,serial#,opname,target,sofar,totalwork tot_work,units,time_remaining remaning,elapsed_seconds elapsed,last_update_time last_timefrom v&session_longops --where sid=73order by last_update_time desc;--All active sessionsselect * from v&session where status='ACTIVE'--and sid in (37,43)order by sid;--Find session's sid or process id by it's sid or process ayyudbahttp://www.blogger.com/profile/00046200352601718598noreply@blogger.com6

keeping-eye-on-sessions

Ayyu's Blog - Sun, 2007-12-23 04:01
Categories: DBA Blogs

moving-tablespartitions-9i-onwards

Ayyappa Yelburgi - Sun, 2007-12-23 04:00
-1 Move table from one tablespace to another-- (check for unusable indexes after that).alter table $OWNER.$TABLE_NAME move tablespace $NEW_TS_NAME;--2 Move table partition from one tablespace to another-- (check for unusable indexes and partitoned indexes after that).alter table $OWNER.$TABLE_NAMEmove partition $TAB_PART_NAME tablespace $NEW_TS_NAME;--3 Move table subpartition from one tablespaceayyudbahttp://www.blogger.com/profile/00046200352601718598noreply@blogger.com20

moving-tablespartitions-9i-onwards

Ayyu's Blog - Sun, 2007-12-23 04:00
Categories: DBA Blogs

Solaris 10 on Dell PowerEdge 1950 and 2950

Hampus Linden - Thu, 2007-12-20 11:58
I probably get an email every couple a weeks about this one, not sure why.
E-mails regarding something in Solaris not working on Dell PowerEdge 9th gen servers.

For any device driver related issues with Solaris on x86, the first resource to check if you run in to trouble is the Sun HCL, the 'hardware compatability list'.
Solaris 10 works fine on most Dell boxes, but some need NIC or HBA drivers.

For a Dell 2950 that tells us that Solaris works, from release 11/06 upwards, it also tells us to download the ethernet driver here and the MegaRAID SAS driver here.

NB, the Sun HCL does not mention the new 'III' series 9-gen server yet. I would guess they are working on as Sun and Dell recently partenered to provide better Solaris support. The PERC6 card may need some new drivers.

Agile PLM 9.2.2.1 - Part I

Aviad Elbaz - Thu, 2007-12-20 02:27
In May this year, Oracle has acquired Agile, a leading provider of Product Lifecycle Management (PLM) software solutions.

In this post I will explain about the various components of Agile PLM system, and the following posts will be dedicated to Agile PLM installation.

The components of Agile PLM application are:
1) Agile Database
2) Agile Application Server
3) Agile File Manager
4) Agile Web Proxies
These components should be installed in the above order.

Agile Database
Used to hold all Agile data.
It is recommended to install the database and application server on separated machines.

Agile Application Server
The Agile Application Server can be run on Oracle Application Server 10g or BEA WebLogic Server.
The Application Server is the main component of the Agile system, all services and business logic reside on it.
The Agile application deployed on the Application Server.
All users (Java/Web clients) connect to the Application Server in 2 possible ways:
- Directly
- Indirectly, via Wev proxy Server.

Agile File Manager
The File Manager manages all Agile files in the file system (File Vault).
The File Manager runs on Tomcat.

Agile Web Proxies Server
The Agile web client allows connection to both internal and external users.
Agile web client uses Microsoft IIS (Internet Information Services) or Apache web server.
The Agile web client connects to an Agile Application Server the same way like other Agile Application Server clients.



Next post I will show, step by step, how to install Agile PLM 9.2.21 on two nodes configuration.

Aviad
Categories: APPS Blogs

Need to Kill j000 process to shutdown the database after upgrade from 8.1.7.4 to 9.2.0.8.

Madhu Thatamsetty - Wed, 2007-12-19 22:20
This is a common problem after the upgrade. One important point to remember is to disable all the schedules for dbms jobs before kicking off the upgrade and enable the schedule after the uprade is over. Alternatively job_queue_processes parameter can be set to '0'.You see following symptoms in the alert log.-- Clip --Active call for process 21244 user 'oracle' program 'oracle@hostname (J000)'Madhu Sudhanhttp://www.blogger.com/profile/11947987602520523332noreply@blogger.com1

Oracle EBS R12 beta exam are you ready

Fadi Hasweh - Wed, 2007-12-19 03:18
I received the following email from oracle certification program
“Oracle University is pleased to announce the availability of the following new Oracle E-Business Suite (R12) certification exam in beta version:
Oracle EBS R12: Install, Patch and Maintain Applications [1Z0-238]
This new exam is required as part of earning the new Oracle EBS R12 Applications Database Administrator Certified Professional certification.
By participating in this beta exam, you have the opportunity to provide the Oracle Certification program with feedback about exam content which is integral to the process of exam development. We depend on the contributions of experienced professionals and developers as we continually improve exam content and maintain the value of Oracle certifications.

Beta exam period runs:
January 03 – April 04, 2008
These beta periods are subject to change, please check the exam pages for the most up-to-date information


Good luck with your exams
fadi

Regular Expressions: REGEXP_COUNT (New SQL Snippets Tutorial)

Joe Fuda - Sun, 2007-12-16 14:00
Introduced in Oracle 11g the REGEXP_COUNT function greatly simplifies counting the number of times a pattern appears inside a string. The following examples demonstrate how to use REGEXP_COUNT with some simple patterns. For versions prior to 11g alternative logic that provides similar functionality is also presented.
...

Mac OSX, Bootcamp and a Missing Hash Key

Duncan Mein - Sun, 2007-12-16 12:58
I bought a Mac Book Pro about 8 months ago as my main business Laptop. Coupled with a copy of Parallels, I built my Oracle Server (Database and Apps Server) on a Windows VM environment which left Mac OSX free for Development using SQL Developer, Dreamweaver etc

A couple of weeks back I decided to upgrade to OSX Leopard and install windows natively using Bootcamp to utilise both core's on the CPU and all 3 Gig of memory. All well and good until i tried to use the Hask key (Alt + 3 in OSX) when working on some APEX templates. After much research on the web, it appears that most OSX key mappings are installed when using bootcamp but in order to print the hash (#) symbol, you must use Ctrl + Alt + 3

Simple when you know how

Autopatch Timing Report. Useful information that helps you to plan your Production Downtime.

Madhu Thatamsetty - Fri, 2007-12-14 23:24
I was all set to write a shell script that will parse the patch log file and arrive at top time consuming sqls that will help me know the time taken by the sqls for the upgrades. After constantly watching the Maintenance Pack ( 3480000 ) session for 30+ hours. The following lines of the autopatch session caught my attention.-- Clip --sqlplus -s APPS/***** @/xxxx/xxxx/xxxxappl/ad/11.5.0/admin/sql/Madhu Sudhanhttp://www.blogger.com/profile/11947987602520523332noreply@blogger.com2

My Experience with Oracle Service Contracts (OKS) Rule Migration Metalink Note:265048.1

Madhu Thatamsetty - Fri, 2007-12-14 18:28
Does this Note:265048.1 sound familiar ? At least I can't forget this Note#. I can tell this Note# even if someone wakes me up in the middle of the night. This is because I have spent so much of time and iterations doing Service Contracts Rule Migration before 11.5.10.2 upgrade to cut down the time the Maintenace Pack (Patch# 3480000) takes. Coming to the technical aspect of this Note#, Madhu Sudhanhttp://www.blogger.com/profile/11947987602520523332noreply@blogger.com0

Tahiti Rising

Mary Ann Davidson - Fri, 2007-12-14 10:55

As is obvious from my previous blog entries, I have a great appreciation for nā mea 'apau Hawai'i - all things Hawai'i Since Polynesian culture is not, in general, something a lot of people know anything about, I enjoy opportunities to talk about it to friends. A few weeks ago, when I was at the Army-Navy* game with an "infidel" friend (I mean, a friend who had gone to West Point instead of the Naval Academy), we talked about our relative experiences in the military, specifically, the differences between finding your way on land and finding your way on the sea. I had a chance to talk about Polynesia in the context of navigation. (I skipped over the fact that I was singularly lousy at seamanship, one of many reasons the Navy in its infinite wisdom did not make me a navigator.)

 

The traditional (Western) history of the world ascribes amazing feats of navigation to Ferdinand Magellan, who was the first to circumnavigate the globe. Personally, I'm not all that impressed, since Magellan had a compass and a quadrant at his disposal, and the rest was testosterone and favorable winds. OK, that probably isn't fair. What I mean to say is that even if Magellan deserves some accolades as a mariner, he had mechanical help (the aforementioned compass and quadrant), and he certainly wasn't the first to make long voyages in the Pacific.

 

The true master mariners, in my opinion, were the Polynesians, who traveled thousands of miles in the Pacific, between New Zealand, Tahiti, Hawai'i and the Marquesas, even Easter Island (Rapa Nui). They were able to do so using traditional Polynesian wayfinding, which considers the wind, the waters, the stars, even the birds. No mechanical assistance, in other words, which makes their navigational skills truly extraordinary. (Lewis and Clark carved a way through the wilderness; today, people can drive an RV from St. Louis, Missouri to Astoria, Oregon, with all the creature comforts of home, the interstate highway network and a GPS system. It's hardly comparable, is it?) The Polynesians navigated the Pacific long before Magellan, because they were an island people. To expand their territory, they had to become voyagers.

 

By the 1970s, the art of Polynesian wayfinding had all but died out. One of the last practitioners was a master navigator from Micronesia named Mau Piailug. He came to Hawai'i to teach Nainoa Thompson, a Hawaiian whose dream it was to sail a double-hulled voyaging canoe called the Hōkūle'a from Hawai'i to Tahiti using Polynesian wayfinding.

 

The story is told that one night, Mau stood under a star-filled sky and asked Nainoa to point to Tahiti. Nainoa pointed to the star (hōkū) under which Tahiti lay. Then Mau asked him, "Do you see Tahiti?" Such a strange question; how could Nainoa possibly see an island 2700 miles away? Finally, after much thought, Nainoa said, "I see the island in my mind," to which Mau replied that Nainoa must never forget what he was seeing or he would be lost.

 

The day came when the Hōkūle'a set sail with Nainoa as the navigator. Nainoa could see Tahiti in his mind, until the day came when Hōkūle'a was approaching the equator, and with it a great cloud of rain. Nainoa feared that he would not be able to steer the ship without the benefit of the stars to guide him. Great fear and restlessness over came him, until he remembered Mau telling him that he needed to look inside, that he would be lost if he tried to see with his eyes. Suddenly, Nainoa had a feeling of deep relaxation, and he felt the moon over his shoulder, despite the fact it could not be seen in the cloudy sky. He was able to navigate by a sense of knowing where Tahiti was. Days later, he turned towards the horizon and saw Tahiti rising, as he had hoped and dreamed.

 

Because of the voyages of Hōkūle'a and other Polynesian voyaging canoes over the last 30 years, the Hawaiian people have come to have great pride -  a resurgence of pride - in their culture. In fact, Hawaiian culture has had a sort of renaissance in all areas - language, music, dance. At one point, there were less than 50 native speakers of Hawaiian under the age of 18. Now, there are multiple immersion schools in the Hawaiian language and many young people speak the language. The story of Hōkūle'a does not end with a single voyage. One man's willingness to see Tahiti rising and keep after it affected an entire culture.

 

Something else I find interesting about this story is that it challenges our assumptions that in the steady march of progress,  new technology is inevitably more sophisticated and better than knowledge built up over hundreds or thousands of years. Maybe it isn't -  maybe we are just lazier than we used to be and nobody wants to put in the time to learn anything of substance anymore.

 

I recently read a book about the impending extinction of so many of the world's languages. Loss of a language also means losing the knowledge encompassed in that language. A Siberian language (Tuvan) can use a single word to describe all the attributes of a reindeer (age, health, disposition) that are useful if you want to ride him. In English, the linguistic encapsulation of "male reindeer, 5 years old, excellent health and easy to ride" takes 11 times as many words as in the Siberian language.

 

There is no such thing as a living language with no native speakers. When a language dies - because there are no longer native speakers - the culture dies and most of the knowledge embedded in the culture dies with it. Only today, I read that pharmaceutical companies may be reaching the upper limit of what they can do with purely chemical treatments of disease: they are shifting their investments to biologically-based treatments. Of course, many indigenous peoples - many of whose languages are facing extinction - use plant and animal-based medicines, the use and utility of which is incorporated into their language. If the language dies, so does that knowledge.

 

In a strange way, we may become collectively more ignorant when we increase our reliance on technology and forget (or don't learn) what our fathers and mothers knew, however simple. How many of us have been in a checkout line when the clerk could not do basic addition and subtraction (to make change) unless the cash register was able to do it for him? I confess, I once had a perfectly lovely financial calculator that could amortize a bond for you. Unfortunately, I was able to get the right answer in some of my MBA classes while in many respects not grokking the basics of finance (one of many reasons I don't work in finance). 

 

How many people can't actually read a map any more? (I've had some spectacular failures with mapquest.com and just lived through one with a GPS system.) I cringe when I hear about people going hiking or skiing in the backcountry without basic equipment and survival skills (including a compass and knowledge of how to use it). Too many assume that they can use a cell phone to call for help if they get lost. (Not if there is no cel tower in range or your battery dies.) 

 

We've become so removed from nature that many of us have lost respect for it. A couple of years ago, some (allegedly expert) climbers decided to ascend Mount Hood in advance of a storm (I am told by my climbing friends in Idaho that you should never, ever climb thinking you will "beat" the weather. If there is a better definition of "hubris" than that I don't know what it is). The climbers died. The tragedy was that their deaths were avoidable if only they had respected the weather report. If you live in a wilderness area, you learn to respect it. You don't "tame" it anymore than you "tame" a grizzly bear.

 

A concomitant of busy lives and "more technology" is that too many of us spend too much time with gadgets and gizmos and not enough time experiencing the natural world. Dare I say, "the real world." A particularly pathetic example of this was a wealthy real estate developer who installed a state-of-the-art golf simulator in his basement. He bragged on camera (one of those home improvement shows on HGTV) that he had "played"  all the best courses in the world - from his basement. The reality is that he is so busy with work that he does not have time to actually play golf. Real golf. Playing virtual golf and saying you've played St. Andrews is like reading the Cliff Notes and saying you read Dickens. It isn't the same - not even close. It's not about your golf score, it is about being there, because the game isn't going to show you the elk that peek out on the course, the geese that fly overhead, or the particularly beautiful late afternoon lighting on the water.

 

I am sure a lot of people will read the story of Nainoa Thompson and conclude that there are lots of faster and easier ways to get to Tahiti than recreating a Polynesian voyaging canoe and learning the art of wayfaring (so, they think, "why bother?"). Just like you can play a really great round of golf in your basement, without bugs, divots, wind or other annoyances. It's only when you look at a dream of Tahiti rising that the real value of the trip becomes apparent.

 

Several years ago, I saw the Hōkūle'a entering San Francisco Bay, and it gave me chicken skin, as the Hawaiians say. I can say this after years of seeing some of the great warships of history, and great warships of today's modern Navy: the feeling I got when I saw the Hōkūle'a was unlike anything, because I knew what it meant to the Hawaiian people. Respect. Pride in their culture. Now, people who will never sail a double-hulled voyaging canoe can nonetheless see Tahiti in their mind, because one man could. As Antoine de Ste. Exupery said, "It is only with the heart that one can see rightly; what is essential is invisible to the eye."

 

My sister and I are writing a book together, an IT murder mystery. This is the main reason I have not been blogging as much -  my writing energies are going into the book. We have gone from chatting about "we ought to write a book together" to an outline, drafts, character sketches, and entire chapters. My sister has become the book navigator - she sees where she wants it to go, and plots the course in her mind. She is like Nainoa Thompson, being disciplined about spending time learning her craft, and always, she sees Tahiti rising. A couple of months ago, she said she wanted us to have a draft finished by the end of the year, and now, we have.

 

I can write, but I have always lacked discipline and a vision greater than "I ought to write more." I was like the guy with the golf simulator: I dabbled at something I loved but did not make time for the real deal. I needed a navigator, and my sister stepped up to the helm. I did not envision the book, or know that the moon was over my shoulder: my sister did that. And because of her, now I can see Tahiti on the horizon. For once, I will have finished a writing project I mused about and thought about but never showed the discipline to accomplish. And like the Hōkūle'a, who knows what other adventures will come from that first dream of Tahiti? It seems fitting that the culture of Polynesia figures prominently in our book. Nainoa Thompson inspires me, too, because of all the dreams of Tahiti rising in my mind, one of which is to some day live in Hawai'i, where I already feel at home.

 

As we come to the end of another year, it is a good time for all of us to imagine what is possible if you have a star to steer by, or one you hold in your heart that you follow to a place of your imagining. Two thousand years ago, wise men followed a star to Judea, asking the question, "Where is the one who has been born king of the Jews? We saw his star in the east and have come to worship him." (Matthew 2: 2)  They were not the first - and they have not been the last - to follow a star and find more illumination than their hearts could hold.

 

* Navy won, 38-3, the 6th straight win over Army. And, the Navy quarterback is Hawaiian, how cool is that?

 

For more information:

 

Book of the week: When Languages Die: The Extinction of the World's Languages and the Erosion of Human Knowledge by K. David Harrison

 

In all the hoopla about endangered species, people forget that a substantial percentage of the world's languages are in danger of becoming extinct. Lose a language, lose a culture, and all the priceless knowledge that goes with it.  You will never look at linguists the same after reading this book.

 

http://www.amazon.com/When-Languages-Die-Extinction-Knowledge/dp/0195181921

 

Nathan Aweau (nui kona mana!) has a new CD out (Kāne’ohe) which is absolutely kamaha'o (amazing). The very first song is about seafaring voyagers (E Pi'i Mai Ke Kai), and the Hōkūle’a is in the lyric, too. Read about it or order it at:

 

http://www.nathanaweau.org/kaneohe.htm

 

About the Polynesian Voyaging Society

 

http://en.wikipedia.org/wiki/Polynesian_Voyaging_Society

 

About Chicken Soup from the Soul of Hawai'i (the source of the story about Nainoa Thompson):

 

http://www.amazon.com/Chicken-Soup-Soul-Hawaii-Paradise/dp/0757300618

 

About wayfinding:

 

http://www.pbs.org/wayfinders/wayfinding3.html

http://www.celestialnavigation.net/wayfinding.html

 

More on Nainoa Thompson:

 

http://www.ifa.hawaii.edu/tops/nainoa.html

 

Herb Kawaianui Kane's amazing art of a navigator and a voyaging canoe:

 

http://hawaiiantrading.com/cgi-bin/mivavm?/store/merchant.mvc+Screen=PROD&Product_Code=hk-22&Category_Code=herb-kane&Store_Code=hieyes

http://hawaiiantrading.com/cgi-bin/mivavm?/store/merchant.mvc+Screen=PROD&Product_Code=hk-199&Category_Code=herb-kane&Store_Code=hieyes

 

And Herb Kawaianui Kane's imagining of the discovery of Hawai'i:

 

http://hawaiiantrading.com/cgi-bin/mivavm?/store/merchant.mvc+Screen=PROD&Product_Code=hk-256&Category_Code=canoe&Store_Code=hieyes

 

About the Tuvan language:

 

http://en.wikipedia.org/wiki/Tuvan_language

Integrating the Google Chart API in APEX

Jornica - Thu, 2007-12-13 16:13

A few days ago, Google released the Google Chart API. By calling a special crafted URL Google returns a image containing a chart derived from the parameters included in the URL. For instance

http://chart.apis.google.com/chart?cht=p3&chd=t:3,5,6,0&chs=200x100&chl=10|20|30|40
&chco=ff0000,00ff00,0000ff,000000

will give the image shown left. At the right the Flash equivalent generated by APEX is shown.


To include this dynamic Google chart based on a query on a page, define a PL/SQL dynamic content region with the following PL/SQL source:

DECLARE
l_url VARCHAR2(2000) := 'http://chart.apis.google.com/chart?cht=p3&chs=200x100&chco=ff0000,00ff00,0000ff,000000';
l_chd VARCHAR2(2000);
l_chl VARCHAR2(2000);
BEGIN
FOR rec IN
(SELECT d.deptno LABEL,
COUNT(e.empno) VALUE
FROM dept d,
emp e
WHERE d.deptno = e.deptno(+)
GROUP BY d.deptno)
LOOP
l_chd := l_chd || ',' || rec.VALUE;
l_chl := l_chl || '|' || rec.LABEL;
END LOOP;
l_url := l_url || 'chd=t:' || SUBSTR(l_chd, 2) || '&chl=' || SUBSTR(l_chl, 2);
htp.p('<img src="' || l_url || '"/>');
END;

If you do not want to use the built-in Flash chart functionality, the Google Chart API is a simple alternative.

Revisting My Previous Post "DBUA FAILS WITH unable to extend rollback segment ODMA_RBS01"

Madhu Thatamsetty - Wed, 2007-12-12 04:15
I thought i'll add in more details to the post "DBUA FAILS WITH unable to extend rollback segment ODMA_RBS01".Just changing the entry " InNewTablespace name="ODMA_RBS" size="50" unit="MB"" in $ORACLE_HOME/assistants/dbma/mep.cfg is not sufficient. There are couple of more tags that you need to play with in mep.cfg. Refer to "CreateRollbackSegmentsODMA_RBS01" revert="true" " section in the Madhu Sudhanhttp://www.blogger.com/profile/11947987602520523332noreply@blogger.com0

Agent Update Packs

Mark Vakoc - Tue, 2007-12-11 20:49
If you navigate to the 'Managed Software' page in the quick links you'll see a listing of all the tools releases and JDBC drivers that have been uploaded to the management console. These packages, obtained from the Update Center, are used to stage tools releases prior to installing or upgrading them.

You may notice an entry named 'Agent Install Pack 7' of type 'Management Agent Installer Bundle'.

What is this? Well, in a previous post I mentioned that we have two separate downloads for Server Manager for each tools release.
  • A large (~1 GB) Installer, and
  • A much smaller (< 50 MB) console update
The large installer is used the first time you install SM. This includes the J2EE container used to run the web application, the SM management console application, and the corresponding agent install pack. The SM update contains only updated SM management console (and corresponding management agent code). Installing a new SM application and later applying an update is functionally equivalent to initially installing the later release using the installer. Our goal here was to keep updates small and easy to apply.

There's actually a third possible download from the update center: a new agent update pack. This contains items that are rarely going to change with each tools release update. Specifically it contains the platform specific managed home installer for all the platforms we support and the platform specific Oracle Configuration Manager agent that is installed along with the managed home.

These items are rarely updated because
  1. The managed home agent installer obtains the actual agent codebase from the management console, thus installing whatever is current with the console application
  2. The Oracle Configuration Manager agent will automatically update with the latest version provided by Oracle, therefore not requiring OCM updates with Server Manager
So why the potential separate download for the management agent pack? In the event we need to update either the managed home installer or OCM agent that is initially installed we will create a new management agent pack that can be downloaded from the Update Center. Why would we do this? It won't be typical, but an example in which we might need to update the pack is if we added support for a new platform that isn't currently available or need to deploy code fixes to the managed home installer. As of this posting we have no foreseeable need to update the agent installer pack.

How do I know if I need a new agent installer pack? Should we need to update the agent installer pack we will create and add the package to the Update Center. Navigating to the 'Management Agents' page will tell you if a new agent installer pack is required. For example, assume:
  1. We updated the agent install pack with a particular tools release of Server Manager
  2. You have applied that SM update to an existing SM installation
  3. You want to install a new managed home agent
In this case you will be instructed to download the corresponding agent installer pack and add it to the SM software repository.

What if I delete the existing agent installer pack? This software component is treated just like any other software component; that is you can delete it either through SM or directly from the file system. If you have deleted the agent installer pack and navigate to the 'Management Agents' screen you will be instructed to obtain the installer pack from the Update Center.

Since we have not had the need to rev the installer pack it is not currently available for download from the Update Center. So, in the case you accidentally deleted the existing installer package you may obtain it only by performing a new SM installation and copying/uploading the file from the new install to the existing installation. After doing so you may uninstall the new SM console just installed.

The file will be named something along the lines of agentPackage7.jar and will be located in the directory INSTALL_BASE/components, where INSTALL_BASE refers to the installation path of the SM application.

Outerjoins in Oracle

Oracle Optimizer Team - Tue, 2007-12-11 15:00
Since release 6, Oracle has supported a restricted form of left outerjoin, which uses Oracle-specific syntax. In 9i, Oracle introduced support for ANSI SQL 92/99 syntax for inner joins and various types of outerjoin. The Oracle syntax for left outerjoin and that of ANSI SQL 92/99 are not equivalent, as the latter is more expressive.

There appears to be some confusion about equivalence between ANSI outer join and Oracle outer join syntaxes. The following examples explain the equivalences and inequivalences of these two syntaxes.

Oracle-Specific Syntax

Consider query A, which expresses a left outerjoin in the Oracle syntax. Here T1 is the left table whose non-joining rows will be retained and all non-joining rows of T2 will be null appended.


A.
SELECT T1.d, T2.c
FROM T1, T2
WHERE T1.x = T2.x (+);


ANSI Left Outerjoin

In the ANSI outer join syntax, query A can be expressed as query B.


B.
SELECT T1.d, T2.c
FROM T1 LEFT OUTER JOIN T2
ON (T1.x = T2.x);


Equivalence

Consider the following queries. In the Oracle semantics, the presence of (+) on the filter predicate (e.g., T2.y (+) > 5 in query C) indicates that this filter must be applied to the table T2 before the outer join takes place.


C.
SELECT T1.d, T2.c
FROM T1, T2
WHERE T1.x = T2.x (+) and T2.y (+) > 5;


The ANSI left outer join query D is equivalent to C. Applying the filter on the right table in the left outer join is the same as combining the filter with the join condition.


D.
SELECT T1.d, T2.c
FROM T1 LEFT OUTER JOIN T2
ON (T1.x = T2.x and T2.y > 5);


Similarly, the presence of (+) on the filter predicate, T2.y (+) IS NULL, in query E indicates that this filter must be applied to the table T2 before the outer join takes place.


E.
SELECT T1.d, T2.c
FROM T1, T2
WHERE T1.x = T2.x (+) and T2.y (+) IS NULL;


The ANSI left outer join query F is equivalent to E.


F.
SELECT T1.d, T2.c
FROM T1 LEFT OUTER JOIN T2
ON (T1.x = T2.x and T2.y IS NULL);


Consider query G. Oracle will apply the filter, T2.y IS NULL, in query G after the outer join has taken place. G will return only those rows of T2 that failed to join with T1 or those whose T2.y values happen to be null.


G.
SELECT T1.d, T2.c
FROM T1, T2
WHERE T1.x = T2.x (+) and T2.y IS NULL;


The ANSI left outer join query H is equivalent to G, as the WHERE clause in H is applied after the left outer join is performed based on the condition specified in the ON clause.


H.
SELECT T1.d, T2.c
FROM T1 LEFT OUTER JOIN T2
ON (T1.x = T2.x)
WHERE T2.y IS NULL;


Consider query I, where the filter on the left table is applied before or after the outer join takes place.


I.
SELECT T1.d, T2.c
FROM T1, T2
WHERE T1.x = T2.x (+) and T1.Z > 4;


The ANSI left outer join query J is equivalent to query I.


J.
SELECT T1.d, T2.c
FROM T1 LEFT OUTER JOIN T2
ON (T1.x = T2.x)
WHERE T1.Z > 4;


Lateral Views

In Oracle, ANSI left and right outerjoins are internally expressed in terms of left outerjoined lateral views. In many cases, a left outerjoined lateral view can be merged and the ANSI left (or right) outerjoin can be expressed entirely in terms of Oracle native left outerjoin operator. (A lateral view is an inline view that contains correlation referring to other tables that precede it in the FROM clause.)

Consider the ANSI left outer join query K, which is first represented internally as L.


K.
SELECT T1.d, T2.c
FROM T1 LEFT OUTER JOIN T2
ON (T1.x = T2.x and T2.k = 5);

L.
SELECT T1.d, LV.c
FROM T1,
LATERAL (SELECT T2.C
FROM T2
WHERE T1.x = T2.x and T2.k = 5)(+) LV;


The lateral view in query L is merged to yield query M.


M.
SELECT T1.d, T2.c
FROM T1, T2
WHERE T1.x = T2.x (+) and T2.k (+)= 5;


Consider query N, which expresses a left outerjoin in the ANSI join syntax. Currently query N cannot be expressed using the Oracle native left outer join operator.


N.
SELECT T1.m, T2.n
FROM T1 LEFT OUTER JOIN T2
ON (T1.h = 11 and T1.y = T2.y)
WHERE T1.q > 3;


The query N is converted into query O with a left outer-joined lateral view. The lateral view in O cannot be merged, since the filter on the left table specified in the ON clause must be part of the left outerjoin condition.


O.
SELECT T1.m, LV.n
FROM T1,
LATERAL(SELECT T2.n
FROM T2
WHERE T1.h = 11 and T1.y = T2.y)(+) LV
WHERE T1.q > 3;


Consider query P with a disjunction in the outer join condition. Currently N cannot be expressed using the Oracle native left outer join operator.


P.
SELECT T1.A, T2.B
FROM T1 LEFT OUTER JOIN T2
ON (T1.x = T2.x OR T1.Z = T2.Z);


The query P will be converted into Q with a left outer-joined lateral view containing the disjunctive join condition.


Q.
SELECT T1.A, LV.B
FROM T1,
LATERAL (SELECT T2.B
FROM T2
WHERE T1.x = T2.x OR T1.Z = T2.Z) (+) LV;


ANSI Full Outerjoin

Before Oracle 11gR1 all ANSI full outerjoins were converted into a UNION ALL query with two branches, where one branch contained a left outerjoined lateral view and the other branch contained a NOT EXISTS subquery. A native support for hash full outerjoin was introduced in 11gR1 to overcome this problem. When the native full outerjoin, cannot be used, Oracle reverts to the pre-11gR1 strategy.

Consider query R, which specifies an ANSI full outerjoin.


R.
SELECT T1.c, T2.d
FROM T1 FULL OUTER JOIN T2
ON T1.x = T2.y;


Before 11gR1, Oracle would internally convert query R into S.


S.
SELECT T1.c, T2.d
FROM T1, T2
WHERE T1.x = T2.y (+)
UNION ALL
SELECT NULL, T2.d
FROM T2
WHERE NOT EXISTS
(SELECT 1 FROM T1 WHERE T1.x = T2.y);


With the native support of hash full outerjoin, R will be simply expressed as query T, where the view, VFOJ, is considered unmergeable.


T.
SELECT VFOJ.c, VFOJ.d
FROM (SELECT T1.c, T2.d
FROM T1, T2
WHERE T1.x F=F T2.y) VFOJ;


Conversion of Outerjoin into Inner Join

Consider query U. Here the filter predicate on the outer-joined table T2 does not contain the outerjoin operator (+); thus it will be applied after the left outerjoin has taken place. This will result in the elimination of all null appended rows of T2. Hence, Oracle converts the outer join into an inner join.


U.
SELECT T1.d, T2.c
FROM T1, T2
WHERE T1.x = T2.x (+) and T2.y > 5;


The ANSI left outer join query V is equivalent to query U, as the WHERE clause in V is applied after the left outer join is performed based on the condition specified in the ON clause.


V.
SELECT T1.d, T2.c
FROM T1 LEFT OUTER JOIN T2
ON (T1.x = T2.x)
WHERE T2.y > 5;


Oracle converts the queries U and V into query W with an inner join.


W.
SELECT T1.d, T2.c
FROM T1, T2
WHERE T1.x = T2.x and T2.y > 5;



Q&A

Q1: I do not understand the queries N and O. What is the difference between
a filter appearing in the ON Clause or a filter appearing in the WHERE
clause?

A1: Consider two tables T11 and T22.

T11:
A | B
1 | 2
2 | 3
3 | 5

T22:
X | Y
7 | 2
8 | 4
9 | 4

The following ANSI left outer join query N’ involving
the tables T11 and T22 will return three rows, since
the filter, which always fails, is part of the join
condition. Although this join condition, which
comprises both the predicates in the ON clause,
always evaluates to FALSE, all the rows of the left
table T11 are retained in the result.

N’.
SELECT *
FROM T11 LEFT OUTER JOIN T22
ON (T11.A > 9 and T11.B = T22.Y);

A B X Y
------ ---------- ---------- ---------
1 2
2 3
3 5

However, if the filter, T11.A > 9, is moved to the WHERE clause,
the query will return zero rows.


Q2: Is the outer to inner join conversion a new feature?

A2: No. This feature has been avaliable since Release 7.


Q3: Has native full outer join been made available in
versions prior to 11gR1?

A3: Yes. It is available in 10.2.0.3 and 10.2.0.4, but not by
default.

Outerjoins in Oracle

Inside the Oracle Optimizer - Tue, 2007-12-11 15:00
Since release 6, Oracle has supported a restricted form of left outerjoin, which uses Oracle-specific syntax. In 9i, Oracle introduced support for ANSI SQL 92/99 syntax for inner joins and various types of outerjoin. The Oracle syntax for left outerjoin and that of ANSI SQL 92/99 are not equivalent, as the latter is more expressive.

There appears to be some confusion about equivalence between ANSI outer join and Oracle outer join syntaxes. The following examples explain the equivalences and inequivalences of these two syntaxes.

Oracle-Specific Syntax

Consider query A, which expresses a left outerjoin in the Oracle syntax. Here T1 is the left table whose non-joining rows will be retained and all non-joining rows of T2 will be null appended.


A.
SELECT T1.d, T2.c
FROM T1, T2
WHERE T1.x = T2.x (+);


ANSI Left Outerjoin

In the ANSI outer join syntax, query A can be expressed as query B.


B.
SELECT T1.d, T2.c
FROM T1 LEFT OUTER JOIN T2
ON (T1.x = T2.x);


Equivalence

Consider the following queries. In the Oracle semantics, the presence of (+) on the filter predicate (e.g., T2.y (+) > 5 in query C) indicates that this filter must be applied to the table T2 before the outer join takes place.


C.
SELECT T1.d, T2.c
FROM T1, T2
WHERE T1.x = T2.x (+) and T2.y (+) > 5;


The ANSI left outer join query D is equivalent to C. Applying the filter on the right table in the left outer join is the same as combining the filter with the join condition.


D.
SELECT T1.d, T2.c
FROM T1 LEFT OUTER JOIN T2
ON (T1.x = T2.x and T2.y > 5);


Similarly, the presence of (+) on the filter predicate, T2.y (+) IS NULL, in query E indicates that this filter must be applied to the table T2 before the outer join takes place.


E.
SELECT T1.d, T2.c
FROM T1, T2
WHERE T1.x = T2.x (+) and T2.y (+) IS NULL;


The ANSI left outer join query F is equivalent to E.


F.
SELECT T1.d, T2.c
FROM T1 LEFT OUTER JOIN T2
ON (T1.x = T2.x and T2.y IS NULL);


Consider query G. Oracle will apply the filter, T2.y IS NULL, in query G after the outer join has taken place. G will return only those rows of T2 that failed to join with T1 or those whose T2.y values happen to be null.


G.
SELECT T1.d, T2.c
FROM T1, T2
WHERE T1.x = T2.x (+) and T2.y IS NULL;


The ANSI left outer join query H is equivalent to G, as the WHERE clause in H is applied after the left outer join is performed based on the condition specified in the ON clause.


H.
SELECT T1.d, T2.c
FROM T1 LEFT OUTER JOIN T2
ON (T1.x = T2.x)
WHERE T2.y IS NULL;


Consider query I, where the filter on the left table is applied before or after the outer join takes place.


I.
SELECT T1.d, T2.c
FROM T1, T2
WHERE T1.x = T2.x (+) and T1.Z > 4;


The ANSI left outer join query J is equivalent to query I.


J.
SELECT T1.d, T2.c
FROM T1 LEFT OUTER JOIN T2
ON (T1.x = T2.x)
WHERE T1.Z > 4;


Lateral Views

In Oracle, ANSI left and right outerjoins are internally expressed in terms of left outerjoined lateral views. In many cases, a left outerjoined lateral view can be merged and the ANSI left (or right) outerjoin can be expressed entirely in terms of Oracle native left outerjoin operator. (A lateral view is an inline view that contains correlation referring to other tables that precede it in the FROM clause.)

Consider the ANSI left outer join query K, which is first represented internally as L.


K.
SELECT T1.d, T2.c
FROM T1 LEFT OUTER JOIN T2
ON (T1.x = T2.x and T2.k = 5);

L.
SELECT T1.d, LV.c
FROM T1,
LATERAL (SELECT T2.C
FROM T2
WHERE T1.x = T2.x and T2.k = 5)(+) LV;


The lateral view in query L is merged to yield query M.


M.
SELECT T1.d, T2.c
FROM T1, T2
WHERE T1.x = T2.x (+) and T2.k (+)= 5;


Consider query N, which expresses a left outerjoin in the ANSI join syntax. Currently query N cannot be expressed using the Oracle native left outer join operator.


N.
SELECT T1.m, T2.n
FROM T1 LEFT OUTER JOIN T2
ON (T1.h = 11 and T1.y = T2.y)
WHERE T1.q > 3;


The query N is converted into query O with a left outer-joined lateral view. The lateral view in O cannot be merged, since the filter on the left table specified in the ON clause must be part of the left outerjoin condition.


O.
SELECT T1.m, LV.n
FROM T1,
LATERAL(SELECT T2.n
FROM T2
WHERE T1.h = 11 and T1.y = T2.y)(+) LV
WHERE T1.q > 3;


Consider query P with a disjunction in the outer join condition. Currently N cannot be expressed using the Oracle native left outer join operator.


P.
SELECT T1.A, T2.B
FROM T1 LEFT OUTER JOIN T2
ON (T1.x = T2.x OR T1.Z = T2.Z);


The query P will be converted into Q with a left outer-joined lateral view containing the disjunctive join condition.


Q.
SELECT T1.A, LV.B
FROM T1,
LATERAL (SELECT T2.B
FROM T2
WHERE T1.x = T2.x OR T1.Z = T2.Z) (+) LV;


ANSI Full Outerjoin

Before Oracle 11gR1 all ANSI full outerjoins were converted into a UNION ALL query with two branches, where one branch contained a left outerjoined lateral view and the other branch contained a NOT EXISTS subquery. A native support for hash full outerjoin was introduced in 11gR1 to overcome this problem. When the native full outerjoin, cannot be used, Oracle reverts to the pre-11gR1 strategy.

Consider query R, which specifies an ANSI full outerjoin.


R.
SELECT T1.c, T2.d
FROM T1 FULL OUTER JOIN T2
ON T1.x = T2.y;


Before 11gR1, Oracle would internally convert query R into S.


S.
SELECT T1.c, T2.d
FROM T1, T2
WHERE T1.x = T2.y (+)
UNION ALL
SELECT NULL, T2.d
FROM T2
WHERE NOT EXISTS
(SELECT 1 FROM T1 WHERE T1.x = T2.y);


With the native support of hash full outerjoin, R will be simply expressed as query T, where the view, VFOJ, is considered unmergeable.


T.
SELECT VFOJ.c, VFOJ.d
FROM (SELECT T1.c, T2.d
FROM T1, T2
WHERE T1.x F=F T2.y) VFOJ;


Conversion of Outerjoin into Inner Join

Consider query U. Here the filter predicate on the outer-joined table T2 does not contain the outerjoin operator (+); thus it will be applied after the left outerjoin has taken place. This will result in the elimination of all null appended rows of T2. Hence, Oracle converts the outer join into an inner join.


U.
SELECT T1.d, T2.c
FROM T1, T2
WHERE T1.x = T2.x (+) and T2.y > 5;


The ANSI left outer join query V is equivalent to query U, as the WHERE clause in V is applied after the left outer join is performed based on the condition specified in the ON clause.


V.
SELECT T1.d, T2.c
FROM T1 LEFT OUTER JOIN T2
ON (T1.x = T2.x)
WHERE T2.y > 5;


Oracle converts the queries U and V into query W with an inner join.


W.
SELECT T1.d, T2.c
FROM T1, T2
WHERE T1.x = T2.x and T2.y > 5;



Q&A

Q1: I do not understand the queries N and O. What is the difference between
a filter appearing in the ON Clause or a filter appearing in the WHERE
clause?

A1: Consider two tables T11 and T22.

T11:
A | B
1 | 2
2 | 3
3 | 5

T22:
X | Y
7 | 2
8 | 4
9 | 4

The following ANSI left outer join query N’ involving
the tables T11 and T22 will return three rows, since
the filter, which always fails, is part of the join
condition. Although this join condition, which
comprises both the predicates in the ON clause,
always evaluates to FALSE, all the rows of the left
table T11 are retained in the result.

N’.
SELECT *
FROM T11 LEFT OUTER JOIN T22
ON (T11.A > 9 and T11.B = T22.Y);

A B X Y
------ ---------- ---------- ---------
1 2
2 3
3 5

However, if the filter, T11.A > 9, is moved to the WHERE clause,
the query will return zero rows.


Q2: Is the outer to inner join conversion a new feature?

A2: No. This feature has been avaliable since Release 7.


Q3: Has native full outer join been made available in
versions prior to 11gR1?

A3: Yes. It is available in 10.2.0.3 and 10.2.0.4, but not by
default.
Categories: DBA Blogs, Development

UKOUG - Day 3

Anthony Rayner - Mon, 2007-12-10 11:02
So with my presentation firmly behind me, and after having the best night's sleep in weeks, I headed into day 3 looking forward to catching John Scott talk about 'Debugging APEX Applications', Dimitri Gielis present 'Integration of BI (XML) Publisher and APEX (Oracle Application Express)' and also sit on the panel for my first APEX roundtable discussion group.

John's presetation was very good, he presented well and showed numerous techniques for debugging your APEX applications. Specifically of interest was the use of the DBMS_APPLICATION_INFO package which can be used to monitor progress of long running queries / reports and display this progress information back to the user. He also mentioned that as an APEX developer he believes that you have the responsibility to have a good understanding of the database, so as to be able to benefit from the features and build better applications, a point which I totally agree with. If you ever get a chance to watch John present, then do it as I'm positive there will be something in there that you can benefit from. Nice job John!

So then it was time to get ready for the APEX roundtable. I was pretty nervous and left John's presentation quarter of an hour before the end so as to go up to the room and get myself ready. The session was chaired by Jeremy Duggan (Chair of the Modelling, Analysis and Design SIG), and I was on the panel with Dimitri Gielis, Peter Lorenzen and unofficially, but answering a lot of the questions John Scott. Around 20 people turned up, which was reasonable considering it was lunch time! Some of the topics that came up were:

  • APEX / Forms / ADF
  • Choosing the right tool for the right project. This is a massive topic with lots of arguments for and against. For a good overview of the main factors, take a look at Duncan Mills' article, 'The Right Tool For the Right Job'. Also, if you are in the Netherlands, Dimitri will be discussing this with Lucas Jellema on Monday 17th December.
  • Validations
  • Specifically around the current issues with validating tabular form data. Dimitri mentioned Patrick Wolf's ApexLib framework which greatly improves tabular form handling, including out-of-the-box client and server validations for mandatory, date and numeric fields. Alternatively, if you don't want to or cannot use Patrick's framework, you can code your validations referencing the global fxx arrays as mentioned in this rather old, but still useful article. Finally, the Statement of Direction implies that version 4.0 will give us, 'Improved tabular forms, including support for validations...', so looking forward to that.
  • URL Tampering
  • And how this can be combated using the APEX built-in 'Session State Protection', see Dimitri's article or the official documentation.
  • Page Comments
  • Specifically, can these be mandated if this is a development standard? No, not currently but as John suggested this could be easily monitored through the APEX views. The issue also came up that the comments are right down the bottom of the page, which can lead to oversight. This question came up in the forums a couple of weeks ago and Patrick Wolf added a feature to the APEX Builder Plugin which addresses this issue by highlighting the 'Comments' link in yellow if there is a comment. Thank you Patrick!
  • Source Control
  • This keeps on cropping up at the moment. Basically if you are using a source control system such as CVS or SubVersion, what is the best strategy for managing the APEX application files? This can either be done at page level or application level and can be automated through use of the supplied command line tools 'ApexExport' and 'ApexExportSplitter'. APEX development team, how do you manage this?
  • JavaScript
  • A few JavaScript related questions came up. There are lots of libraries available in the APEX release that you can make use as a developer, the functions are unofficially documented by Carl Backstrom and according to the statement of direction will be officially documented and supported from 3.1, which is good news. Also what happens if a user has JavaScript turned off? Well, APEX can be used to build applications that meet accessibility requirements, but it requires some workarounds. See Sergio's article Application Express and Accessibility if you are interested in the steps involved. Peter also added that all client-side validations should be backup up with server-side / APEX validations as best practice.
  • Team Development
  • What is best practice for working on large projects with many developers? We mentioned an excellent article written by Ben Wootton, entitled, 'Best Practices for Oracle Application Express Collaborative Development' as an excellent reference point. This details use of page locks, page groups, use of Application Reports for monitoring, commenting changes, use of PL/SQL functions / procedures rather than embedding logic in page processes and lots more.

Dimitri and John after the session.

So that was about it, a very interesting and interactive discussion with loads of input from all the panel and much of the group, Jeremy doing a good job of keeping it all together. And it was great to meet Dimitri and Peter for the first time and catch up with John having met him on day 2.

Unfortunately I had to head back to Reading earlier than expected in the afternoon, so was unable to catch Dimitri's presentation. A very enjoyable day none the less and looking forward to next time!

Categories: Development

UKOUG - Day 2

Anthony Rayner - Mon, 2007-12-10 05:34
It had finally arrived, Tuesday 4th December. A day that had been engrained on my brain ever since receiving 'UKOUG 2007 - Speaker Confirmation' in my Inbox back in July. The day of my first big presentation, 'Building The Rich User Interface with Oracle Application Express and Ajax'.

So very nervous I headed down to Birmingham from Reading and arrived at the ICC at around 9.30am. I was speaking at 11.15 so gave myself a bit of time to register and go over the slides one last time. A worthwhile activity as I was soon to realise on discovering 3 of my slides were missing which I must have deleted the previous night. Thank goodness for backups!!

I headed up to Hall 8b, the venue for my presentation. It was a good size, around 170 capacity but didn't seem to big so I was ok with that. There was no session in there before me so had plenty of time to get setup and settled, or so I thought. The technician arrived soon after me and asked if I was going to be using the supplied laptop or my own, I said my own and he worringly replied, 'You are the first person who's wanted to use their own'. I replied 'Is that a problem?', and he assured it wasn't but there would just be a bit of setup to get it working.

Time went by, people started arriving and my opening slide was still no where to be seen on the big wall behind me. The room of around 50 people was filled with silence, Harrison my session chair did a good job of trying to break it with a quick poll of how many people had used APEX before, which was most (but not all) of the room. I then ran through my agenda which seemed like it took about 5 minutes but probably only lasted about 30 seconds. Anyway, with numerous trips back and forth to the control room by the technician, my opening slide finally appeared on the big wall, a sigh of relief from me and after all the waiting and anticipation, I could talk.

It went ok. I know there are a few things I could have explained better and in more detail. I had always been worried about the amount of technical information I was trying to get across in 1 hour (although originally I was worried I wouldn't be able to fill it!) and in hindsight, I think I should have gone for longer.

So having finished talking in about the right time, there were a few questions and the job was done. John Scott came up to me afterwards and introduced himself. It was great to finally meet him having only known of him before. He said my presentation was really good, which means a lot coming from someone like John and also suggested that I should have a longer time slot to get through everything, I definitely agree.

Me, full of relief!

For anyone who is interested in getting the slides I have currently taken them offline just because I wanted to change a few small things. Once these changes are made I will post a link on my blog. I am also trying to get a demo up and running which will have all the code available to download. Watch this space!

Having done my presentation, and with the weight of an elephant lifted from my shoulders I was then free to enjoy some of the conference.

Really enjoyed Zory Zeharieva of CERN present, 'A Real-Life Experience of Rapidly Building Web-Interfaces with Oracle Application Express at CERN'. She presented well and covered some best practices for building scalable applications which was very interesting and also expressed some concerns around the way APEX application files are managed within versioning systems, a common concern which came up in a few places over the conference.

Also then dropped in on Oracle's Barry McGillin talking about, 'Consolidate Your Microsoft Access Applications to Oracle Application Express'. Barry is a great speaker, informative and easy to listen to (even at the end of a very long day!). He showed how to migrate an Access database with data into an Oracle database and then use the APEX 3.0 feature 'Application Migration Workshop' to build your APEX application. Well worth a look for anyone planning on doing this kind of thing.

So the day of presentations was over, and it was up to the free bar to relax and catch up with some people. Had a very pleasant drink with some colleagues and headed back to the hotel for a good night's sleep.

UKOUG day 2 done and dusted. The months of worry and preparation were worth it and my life can now resume normality. Presenting is, although nerve-wrenching and time-consuming, a very rewarding experience and I would recommend it to anyone.

Categories: Development

Pages

Subscribe to Oracle FAQ aggregator