Feed aggregator

Don’t Miss Hands-On Learning with Oracle Product Experts, Oracle ACE’s, and Java Champions!

OTN TechBlog - Wed, 2016-07-06 17:43

The Oracle Technology Network team invites you to attend our latest OTN Summit series. Gain first hand access to highly technical demonstrations, presentations, and hands-on labs. View the full agenda and abstracts.

The Summit opens with Oracle Cloud Insights – A Conversation with Inderjeet Singh, Executive Vice President, Fusion Middleware Development. Then, in just over three hours, you can explore 12 different sessions among four tracks.

  • Database track: Three sessions focused on understanding the scope of your cloud implementation across basic Database operations, Business Analytics and Big Data management so you can evaluate your current database architecture in the context of Oracle Cloud services.
  • Java track: Learn best practices and platforms for improved development and deployment of Java applications, how to optimize your DevOps practices with practical and proven tactics, and create JavaScript applications leveraging Java EE as the backend.
  • Middleware track: This track will focus on Integration in the Cloud, with three sessions presenting three different angles on Oracle’s Integration Cloud Service (OICS) and learn about integration options, examine best practices and integration patterns, and watch a demo involving OICS, Peoplesoft, and Oracle's LinkedIn Adapter.
  • Systems track: For our attendees in this track will introduce the Oracle Public Cloud Machine, examine SQL in Silicon and Security in Silicon features, and a technical overview and best practices of the Oracle Exadata Cloud Service.

View the full agenda and abstracts.

Oracle doubts

Tom Kyte - Wed, 2016-07-06 14:46
c d f ---- ---- ---- 1 a 100 1 b 200 Output: 1 a 100 b 200
Categories: DBA Blogs

cannot fetch plan for SQL_ID: 9m7787camwh4m, CHILD_NUMBER: 0

Tom Kyte - Wed, 2016-07-06 14:46
I am using PL/SQL in front end and Oracle 11gR2 in back end to developing a db app. In the middle of time, I am going to check hard parsed execution plan of one sql statment. What make me surprised is that the PL/SQL Developer seems has no capability...
Categories: DBA Blogs

Do bind variables in APEX trigger row by row processing?

Tom Kyte - Wed, 2016-07-06 14:46
To start this off, I kind of have a solution for my problem, I'm just trying to understand the mechanics behind it. Ok, here's the set-up: - I have a very simple APEX page: a report on a view + 2 date pickers to filter the data; - The query is a...
Categories: DBA Blogs

why does "select user from dba_users" work?

Tom Kyte - Wed, 2016-07-06 14:46
Hi all, I accidently typed today "select <b>user</b> from dba_users" in SQLPlus instead of "select <b>username</b> from dba_users" and it worked. Not like the correct SQL would have, but i got 21 rows (which is the correct number of users in the dat...
Categories: DBA Blogs

Slow query because the cardinality estimate is wrong for joins on foreign keys

Tom Kyte - Wed, 2016-07-06 14:46
While investigating a very slow query in our OLTP db, I noticed that Oracle severly under estimates the cardinality for joins that are on foreign key. The following script replicates the issue. create table A (part number not null, rec number not...
Categories: DBA Blogs

Tune order by clause in query.

Tom Kyte - Wed, 2016-07-06 14:46
Hi Tom, In the below query order by is taking a lot of time. so i thought of creating a composite index on columns that are present in the order by clause and force that index using hint. <b>But my problem is, here i have to fetch data of lo...
Categories: DBA Blogs

I need to delete 18000 rows from a table but where clause condition varies. How to complete this deletion in simple way

Tom Kyte - Wed, 2016-07-06 14:46
I need to delete 18000 rows from a table but where clause condtion varies for each set of records. A particular where clause condition can delete 3 records. Another particular where clause condition can delete 1 record. I combined both delete stat...
Categories: DBA Blogs

where are the executed statments stored?

Tom Kyte - Wed, 2016-07-06 14:46
suppose i execute a plsql block , all the statments are not executed. Only high load statments are executed. How to see which of the statements are executed and where are they stored, Like which view/table?
Categories: DBA Blogs

Cloud Raining - Where is Oracle with the Cloud - Is the DB Giant sleeping ?

Tom Kyte - Wed, 2016-07-06 14:46
Hello AskTom Team, I have been working with Oracle Database for over a decade and half. With the recent shift of Companies wanting to put their systems in Cloud rather then on prem What is the future of Oracle. I just attended a...
Categories: DBA Blogs

Inner join vs Where

Tom Kyte - Wed, 2016-07-06 14:46
Categories: DBA Blogs

Getting Started with Oracle JET

Shay Shmeltzer - Wed, 2016-07-06 14:31

Last week I did an "Introduction to Oracle JET" session at the KScope16 conference, and I wanted to share the demo I used there with more people.

Specifically the demo shows how you can adopt the code from the Oracle JET cookbook samples to work in the quick start template project.

In this demo you'll learn how to create your first JET application and build a basic JET page.

Specifically it shows the following steps:


Hopefully this video can help you build your first Oracle JET page.

Now that you watched this video that shows how to use the pre-configured project provided as a quick start, you might want to follow up and watch the video that shows you how to work with the base distribution and hook up the JET libraries. 

Need more help with Oracle JET? Join the JET community on OTN

Categories: Development

Outer Join with OR and Lateral View Decorrelation

Dominic Brooks - Wed, 2016-07-06 11:33

Use of ANSI SQL is a personal thing.

Historically I have not been a fan apart from where it makes things easier/possible.

This reticence was mainly due to optimizer bugs and limitations in the earlier days.

Recently I have been using it much more because I find that the developers I interact with prefer it / understand it better.

You might/should be aware that Oracle will rewrite ANSI SQL to an Oracle syntax representation, this transformation being listed in the optimizer trace file.

You might/should also be aware that Oracle outer join syntax does not allow OR or IN:

drop table t1;
drop table t2;

create table t1
select floor((rownum+1)/2) col1
,      case when mod(rownum,2) = 0 then 1 else 2 end col2
,      10 col3
from   dual
connect by rownum <= 20;

create table t2
select rownum col1
,      case when mod(rownum,2) = 0 then 2 else 1 end col3
from   dual
connect by rownum <= 10;

select *
from   t1
,      t2
where  t1.col1 = t2.col1 (+) 
and  ((t1.col2 = 1
and    t2.col3 (+) > t1.col3)
or    (t1.col2 = 2
and    t2.col3 (+) < t1.col3));

ORA-01719: outer join operator (+) not allowed in operand of OR or IN

ANSI SQL remedies this:

alter session tracefile_identifier = 'domlg1';
alter session set events 'trace[rdbms.SQL_Optimizer.*]';
select *
from   t1
left join t2
on    t1.col1 = t2.col1
and ((t1.col2 = 1
and   t2.col3 > t1.col3)
or   (t1.col2 = 2
and   t2.col3 < t1.col3));

alter session set events 'trace off';

But it comes at a price.

Note the execution plan:

| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT    |      |    20 |  1300 |    42   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER |      |    20 |  1300 |    42   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL | T1   |    20 |   780 |     2   (0)| 00:00:01 |
|   3 |   VIEW              |      |     1 |    26 |     2   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| T2   |     1 |    26 |     2   (0)| 00:00:01 |

Predicate Information (identified by operation id):

   4 - filter("T1"."COL1"="T2"."COL1" AND ("T1"."COL2"=1 AND
              "T2"."COL3">"T1"."COL3" OR "T1"."COL2"=2 AND "T2"."COL3"<"T1"."COL3"))   

Now, maybe you will have better luck than me but no matter what I try I cannot change the NESTED LOOPS OUTER operation.
So, if that lateral view involves some full table scans or other significant operations, they might be very expense on the outer operation of a nested loop.

The reason is in the optimizer trace.

Query after View Removal
******* UNPARSED QUERY IS ********
SELECT "T1."COL1" "COL1", "T1."COL2" "COL2", "T1."COL3" "COL3", "VW_LAT_AE9E49E8"."ITEM_1_0" "COL1", "VW_LAT_AE9E49E8"."ITEM_2_1" "COL3" FROM "DOM"."T1" "T1", LATERAL( (SELECT "T2"."COL1" "ITEM_1_0", "T2"."COL3" "ITEM_2_1" FROM "DOM"."T2" "T2" WHERE "T1"."COL1"="T2"."COL1" AND ("T1"."COL2"=1 AND "T2"."COL3">"T1"."COL3" OR "T1"."COL2"=2 AND "T2"."COL3" < "T1"."COL3"))) (+) "VW_LAT_AE9E49E8"
DCL:Checking validity of lateral view decorrelation SEL$BCD4421C (#1)
DCL: Bypassed: view has non-well-formed predicate
DCL: Failed decorrelation validity for lateral view block SEL$BCD4421C (#1)

The OR prevents the decorrelation which seems to mean that we’re stuck with a NESTED LOOP for now.

Further Reading on ANSI:
Oracle Optimizer Blog
Jonathan Lewis on ANSI Outer
Jonathan Lewis on ANSI


Jonathan Lewis - Wed, 2016-07-06 11:02

I rarely blog about anything non-technical but after the events last Friday (1st July) I wanted to say something about the pride that I shared with several hundred parents around the country as they saw the effect their offspring created through a living memorial of the terrible waste of life that happened  a hundred years ago on 1st July 1916 when some 70,000 soldiers (a very large fraction of them British) were killed or injured on the first day of the battle of the Somme.

While a memorial service was being held at Thiepval – a monument to 72,000 British (Empire) soldiers who died in the battle of the Somme but have no known grave – 1,500 “ghosts of the Somme” were silently wending their way in small groups through the streets, shopping centres, and train stations of cities across the UK, pausing to rest from time to time and occasionally bursting into the song of the trenches: “We’re here because we’re here”.

Each “ghost” represented a specific solder killed on the first day of the Somme and if you approached one of them to ask what was going on their only response was to look you in the eye and hand you a card stating their name, rank, regiment and, where known, the age at which they died.

Although many of the posts and tweets about the event mention the collaboration and assistance of various theatre groups around the country almost all of the soldiers were simply people who had responded to an advertisement for Project Octagon and had spent time over the previous 5 weekends rehearsing for the event. My son Simon was one of the volunteers who was on the London beat, starting with the morning commuters at Kings Cross then trekking around London all day – in hobnailed leather boots – to end at Waterloo station for the evening commuters.

After hours of walking this was how he appeared at Waterloo at the end of the day:


Like me he normally sports a beard and moustache but he’d shaved the beard off and trimmed the moustache to the style of an older era. The absent, dazed, look is in character for the part but also, I think, an indication of exhaustion, both physical and emotional. I wasn’t even sure he’d realised I was crouching six feet away to take this photo until I asked him about it the following day. When I showed the picture to my wife it brought tears to her eyes to think that 100 years ago that might have been the last sight of her son she’d see before he went off to die – it’s a sentiment that appeared more than once on Twitter through the day.

Shortly before 6:00 pm several more groups converged on Waterloo for a final tableau giving us a rendition of “We’re here because we’re here” that ended in an agonised scream:


It’s a gut-wrenching thought that a group that size would have been killed roughly every 6 minutes, on average, on the first day of the Somme though, realistically, the entire 1,500 that volunteered for the day would probably have died in the first few minutes of the first wave.

Behind the Scenes

There was no announcement of this living memorial so throughout the day people were asking where the soldiers came from and who had organised the event. Finally, at 7:00 in the evening 1418-Now identified themselves as the commissioning body, with Jeremy Deller as the artist in collaboration with Rufus Norris of the National Theatre.

Like any military operation, though, between the generals at the top and the troops at the bottom there was a pyramid of personnel connecting the big picture to the final detail. Under Jeremey Deller and Rufus Norris there was a handful of key characters without whom the day would have been very different. I can’t tell you who they all were but I’m proud to say that one of them was my daughter Anna who, along with a colleague, spent a large fraction of the last 16 months in the role of “Lead Costume Supervisor ” preparing for the day. Under the pair there were several regional costume supervisors, and each costume supervisor was responsible for several dressers who would have to help the volunteers put on the unfamiliar battledress.

Despite working on the project for 16 months Anna told me very little about what was going on until the day was over, and this is a thumbnail sketch (errors and omissions are my fault) of what she’s told me so far.

Amongst other things she selected a list of names from the soldiers who had died on the first day of battle, recording their rank, regiment, battalion and, where known, age. She then had to find out exactly what kit each battalion would have been wearing on the day, allowing for some of the variation that would have appeared within each battalion and catering for the various ranks; then she had to find a supplier who could make the necessary uniforms in a range of sizes that would allow for the variation in the build of the (as yet unknown, unmeasured) volunteers.

As batches of uniforms arrived each one had to be associated with its (historic) owner and supplied with 200 cards with the owner’s details – and it was really important to ensure that the right name was attached to a uniform before the uniforms could be dispatched around the country. Ideally a uniform would arrive at a location and find a volunteer who was the right size to wear it, with the right apparent age to match the card that went with the uniform; inevitably some uniforms had to be moved around the country to match the available volunteers.

The work didn’t stop with the uniforms being in the right place at the right time, of course. There aren’t many people alive who know how to dress in a British Army uniform from 1916 – so Anna and her colleague had to create a number of videos showing the correct way to wear webbing, how to put on puttees, etc. The other problem with the uniforms was that they were brand new – so they had to be “broken down”. That’s a lot of work when you’ve got 1,500 costumes. In part this was carried out by the volunteers themselves who spent some of their rehearsal time wearing the costumes while doing energetic exercises to wear them in and get them a little grubby and sweaty; but it also meant a lot of work for the dressers who were supplied with videos showing them how to rub (the right sort of) dirt into clothes and how to rough them up and wear them down in the right places with wire brushes etc.

One of the bits of the uniform you probably won’t have seen – or even if you saw it you might not have noticed it – was the T-shirt: the army uniform of the day would have been rather sweaty, itchy and uncomfortable on a hot summer’s day, so the soldiers weren’t wearing the real thing. Anna and her colleague designed a T-shirt that looked like the front of the shirt the troops should have worn under their battledress made of a material that was thinner, softer and much more comfortable than the real thing. In the end the day wasn’t as hot as expected so very few volunteers seemed to unbutton their tops – but if they had done so the T-shirts would have appeared to be the real thing.

Walking the Walk.

Apart from the authenticity of the uniforms another major feature of the day was the way that the ghosts made their way around from place to place silently, in single file, with no apparent references to maps (or satnav). Every group had a carefully planned route and timetable and two stage managers wearing brightly coloured backpacks so that they could be seen easily by the soldiers but, since one who walked 50 metres ahead and one 50m behind, were unlikely to be noticed by anyone who wasn’t looking. The stage managers were following carefully planned and timetabled routes – allowing the soldiers to stay in character all the time.

You may have seen pictures of the troops on the various underground trains – that’s just one demonstration of the level of detailed planning that went into the day. With a tight timetable of action and previous communications to station masters and other public officials to ensure that there would be no hold-ups at critical points the lead stage manager could (for example) get to a station guard to warn them of the imminent arrival of a squad, show them the necessary travel cards, and get the gate held open for them. No need for WW1 ghosts to break character by fumbling for electronic travel cards, just a silent parade through an open gate.

Just as Anna was the Lead Costumer Supervisor, there was a Lead Stage Manager with a cascade of local route masters beneath her. She was based in Birmingham and was responsible for working out how to make the timetabling and routing possible, using her home town as the test-bed for the approach, then briefing the regional organizers who applied the methods to prepare routes and handle logistics for their own locations.

End Results

To the people of London and Manchester and Belfast and Swansea and Penzance and Shetland and half a dozen places around the UK, it just happened: hundreds of ghosts of the past appeared and wandered among us. The uniforms were “real”, the journeys from place to place were “spontaneous” and “unguided”, and the ghosts were haunting. To most of us “it just happened” but behind the scenes the effort involved in preparation, and the attention to detail was enormous.

Between the “headline” names at the top of the pyramid and the highly visible troops on the ground at the bottom, it took the coordinated efforts of about 500 people to turn a wonderful idea into a reality that moved millions of people in their daily lives.


If you want to see more images and comments about the day you can follow the hashtag #wearehere and there is a collection of instagram images at http://becausewearehere.co.uk/  and if you’re in the London area on 11th July and want to hear more about the instigation and implementation of the day there’s an evening event on at the National Theatre on Monday 11th July featuring Jenny Waldman, Jeremy Deller and Rufus Norris discussing the event with a BBC correspondent.



OSB 12c Logging part 2

Darwin IT - Wed, 2016-07-06 06:52
Two weeks ago, I wrote about how to set the log level voor SB Pipelines (12c) to be able to see the logging of the Log activity in the WebLogic Server logs.

Today I encountered that for a developer at my customer the complete oracle.osb.logging.pipeline logger was missing in the log-configuration. So setting the level from EM (Fusion Middleware Control) following the article above is a little hard.

I could not find why in that case the logger was missing. But I did find a simple solution.
In the paragraph '7.1.4 ODL Log Configuration' of the Administering Oracle Service Bus documentation, I found that  you can change the logging via the EM, wlst and the logging.xml file. This file can be found in ${osb.domain.home}/config/fmwconfig/servers/${osbserver.name}, eg. 'c:\Data\JDeveloper\SOA\system12.\DefaultDomain\config\fmwconfig\servers\DefaultServer\'.

Go to the end of the file:

<logger name='com.sun.xml.ws' level='WARNING' useParentHandlers='true'/>

Copy the last logger and rename it to create an entry for the oracle.osb.logging.pipeline logger:

<logger name='com.sun.xml.ws' level='WARNING' useParentHandlers='true'/>
<logger name='oracle.osb.logging.pipeline' level='TRACE:16' />

Set the level and remove the useParentHandlers attribute.
Restart your server and then you should find the option in the EM OSB Log Configuration. If you have multiple OSB servers you'd probably need to update this change for every osb server, since the logging.xml resides in a server specific sub-folder. I haven't tried it to add it for one server and change it to see if it is automatically added to the other server. Would be a nice experiment.

Always fill in the DNS Hostname Prefix in Oracle Compute Cloud Instance

Always fill in the DNS Hostname Prefix when creating a new instance in Oracle Compute Cloud. What seems an unimportant optional field can make your life harder than it should be,...

We share our skills to maximize your revenue!
Categories: DBA Blogs

ora-01008, what is the bind variable's name?

Tom Kyte - Tue, 2016-07-05 20:26
Good time of day, Tom! I run several SQL via DBMS_sql package. Each of that SQL has a set of bind variables. Is there any feature to get a list of variables' names for given SQL? For instance. I wonder to get a list of ':v_name',':p_result' ...
Categories: DBA Blogs

Generate tree paths for hierarchy

Tom Kyte - Tue, 2016-07-05 20:26
Hello , I have one question which are asked into interview ,To make a tree when user insert a node into table its path get automatically reflected into table Table: Tree ---------------------- node(int) parentNode(int) path(...
Categories: DBA Blogs


Tom Kyte - Tue, 2016-07-05 20:26
Hello, I am trying to use the below SQL : SELECT least ( DECODE (:VAR1, 9999, NULL, :VAR1), DECODE (:VAR2,9999, NULL,:VAR2) ) FROM DUAL; VAR1 & VAR2 need to be NUMBERs (not varchar) the above SQL seems to work for all numbers exce...
Categories: DBA Blogs


Tom Kyte - Tue, 2016-07-05 20:26
Hi, my table is with fist name , last name , status. Now the thing is I want to change the status to "APPROVED" as soon as I made the entry in last name, if last name column is empty status should be default lets say "PENDING". I tried it u...
Categories: DBA Blogs


Subscribe to Oracle FAQ aggregator