Feed aggregator

I Can Help You Trace It

Cary Millsap - Fri, 2011-11-18 22:59
The first product I ever created after leaving Oracle Corporation in 1999 was a 3-day course about optimizing Oracle performance. The experiences of teaching this course from 2000 through 2003 (heavily revising the material each time I taught it) added up to the knowledge that Jeff Holt and I needed to write Optimizing Oracle Performance (2003).

Between 2000 and 2006, I spent many weeks on the road teaching this 3-day course. I stopped teaching it in 2006. An opportunity to take or teach a course ought to be a joyous experience, and this one had become too much of a grind. I didn’t figure out how to fix it until this year. How I fixed it is the story I’d like to tell you.
The ProblemThe problem was simply inefficiency. The inefficiency began with the structure of the course, the 3-day lecture marathon. Realize, 6 × 3 = 18 hours of sitting in a chair, listening attentively to a single voice (my voice) is the equivalent of a 6-week university term of a 3-credit-hour course, taught straight through in three days. No hour-plus homework assignment after each hour of lecture to reinforce the lessons; but a full semester’s worth of listening to one voice, straight through, for three days. What retention rate would you expect from a university course compressed into just 3 days?

So, I optimized. I have created a new course that lasts one day (not even an exhausting full day at that). But how can a student possibly learn as much in 1 day as we used to teach in 3 days? Isn’t a 1-day event bound to be a significantly reduced-value experience?

On the contrary, I believe our students benefit even more now than they used to. Here are the big differences, so you can see why.
The Time SavingsIn the 3-day course, I would spend half a day explaining why people should abandon their old system-wide-ratio-based ways of managing system performance. In the new 1-day course, I spend less than an hour explaining the Method R approach to thinking about performance. The point of the new course is not to convince people to abandon anything they’re already doing; it’s to show students the tremendous additional opportunities that are available to them if they’ll just look at what Oracle trace files have to offer. Time savings: 2 hours.

In the 3-day course, I would spend a full day explaining how to interpret trace data. By hand. These were a few little lab exercises, about an hour’s worth. Students would enter dozens of numbers from trace files into laptops or pocket calculators and write results on worksheets. In the new 1-day course, the software tools that a student needs to interpret files of any size—or even directories full of files—are included in the price of the course. Time savings: 5 hours.

In the 3-day course, I would spend half a day explaining how to collect trace data. In the new 1-day course, the software tools that a student needs to get started collecting trace files are included in the price of the course. For software architectures that require more work than our software can do for you, there’s detailed instruction in the course book. Time savings: 3 hours.

In the 3-day course, I would spend half a day working through about five example cases using a software tool to which students would have access for 30 days after they had gone home. In the new 1-day course, I spend one hour working through about eight example cases using software tools that every student will take home and keep forever. I can spend less time per case yet teach more because the cases are thoroughly documented in the course book. So, in class, we focus on the high-level decision making instead of the gnarly technical details you’ll want to look up later anyway. Time savings: 3 hours.

...That’s 13 classroom hours we’ve eliminated from the old 3-day experience. I believe that in these 13 hours, I was teaching material that students weren’t retaining to begin with.
The BookThe next big difference: the book.

In the old 3-day course, I distributed two books: (1) the “Course Notebook,” which was a black and white listing of the course PowerPoint slides, and (2) a copy of Optimizing Oracle Performance (O’Reilly 2003). The O’Reilly book was great, because it contained a lot of detail that you would want to look up after the course. But of course it doesn’t contain any new knowledge we’ve learned since 2003. The Course Notebook, in my opinion, was never worth much to begin with. (In my opinion, no PowerPoint slide printout is worth much to begin with.)

The Mastering Oracle Trace Data (MOTD) book we give each student in my new 1-day course is a full-color, perfect-bound book that explains the course material and far more in deep detail. It is full-color for an important reason. It’s not gratuitous or decorative; it’s because I’ve been studying Edward Tufte. I use color throughout the book to communicate detailed, high-resolution information faster to your brain.

Color in the book helps to reduce student workload and deliver value long after a student has left the classroom. In this class, there is no collection of slide printouts like you’ve archived after every Oracle class you’ve been to since the 1980s. The MOTD book is way better than any other material I’ve ever distributed in my career. I’ve heard students tell their friends that you have to see it to believe it.
“A paper record tells your audience that you are serious, responsible, exact, credible. For deep analysis of evidence and reasoning about complex matters, permanent high-resolution displays [that is, paper] are an excellent start.” —Edward TufteThe SoftwareSo, where does a student recoup all the time we used to spend going through trace files, and studying how to collect trace data on half a dozen different software architectures? In the thousands of man-hours we’ve invested into the software that we give you when you come to the course. Instead of explaining every little detail about quirks in Oracle trace data that change between Oracle versions 10.1 and 10.2 and 11.2 or 11.2.0.2 and 11.2.0.4, the software does the work for you. Instead of having to explain all the detail work, we have time to explain how to use the results of our software to make decisions about your data.

What’s the catch? Of course, we hope you’ll love our software and want to buy it. The software we give you is completely full-featured and yours to keep forever, but the license limits you to using it only with one login id, and it doesn’t include patches and upgrades, which we release a few times each year. We hope you’ll love our software so much that you’ll want to buy a license that lets you use it on any of your systems and that includes the right to upgrade as we fix bugs and add features. We hope you’ll love it so much that you encourage your colleagues to buy it.

But there’s really no catch. You get software and a course (and a book and a shirt) for less than the daily rate that we used to charge for just a course.
A Shirt?MOTD London 2011-09-08: “I can help you trace it.”Yes, a shirt. Each student receives a Method R T-shirt that says, “I can help you trace it.” We don’t give these things away to anyone except for students in my MOTD course. So if you see one, the person wearing it can, in actual fact, Help You Trace It.
The Net ResultThe net result of all this optimization is benefits on several fronts:
  • The course costs a lot less than it used to. The fee is presently only about 25% of the 3-day course’s price, and the whole experience requires less than ⅓ of time away from work that the original course did.
  • In the new course, our students don’t have to work so hard to make productive use of the course material. The book and the software take so much of the pressure off. We do talk about what the fields in raw trace data mean—I think it’s necessary to know that in order to use the data properly, and have productive debates with your sys/SAN/net/etc. administration colleagues. But we don’t spend your time doing exercises to untangle nested (recursive) calls by hand. The software you take home does that for you. That’s why it is so much easier for a student to put this course to work right away.
  • Since the course duration is only one day, I can visit far more cities and meet far more students each year. That’s good for students who want to participate, and it’s great for me, because I get to meet more people.
PlansThe only thing missing from our Mastering Oracle Trace Data course right now is you. I have taught the event now in Southlake, Texas (our home town), in Copenhagen, and in London. It’s field-tested and ready to roll. We have several cities on my schedule right now. I’ll be teaching the course in Birmingham UK on the day after UKOUG wraps up, December 8. I’ll be doing Orlando and Tampa in mid-December. I’ll teach two courses this coming January in Manhattan and Long Island. There’s Billund (Legoland) DK in April. We have more plans in the works for Seattle, Portland, Dallas, and Cleveland, and we’re looking for more opportunities.

Share the word by linking the official
MOTD sticker to http://method-r.com/.My wish is for you to help me book more cities in North America and Europe (I hope to expand beyond that soon). If you are part of a company or a user group with colleagues who would be interested in attending the course, I would love to hear from you. Registering en masse saves you money. The magic number for discounting is 10 students on a single registration from one company or user group.

I can help you trace it.

Getting Started with Mobile in APEX - Part 2

Marc Sewtz - Thu, 2011-11-17 16:33
jQuery Mobile 1.0 Final was released today! Time to take it for a spin. As outlined in my previous post, jQuery Mobile is available for download on the jquerymobile.com web site:

Alternatively, if you were referencing the CDN, you could of course also simply update your jQuery Mobile enabled templates and remove the "RC2" or "RC3" suffix from your file references:
<link rel="stylesheet" href="http://code.jquery.com/mobile/1.0/jquery.mobile-1.0.min.css">
<script src="http://code.jquery.com/jquery-1.6.4.min.js"></script>
<script src="http://code.jquery.com/mobile/1.0/jquery.mobile-1.0.min.js"></script>

Then just re-run your pages, and you'll be up and running using the shiny, new final release of jQuery Mobile 1.0!

Now running my original "Hello World!" sample with jQuery Mobile 1.0 would be a bit boring. So here's a new sample application, this one should be more interesting. It's a very basic message board application. Point your mobile device (or desktop browser) to this URL:

You'll get a home page showing recent messages, along with the title, author and create date and time. When you click on a message, you'll get to a read-only view of the message details. When you click on the create button on the home page, you can enter a new message, with your name, email and message title. Give it a try, leave me some feedback and comments, and if you like the app, it's available for download as well. It comes with the underlying database objects bundled in as supporting objects, i.e. they're created when you install the application.

For the most part, this is a standard APEX application, nothing too fancy or out of the ordinary. The only relevant templates are those containing "mobile" in their names. The home page was built as a standard classic report page, using a customized named-column report template. The form page is a standard APEX form, which is omitting the form table-grid using the corresponding region template attribute and using the new label template field container attributes. The buttons use simple anchor tags, and illustrate how you can have different button colors, using the new "hot" attribute for buttons. And for the back button, I used a jQuery Mobile button icon, which can be easily included using the HTML data- attributes, data-icon in this case.

So try out the application, and leave your comments in the message board. I'll follow up with additional details on some of the more advanced concepts in upcoming posts.

Processing Binary Data in SOA Suite 11g

Ramkumar Menon - Thu, 2011-11-17 12:16

SOA Suite 11g provides a variety of ways to exchange binary data amongst applications and endpoints. The illustration below is a bird's-eye view of all the features in SOA Suite to facilitate such exchanges.

Handling Binary data in SOA Suite 11g Composites

Samples and Step-by-Step Tutorials

A few step-by-step tutorials have been uploaded to java.net that illustrate key concepts related to Binary content handling within SOA composites. Each sample consists of a fully built composite project that can be deployed and tested, together with a Readme doc with screenshots to build the project from scratch.

For detailed information on binary content and large document handling within SOA Suite, refer to Chapter 42 of the SOA Suite Developer's Guide.

Handling Binary data in Oracle B2B

The following diagram illustrates how Oracle B2B facilitates exchange of binary documents between SOA Suite and Trading Partners.

Processing Binary Data in SOA Suite 11g

Ramkumar Menon - Thu, 2011-11-17 12:16

SOA Suite 11g provides a variety of ways to exchange binary data amongst applications and endpoints. The illustration below is a bird's-eye view of all the features in SOA Suite to facilitate such exchanges.

Handling Binary data in SOA Suite 11g Composites

Samples and Step-by-Step Tutorials

A few step-by-step tutorials have been uploaded to java.net that illustrate key concepts related to Binary content handling within SOA composites. Each sample consists of a fully built composite project that can be deployed and tested, together with a Readme doc with screenshots to build the project from scratch.

For detailed information on binary content and large document handling within SOA Suite, refer to Chapter 42 of the SOA Suite Developer's Guide.

Handling Binary data in Oracle B2B

The following diagram illustrates how Oracle B2B facilitates exchange of binary documents between SOA Suite and Trading Partners.

OSB: Deployment issues with DBAdapter

Marc Kelderman - Wed, 2011-11-16 02:05
In normal circumstances changing the configuration of the DBAdapter should work. You create a JDBC datasource and create in the outbound connections of the DbAdpater a new JNDI entry. After saving your settings you should update the DBAdapter application and apply the changes. Normally you see:





Here is the issue I was struggeling with. I created a new JNDI entry in the DBAdapter, applied the changes, deployed the Plan.xml files, updated the DBAdapter. I saw that the managed servers did not picked up the new change or even the old entries.

After a while I fixed the issue. This was related due to the fact the DbAdapter was also targed to the AdminServer. So here are the steps to update or add new JNDI entries in the DbAdapter application or any other, AqAdapter or FileAdapter.
  1. Update/Add JNDI entry in your outbound connections of the Adapter.
  2. Save the changes.
  3. Bring down the Managed Servers.
  4. Update the Adapter application.
  5. Bring down the Admin Server.
  6. Copy the Plan.xml file of the Adapter to the managed servers.
  7. Start the AdminServer.
  8. Start the managed servers.
This solution is strange, because you expect that Weblogic should do this while the AdminServer and Managed Servers are running. But in my case this was a working solution.

To create and deploy your own Adapters can be found @Edwin Biemond's Blog.





How to check the Installed Packages/Patches in Sun Solaris

Madan Mohan - Sat, 2011-11-12 05:15
/bin/pkginfo -i SUNWarc SUNWbtool SUNWcsl SUNWhea SUNWi15cs SUNWi1cs SUNWi1of SUNWlibC SUNWlibm SUNWlibms SUNWsprot SUNWtoo SUNWxwfnt

pkgadd -p | grep i.e 123456

showrev -p | grep i.e 123456-01

showrev -p | sort -n +2 | nawk '{printf "%s ",$2}'

Getting Started with Mobile in APEX - Part 1

Marc Sewtz - Fri, 2011-11-11 13:59
With jQuery Mobile moving closer to production, I finally started working on my paper documenting how to develop mobile applications with APEX 4.1 We've put a lot of changes into APEX 4.1 that make the integration of frameworks like jQuery Mobile easier. Full mobile support, with mobile templates and components that are optimized for mobile devices, is currently planned for APEX 4.2. But that doesn't mean that you couldn't start building mobile apps with APEX today. It'll just be a bit more manual work for now. So while working on the paper, I've figured I'll start blogging about this as well, taking you step by step through the process of enabling mobile development in APEX 4.1, integrating the jQuery Mobile library and building your first mobile app.

So let's get started. The first thing you'll need to do is set a system preference in APEX that enables mobile development, to do this, logon as SYS and run the following:

exec apex_040100.wwv_flow_platform.set_preference('MOBILE_DEVELOPMENT_ENABLED','Y') ;
Don't worry, this will have no effect on any of your existing applications. What this system preference does is that in a number of places in APEX, you'll now see a select list that lets you choose between a "Desktop" and "Mobile" mode. The most important place where that's relevant is when editing a page template, i.e. you can now define that page template to be a mobile page template or a full-size / desktop page template. Once we have jQuery Mobile fully integrated, currently targeted for APEX 4.2, this will trigger the inclusion of the jQuery Mobile library, and the omission of some JavaScript and CSS references that are not needed for mobile. For the time being though, all this is doing is that it makes APEX render form elements on your mobile pages without a table grid. This gives you better control over the HTML generated by APEX, which allows the generation of HTML code that follows to jQuery Mobile syntax. Having a mobile page template in your current theme also triggers the mobile option in the create page wizards. So you can choose to build a mobile page, which hides page types from the wizard that don't yet work well on mobile, like e.g. Flash charts. And as you step through the wizard, the wizard will pick up your default mobile templates, instead of the standard templates, but only if you have actually set your mobile defaults for your theme.

Next you'll need to install or reference the jQuery Mobile libraries. These libraries are not yet bundled into APEX because jQuery Mobile is not yet production software. However you can download the libraries directly from jquerymobile.com or even easier, reference the CDN-hosted libraries in your page template. To reference the CDN, simply include the following in your page template:
<link rel="stylesheet" href="http://code.jquery.com/mobile/1.0rc2/jquery.mobile-1.0rc2.min.css" />
<script src="http://code.jquery.com/jquery-1.6.4.min.js"></script>
<script src="http://code.jquery.com/mobile/1.0rc2/jquery.mobile-1.0rc2.min.js"></script>

If you prefer downloading and installing jQuery Mobile in your APEX instance, go to this URL and download the ZIP File from there:

http://jquerymobile.com/download/

Then extract the contents of this ZIP file and load them into your APEX images directory. When using the APEX Listener or Apache/mod_plsql, you can simply copy the files to the images directory. When using the Embedded Gateway, you will need to connect to your database via WebDAV or FTP and upload the files into XML DB. To follow the folder structure that APEX uses for other jQuery libraries, make sure to copy the files to a folder you create as \i\libraries\jquery-mobile\1.0rc2\ (that's assuming your downloading the Release Candidate 2 of jQuery Mobile). The JS and CSS files should be copied directly in this folder, the images directory one below. Once installed, you can include the following in your page template:
<link rel="stylesheet" href="#IMAGE_PREFIX#libraries/jquery-mobile/1.0rc2/jquery.mobile-1.0rc2.min.css" />
<script src="#IMAGE_PREFIX#libraries/jquery/1.6.1/jquery-1.6.1.js""></script>
<script src="#IMAGE_PREFIX#libraries/jquery-mobile/1.0rc2/jquery.mobile-1.0rc2.min.js"></script>

And then you're ready to get started with Mobile development. To create a minimalist mobile page template, use the following code samples:

Header:
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>#TITLE#</title>
#HEAD#
<link rel="stylesheet" href="http://code.jquery.com/mobile/1.0rc2/jquery.mobile-1.0rc2.min.css" />
<script src="http://code.jquery.com/jquery-1.6.4.min.js"></script>
<script src="http://code.jquery.com/mobile/1.0rc2/jquery.mobile-1.0rc2.min.js"></script>
</head>
<body #ONLOAD#>
#FORM_OPEN#
Body:
<div data-role="page" id="#TITLE#">

<div data-role="header" data-theme="b" data-position="fixed">
<h1>#TITLE#</h1>
</div>

<div data-role="content">
#BOX_BODY#
</div>

<div data-role="footer" class="ui-bar" data-position="fixed">
#REGION_POSITION_08#
</div>

</div>

Footer:
#FORM_CLOSE#  
</body>
</html>
After that, create a new page, choosing the newly created mobile template, and include e.g. a HTML region with some static text, like "Hello World". Then open that page in your mobile device. You should see something like this:

You can download this sample app here (this is an updated version, in my previous version I used an application alias that caused the app to not work if it was installed more than once on an instance like apex.oracle.com), just import into your own workspace and run:

Please note that this demo references the CDN, if you want to reference your locally installed jQuery Mobile files, please modify your page template as outlined above.

This concludes my first "Getting Started with Mobile" blog post. Next time I'll cover the jQuery Mobile syntax and how you would go about structuring your mobile APEX pages.


Where is the sql_id of active session?

Coskan Gundogar - Fri, 2011-11-11 09:39

In this post I will give information on quick ways to find the sql_id’s of sessions for which you can’t see any entry when you look at v$session.

I usually see this behavior when sql is burning CPU in a nested loop and after a long time of running sql_id column suddenly not updated anymore. When I tweeted about it , ,Tanel Poder answered (If I a recall it right damn twitter does not have a proper search and history) something like ” this happens when oracle does too many recursive calls and loses track of the sql_id” (Tanel, if you are reading, please correct me if I am wrong about what you said)

On Metalink there are two notes I could find

Reason for Apparently Incorrect or Null Sql_id in v$sql [ID 406452.1]

This is caused by an architectural change in the 10g release: from 10.2 onwards, the v$sql columns (sql_id, sql_address and so on) will only show the top level user cursor,  if any (ie it will not display the recursive cursors),.
When using a remote procedure call, such as when a rpc call from a client (eg forms client) requests that a plsql object is executed on the server, there is no top level sql and, consequently, there is no sql_id in v$session.

BUG:5146994 - CANNOT FIND RECURSIVE SQL IN V$SQLAREA FOR A RUNNING SNAPSHOT REFRESH JOB
BUG 5724661 Abstract: WRONG VALUES FOR SQL_ID IN V$SESSION

On Null SQL_ID For Active Sessions In V$Session [ID 1343503.1]

From 10.2 onwards sql_id will only show the top level user cursor ( if any). 

Sometimes,Session does a plsql rpc call ie a client ( eg forms client) can request a plsql object to be executed at the server. In this case there is no top level sql and as such we don't see the sql_id in v$session.

Currently, oracle don't store the current sql thru any v$ views. The only way to find that is thru event 10046.
This is covered in Unpublished bug 5528670: "NULL VALUES FOR SQL_ID, SQL_HASH_VALUE, SQL_ADDRESS AND OTHERS IN V$SESSION", Which was closed as "Not a Bug".

When I have this issues I sometimes get it even on a sql which I run from sql_plus without a pl/sql call so I think limitation on these notes is a bit misleading or I am interpreting the information wrong. Anyway finding the reasons needs too much tracing research bla bla bla which you will never have time to go through when you hit the issue on a production system so best I cut the chase and start giving information how to find the sql_id

Notes says there is no v$ view which is a a bit misleading and notes also say 10046 is the only way to find it which is also another documentation error ignoring method 2 below.

On 10G there are 2 ways that I know will usualls work one of which needs tuning and diagnostics pack license other does not

On 11G there is one more way which again needs tuning and diagnostics pack license.

Method 1- using ASH/AWR data + v$sql combination (10g-11g license needed)

1a- Find the latest not null sql_id column for the given session and serial in v$active_session_history

1b- If you cant find it at first step, due to the reason sql_id information already lost in v$active_session_history, then you need to check similar way using dba_hist_active_sess_history

2- Once you find the sql_id,plan_hash_value double check it on v$sql if number on users_executing column is matching in current v$session then you have what you looking for

—Method-1 on Action

DATABASE1> @swact

    SID USERN STATE   EVENT                        SEQ# SEC_IN_STATE         P1         P2         P3  SQL_ID         CHN
------- ----- ------- ---------------------- ---------- ------------ ---------- ---------- ----------  ------------- ----
   1352 XXWEB WAITING db file scattered read      40906            0         67    2638509         32                       --->sql_id empty
    294 XXWEB WAITING db file scattered read      16727            0         82     234468         32                       --->sql_id empy
   1263 COSKA WORKING On CPU / runqueue              44            0 1413697536          1          0  f5cqythck1gfz    1

DATABASE1> @last_sql 1352 %

SAMPLE_TIME                           SESSION_ID SESSION_SERIAL# SQL_ID		PLAN_HASH_VALUE
------------------------------------- ---------- --------------- -------------  ---------------
31-JAN-11 11.00.35.524                      1352              72 3p1b93fq81x0f  2118159443	   ----> very likely our sql
31-JAN-11 11.00.34.514                      1352              72 3p1b93fq81x0f  2118159443
31-JAN-11 11.00.33.514                      1352              72 3p1b93fq81x0f  2118159443
31-JAN-11 11.00.32.514                      1352              72 3p1b93fq81x0f  2118159443
31-JAN-11 11.00.31.514                      1352              72 3p1b93fq81x0f  2118159443
31-JAN-11 11.00.30.514                      1352              72 3p1b93fq81x0f  2118159443
31-JAN-11 11.00.29.504                      1352              72 3p1b93fq81x0f  2118159443
31-JAN-11 11.00.28.494                      1352              72 3p1b93fq81x0f  2118159443
31-JAN-11 11.00.27.494                      1352              72 3p1b93fq81x0f  2118159443

9 rows selected.

DATABASE1> @last_sql 294

SAMPLE_TIME                            SESSION_ID SESSION_SERIAL# SQL_ID	PLAN_HASH_VALUE
-------------------------------------- ---------- --------------- ------------- ---------------
31-JAN-11 10.50.40.071                        294              39 3p1b93fq81x0f 2118159443	  ----> very likely our sql
31-JAN-11 10.50.39.061                        294              39 3p1b93fq81x0f 2118159443
31-JAN-11 10.50.38.061                        294              39 3p1b93fq81x0f 2118159443
31-JAN-11 10.50.37.061                        294              39 3p1b93fq81x0f 2118159443
31-JAN-11 10.50.36.061                        294              39 3p1b93fq81x0f 2118159443
31-JAN-11 10.50.35.061                        294              39 3p1b93fq81x0f 2118159443
31-JAN-11 10.50.34.051                        294              39 3p1b93fq81x0f 2118159443
31-JAN-11 10.50.33.051                        294              39 3p1b93fq81x0f 2118159443
31-JAN-11 10.50.32.051                        294              39 3p1b93fq81x0f 2118159443

9 rows selected.

DATABASE1>-----LETS CHECK THE SQL

DATABASE1> @sqlid 3p1b93fq81x0f

SQL_ID        HASH_VALUE SQL_TEXT
------------- ---------- --------------------------------------------------------------------------------------------------------
3p1b93fq81x0f 2894132238 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

 CH#   PLAN_HASH OPT_COST EXECUTIONS    FETCHES ROWS_PROCESSED     CPU_MS     ELA_MS    AVG_ELA       LIOS       PIOS USERS_EXECUTING
----  ---------- -------- ---------- ---------- -------------- ---------- ---------- ---------- ---------- ---------- --------------
   0  2118159443   956322          3          1              0 861533.028    5876120 1958.70667   21851873   21739212          2

Elapsed times + users_executing columns saying that these sqls has been running for a very long time and they are %99.999 the ones we are looking for

There are tiny challenges with this approach but not that much challeging.

I used it very much in RAC env with a system where multiple users and child cursors are also running
so if you are careful enough to compare apples with apples (same instance, same plan hash value,right session serial, longer elapsed time cursor) you are very unlikely to be mislead

code used for last_sql is like below (modified a bit for rac aware)


select inst_id,sample_time,session_id,session_serial#,sql_id from (
select inst_id,sample_time,session_id,session_serial#,sql_id from
gv$active_session_history
where
sql_id is not null
 and session_id=&1 and inst_id=&2
 order by 1 desc
) where rownum

Method 2- Oradebug dump error stack (10g-11g no license needed)- Most reliable and certain way it is %100 accurate and good part is it does not need any input from me since Tanel Poder already covered how to go through error stack on his post how to read errorstack output

I tried 10046 and never seen any sql_id information written for the 10 mins period I gathered the trace so I’m not %100 sure if it needs whole call to be finished before it writes the sql_id information. I personally won’t use 10046 for this case.

Method-3- SQL Monitoring (11g onwards-license needed). This is my favourite and its damn quick and %99.9 it works if the sql was running long enough to get monitored and then lost the track. It is damn simple like this piece of code

select distinct inst_id,sid,status,sql_id,sql_plan_hash_value,sql_child_address,sql_exec_id
 from gv$sql_plan_monitor
 where sid in (&1) and status='EXECUTING';

—Method 3 On Action

DATABASE1> @swact

    SID USERN STATE   EVENT                        SEQ# SEC_IN_STATE         P1         P2         P3  SQL_ID         CHN
------- ----- ------- ---------------------- ---------- ------------ ---------- ---------- ----------  ------------- ----
   1352 XXWEB WAITING db file scattered read      40906            0         67    2638509         32                       --->sql_id empty
    294 XXWEB WAITING db file scattered read      16727            0         82     234468         32                       --->sql_id empy
   1263 COSKA WORKING On CPU / runqueue              44            0 1413697536          1          0  f5cqythck1gfz    1

---Check sql_monitoring
SQL> @mon_sql 1352,294

INST_ID  SID STATUS              SQL_ID        SQL_PLAN_HASH_VALUE SQL_CHILD_ADDRES SQL_EXEC_ID
-------	---- ------------------- ------------- ------------------- ---------------- -----------
1        1352 EXECUTING           3p1b93fq81x0f          2118159443 00000002D88378E8 16777216
1        1352 EXECUTING           2kgnch9h5hbkz          1874321290 00000002D6223418 16777216  ---paren pl/sql
1         294 EXECUTING           3p1b93fq81x0f          2118159443 00000002D88378E8 16777217
1         294 EXECUTING           2kgnch9h5hbkz          1874321290 00000002D6223418 16777217

The second sql_id 2kgnch9h5hbkz is from pl_sql parent call which was calling 3p1b93fq81x0f so they both get monitored by v$sql_monitor

Apart from all these again if you have diagnostics pack license you can always use OEM/Grid Control to check back the sessions history but comparing with querying v$sql_monitor it is as slow as driving an electric car when you have ferrari.

As you can see there are more ways then listed if your session lost track of the sql_ids. If you have more ways (especially with tracing -like dump sql if there is an option like that) I would really like to hear.

Update after first comment

Method-4 Using v$open_cursor

First comment on which Michael Dinh mentioned he is using toad open cursors option to hunt down the sqls but he was not sure for the sqlplus.
I had a chance to to check with one of our systems. Looks like v$open_cursor is giving us information which not as clear as the other methods but still can be very
useful for standart edition or users without diagnostics pack and also can be easier than error stack which usually needs a change request on PRD systems.

SQL> @sw 207,980

    SID USERN STATE   EVENT                    SEQ# SEC_IN_STATE SQL_ID         CHN                        P1                        P2                        P3
------- ----- ------- ------------------ ---------- ------------ ------------- ---- ------------------------- ------------------------- -------------------------
    207 XXXXX WORKING On CPU / runqueue       62033            3                    cellhash#=203CB5D5         diskhash#= 349428727      bytes= 16384
    980 XXXXX WORKING On CPU / runqueue         759            1                    cellhash#=D84BEC46         diskhash#=56AC941E       bytes= 16384

SQL> @mon_sql 207,980

   INST_ID        SID STATUS              SQL_ID        SQL_PLAN_HASH_VALUE SQL_CHILD_ADDRES SQL_EXEC_ID
---------- ---------- ------------------- ------------- ------------------- ---------------- -----------
         2        980 EXECUTING           91uc7cvw2rqab          4166729024 000000040D4560F8    33554458
         2        207 EXECUTING           91uc7cvw2rqab          4166729024 000000040D4560F8    33554456

SQL> select * from v$open_cursor where sid in (207,980) order by 2;

SADDR                   SID USER_NAME  ADDRESS          HASH_VALUE SQL_ID        SQL_TEXT LAST_SQL_ SQL_EXEC_ID CURSOR_TYPE
---------------- ---------- ---------- ---------------- ---------- ------------- -------- --------- ----------- -----------------------------------
00000004508BA640        207 XXXXXXX    00000003A72340A0 2292456266 gw771824a86ua Begin PK              33554456 OPEN
00000004508BA640        207 XXXXXXX    00000003E6F0C230 2181403846 5bvw0ka10b566 Begin PK                       OPEN
00000004508BA640        207 XXXXXXX    000000040E81D128 4111302712 6k03snbuhv01s Begin PK                       OPEN
00000004508BA640        207 XXXXXXX    00000003EA88E4F0 1535077424 68u7tx5dryv1h Begin PK                       OPEN
00000004508BA640        207 XXXXXXX    00000004592E3690  914163366 4vs91dcv7u1p6 insert i                       OPEN-RECURSIVE
00000004508BA640        207 XXXXXXX    0000000411FC64C0 1407509578 8nwyam59y9t2a Begin PK                       OPEN
00000004508BA640        207 XXXXXXX    00000004117E1340 2399958818 62h75aq7hsxt2 Begin PK                       OPEN
00000004508BA640        207 XXXXXXX    00000004125C3DC0 4163623243 91uc7cvw2rqab INSERT I              33554456 OPEN-PL/SQL
0000000452AA1BB8        980 XXXXXXX    00000004125C3DC0 4163623243 91uc7cvw2rqab INSERT I              33554458 OPEN-PL/SQL
0000000452AA1BB8        980 XXXXXXX    00000004592E3690  914163366 4vs91dcv7u1p6 insert i                       OPEN-RECURSIVE
0000000452AA1BB8        980 XXXXXXX    000000040E81D128 4111302712 6k03snbuhv01s Begin PK                       OPEN
0000000452AA1BB8        980 XXXXXXX    0000000411FC64C0 1407509578 8nwyam59y9t2a Begin PK                       OPEN
0000000452AA1BB8        980 XXXXXXX    00000003E6F0C230 2181403846 5bvw0ka10b566 Begin PK                       OPEN
0000000452AA1BB8        980 XXXXXXX    00000004117E1340 2399958818 62h75aq7hsxt2 Begin PK                       OPEN
0000000452AA1BB8        980 XXXXXXX    00000003EA88E4F0 1535077424 68u7tx5dryv1h Begin PK                       OPEN
0000000452AA1BB8        980 XXXXXXX    00000003A72340A0 2292456266 gw771824a86ua Begin PK              33554458 OPEN

Once we spot the sqls we need to check all of them one by one on v$sql where if users_executing column (UEX) populated

SQL> @sqlid gw771824a86ua   ---->bingo with two users and high ELAPSED TIMES. this is a pl/sql package calling the sql below

SQL_ID        HASH_VALUE SQL_TEXT
------------- ---------- --------------------------------------------------------------------------------------------------------------------------------
gw771824a86ua 2292456266 xXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

INS  CH#  UEX  PLAN_HASH   OPT_COST        CPU_MS     ELA_MS    AVG_ELA OFFLOAD IO_SAVED_% IO_ELIG_GB IO_INTER_GB       LIOS       PIOS
--- ---- ---- ---------- ----------  - ---------- ---------- ---------- ------- ---------- ---------- ----------- ---------- ----------
  2    0    2          0          0     412784696  414481976  16579.279 No             .00        .00      58.007 2653518789    3248794
  3    0    0          0          0    3215639.15 3307820.05 1653.91002 No             .00        .00       3.212 1480568056     184192

----
---- Tried for all other SQLS but thet do not have users_executing column populated
----

SQL> @sqlid 91uc7cvw2rqab   -->two users and high ELAPSED TIMES on 4th child

SQL_ID        HASH_VALUE SQL_TEXT
------------- ---------- -----------------------------------------------------------------------------------------------------------------------------------
91uc7cvw2rqab 4163623243 xXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

INS  CH#  UEX  PLAN_HASH   OPT_COST       CPU_MS     ELA_MS    AVG_ELA OFFLOAD IO_SAVED_% IO_ELIG_GB IO_INTER_GB       LIOS       PIOS
--- ---- ---- ---------- ----------   ---------- ---------- ---------- ------- ---------- ---------- ----------- ---------- ----------
  2    0    0 4166729024      13081   91245882.5 91690487.2 18338.0974 No             .00        .00      11.644 3739546208     657923
  2    1    0 4166729024      13081    120056449  120491925 15061.4907 No             .00        .00      15.889 1250311783     883494
  2    2    0 4166729024      13081   64616814.8 64899824.1 12979.9648 No             .00        .00      10.564  424413836     587066
  2    3    0 4166729024      13081   58282307.7 58454183.2 19484.7277 No             .00        .00       5.442 1809526416     303954
  2    4    2 4166729024      13081   78886102.5 79233558.3 19808.3896 No             .00        .00      13.555 4163654789     756589

Thanks Michael for the information so we have 1 more method which does not need license


Oracle Database Cloud Service

Marc Sewtz - Thu, 2011-11-10 15:56
Learn all about Oracle's Database Cloud Service at my upcoming presentations in New York and Birmingham, UK. Announced at Oracle Open World 2011, the Oracle Public Cloud is a set of integrated services that provide access to Oracle Fusion Applications, Oracle Fusion Middleware, and the Oracle Database. The focus of my sessions will be on the Database Cloud Service, which includes Oracle Application Express, access to the Oracle Database using RESTful Web Services and a set of business productive application, which are based on Oracle Application Express and can be installed with just a few clicks.

UKOUG, Birmingham, UK, 12/06/2011

Customize/Personalize Oracle APEX Workspace Environment

Ittichai Chammavanijakul - Wed, 2011-11-09 16:06

When you have multiple Oracle APEX environments, e.g., development, test, UAT and production, and for multiple groups, one of the confusing things is that sometimes developers are lost on which environment they’re working on. Most of technically proficient developers can make a distinction easily from the URLs they’re currently using. However, most of less technically proficient (e.g., business users) sometimes cannot.

A quick and simple solution here is to make a visual distinction on the APEX workspace pages especially on the logo area. Instead of using plain vanilla logo images, they are replaced with more distinct and informative images.

This is the default APEX workspace logon page.

Default Workspace Logon Image

This is a sample of the personalized workspace logon page with a company logo and it is showing that this is the development environment.

Customized APEX workspace logon page

This is the main workspace page after logon showing the default logo image.

Default Workspace Page

You can change it to be like this.

Customized APEX workspace development page

With just this minor change, our users feel more comfortable knowing that they’re working the right environment.

How to change it?

With a quick investigation, you can easily find the paths of those image files as follows:

Workspace logon page:

Image path: /i/apex/builder/apex-logo-white.gif
Image size: 300px x 30px
Transparent background

Image path:/i/apex/builder/apex-db-apps.png
Image size: 240px x 200px
Transparent background

Workspace main page after logon:
Image path: /i/htmldb/apex_logo.gif
Image size: 300px x 30px
White background

  • Use Photoshop or any image editing tools to recreate those image files.
  • Copy and replace those image files on the web server. Please make the backup of those files before replacing them.

Note that this approach will NOT work if you’re using a shared web server for multiple APEX database environments because they’re using the same image files.

Update: Using ApexLib script in the login message (under Manage Instance) as mentioned by Peter in the comment section is another solution for customization. I like this approach as not only you can customize the logo, but you can also change the description text on the workspace logon page. In addition, since the change is on the APEX instance itself, it can definitely be used on a shared web server.

Categories: DBA Blogs

NFC and HTML5 emerging as Winners on Mobile

Khanderao Kand - Wed, 2011-11-09 13:03
Near Field Communication(NFC) ( for more details read my earlier blog http://khanderaotech.blogspot.com/2011/05/nfc-getting-momentum.html) is very useful in very short distance communication ideal for mobile devices. It has got another backer that is from RIM. RIM is bating high on NFC based Apps as its one of the main four focus area (according to its Dev relations VP Saunders).

This week there is another from Adobe. It seems that it is stopping further development on MObile Flash and bating on HTML5 which they should have done it long back after listening to Steve Jobs. Anyway, recently Adobe taking good steps. Adobe has recently acquired Phone Gap which is an opensource for developing HTML5 based hybrid apps of variety of mobile devices like iPhone and Android. We are using it at myBantu. In this case 90% code base is independent of the mobile device. (HTML5 related my blog entry: http://texploration.wordpress.com/2011/09/28/html5-winning-high-volume-websites/ )

Logger Project Moved Temporarilly

Tyler Muth - Wed, 2011-11-09 08:43
The site samplecode.oracle.com was decommissioned recently. I was hosting a number of projects there including “Logger”, my PL/SQL instrumentation package. Until I find a new home, here’s a temporary link to the latest release (1.4) or if you just want to view the readme use this link. I’ll update this post when I decide on […]
Categories: DBA Blogs, Development

ADF Faces - a logic bomb in the order of bean instantiations

Chris Muir - Tue, 2011-11-08 22:02
One of my talented colleagues discovered an interesting ADF logic bomb which I thought I'd share here. The issue is with the instantiation order of ADF Faces scoped beans in JDev 11g when using Bounded Task Flows embedded as regions in another page.

Regular readers would be familiar that Oracle's ADF solution is built on top of JavaServer Faces (JSF). ADF supports bean scopes such as ViewScope, PageFlowScope and BackingBeanScope on top of the regular JSF ApplicationScope, SessionScope and RequestScope beans. Readers should also be familiar that the beans have a defined life (ie. scope) as detailed in the JDev Web Guide:

(Source: Oracle JDeveloper Web Guide 11.1.2.1 section 5.6 figure 5-11)

In specifically focusing on the life cycle of ADF PageFlowScope and BackingBeanScope beans, the guide states (to paraphrase):

1) A PageFlowScope bean for a Task Flow (either bounded or unbounded) survives for the life of the task flow.

2) A BackingBeanScope bean for a page fragment will survive from when receiving a request from the client and sending a response back.

The implication of this is when we're using Bounded Task Flows (BTFs) based on page fragments, it's typical to have a PageFlowScope bean to accept and carry the parameters of the BTF, and one or more BackingBeanScope beans for each fragment within the BTF.

Sample Application

With this in mind let's explore a simple application that shows this sort of usage in play. You can download the sample application from here.

The application's Unbounded Task Flow (UTF) includes a single SessionScope bean MySessionBean carrying one attribute mySessionBeanString as follows:
public class MySessionBean {

private String mySessionBeanString = "mySessionBeanValue";

public MySessionBean() {
System.out.println("MySessionBean initialized");
}

public void setMySessionBeanString(String mySessionBeanString) {
this.mySessionBeanString = mySessionBeanString;
}

public String getMySessionBeanString() {
return mySessionBeanString;
}
}
Note the System.out.println on the constructor to tell us when the bean is instantiated.

The UTF also includes a single page MyPage.jspx containing the following code:
<?xml version='1.0' encoding='UTF-8'?>












In MyPage.jspx note the inputText control to output the SessionScope String from MySessionBean, and a commandButton to submit any changes. Second note the region containing a call to a BTF entitled FragmentBTF. However let's put the region aside for a moment and talk about the inputText and SesssionScope bean.

When this page is first rendered the inputText makes reference to the SessionScope variable. JSF doesn't pre-initialize managed beans, only on first access do they get instantiated. As such as soon as the inputText is rendered we should see the message from the MySessionBean constructor when it accesses the mySessionBeanString via the EL expression:

MySessionBean initialized

If we were to comment out the region, run this page and press the commandButton, we would only see the initialized message once, as the session bean lives for the life of the user's session.

Now let's move onto considering the region and embedded Bounded Task Flow (BTF) called FragmentBTF.xml. Points of note for the BTF are:

a) The Task Flow binding has its Refresh property = ifNeeded

b) And the BTF expects a parameter entitled btfParameterString, which takes its value from our SessionScope beans variable:

In terms of the FragmentBTF (as separate to the region/task flow binding) it has the following characteristics:

a) The BTFs has its initializers and finalizers set to call a "none" scope TaskFlowUtilsBean to simply print a message when the task flow is initialized and finalized. This will help us to understand when the BTF restarts and terminates.

b) The BTF has one incoming parameter btfParameterString. To store this value the BTF has its own PageFlowScope bean called MyPageFlowBean, with a variable myPageFlowBeanString to carry the parameter value.
public class MyPageFlowBean {

private String myPageFlowBeanString;

public MyPageFlowBean() {
System.out.println("MyPageFlowBean initialized");
}

public void setMyPageFlowBeanString(String myPageFlowBeanString) {
this.myPageFlowBeanString = myPageFlowBeanString;
}

public String getMyPageFlowBeanString() {
return myPageFlowBeanString;
}
}
Again note the System.out.println to help us understand when the PageFlowScope bean is initialized.

c) The BTF contains a single fragment MyFragment.jsff with the following code:
<?xml version='1.0' encoding='UTF-8'?>







Inside the fragment are:

c.1) An inputText to output the current value for the MyPageFlowBean.myPageFlowBeanString. Remember this value is indirectly derived from the btfParamaterString of the BTF.

c.2) A second inputText to output the value from another bean, this time a BackingScopeBean which we consider next.

d) The BackingBeanScope bean is where we'll see some interesting behaviour. Let's explain its characteristics first:

d.1) The BackingBeanScope bean entitled MyBackingBean is managed by the BTF.

d.2) It is only referenced by the MyFragment.jsff within the BTF, via the inputText above in c.2.

d.3) The BackingBeanScope bean has the following code which includes the usual System.out.println on the constructor:
public class MyBackingBean {

private String myBackingBeanString;
private MyPageFlowBean myPageFlowBean;

public MyBackingBean() {
System.out.println("MyBackingBean initialized");

myPageFlowBean = (MyPageFlowBean)resolveELExpression("#{pageFlowScope.myPageFlowBean}");

myBackingBeanString = myPageFlowBean.getMyPageFlowBeanString();
}

public static Object resolveELExpression(String expression) {
FacesContext fctx = FacesContext.getCurrentInstance();
Application app = fctx.getApplication();
ExpressionFactory elFactory = app.getExpressionFactory();
ELContext elContext = fctx.getELContext();
ValueExpression valueExp = elFactory.createValueExpression(elContext, expression, Object.class);
return valueExp.getValue(elContext);
}

public void setMyBackingBeanString(String myBackingBeanString) {
this.myBackingBeanString = myBackingBeanString;
}

public String getMyBackingBeanString() {
return myBackingBeanString;
}
}
d.4) It contains a variable myBackingBeanString which is referenced via an EL expression by the inputText explained in c.2.

d.5) However note that the constructor of the bean grabs a reference to the PageFlowScope bean and uses that to access the myPageFlowBeanString value, writing the value to the myBackingBeanString.

While this example is abstract for purposes of this blog post, it's not uncommon in context of a BTF for a backing bean to want to access state from the BTF's page flow scope bean. As such the technique is to use the JSF classes to evaluate an EL expression to return the page flow scope bean. This is what the resolveELExpression method in the backing bean does, called via the constructor and given to a by-reference-variable in the backing bean to hold for its life/scope.

At this point we have all the moving parts of our very small application.

Scenario One - Initialization

Let's run through the sequence of events we expect to occur when this page renders for the first time agaom, this time including the BTF-region processing as well as the parent page's processing:

1) From earlier we know that when the page first renders we'll see the SessionScope bean's constructor logged as the inputText in MyPage.jspx accesses mySessionBeanString.

MySessionBean initialized

2) Next as the region in MyPage.jspx is rendered, the FragmentBTF is called for the first time and we can see two log messages produced:

MyPageFlowBean initialized
Task Flow initialized


It's interesting we see the PageFlowScope bean instantiated before the Task Flow but this makes sense as the MySessionBean.mySessionBeanString needs to be passed as a parameter to the BTF before the BTF actually starts.

3) As the MyFragment.jsff renders for the first time, we then see the MyBackingBean initialized for the first time:

MyBackingBean initialized

So to summarize at this point by just running the page and doing nothing else, the following log entries will have been shown:

MySessionBean initialized
MyPageFlowBean initialized
Task Flow initialized
MyBackingBean initialized


In turn the page looks like this, note the value from the MySessionBean pushing itself to the MyPageFlowBean and the MyBackingBean:

The order the beans are instantiated and the values down the page all makes logical sense when we've an understanding of how the page is rendered.

Scenario 2 - The logic bomb

With the page now running we'll now investigate how there's a bomb in our logic.

Up to now if we've been developing an application based on this structure, we've probably run this page a huge amount of times and seen the exact same order from above. One of the problems for developers is we start and stop our application so many times, we don't use the system like a real user does where the application is up and running for a long time. This can hide issues from the developers.

With our page running, say we want to change the SessionScope bean's value. Easy enough to do, we simply change the value in the mySessionBeanString inputText:

When we press the "Page Submit" commandButton embedded in MyPage.jspx, given our understanding so far we'd expect the following behaviour:

1) As the session scope bean lives for the life of the user's session, we don't expect to see the bean newly instantiated.

2) Because the region's task flow binding refresh property is set to ifNeeded, and the source value of the btfParameterString has been updated, we expect the BTF to restart. As the content of the region are executed, based on our previous understanding logically we should see the following log entries:

Task Flow finalized
MyPageFlowBean initialized
Task Flow initialized
MyBackingBean initialized


(Note the Task Flow finalized message first. This is separate to this discussion but given the BTF is restarting, the previous BTF instance need to be finalized first).

Yet the actual log entries we see are:

MyBackingBean initialized
Task Flow finalized
MyPageFlowBean initialized
Task Flow initialized


And the resulting page looks like this:

Something definitely fishing is going on here. In the logs we see the BackingBean is now initialized before the previous Task Flow is finalized and before the PageFlowScope bean is instantiated. In turn on the running page we can see the "fishy" value has made it to the PageFlowScope bean but not the BackingBean. What's going on?

The explanation is simple enough based on 2 rules we've established in this post:

1) Firstly we know beans are only instantiated on access.

2) In returning to the Oracle documentation the scope of a BackingBean is:

"A BackingBeanScope bean for a page fragment will survive from when receiving a request from the client and sending a response back."

With these two rules in mind, when we consider the first scenario, the reason the BackingBean is instantiated after the PageFlowScope bean is because the contents of the BTF fragment are rendered after the BTF is started. As such the access to the BackingBean is *after* the PageFlowScope bean as the fragment hasn't been rendered yet.

With the second scenario, as the fragment is already rendered on the screen, the reason the BackingBean is instantiated before the PageFlowScope bean (and even the termination and restart of the BTF) is the incoming request from the user will reference the BackingBean (maybe writing values back to the bean) causing it to initialize at the beginning of the request as per rule 2 above. Officially "Erk!". Then as the BackingBean in its constructor references the PageFlowScope bean, it gets a handle on the previous BTF instance's PageFlowScope bean as the new BTF instance has yet to start and create a new PageFlowScope instance with the new value passed from the caller, and thus why we see the old value in the page for myBackingBeanString.

The specific coding mistake in the examples above is the retrieval of the PageFlowScope bean in the BackingBeanScope's constructor. The solution is that any methods of the BackingBeanScope that require the value from the PageFlowScope should resolve access to the PageFlowScope each time it's required, not once in the constructor. If you consider the blog post References to UIComponents in Session-Scope beans by Blake Sullivan you'll see a number of techniques for solving this issue.

Conclusion

Understanding the scope of beans is definitely important for JSF and ADF programming. But the scope of a bean doesn't imply the order of instantiation, and the order of instantiation is not guaranteed so we need to be careful our understanding doesn't make assumptions about when beans will be in/out of scope.

Readers who are familiar with JSF2.0 know that CDI and Annotations will work around these issues. However for ADF programmers CDI for the ADF scoped beans is currently not a choice (though this may change). See the Annotations section of the ADF-JSF2.0 whitepaper from Oracle.

Errata

This blog post was written against JDev 11.1.1.4.0.

Moved to a new blog

Vikas Jain - Mon, 2011-11-07 16:15
I've moved to a new blogging platform provided by my employer Intel at http://blogs.intel.com/cloud-access-security/
Hope, you will follow my posts there.

wish list for 12c

Nuno Souto - Sat, 2011-11-05 07:59
Back in 2008 my wish list included sparse object allocation (deferred allocation in Oracle's translation).  And bingo, I got it in 11gr2! It was almost as if someone came here for inspiration! Prior to that in my "No Moore" series I actually described the Exadata architecture, long before it became public. Not that such a thing would ever be acknowledged by Oracle, we know that!  After all, I Noonsnoreply@blogger.com9

datapump gives wrong privileges

Freek D’Hooge - Wed, 2011-11-02 13:58

When performing an export / import using datapump or with the legacy exp / imp utilities, a bug can cause wrong privileges to be granted to users.
The circumstances under which the bug occurs seems to be that a privilege is given on one user with grant option and on a second user (or role) without grant option and that the object must be a schema procedural object (job, program, schedule, …).

I could reproduce this issue on 10.2.0.4, 11.2.0.2 and 11.2.0.3, so changes are that all versions since 10.2 (or even 10.1) are affected.

SQL> @reproduce.sql
SQL> set feedback on
SQL> set linesize 120
SQL> set pages 9999
SQL> set trimspool on
SQL>
SQL> select
  2    banner
  3  from
  4    v$version
  5  ;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

5 rows selected.

SQL>
SQL> drop user fdh cascade;

User dropped.

SQL> drop user grant_test_usr;
drop user grant_test_usr
          *
ERROR at line 1:
ORA-01918: user 'GRANT_TEST_USR' does not exist

SQL> drop user grant_test_usr2;
drop user grant_test_usr2
          *
ERROR at line 1:
ORA-01918: user 'GRANT_TEST_USR2' does not exist

SQL> drop role grant_test_role;
drop role grant_test_role
          *
ERROR at line 1:
ORA-01919: role 'GRANT_TEST_ROLE' does not exist

SQL>
SQL> select
  2    directory_path
  3  from
  4    dba_directories
  5  where
  6    directory_name = 'DATA_PUMP_DIR';

DIRECTORY_PATH
------------------------------------------------------------------------------------------------------------------------
/u01/oracle/oracle1/admin/gunnar/dpdump/

1 row selected.

SQL>
SQL> create user fdh
  2  identified by blabla
  3  default tablespace users
  4  quota unlimited on users
  5  /

User created.

SQL>
SQL> create user grant_test_usr
  2  identified by blabla
  3  default tablespace users
  4  quota unlimited on users
  5  /

User created.

SQL>
SQL> create role grant_test_role
  2  /

Role created.

SQL>
SQL> create user grant_test_usr2
  2  identified by blabla
  3  default tablespace users
  4  quota unlimited on users
  5  /

User created.

SQL>
SQL> BEGIN
  2    dbms_scheduler.create_job
  3      ( job_name          =>  'FDH.TEST',
  4        job_type          =>  'PLSQL_BLOCK',
  5        job_action        =>  'begin null; end;',
  6        repeat_interval   =>  'FREQ=DAILY; BYHOUR=5',
  7        end_date          =>  NULL,
  8        enabled           =>  FALSE,
  9        auto_drop         =>  FALSE
 10      );
 11
 12    commit;
 13  END;
 14  /

PL/SQL procedure successfully completed.

SQL>
SQL> create procedure
  2    fdh.procedure_test
  3  is
  4  begin
  5    null;
  6  end;
  7  /

Procedure created.

SQL>
SQL> grant alter on fdh.test to grant_test_usr with grant option;

Grant succeeded.

SQL> grant alter on fdh.test to grant_test_role;

Grant succeeded.

SQL> grant alter on fdh.test to grant_test_usr2;

Grant succeeded.

SQL>
SQL> grant debug on fdh.procedure_test to grant_test_usr with grant option;

Grant succeeded.

SQL> grant debug on fdh.procedure_test to grant_test_role;

Grant succeeded.

SQL> grant debug on fdh.procedure_test to grant_test_usr2;

Grant succeeded.

SQL>
SQL> select
  2    table_name, grantee, privilege, grantable
  3  from
  4    dba_tab_privs
  5  where
  6    owner = 'FDH'
  7    and table_name in
  8    ( 'TEST', 'PROCEDURE_TEST'
  9    )
 10  order by
 11    table_name, grantee, privilege, grantable;

TABLE_NAME                     GRANTEE                        PRIVILEGE                                GRA
------------------------------ ------------------------------ ---------------------------------------- ---
PROCEDURE_TEST                 GRANT_TEST_ROLE                DEBUG                                    NO
PROCEDURE_TEST                 GRANT_TEST_USR                 DEBUG                                    YES
PROCEDURE_TEST                 GRANT_TEST_USR2                DEBUG                                    NO
TEST                           GRANT_TEST_ROLE                ALTER                                    NO
TEST                           GRANT_TEST_USR                 ALTER                                    YES
TEST                           GRANT_TEST_USR2                ALTER                                    NO

6 rows selected.

SQL>
SQL> /* execute the following part on the os
SQL>    in the data_pump_dir directory
SQL>
SQL> expdp system schemas='FDH' dumpfile=grant_test.dmp
SQL> impdp system schemas='FDH' dumpfile=grant_test.dmp sqlfile=grant_test_dump.txt
SQL>
SQL> grep GRANT grant_test_dump.txt
SQL> */
SQL>
SQL>
SQL> !
[oracle1@elin ~]$ cd /u01/oracle/oracle1/admin/gunnar/dpdump/
[oracle1@elin dpdump]$ expdp system schemas='FDH' dumpfile=grant_test.dmp

Export: Release 11.2.0.2.0 - Production on Wed Nov 2 19:49:15 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Password:

UDE-28002: operation generated ORACLE error 28002
ORA-28002: the password will expire within 6 days

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** schemas=FDH dumpfile=grant_test.dmp
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
Processing object type SCHEMA_EXPORT/POST_SCHEMA/GRANT/PROCOBJ_GRANT
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /u01/oracle/oracle1/admin/gunnar/dpdump/grant_test.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 19:49:49

[oracle1@elin dpdump]$ impdp system schemas='FDH' dumpfile=grant_test.dmp sqlfile=grant_test_dump.txt

Import: Release 11.2.0.2.0 - Production on Wed Nov 2 19:49:57 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Password:

UDI-28002: operation generated ORACLE error 28002
ORA-28002: the password will expire within 6 days

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_SQL_FILE_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_SQL_FILE_SCHEMA_01":  system/******** schemas=FDH dumpfile=grant_test.dmp sqlfile=grant_test_dump.txt
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
Processing object type SCHEMA_EXPORT/POST_SCHEMA/GRANT/PROCOBJ_GRANT
Job "SYSTEM"."SYS_SQL_FILE_SCHEMA_01" successfully completed at 19:50:02

[oracle1@elin dpdump]$ grep GRANT grant_test_dump.txt
-- new object type path: SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
GRANT DEBUG ON "FDH"."PROCEDURE_TEST" TO "GRANT_TEST_USR" WITH GRANT OPTION;
GRANT DEBUG ON "FDH"."PROCEDURE_TEST" TO "GRANT_TEST_ROLE";
GRANT DEBUG ON "FDH"."PROCEDURE_TEST" TO "GRANT_TEST_USR2";
-- new object type path: SCHEMA_EXPORT/POST_SCHEMA/GRANT/PROCOBJ_GRANT
SYS.DBMS_UTILITY.EXEC_DDL_STATEMENT('GRANT ALTER ON "FDH"."TEST" TO "GRANT_TEST_USR" WITH GRANT OPTION');
SYS.DBMS_UTILITY.EXEC_DDL_STATEMENT('GRANT ALTER ON "FDH"."TEST" TO "GRANT_TEST_ROLE" WITH GRANT OPTION');
SYS.DBMS_UTILITY.EXEC_DDL_STATEMENT('GRANT ALTER ON "FDH"."TEST" TO "GRANT_TEST_USR2" WITH GRANT OPTION');

As you can see, all three grants on fdh.test are now including the “with grant option” clause, while only the grant to grant_test_usr should have been using the “with grant option”.
The privileges on the procedure are correct.
I have logged a case about this with Oracle, but no bug number is assigned at this moment


Categories: DBA Blogs

Is SQL*Plus Resumable?

Jeff Hunter - Wed, 2011-11-02 08:15
I am using resumable space for a lot of my operations. However, it seems as though the SQL*Plus copy command doesn't respect the resumable feature? Note, I am issuing an "alter session enable resumable timeout 14400" through a login trigger. I know this works because if I do the same type of operation through an INSERT/SELECT through a dblink, my session waits for more space to be added.

Oracle NoSQL Database Slides From HPTS

Charles Lamb - Wed, 2011-11-02 05:47

Here are my slides from my HPTS. There are some slides with performance figures starting at slide 28.


Tailoring Fusion CRM

Peter O'Brien - Tue, 2011-11-01 17:36
The journey for the Fusion CRM development team has been a long one. What a great feeling for all of us when Fusion Applications was officially released this year as Generally Available. During his keynote speech at this year's Oracle OpenWorld Steve Miranda reiterates that statement. Also during his session there were some great demonstrations of Fusion CRM. Anthony Lye demonstrated (about 25 minutes into the presentation) how the application can be extended at run time. Design time at run time features are made possible by a number of Fusion Middleware technologies including WebCenter and MDS (pdf 592kb). Extending the application, as Anthony Lye demonstrated, is one form of tailoring that can be performed with the out of the box application. Other forms of tailoring include personalisation and customisation (personalization and customization in the documentation). Both concepts are very similar in that changes to the application are persisted via MDS but they are differentiated by the scope of the change and for whom the change is made.

Available to all users.
Put simply, personalisation is a tailoring action performed by an end user for themselves to set their own preferences for how they work with the application. These changes can be to include dynamic content such as Business Intelligence reports or a syndication feed from an external source. The key point is that these are personal preferences explicitly specified by the user for the user by editing the page via the WebCenter Page Composer. A user can also perform implicit personalisation of the Fusion CRM application without ever touching Page Composer simply by hiding columns in a table, or rearranging their order relative to each other by dragging and dropping. These personal preferences are also reflected in the shared components across Fusion CRM including the Activities UI components for Notes, Interactions, Appointments and Tasks. Setting a preference for the display width of the comments column for Tasks in the Marketing application is reflected in Opportunity Management.

Only available to users with Administration privileges.In the context of Fusion CRM, customisation is a modification made by an administrator, or someone with the appropriate permissions, to the application for a set of users. This set, referred to as Customization Level, can be defined as all users for the installed application, internal users, external users or users with a particular set of roles. For the Partner Relationship Management functionality in Fusion CRM V1 the internal vs external layer was introduced so that a Channel Administrator could tailor how they wanted their partners (external users) to see the Fusion CRM screens. Both internal and external users are interacting with the same screen, but the external users may see a lot more branding and content more suitable for partners rather than employees.

From a development perspective, MDS terminology can make things a bit confusing because irrespective of the nature of change, MDS refers to it as a customisation. There are many cross platform components used in Fusion CRM that are individual products in themselves. Getting to grips with the terminology used by these different product teams and understanding the context has been a challenge, but really rewarding when you see the results.

Are you in Higher Education and use APEX?

David Peake - Wed, 2011-10-26 19:08

For some time I have been interested in furthering the use of Oracle Application Express in higher education.
There are a number of institutions currently using Application Express either as part of their curriculum or for internal use.
One of the big problems is knowing who else in higher education is using Oracle Application Express and SQL Developer.

Therefore to provide a forum for people to come together I have set up a LinkedIn Group: Oracle APEX Educators SIG
Please join this group as a way to communicate ideas, collateral and knowledge.

Cheers,
David

Pages

Subscribe to Oracle FAQ aggregator