SQL 101 Class, Session 3

Slides for session 3 today…. covering Number functions.



Column formatting in a CASE statement

I have a daily RMAN report that I send to myself and I wanted to color code the duration of the backup… for instance, backups taking less than 100 minutes were green, between 100 and 300 minutes, orange, over that red.

Currently, I’m using the following to format the column in the HTML output:

column “B/Up Duration in mins” ON HEADING ‘B/Up Duration!in mins’ format 999.99

Well, that works fine until I put that column into a CASE statement.  It still splits the heading on two lines but no longer gives me the number format I wanted.  Also, I needed to alias the column so the column formatting would take affect… to get around the two problems, here’s what I did.  First, I format the column within the CASE statement and then put the alias at the end of the CASE statement as seen below:


    WHEN elapsed_seconds/60 < 100 then

      ‘<span class=”threshold-ok”>’||rpad(trunc(elapsed_seconds/60,2),4,0)||'</span>’

   WHEN elapsed_seconds/60 between 100 and 300 then

       ‘<span class=”threshold-warning”>’||rpad(trunc(elapsed_seconds/60,2),5,0)||'</span>’


        ‘<span class=”threshold-critical”>’||rpad(trunc(elapsed_seconds/60,2),5,0)||'</span>’

   END as “B/Up Duration in mins”


Stopping/Starting RAC instances for a rolling patch

You’ve done a rolling patch for a RAC test environment right?  And some of you have multiple instances running on the same node for testing, development, training, whatever… it can be a pain taking all those instances up and down.

Patching has come a long way with Oracle and the rolling patch is a beautiful thing compared to what it used to be, however, it’s still a pain to take down each instance on a particular node especially when you have more than a few when doing a rolling patch.

So… I’ve created a customizable script for doing just that.  It will take down the instances you want on each node and restart them before moving on to the next node.  This script was built with our PSoft environment in mind.


Comparing exp with expdp + compression=all

What a difference between EXP and EXPDP with compression as seen below:

Just a quick note on the difference between running old school EXP and the somewhat newer EXPDP with COMPRESSION=ALL turned on…. if using COMPRESSION=ALL, it’s only available in 11g EE and you must pay the licensing fee for Advanced Compression to use “ALL” with the COMPRESSION parameter.

1st, I run the exp on a single mount point with the max recordlength=65535 and direct=y… this exp ran for 71 minutes and consumed roughly 45GB:

-rw-r–r– 1 oracle oinstall 45556327575 Oct 30 11:48 full_exp.dmp

The 2nd run was with expdp over 3 mount points and compression=all… it ran for 41 minutes and consumed roughly 8+GB:

-rw-r—– 1 oracle asmadmin 2147483648 Oct 30 13:54 tstht90_a01.dmp

-rw-r—– 1 oracle asmadmin 2147483648 Oct 30 13:54 tstht90_a02.dmp

-rw-r—– 1 oracle asmadmin 2147483648 Oct 30 13:55 tstht90_b01.dmp

-rw-r—– 1 oracle asmadmin 1443262464 Oct 30 14:08 tstht90_b02.dmp

-rw-r—– 1 oracle asmadmin  516943872 Oct 30 14:08 tstht90_c01.dmp

-rw-r—– 1 oracle asmadmin 1042018304 Oct 30 14:08 tstht90_c02.dmp

Continue reading

Scripting a People Soft clone with RMAN

I’ve done this with E-Biz so I thought I’d do it with People Soft.  Now before you go off trying this script, TEST IT FIRST!  If it blows up your database, you don’t know me!  Also, this will not work unless you’re using a catalog/repository for RMAN

This script is really made up of 3 parts:

1.the addition of some code in your RMAN backup script (assuming you already have one that is) that builds an RMAN run module for the clone build, cleans up the previous days SCP and then SCP’s your rman backups and run modules to the clone box


2.the main cloning script.  the script assumes you’re using ASM and you may need to fiddle with the script to fit your ASM structure, or if not using ASM, just comment it out, and


3.the ASM cleanup script (if you need it).


Now I’m sure there are a million ways to get this done and this may not be the most efficient…. but it works.  As you can see from the main cloning script, we don’t do a lot of post-clone stuff with the people soft clones as we normally leave that up to the app developers.

If you see any glaring errors, please let me know.

scheduler unavailable at SYS.DBMS_ISCHED

This is one of those scenarios where you get down in the weeds trying to fix a problem and can’t see the forest for the trees.  Sometimes I see the problem and get right intto the weeds without taking a step back and looking at a simpler view.  This is one of those problems.

Ever seen this when submitting a job (either via OEM or from the command line)?

java.sql.SQLException: ORA-27492: unable to run job “SYS.GATHERSCHEMASTATS_JOEBOB”: scheduler unavailable ORA-06512: at “SYS.DBMS_ISCHED”, line 185 ORA-06512: at “SYS.DBMS_SCHEDULER”, line 486 ORA-06512: at line 2

This was running, or trying to run, against a database I’ve had little interaction with and is more of a backend for a 3rd party appliance.  I dug and dug and was really scratching my head…. opened an SR with MOS.  It took him about two minutes to figure it out because he didn’t get down into the weeds…. the answer?

JOB_QUEUE_PROCESSES.  yep.  set to ZERO.  Not too many scheduler jobs are going to run with that setting.