ORACLENERD

DBMS_UTILITY.WAIT_ON_PENDING_DML

I've been reading up on Edition-Based Redefinition (which I can't link to because the docs seem to be missing right now).

In my readings I ran across DBMS_UTILITY.WAIT_ON_PENDING_DML which appears to be new to 11gR2 (there is no entry for it in the 11gR1 docs, which again, I can't link up currently). Based on my reading (I have the docs locally), this function appears to be used specifically for the new editioning feature.

It's listed as a procedure in the docs (don't ask for the link):

and a function if you do a describe on dbms_utility.

This procedure waits until all transactions (other than the caller's own) that have locks on the listed tables and began prior to the specified SCN have either committed or been rolled back.

So I wanted to see how it works.

First, I create the table:

CREATE TABLE s
(
  x NUMBER
);

I then open up a second session. In that session, I will run the WAIT_ON_PENDING_DML function which should monitor session 1.

Session 1:

BEGIN
  FOR i IN 1..100 LOOP
    INSERT INTO s ( x ) 
    VALUES ( DBMS_RANDOM.VALUE );
    dbms_lock.sleep( .5 );
  END LOOP;
  COMMIT;
END;
/

I'm inserting 100 records with a wait of half a second between inserts. This should take a little over 50 seconds.

Session 2

Note I've set the timeout to 30 seconds so that it will finish prior to Session 1

DECLARE
  l_bool BOOLEAN;
  l_scn NUMBER;
BEGIN
  l_bool := dbms_utility.wait_on_pending_dml
              ( tables => 'cjustice.s',
                timeout => 30,
                scn => l_scn );

  dbms_output.put_line( 'scn:  ' || l_scn );
  IF l_bool THEN
    dbms_output.put_line( 'true' );
  ELSE
    dbms_output.put_line( 'false' );
  END IF;
END;
/

scn:  924643
false

PL/SQL procedure successfully completed.

Elapsed: 00:00:30.03

The return value of "false" means that Session 1 is still inserting, which I confirmed visually (I should really create a video for this). Once Session 1 completed, I reran Session 2's anonymous block and received these results:

CJUSTICE@TESTING>/
scn:  924773
true

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

In regards to editioning, you would poll the database using this utility to see if there is, well, any pending DML. One aspect of Editioning allows you to create temporary triggers that will help you to migrate your application to the latest version without worrying about locking or timeouts. Pretty cool stuff.

I'll have more Editioning soon.

Labels: 11g, dbms_utility, edition, oracle

¶ 11/15/2009 02:53:00 AM 0 Comments Links to this post

11gR2: Installfest

I installed Oracle Enterprise Linux 5.4 a few weeks ago in a VirtualBox virtual machine...I just didn't have the inclination to install the database.

Way back in June, I installed VirtualBox, OEL (5.3), 11gR1 and ApEx. To do that, I followed this guide on OTN by John Smiley (thanks John!). This time, I was going to try to do it from the docs. I can't say it was easy to read, so I was back at John's article before to long.

First up was preparing OEL for the database install. This included setting up kernel parameters and loading some extra libraries. There were slight differences between OEL 5.3 and 5.4, but I had the docs handy...and the installer complained loudly if something was amiss.

Next up, mount a shared folder in the virtual machine so that I could keep it to a relatively small size, 15 GB to be exact. Plus, I wanted to use the shared folders functionality. So I created a directory in the /mnt directory called software (as root of course), then mounted the shared folder.

root@oracledb#mkdir software
root@oracledb#mount -t vboxsf software /mnt/software

"software" was the name I gave the shared folder through the VirtualBox interface, that mapped to /opt/software on my host machine (ubuntu 9.04).

Then I downloaded the 11gR2 software, 2 files, linux_11gR2_database_1of2.zip and linux_11gR2_database_2of2.zip. I then used Archive Manager to extract the files. Strangely, it created 2 folders, "database" and "database(2)." That can't be right.

Back to OEL and I cd'd to the directory, /mnt/software/11gR2/database and ran the installer.

I did not provide my email address and I unchecked the box that would send me updates from Oracle Support.

Selected Yes to continue.

I'm just accepting the defaults here.

Not really sure what the difference is between a Desktop Class and Server Class install, so I again accepted the default (Desktop).

Now I have to fill in 4 fields:
* OSDBA Group (drop down)
* Global database name
* Administrative password
* Confirm password

* OSDBA Group = oracle (for me)
* Global database name = testing
* Administrative password = testing, whoops, strong password rules, not testing
* Confirm password = same as above

I chose "oracle" as the oraInventory Group Name.

Some prerequisite checks, to make sure I have the right libraries and the kernel parameters are OK.

This is when I realized the values were different between 11gR1 and 11gR2 and went back to the docs to get the correct ones. I realized afterwards though, that if you select the particular failure, it will tell you what the values should be. Nice.

After I went back and changed the values, I only had a failure on the memory check, but I checked the "Ignore All" checkbox and proceeded forward.

(I missed a screen shot the second go-around, so ignore the OS Kernel Parameters and Package errors.

Confirmation screen.

And were off!

I like to take a lot of pictures.

Creating the database...

Screen behind the screen.

Almost done.

Post-installation tasks.

Running /u01/app/oraInventory/orainstRoot.sh

Running /u01/app/oracle/product/11.2.0/db_home_1/root.sh

Done.

And done.

I can login from the virtual machine...

And I can login from the host machine.

Enterprise Manager from the host machine.

And my new account from the host machine.

Easy right?

Special thanks to Tim Hall as well, he's got a great primer for installing 11gR2, Oracle Database 11g Release 2 (11.2) Installation On Enterprise Linux 5.0.

Almost forgot, my problem with Archive Manager creating "database" and "database(2)"? Solved by twitter naturally:

So I unzipped the 2 files from a terminal window and it (apparently) put everything in the same folder. Win.

Labels: 11g, database, howto, install, oracle

¶ 11/13/2009 02:22:00 AM 2 Comments Links to this post

11g New Feature: PIVOT

I do have an interview tomorrow. Woohoo!

So my go at the PIVOT operator, "new" in 11g.

pivot_clause
Datawarehouse Guide
SQL Reference Examples
Arup Nanda's Example

My example.

Let's create some data first:


CREATE TABLE transaction_types
(
  transactiontypecode VARCHAR2(10)
    CONSTRAINT pk_transactiontypecode PRIMARY KEY
);

INSERT INTO transaction_types( transactiontypecode )
VALUES ( 'DEBIT' );
INSERT INTO transaction_types( transactiontypecode )
VALUES ( 'CREDIT' );

CREATE TABLE transactions
(
  id NUMBER(10)
    CONSTRAINT pk_id PRIMARY KEY,
  transactiontypecode
    CONSTRAINT fk_ttcode_transactions REFERENCES transaction_types( transactiontypecode )
    CONSTRAINT nn_ttcode_transactions NOT NULL,
  amount NUMBER(16,2)
    CONSTRAINT nn_amount_trans NOT NULL
    CONSTRAINT ck_amount_trans CHECK ( amount >= 0 ),
  date_created DATE DEFAULT SYSDATE
    CONSTRAINT nn_datecreated_trans NOT NULL
);

INSERT INTO transactions
  ( id,
    transactiontypecode,
    amount )
VALUES
  ( 1,
    'DEBIT',
    44.44 );

INSERT INTO transactions
  ( id,
    transactiontypecode,
    amount )
VALUES
  ( 2,
    'DEBIT',
    20.34 );

INSERT INTO transactions
  ( id,
    transactiontypecode,
    amount )
VALUES
  ( 3,
    'CREDIT',
    5.60 );

INSERT INTO transactions
  ( id,
    transactiontypecode,
    amount )
VALUES
  ( 4,
    'DEBIT',
    67 );

INSERT INTO transactions
  ( id,
    transactiontypecode,
    amount )
VALUES
  ( 5,
    'DEBIT',
    234.55 );

INSERT INTO transactions
  ( id,
    transactiontypecode,
    amount )
VALUES
  ( 6,
    'CREDIT',
    76.55 );

INSERT INTO transactions
  ( id,
    transactiontypecode,
    amount )
VALUES
  ( 7,
    'DEBIT',
    3.45 );

So what's the big deal with PIVOT? I'm not sure yet other than it's something new and new is cool.

Basically, PIVOT allows you to pivot rows into columns. We often do this for reports we generate. Here's the old way:


SELECT
  TRUNC( date_created ) date_created,
  SUM( CASE
        WHEN transactiontypecode = 'DEBIT' THEN
          amount
       END ) debit_amount,
  SUM( CASE
        WHEN transactiontypecode = 'CREDIT' THEN
          amount
       END ) credit_amount
FROM transactions
GROUP BY 
  TRUNC( date_created )
ORDER BY date_created;

DATE_CREA DEBIT_AMOUNT CREDIT_AMOUNT
--------- ------------ -------------
31-JUL-08       369.78         82.15

Really, not that bad. But if I want to do COUNT and AVG, I have to create more CASE statements like the ones above. My query will go from 14 lines to 30 in a hurry.

Here's the new cool way:


SELECT *
FROM
  (
    SELECT 
      transactiontypecode tt,
      TRUNC( date_created ) date_created,
      amount 
    FROM transactions
  )
PIVOT
(
  SUM( amount ) total_amount
  FOR tt IN ( 'DEBIT' AS "DEBIT", 'CREDIT' AS "CREDIT" )
);

DATE_CREA DEBIT_TOTAL_AMOUNT CREDIT_TOTAL_AMOUNT
--------- ------------------ -------------------
31-JUL-08             369.78               82.15

It happens that is 14 rows as well. Now I'll add AVG and COUNT:

SELECT *
FROM
  (
    SELECT 
      transactiontypecode tt,
      TRUNC( date_created ) date_created,
      amount 
    FROM transactions
  )
PIVOT
(
  SUM( amount ) total_amount,
  COUNT( amount ) total_count,
  AVG( amount ) avg_amount
  FOR tt IN ( 'DEBIT' AS "DEBIT", 'CREDIT' AS "CREDIT" )
);

DATE_CREA      D_AMT      D_CNT      D_AVG      C_AMT      C_CNT      C_AVG
--------- ---------- ---------- ---------- ---------- ---------- ----------
31-JUL-08     369.78          5     73.956      82.15          2     41.075

Nice! Sixteen lines of SQL...not bad at all. That should make code a bit more readable (it'll fit on a single page)...I like it!

Labels: 11g, development, sql

¶ 7/31/2008 05:15:00 PM 0 Comments Links to this post

11g New Feature: Virtual Columns

Not really so new as it's more than a year old...but I've finally had the opportunity to check it out. Well, opportunity at my last job which ended on Monday, but since I have so much free time now, I figured I give it a run.

Virtual Columns

Defined in the New Features doc as:

Virtual columns are defined by evaluating an expression the results of which become the metadata of the columns for tables. Virtual columns can be defined at table creation or modification time.

Virtual columns enable application developers to define computations and transformations as the column (metadata) definition of tables without space consumption. This makes application development easier and less error-prone, as well as enhances query optimization by providing additional statistics to the optimizer for these virtual columns.

So what's it good for?

One thing that I see is that any derived column or expression can be placed at the table level (and indexed too!). This as opposed to putting it in a view and taking the chance that the logic is repeated exactly in every location.

So here goes my example:


CREATE TABLE virtual_columns
(
  id NUMBER
    CONSTRAINT pk_id PRIMARY KEY,
  first_name VARCHAR2(30),
  middle_name VARCHAR2(20),
  last_name VARCHAR2(40),
  full_name AS
  ( CASE
      WHEN middle_name IS NULL THEN
        first_name || ' ' || last_name
      WHEN middle_name IS NOT NULL THEN
        first_name || ' ' || middle_name || ' ' || last_name
    END ) VIRTUAL
);

INSERT INTO virtual_columns
  ( id,
    first_name,
    middle_name,
    last_name )
VALUES
  ( 1,
    'CHET',
    NULL,
    'JUSTICE' );

INSERT INTO virtual_columns
  ( id,
    first_name,
    middle_name,
    last_name )
VALUES
  ( 2,
    'MICKEY',
    'CARL',
    'MOUSE' );

CJUSTICE@>SELECT * FROM virtual_columns;

        ID FIRST_NAME MIDD LAST_NAME  FULL_NAME
---------- ---------- ---- ---------- ------------------------------
         1 CHET            JUSTICE    CHET JUSTICE
         2 MICKEY     CARL MOUSE      MICKEY CARL MOUSE

2 rows selected.

Very cool stuff indeed! One thing that might be an issue is that if someone does a SELECT * from a table without any filters. If the expression is complex (SQL --> PL/SQL context switching), it might slow it down. Of course I have no evidence to back this up yet...just pondering.

Labels: 11g, database, development, oracle

¶ 7/30/2008 07:08:00 PM 2 Comments Links to this post

Guest Authors

Ted Simpson
Gary Myers
Kellyn Pedersen
John Piwowar
Michael O'Neill

How To