1. Slowly Changing Dimensions

    Submitted by dmann on Fri, 05/17/2013 - 13:29

    Slowly Changing Dimensions

    This is a concept I have encountered in many systems in slightly different forms but never had a name to tie the concept together. I now know that there are techniques to handle Slowly Changing Dimensions to keep track of data that changes infrequently. Read more about Slowly Changing Dimensions

  2. Profiling PL/SQL Code - How to find out where your PL/SQL is spending its time

    Submitted by dmann on Mon, 03/29/2010 - 12:07
    For 8i->11g the DBMS_PROFILER interface is available. It has basic profiling functionality which includes:
    • The total number of times a line was executed
    • The the minimum, maximum, and average execution time of each line of code
    • How long SQL statements took to execute to completion
    • Code coverage (the actual lines executed during the analysis period
    Read more about Profiling PL/SQL Code - How to find out where your PL/SQL is spending its time

  4. SQL to help diagnose issues in a pinch...

    Submitted by dmann on Fri, 10/03/2008 - 17:19

    My Session Info

    -- My Session Info
select username, sid, serial#, terminal, program, machine from v$session where sid=userenv('sid');

    List Blocking Sessions

    By: Natalka Roshak 
    select s1.username || '@' || s1.machine
  || ' ( SID=' || s1.sid || ' )  is blocking '
  || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
  from v$lock l1, v$session s1, v$lock l2, v$session s2
  where s1.sid=l1.sid and s2.sid=l2.sid
  and l1.BLOCK=1 and l2.request > 0
  and l1.id1 = l2.id1
  and l2.id2 = Read more about SQL to help diagnose issues in a pinch...

  6. "Playing" Nice with Oracle - Using Play Framework 2.1 with Oracle

    Submitted by dmann on Wed, 03/13/2013 - 18:06

    I am throwing together a quickie Play Framework application for a DB process dashboard. I love Play. Its quick, powerful, and does a pretty good job of getting out of your way when you need it to.

    But as a cutting edge open source app framework the documentation is sometimes a little behind the curve... Or assumes you are always using h2 for dev and mySQL for production.

    Here are some tips on getting your new Play app to play nicely with Oracle via JDBC. Read more about "Playing" Nice with Oracle - Using Play Framework 2.1 with Oracle

    Tags: 
    Java
    Play Framework
    oracle
    JDBC

  8. SQL Tuning Advisor - what profile am I accepting?

    Submitted by dmann on Thu, 10/13/2011 - 13:35
    When working on a SQL Tuning issue I often kick off a SQL Tuning Advisor job on the SQL while I gather info about statistics and other things I like to check when tuning. After I get an idea of where performance might be suffering I use the SQL Tuning Advisor results for a second opion. Unfortunately more often than not the SQL Tuning Advisor reports it can dramatically improve the performance of a SQL Statement by using a SQL Profile to force a different plan.
    Read more about SQL Tuning Advisor - what profile am I accepting?
    Tags: 
    sql
    oem
    tuning

  9. Like the v$session_longops progress bar in OEM? You'll love this…

    Submitted by dmann on Wed, 12/13/2006 - 16:31

    One of the redeeming qualities of the Java Oracle Enterprise Manager client is the progress bar that comes up when you are viewing the long operations of a session. I wanted this functionality but didn’t want to wake up the big lug to get this info (it takes a long time to start up the OEM client and navigate to the screen to see the long operations progress bar).

    So I decided to write a short script to emulate that functionality but take it one step further and show me info about _all_ the longops currently running on an instance. Read more about Like the v$session_longops progress bar in OEM? You'll love this…

    Tags: 
    oracle
    perl
    monitoring

  10. References

    Submitted by dmann on Tue, 02/12/2013 - 14:36
    Testing Your Databases with DBUnit by Ankit Mathur - A good overview of why to use DBUnit and how to get some basic stuff done with it.
    Read more about References

  11. DBUnit - Unit Test your Database Schema

    Submitted by dmann on Tue, 02/12/2013 - 00:54

    DBUnit is a JUnit extension which can be used to load a database with a sample of data and then execute tests directly against the database. This can be useful for verifying that you did not mess up existing functionality of the database when adding new features to a schema. It can also enable Test Driven Development for the database functionality. Read more about DBUnit - Unit Test your Database Schema

  12. Including External vbScript Files in PowerDesigner

    Submitted by dmann on Fri, 12/28/2012 - 00:02

    You can automate PowerDesigner by running vbScript directly or accessing the PD application objects from a COM aware language such as C# or VB. At this point I would like to keep everything simple and just run some vbScript from inside of the PowerDesigner Edit/Run Script console.

    To really make some useful scripts in PowerDesigner you are going to want to leverage some shared code. This is easy in C# or VB but not so much in the basic vbScript environment. I can do most of what I want to do in vbScript but there is no obvious/simple way to include external files. Read more about Including External vbScript Files in PowerDesigner

  13. PowerDesigner Annoyances 1

    Submitted by dmann on Sat, 12/22/2012 - 09:16

    Yes, I tagged it with a '1' because I already know there are more to come :)

    I created a notebook on this site to collect information about PowerDesigner. My first entry deals with basic script generation options for Oracle and how to get better scripts that are more error-free when the are generated by PowerDesigner.

    You can check it out here:
    http://ba6.us/PowerDesigner-Basic-Gen-Script-Settings

    -Dave Read more about PowerDesigner Annoyances 1

    Tags: 
    PowerDesigner
    oracle
    DDL
    scripting
    sqlplus

  14. PowerDesigner Generate Script Settings for Oracle

    Submitted by dmann on Fri, 12/21/2012 - 20:38

    PowerDesigner generally does a pretty good job of creating DDL to generate a full database or to generate a delta script to upgrade one structure to another. I am using 12.5 right now and have noticed there are some cases which produce scripts that don't run without errors. Sometimes PD likes to make sure tmp_ tables don't exist so it drops them whether it knows they exist or not. Also sometimes it seems to freak out when dealing with sequences and tries to recreate them all even when just 1 has changed. Read more about PowerDesigner Generate Script Settings for Oracle

  15. PowerDesigner

    Submitted by dmann on Fri, 12/21/2012 - 20:32
    Tips and Tricks for making life with PowerDesigner a little easier.
    Read more about PowerDesigner

  16. DB Links across the Enterprise, a Connectogram with d3.js (Work In Progress)

    Submitted by dmann on Mon, 07/16/2012 - 00:43

    The Idea

    I had been seeing a lot of chord and connectogram types of visualisations and an idea immediately popped into my mind. I have a homegrown system that collects information from 500+ databases every evening. The plumbing was in place to talk to the databases so if I ever decided I wanted to hold more information I could always wrap it into the procedure and have it available the next morning after the data collection ran. I wanted a way to visualize the DB link connections between all of these databases.
    Read more about DB Links across the Enterprise, a Connectogram with d3.js (Work In Progress)

  17. Application Express Dynamic Actions with d3.js

    Submitted by dmann on Thu, 08/09/2012 - 00:56

    This tutorial will show you how to get data dynamically into a d3.js graph in your Apex application. I did all my work on http://apex.oracle.com so if you want to mess around you can grab your own free account there and give it a shot. Read more about Application Express Dynamic Actions with d3.js

  18. To Do

    Submitted by dmann on Sat, 06/16/2012 - 15:52

    Blog posts and Projects - In Progress or Would Like to Do

    Moving Beyond Created Date / Created User

    Apex/Ajax/Javascript nuts and bolts - technique

    DBMS Parsingmentioned here

    D3.js + Apex Research + Presentation

    DBMS_SQL, parsing your queries - can you get info without running your SQL?

    Siebel Extension Tables + Golden Gate

    External tables to move large tables (LOB, etc) Read more about To Do

  19. Updated Heat Map Report

    Submitted by dmann on Wed, 10/24/2012 - 23:50

    During my preparation for a visualization presentation at East Coast Oracle Users Group I realized I had an opportunity for a teaching moment on Levels of Abstraction. A few months back I published my PL/SQL code for an Archived Log Switch Heat Map. Read more about Updated Heat Map Report

    Tags: 
    SQL Developer
    pl/sql
    visualization

  20. A SQL_ID by any other name... Same SQL / Different Tools / Different SQL_ID

    Submitted by dmann on Tue, 10/11/2011 - 23:33
    I have been trying to chase down the same SQL being submitted against the same 10.2.0.4 Solaris database from different query tools used in our organization. I have been striking out when trying to match up with SQL IDs. I connected with 5 different tools (including SQLPLUS on 2 platforms) and submitted the following 4 lines of SQL: 
    SELECT *
  FROM EMP, 
       DEPT 
 WHERE EMP.DEPTNO=DEPT.DEPTNO
 Read more about A SQL_ID by any other name... Same SQL / Different Tools / Different SQL_ID
    Tags: 
    sql

  21. myBatis Migrations and Oracle RDBMS

    Submitted by dmann on Tue, 10/02/2012 - 12:25

    I am evaulating some schema migration tools for a project I am working on. I want some basic help organizing scads of changes that we will make during development. I also want the ability to produce generic SQL scripts as output to pass along for review / execution by folks that are not knee deep in our development environment.

    The SQL Script output requirement cut out a lot of options. For a comprehensive list of options check out the feature matrix on the Flyway page which compares 9 popular schema migration tools. Read more about myBatis Migrations and Oracle RDBMS

    Tags: 
    mybatis
    schema
    migration
    DDL

  23. Data Modeling

    Submitted by dmann on Thu, 09/13/2012 - 17:17
    Read more about Data Modeling

  24. A complete sandbox installation for VirtualBox... Thanks to Developer Days!

    Submitted by dmann on Thu, 01/27/2011 - 22:21
    Oracle is supplying some ready-to-run downloadable VMs including Operating Systems, OSes with Oracle already installed and ready-to-run RAC systems. I was hoping to leverage these to save some time for sandbox environments but from what I have seen so far they are only for the enterprise level Oracle VM product. I only have the horsepower to run Oracle VirtualBox which is their workstation level product. It seems they use different formats for each product and while it may be possible to convert VMs backs and forth, I'm just not up for it right now.
    Read more about A complete sandbox installation for VirtualBox... Thanks to Developer Days!
    Tags: 
    oracle
    Virtual Machine
    Developer Tools

  25. SQLDeveloper Extensions

    Submitted by dmann on Wed, 10/29/2008 - 17:05
    Collection of information relating to creating your own Oracle SQL Developer extensions. SQL Developer Main Page JDeveloper Extension SDK ------------------

    SQL Developer SDK on wiki.oracle.com

    Home page: http://wiki.oracle.com/page/SQL+Developer+SDK SQL Developer SDK How Tos (Oracle Wiki): http://wiki.oracle.com/page/SQL+Dev+SDK+How+Tos?t=anon
    Read more about SQLDeveloper Extensions