dbms_output and the scheduler

One of the nifty things in 12c is the ability to pick up DBMS_OUTPUT output from your scheduler jobs. So if you haven’t built an extensive instrumentation or logging facility, you’ll still have some details you can pick up from the scheduler dictionary views. Let’s look at an example


SQL> create or replace
  2  procedure do_stuff is
  3  begin
  4    dbms_output.put_line('Commenced');
  5    dbms_lock.sleep(30);
  6    dbms_output.put_line('Working');
  7    dbms_lock.sleep(30);
  8    dbms_output.put_line('Done');
  9   (more...)

Video : CASE Expressions and CASE Statements

Today’s video is about CASE expressions and CASE statements.

The guest in today’s video is Scott Wesley, who is currently plugging his new book called Pro jQuery in Oracle Application Express. He will no doubt use the vast quantities of money he makes from the books sales to throw outlandishly extravagant barbecues, because that’s all they do down under. 🙂

Cheers

Tim…


Video : CASE Expressions and CASE Statements was first posted on June (more...)

Top Reason I’ve Learned Everyone Should Use Delphix- Week 2

I’ve been going through some SERIOUS training in just over a week.  This training has successfully navigated the “Three I’s”, as in its been Interesting, Interactive and Informative.  The offerings are very complete and the knowledge gained is limitless.

I’d also like to send a shout out to Steve Karam, Leighton Nelson and everyone else at Delphix who’s had a hand in designing the training, (more...)

Never …

From time to time a question comes up on OTN that results in someone responding with the mantra: “Never do in PL/SQL that which can be done in plain  SQL”. It’s a theme I’ve mentioned a couple of times before on this blog, most recently with regard to Bryn Llewellyn’s presentation on transforming one table into another and Stew Ashton’s use of Analytic functions to solve a problem that I got stuck with.

Here’s a (more...)

EU Referendum : Oh Hell No!

If you care, you’ve probably heard the UK voted to leave the European Union (EU) yesterday. Suffice to say I’m gutted!

I’ve just deleted most of the content this post because it contained a lot of inflammatory and negative comments. I could question the motives of the leavers, but what good would that do now. Suffice to say, IMHO this is a dark day for the UK.

For all my friends around the world, I (more...)

Granular detail from a summary

We had an interesting question on AskTom a few days ago.  Given a set of 12 values (forecasts in this case), one for each month of the year , can we manufacture a set of weekly forecasts for the same period.  Now it is perhaps a little dubious to “invent” detailed data out of summarised data, but we can come up with a reasonable algorithm for doing so.

We could simply divide each (more...)

Intro to Database Security – Part 4

In the previous parts I talked about database security and the infrastructure part of database security. In this post I will discuss the development aspect of the database. Links to the previous posts: Part 1 – general introduction to security Part 2 – introduction to database security Part 3 – infrastructure aspect of database security […]

HP Systems Management vacancies – Bradford , West Yorkshire

I have recently taken over our Systems  Management team and we have several vacancies for people used to working with the HP toolset. I know my blog is read almost entirely by DBAs but I am sure some of you work with Systems management teams and hopefully you can pass a link on to anyone you think might be interested.

The main tools used are

  • HP Service manager 9.x
  • HP Operations manager for Windows, (more...)

Conditional SQL- 6

An odd little anomaly showed up on the OTN database forum a few days ago where a query involving a table covered by Oracle Label Security (OLS) seemed to wrap itself into a non-mergeable view when written using traditional Oracle SQL, but allowed for view-merging when accessed through ANSI standard SQL. I don’t know why there’s a difference but it did prompt a thought about non-mergeable views and what I’ve previously called “conditional SQL” – (more...)

Query tuning by waste minimization: a real-life example

Today I’d like to share another tuning example from a recent case at work, which in my opinion is good for illustrating typical steps involved in SQL optimization process.

I was handed a poorly performing query with a relatively verbose text, so I will only give the general structure here (it will also prevent me from accidentally disclosing some sensitive information from that application):

SELECT 
       /* long list of columns here */
       ,
	   (select *  (more...)

Oracle DBA vacancy – Bradford, West Yorkshire

My team has a permanent vacancy which almost anyone could apply for.

We are looking for a good DBA with RAC, RMAN and Dataguard skills

We  are thinking that we might wish to take on a Junior DBA who has some skills but maybe not all three listed above.

We would also consider someone who has no real experience  – maybe has used RDBMS at University

Whoever we take-on will get a broad experience of (more...)

With a Modern Storage Infrastructure, Companies Must Find an Excellent Data Management Tool

One of the more “weighty” questions within the IT world is in reference to the value of each company’s particular data.  Many wonder what the true value of protected data is in the long-run, eventually view it as a cost center where money continuously gets used up.  In order to make data work in the favor of a business and to help generate some income, companies must get smarter with their approaches to (more...)

WordPress 4.5.3 Released

If you have a WordPress blog, by the time you read this you will probably already be running WordPress 4.5.3.

It’s a maintenance release that fixes a bunch of nasty things. If your site hasn’t already auto-updated, you should probably log on and give it a nudge. I’ve got 5 different installations and all auto-updated successfully.

Cheers

Tim…


WordPress 4.5.3 Released was first posted on June 21, 2016 at 9:20 pm.
(more...)

Time Slider Bug In Solaris 11: Snapshots Created Only For Leaf Datasets

Time Slider is a feature in Solaris that allows you to open past versions of your files.

It is implemented via a service which creates automatic ZFS snapshots every 15 minutes (frequent), hourly, daily, weekly and monthly. By default it retains only 3 frequent, 23 hourly, 6 daily, 3 weekly and 12 monthly snapshots.

I am using Time Slider on several Solaris 11.x servers and I found the same problem on all of them (more...)

ASMCMD&gt: A Better DU, Version 2

A while ago, I posted a better “du” for asmcmd . Since then, Oracle 12cR2 beta has been released but it seems that our poor old “du” will not be improved.

I then wrote a better “better du for asmcmd” with some cool new features compared to the previous one which was quite primitive.

In this second version you will find :

  • No need to set up your environment, asmdu will do it for you
  • (more...)

My New Book!

After what seems like eons since we first started on it, I’m excited to announce a new book I co-authored is finally out. The book is called “Building Database Clouds in Oracle 12c” and is available on Amazon. Of course, it really isn’t that long ago that we started writing the book, but there’s been a lot happening between then and now!

The book was co-authored with Tariq Farooq and Sridhar Avantsa. Tariq asked me (more...)

When CURSOR_SHARING=FORCE, Does Literal Replacement Always Take Place?

The concept of cursor sharing is simple. If an application executes SQL statements containing literals and if cursor sharing is enabled (i.e. CURSOR_SHARING=FORCE), the database engine automatically replaces the literals with bind variables. Thanks to these replacements, hard parses might be turned into soft parses for the SQL statements that differ only in the literals.

The question raised by the title of this post is: in case cursor sharing is enabled, does literal replacement (more...)

Canberra Event – Next Let’s Talk Database presentation by Richard Foote

Let’s Talk Database – Thursday, 28 July 2016

The next in Richard Foote’s popular Let’s Talk Database series – Let’s Talk Database: Oracle Database 12c – Built for Data Warehousing – is on in Canberra on July 28th. These are free events but due to limited places have often “sold out” in the past, so booking early is recommended to avoid disappointment.

Session Details

The Oracle Database is the leading database in market but it (more...)

Why You Should Use Consultants

As you probably know by now, I’m a consultant. By consultant I don’t mean a person who sits in meetings and talks all day (even though I like to talk), I mean a professional services guy who can do DBA tasks and can design and execute projects. In many cases companies prefer to hire someone […]

Plan Shapes

There are a number of articles, webinars, and blogs online about how to read execution plans, but many of them seem to stop after the the minimum description of the simplest type of plan, so I thought I’d throw out a brief comment on a couple the slightly more complicated things that are likely to appear fairly commonly because you sometimes find plans with very similar shapes but extremely different interpretation.

First: select with scalar (more...)