Tuesday, December 29, 2009

Yahoo! sucks on my netbook

I tried the new Yahoo! Mail today. I switched back to "classic" mail immediately. The first issue:

There seems to be a screen resolution problem. Your screen resolution is set below our minimum recommendation. When it's set under 1024 x 768 pixels, the all-new Yahoo! Mail won't look as good. You can continue with your current settings, but you won't be able to enjoy the full effect.

Really? My netbook has a 1024x600 resolution and I can go to 1024x768 with a scrolling desktop. The scrolling desktop is annoying and makes things unusable. Applications should scroll, not desktops.

The new mail does not render well in Opera. I use Opera because I think the scaling feature is superior to Firefox, IE and Chrome. It is nice to be able to size the contents of the web page to 70 to 90 percent while maintaining usability and readability. That is a nice feature when you have that 1024 x 600 netbook display. At 100 percent it looks like crap and does not get any better at zoomed levels.

I tried using Firefox and IE to see the full effects I should be enjoying. There are fixed frames and an annoying status bar, menu, message scroll, etc. I can see why they want a large fixed resolution.

I have been a Yahoo! mail user since 1998. That is over a decade of using Yahoo! One of the reasons why I liked Yahoo! was simplicity. While other web sites were going through the phases of hipness like massive image filled pages, Java applets, tons of unwanted JavaScript, excessive use of CSS and everything - Yahoo! kept most things simple. As the years passed, however, all of their services and applications got fatter. Messenger got fatter so I ended up using Gaim (Pidgin). Mail started getting fatter so I started using Google Mail more. Workstations are more powerful now than years ago and are able to handle newer technologies but it seems like some applications and web sites are forgetting that hardware using them are getting smaller or are coming from non-traditional sources (like game consoles and media devices). 

Maybe I am just getting old.

(I also realize the reason for some of the new Web 2.0 stuff is for smaller devices like phones and hand-helds).

Monday, November 23, 2009

Oracle Data Pump for data refreshes part 4

Part one, part two and part three.

This is a follow-up to the third part of this series with some real-world examples to make corrections and to justify some points.

I ran into a known 10g bug when attempting a DATA_ONLY export using parallel processing (ORA-00001: unique constraint () violated. The bug was fixed with 11g (Oracle forums). The source database was small so I did ran parallel=1 to get it to work. For larger databases that might not be feasible. The next test would be to modify the first export to get data and metadata and then do the DATA_ONLY on the first import step.

Other than the error above, the export and import for the first refresh went smoothly. The second import was more of a complete account copy do I could not use exactly what I specified in part three.

I did one export and one import. The export went quickly and without error. The import ground to a halt at the TABLE_STATISTICS step due to a bug in the Oracle Data Pump when schema and/or tablespace remapping is used. I had to terminate the import. Forums suggest using a EXCLUDE=STATISTICS in the parameter file to avoid these two incredibly slow steps and then rebuilding the statistics after the import (done through Enterprise Manager or whatever). The export of 57 GB of data took roughly 12 minutes. The import of that data took roughly 20 minutes for just the data. Source objects seemed to take a while and, as mentioned, the statistics took forever.

The data pump is a remarkable improvement over the previous import and export utilities. What took hours before can now be accomplished in less than an hour.

More to come.

Microblogging

I made this comment on the soccer boards I occasionally frequent but it was a decent rant.

I dislike micro-blogs like Twitter and social web sites used like micro-blogs. Sentence fragments are replacing meaningful dialog. I get so tired of the cryptic crap like "so many things wrong with today." What? Did your cat die? Are you on the rag? Did you get a skunky beer? Is your boss licking your coffee cup while you sleep at your desk?

What?

It seems like every tweet or status update from a friend below the age of 27 is like this.

The problem with these sentence shortened cries for attention is that unless you are tight within the sphere of friends and know what is going on you are left to draw your own conclusions about what is really going on in their lives. This is especially true for public figures that post these dramatic little giblets about their lives leading to four or fifty different interpretations to what they actually meant. Twitter updates sometimes worse than the Bible for interpretation.

Why the drama?

I realize that I am not using the latest and greatest iDroid portable media server/game console/camera/asset manager/phone and probably do not understand what is really going on. I understand that text messaging is the cool thing to be doing right now and that these short cryptic messages are really some English sub-language I will never understand or whatever. But, if you have friends or people that you want to be up-to-date with every little detail of your life then are you really doing a disservice to friends left guessing about the seriousness of the situation? Yoda is a better conversationalist.

Sunday, November 08, 2009

The Columbus Crew 2009, summary

The Columbus Crew exited the major league soccer playoffs Thursday to Real Salt Lake. After winning the Supporter Shield for best record at the end of the regular season the reigning MLS champions lost and away match 0-1, a home match 3-2 and 4-2 on aggregate goals for the playoff series. Now that the sting has edged I feel able to comment on it.

My first thought is that I hate the first round of the MLS playoff system. The team with the best record plays away first and then comes back home for one game. Two games where the best team gets a home field advantage by playing the last game of the two games at home. To me that gives the advantage to the lesser record team because the better record team will be more likely to play defensively for a tie to take the series back home for the win or play defensively to not give up too many goals to take back home and score more goals. If the lesser team gets more goals in the first game then they hold a distinct advantage in game two because the better record team must score one more goal than they did leaving the lesser team to play a clogged defense and counter attack game. This is a moot point since the Crew lost both games. So what happened?

There are lots of opinions on what happened but I think it all boils down to one word: inconsistency.

First there was the inconsistent lineup due to injuries to key players like Schelotto, Hesmer and Marshall. Brunner, Iro, Zayner and O'Rourke stepped up with admirable performances in place of Chad Marshall. Problems with injuries and players not being at full performance complicated mid to late season matches that also included Open Cup and Champions League games. There were also games where players were called up to national teams causing complications in starting lineups.

Second, inconsistent performances. There are a lot of names in this list: Rogers, Hejduk, Gaven, Hesmer, Moreno, and even Schelotto in some ways. Defender Chad Marshall outperformed Moreno, Garey and Lenhart in point production. Moreno spent more time acting than scoring. He looked dangerous one time, the first game he came back from injury. Rogers had one goal in 1,700 minutes playing for the Crew.

Third, inconsistent coaching. Robert Warzycha took over the championship team from 2008 from Sigi Schmid. Some games made him look like a genius but in reality we got lucky. Revolving line-ups were forced by injuries, suspensions, and the introduction of Champions League play. They were also made by Warzycha. The 2008 Crew marched into the playoffs with the starting eleven players they were going to field the entire playoffs. Schelotto did not play the first game of the playoffs leading to questions about the team and the coaching staff.

So where does the Crew go from here?

Warzycha and the coaching staff need to explain their position but should also keep their jobs going forward to next season. There are people calling for Warzycha to be let go but who do we replace him with? Schelotto? If not Schelotto then who do we replace him with and who would we be able to afford?

Goalkeeping and defense has some interesting aspects to it. Hesmer is likely not going anywhere and stays in goal. Gruenebaum will likely be kept as a more than capable backup unless his trade value dictates otherwise. Marshall and O'Rourke were the central defense last season and they took us to a championship. Eric Brunner stepped up this season in central defense while Chad Marshall was injured and should be kept as a key part of the future. Unfortunately that leaves O'Rourke, a strong player out of luck and looking for a position. Gino Padula was just as strong this season as last so O'Rourke cannot occupy his position. On the right side, Crew heart and soul Frankie Hejduk began to show his age especially in the playoffs. Jed Zayner played adequately on the right side when Hejduk was out with injury or the national team and should probably, and unfortunately for Hejduk get the start in 2010 on the right side. This still leaves a strong player, O'Rourke, looking for a position. It also leaves international Andy Iro (England) on the bench and given Iro's 2009 performance was both good and slightly less than adequate he will be retained unless there is an opportunity to obtain better players by trading him.

The 2009 midfield was inconsistent due to injuries, national team call-ups, and coaching. The only consistent player was Brian Carroll who should be retained. Guillermo Barros Schelotto was the offense this season which is only good if other players are also contributing, which they were not. O'Rourke was used as a defensive midfielder this season but Adam Moffat returned in 2009 with both good and sub-adequate performances (again, inconsistency). On the outside Ekpo led the team with assists and had some key goals, Rogers had one fewer goals and assists than Ekpo with fewer minutes and games played. Eddie Gaven has six goals but no assists. This is an adequate contribution but leads to questions regarding who to start and keep for 2010. Crew fan favorite Duncan Oughton remained with the team in 2090 with limited action and as can be kept with the team as long as it is feasible to do so. The remaining midfield did not spend much time on the field and will likely end up as inconsequential players that can be waived, traded or kept.

The forward spots will likely endure the most scrutiny this season and for good reason. The bash brothers, Lenhart and Garey, showed a couple games of promise and dominance followed by barely adequate performances. This is my personal opinion but neither forward has provided a franchise worthy performance. Alejandro Moreno produced a couple game winning goals this season and spent time injured but he has earned a reputation as a flopper and most if not all referees in the league are not buying his acting. Since he is not scoring a large number of goals to offset this he becomes a liability and his position in 2010 will likely be as a bench player or not with the Crew at all. Emilio Renteria came aboard mid-season and had equal portions of bad and good in his performances. Both Moreno and Renteria are from Argentina and I would probably favor the younger Renteria going forward.

If I were asked to name the priorities for the 2010 season they would be: retain Schelotto for one more season as a player, acquire Schelotto's replacement so he can work alongside Guillermo and develop to lead the team. I do not think Gaven, Rogers, Ekpo, Moffat or any of the developmental players are those players so the Crew need to identify someone to fill that role from another MLS team or from another league. Next would be to retain Marshall, Hesmer, Carroll and Padula as they are all key players in the Crew roster. The next thing the Crew needs is a forward capable of playing with Schelotto (and his successor) and scoring a bucket load of goals. Ekpo, Brunner, O'Rourke and Zayner are young and show promise and value for the Crew future. Rogers and Gaven are both young but are both question marks (in my opinion) as both have the potential to create great value but have slumped during the 2010 season. Hejduk and Moreno should have their roles relegated. Last, Warzycha should be retained as coach unless an obvious and adequate replacement is both available and feasible for the organization.

Windows Home Server, part one

I recently decided to take the plunge and invest in newer technology for our home server. I wanted a small, efficient system to replace the iDEQ mini computer running Windows Server 2003. I was favoring an Ubuntu Linux system running on a VIA green computing platform. Unfortunately, the VIA motherboard was too unstable to run Windows or Linux on. I gave up after swapping power supplies and memory; everything else is integrated into the motherboard. During that time the Intel Atom based hardware and Windows Home Server (WHS) appeared. Since Windows in some flavor will always be running somewhere in the house and WHS was getting favorable reviews I took the plunge and purchased an all-in-one unit with the WHS license.

The Hardware

I purchased an Acer easyStore H340 for $360 (before tax and shipping). It features the Intel Atom 230 processor, a Western Digital WD10EAVS 1 TB hard drive, 2 GB DDR2 memory and has 5 USB 2.0 ports, 1 eSATA port and a gigabit Ethernet port. The front door opens to reveal four SATA drive bays (the non-system drives are hot swappable). Windows Home Server (WHS) with Power Pack 1 comes installed and ready to finish on the TB disk drive. The McAfee Total Protection was installed but not active, sitting there waiting for me to activate the trial and then pay later.

The unit is smaller than any of the mini-computers I currently own. It is also quiet and unassuming and would quite at home in any basement likely near the router. There are indicator lights on the left side for power, drive and network activity and a multicolored status indicator light. Each drive has a multicolored status indicator light.

I added a WD10EADS 1 TB hard drive to the system. The drive trays are plastic and cheap feeling so I was careful with the tray I put the hard drive into and when I slid it back into place. It was recognized by the server and I added it to the storage pool without issue. There are close to 2 TB of storage in the pool.

After doing some reading I decided to purchase a 750GB hard drive but not add it to the storage pool. This drive will be used for bit torrent downloads, distributed updates and other high activity data. The 750GB was likely overkill but only $12 more than a 300 GB drive. When you do not add the drive to the pool the corresponding indicator light stays purple instead of a happy blue like the other drives in the pool.

I also purchased a Seagate 1TB hard drive and external enclosure to serve as a backup drive. I was able to hook up the external drive, dedicate it to backups and then make a quick backup of what was on our server.

The Software - Windows Home Server

Windows Home Server (WHS) came pre-installed on the Acer machine and almost ready to go upon start up. Connecting to the WHS begins with the connector software that comes on an accompanying disc. Shares are created for pictures, video, software, recorded TV programs, and for a public folder. WHS seems to prefer Windows authentication and having your user names and passwords synchronized across multiple computers. This was an issue for me because I had an older computer with one user name and most newer computers had my full name as the user name. I renamed the one account and broke a security license key for a piece of software I really do not use anymore. Still, one minor aggravation to play well with WHS.

Management is performed (or should be performed) through the Home Server Console. The console cannot be resized which is an annoyance for those of us that have netbook computers and limited vertical resolutions. Many options are compacted into an one panel and the administrator can expand the console and WHS through plug-in modules. Remote desktop can be used to maintain the server but with caution; anything specific to WHS should be managed by WHS and if you do things outside of WHS you can potentially screw up WHS.

I had problems removing McAfee software. I was not happy with Acer at this point. Why install trial software that nags you without providing a means for un-installing it? I rushed in and used remote desktop to connect and then remove the software with errors. I had to reboot the server to get the components to disappear from the console. I have not checked the registry or disks to see if the product was removed completely.

I have not used the workstation backup feature of WHS. The workstations I have have too much junk on them and I do not like the idea of my netbook being awakened in the middle of the night to have a backup performed. I prefer to have control over tasks like backups and updates on the netbook devices.

Another feature I have not used yet is the ability to put things on the Internet through WHS. Exposing a WHS to the world is scary without doing some reading first. I am happy right now with having the WHS closed to the rest of the planet. Since I am on that topic, how do you think ISPs are going to react to the idea of a technology adequate family putting a server on the Internet. Imagine what happens when someone on this planet figures out to hack the default implementation of WHS. Millions of potential zombie computers and personal data stores open to damage.

The primary goal of WHS is to centralize the protection, access, organization and storage of home data and services. This assumes at least one or more home workstations and portables are in use in the home (it would not make sense to pay for a license and have a server without one). These computers would be routinely backed-up to WHS. Media is to be stored centrally on the WHS server so multiple computers and other devices can get to it. So if everything is centralized for security and convenience then why are two important aspects of Windows not part of WHS.

Why no integration with Microsoft Security Essentials (MSE)?

MSE is free for XP, Vista and 7 users and regularly goes to the Internet for software and virus list updates. WHS coordinates the backup of devices on a regular schedule, why not centralize the security definitions and MSE software updates instead of having each home device connecting to the Internet to do so? Make a plug-in for MSE within WHS, which would download security definitions and updates to protect itself and provide them as a primary source for computers on the home network (the Internet being the secondary source if the WHS was unavailable or the computer were away from home).

Why no integration with Windows Server Update Services?

WSUS is also a free download but is geared more towards corporate environments. I have WSUS running on the current home server and it was nice to have a local copy of the 12 to 16 updates Microsoft just released on Tuesday. With WSUS the patches were downloaded once and then distributed locally to my two desktops in minutes. The same updates took roughly 40 minutes on my netbook, which is new and not currently pulling from the WSUS server. There are instructions on how to install WSUS on WHS available on the Internet but there is no support and no guarantees future updates to WHS would not cause a failure of WSUS and/or WHS for software not installed through plug-in. As with the security services, provide a plug-in for WHS to download and be the primary source for software updates for all local devices that can get to the WHS (leaving the Internet as the secondary source).

Summary

There will be follow-ups to this post as I start getting deeper into WHS and as more and more plug-ins and options become available.

Thursday, October 29, 2009

New software

A lot of recent software updates have appeared in the past 48 hours. The latest version of Ubuntu 9.10 (Karmic Koala) has been released. So download UKK or wait for the KKK (KDE/Kubuntu 9.10 Karmic Koala) if that is what you prefer. Opera released version 10.01 of Opera 10 browser with "turbo". "Neato". Mozilla released version 3.5.4 of Firefox 3.5 browser. Mozilla also released SeaMonkey 2.0; the all-in-one Internet application suite.

Get stuff:

Oracle Data Pump for data refreshes part 3

Part one and part two.

This part gives you the UNIX scripts and SQL that seem to work best in the environments I work in. the standard disclaimers apply. This likely is not the best way of going about this (or it might be) and I provide zero support for this. If it works for you, fantastic; send me some beer. If it frustrates you to no end because most of it works but you constantly find little annoyances and Oracle manuals are absolutely no help; welcome to my world.

The following UNIX steps assume the user on the server has appropriate privileges to execute Oracle binaries and has the environment set correctly.

prod_expdp.sh

This shell script sets the ORACLE_SID environment variable and then executes the data pump export utility twice; once for data and once for sequence metadata.

#!/bin/sh
# prod_expdp.sh - run datapump exports for a complete refresh of PROD to TEST
#
ORACLE_SID=prod
expdp test_schema/testing PARFILE=prod_expdata.par
expdp test_schema/testing PARFILE=prod_expmeta.par

prod_expdata.par

The next two files are parameter files used by the export. The contents of the first file, for the data extract, is self-explanatory. The PARALLEL parameter was not used but included here because you will want some level of parallelism for optimal extract performance. The %U on the DUMPFILE parameter assigns a unique number to each extract file for parallel processing (4 files in this example). This parameter file should contain any EXCLUDE statements for tables that do not need to be exported.

DIRECTORY=test_dp_folder
DUMPFILE=prod_data_%U.dmp
LOGFILE=prod_data_exp.log
CONTENT=DATA_ONLY
SCHEMAS=test_schema
PARALLEL=4

prod_expmeta.par

The second parameter file is for sequence metadata. Why two exports? I have two reasons why, in my opinion, there should be two. The main reason was to separate metadata (small) from table data (massive). You cannot do a metadata and data import in the same import job without doing all objects and I did not want the second step processing a gigantic data extract file just to get the microscopic slice of metadata. Second reason, INCLUDE and EXCLUDE are exclusive; only one can be used per execution and most extracts will want to exclude table data (if any).

DIRECTORY=test_dp_folder
DUMPFILE=prod_meta_%U.dmp
LOGFILE=prod_meta_exp.log
CONTENT=METADATA_ONLY
SCHEMAS=test_schema
INCLUDE=SEQUENCE

Extracted files will need to be relocated to the test server.

test_impdp.sh

This shell script sets the ORACLE_SID environment variable, executes SQL*Plus to build some SQL, executes the data pump import utility twice - once for data and once for sequence metadata, and then executes SQL*Plus to do post import SQL and invalid object compiles.

#!/bin/sh
# test_impdp.sh - run pre/post SQL create SQL, datapump imports for a complete refresh of PROD to TEST
#
ORACLE_SID=test
sqlplus -s test_schema/testing @test_impsql.sql
impdp test_schema/testing PARFILE=test_impdata.par
impdp test_schema/testing PARFILE=test_impmeta.par
sqlplus -s test_schema/testing @test_imp_post.sql
sqlplus -s test_schema/testing @compile_invalid.sql
sqlplus -s test_schema/testing @compile_invalid.sql
sqlplus -s test_schema/testing @compile_invalid.sql

test_impdata.par

The data import parameter file is similar to the data export parameter file but with the extra TABLE_EXISTS_ACTION parameter instructing the data pump to truncate (empty) the table if it exists.

DIRECTORY=test_dp_folder
DUMPFILE=prod_data_%U.dmp
LOGFILE=prod_data_imp.log
CONTENT=DATA_ONLY
SCHEMAS=test_schema
PARALLEL=4
TABLE_EXISTS_ACTION=TRUNCATE

test_impmeta.par

The metadata import parameter file is self-explanatory.

DIRECTORY=test_dp_folder
DUMPFILE=prod_meta_%U.dmp
LOGFILE=prod_meta_imp.log
CONTENT=METADATA_ONLY
SCHEMAS=test_schema

test_impsql.sql

This SQL script is where all the magic happens. This is where all of the pain-in-the-ass, that is damned annoying, why oh why Oracle do you punish us you gigantic corporate fudge-packers, gets taken care of. This script dynamically builds two SQL scripts: a pre-import script and a post-import script. All enabled triggers and referential constraints (foreign keys) must be queries so they can be disabled and then enabled. Do not enable a constraint or trigger that was disabled; it was likely disabled for a reason likely by some developer behind your back. The script must also query all sequences so they can be dropped before import. The data pump import does not have a parameter or option to replace an existing sequence, so sequences must be dropped pre-import. Dropping the sequence causes two problems: privileges granted to those sequences will be lost and all source types containing the sequence will become invalid. The post-import SQL must grant privileges so this SQL script will query existing privileges (the schema owner must be able to query the all_tab_privs view).

SET FEEDBACK OFF TERMOUT OFF TRIMSPOOL ON VERIFY OFF PAGESIZE 0 LINESIZE 200
SPOOL test_imp_pre.sql
SELECT 'ALTER TRIGGER '||trigger_name||' DISABLE;'
FROM user_triggers
WHERE base_object_type = 'TABLE' AND status = 'ENABLED'
ORDER BY trigger_name;
SELECT 'ALTER TABLE '||table_name||' DISABLE CONSTRAINT '||constraint_name||';'
FROM user_constraints
WHERE constraint_type = 'R' AND status = 'ENABLED'
ORDER BY table_name;
SELECT 'DROP SEQUENCE '||sequence_name||';'
FROM user_sequences
ORDER BY sequence_name;
SPOOL OFF
SPOOL test_imp_post.sql
SELECT 'ALTER TRIGGER '||trigger_name||' ENABLE;'
FROM user_triggers
WHERE base_object_type = 'TABLE' AND status = 'ENABLED'
ORDER BY trigger_name;
SELECT 'ALTER TABLE '||table_name||' ENABLE CONSTRAINT '||constraint_name||';'
FROM user_constraints
WHERE constraint_type = 'R' AND status = 'ENABLED'
ORDER BY table_name;
SELECT 'GRANT '||privilege||' ON '||object_name||' TO '||grantee||';'
FROM user_objects
JOIN all_tab_privs ON table_schema = USER AND table_name = object_name
WHERE object_type = 'SEQUENCE'
ORDER BY object_name;
SPOOL OFF
@test_imp_pre.sql
QUIT

compile_invalid.sql

The generic SQL script will query invalid source objects (made invalid by the sequence being dropped), create SQL to compile those invalid source objects, and then execute that SQL. This generic script will need to be executed at least once but up to six times due to objects being invalidated when an object is compiled. For example: if procedure A calls procedure B that uses sequence C, both A and B will be invalid when the sequence is dropped. If A then B is compiled, A will be valid until B is compiled then will become invalid. The second execution of the compile script will compile A and since nothing depends upon A we are done.

SET FEEDBACK OFF TERMOUT OFF TRIMSPOOL ON VERIFY OFF PAGESIZE 0 LINESIZE 200
SPOOL compile_invalid_pass.sql
SELECT 'ALTER '||DECODE(SUBSTR(object_type,1,7),'PACKAGE','PACKAGE',object_type)||' '||object_name||' COMPILE'||DECODE(object_type,'PACKAGE BODY',' BODY;','PACKAGE',' PACKAGE;',';')
FROM user_objects
WHERE object_type IN ('TRIGGER','PACKAGE BODY','PACKAGE','FUNCTION','PROCEDURE') AND status = 'INVALID'
ORDER BY object_name, object_type;
SPOOL OFF
@compile_invalid_pass.sql

Sequence of Events

  1. run export script
    1. export table data
    2. export metadata for sequences
  2. move exported data to test server (if not on the production server)
  3. run import script
    1. run SQL to create pre and post-import SQL
      1. query referential constraints that need to be disabled
      2. query triggers that need to be disabled
      3. query sequences that need to be dropped
      4. query referential constraints that need to be enabled
      5. query triggers that need to be enabled
      6. query privileges to sequences that need to be granted on sequences that were dropped and then imported
      7. run the pre-import SQL
        1. disable referential constraints
        2. disable triggers
        3. drop sequences
    2. import table data
    3. import metadata for sequences
    4. run the post-import SQL
      1. enable referential constraints
      2. enable triggers
      3. grant privileges on sequences to users
    5. run compile SQL
      1. query for invalid objects
      2. compile source types made invalid after the sequence drop
    6. run compile SQL
      1. query for invalid objects
      2. compile source types made invalid after the last compile
    7. run compile SQL
      1. query for invalid objects
      2. compile source types made invalid after the last compile

You are probably thinking to yourself “holy fudging fudge, all of that bullfrog for a stupid little sequence?” I know I am. Hundreds of other individuals are thinking the same thing. There are other ways of handling sequences. Generate DDL for the sequence and then drop and create it; but that has the same effect as above (loss of privileges and invalid objects). Select MAX() and use that value to build SQL to drop and create it; same problems as above. Select MAX(), alter the increment by property of the sequence, get the next value, then restore the increment by property. The last two would require prior knowledge of the table(s) and column(s) using the sequence. One of our production databases is a vendor package that has over forty sequences so some up-front work would be required to match sequence to column to table. The last solution is cleanest because it does not require the recompilation of source objects or restoration of privileges. The whole issue could be avoided in the value of the sequence was considered a data object instead of a metadata object.

The next part will hopefully deal with the dirty details like how much undo/redo is generated, what about performance, etc.

Wednesday, October 21, 2009

Oracle Data Pump for data refreshes part 2

In part 1 tables were created and populated with data. The following UNIX steps assume the user on the server has appropriate privileges to execute Oracle binaries and has the environment set correctly. We now use the data pump to export the whole schema:

ORACLE_SID=prod
expdp test_schema/testing DIRECTORY=test_dp_folder DUMPFILE=prod_full_%U.dmp LOGFILE=prod_full_exp.log SCHEMAS=test_schema

Get the dump files to the target server and then run the import:

ORACLE_SID=test
impdp test_schema/testing DIRECTORY=test_dp_folder DUMPFILE=prod_full_%U.dmp LOGFILE=prod_full_imp.log SCHEMAS=test_schema EXCLUDE=USER

The EXCLUDE=USER assumes the schema owner user already exists in the target database and avoids the annoying error.

The tables, data, constraints, triggers and sequences have been populated. One thing to notice, however: the NEXTVAL for the sequence is 141 and not the 124 from the production system. The reason for this is default CACHE value (20) used when the sequence was created. The sequence imported will always be a multiple of the CACHE setting plus the start value. If your application cannot deal with gaps in the sequence value then use the NOCACHE setting when creating the sequence otherwise every shutdown will introduce gaps and every refresh of test data will introduce gaps.

The next part will document how to refresh data.

Monday, September 28, 2009

CRUD with the Oracle MERGE statement

Most of this post came about after a lengthy search of the clicker-web for information on Oracle materialized views. There are three or fifty situations at work where such a construct might be helpful to performance and overall health of the system (like when five different folks are running the same query over and over and over again).

Most situations that could benefit from a materialized view are "complex materialized views" according to Oracle manuals and guide (i.e. I am doing something more complicated than "hello world" in SQL). Others have documented that the materialized view update is really a MERGE behind the scenes. I am quite familiar with a MERGE and that was actually my first choice before I thought to myself "there is probably something already created for this complicated crap". Why recreate the square wheel, right? In this case the square wheel might be more efficient in the long run.

First, I will create a quick table and add some constraints to it:

CREATE TABLE table_full_of_crud AS
SELECT
LEVEL AS id
, TRUNC(SYSDATE,'YEAR')+(LEVEL-1) AS some_stupid_date
, CASE MOD(LEVEL,7) WHEN 0 THEN NULL ELSE TO_CHAR(TRUNC(SYSDATE,'YEAR')+(LEVEL-1),'DAY"," MONTH D YYYY') END AS varchar_crud
FROM DUAL
CONNECT BY ROWNUM BETWEEN 1 AND 120;
ALTER TABLE table_full_of_crud
ADD (CONSTRAINT pk_table_full_of_crud PRIMARY KEY(id))
MODIFY (some_stupid_date NOT NULL);

Here is what I consider to be a well-formed, structured MERGE that will handle new rows, updated rows and rows that need to be deleted:

MERGE /*+ append */ INTO table_full_of_crud  m
USING (
SELECT
NVL(v1.id,t1.id) AS id
, NVL(v1.some_stupid_date,TRUNC(SYSDATE)-12345) AS some_stupid_date
, v1.varchar_crud
FROM table_full_of_crud t1
FULL JOIN (
SELECT * FROM (
SELECT
LEVEL AS id
, TRUNC(SYSDATE,'YEAR')+(LEVEL-1) AS some_stupid_date
, CASE MOD(LEVEL,20)
WHEN 0 THEN NULL
WHEN 7 THEN TO_CHAR(TRUNC(SYSDATE,'YEAR')+(LEVEL-1),'DAY"," MONTH D YYYY')
ELSE TO_CHAR(TRUNC(SYSDATE,'YEAR')+(LEVEL-1),'MM/DD/YYYY')
END AS varchar_crud
FROM DUAL
CONNECT BY ROWNUM BETWEEN 1 AND 160
)
WHERE MOD(id,69) > 0
) v1 ON v1.id = t1.id
) u
ON (m.id = u.id)
WHEN MATCHED THEN
UPDATE SET
m.some_stupid_date = u.some_stupid_date
, m.varchar_crud = u.varchar_crud
WHERE NOT(
(m.some_stupid_date = u.some_stupid_date)
AND DECODE(m.varchar_crud,u.varchar_crud,'EQ') IS NOT NULL
)
DELETE
WHERE m.some_stupid_date = (TRUNC(SYSDATE)-12345)
AND m.varchar_crud IS NULL
WHEN NOT MATCHED THEN
INSERT (m.id, m.some_stupid_date, m.varchar_crud)
VALUES (u.id, u.some_stupid_date, u.varchar_crud);

Here is a line by line explanation of all this crap:

  1. The start of the MERGE statement, with append hint (direct path, etc.) INTO table; I use a table alias of "m" to indicate this is the table having data merged into it (makes sense)
  2. USING; we will be using a subquery
  3. SELECT (our subquery)
  4. The NVL of the primary key is required due to the FULL [OUTER] JOIN; if the row needs deleted then the merge data primary key will be NULL, if inserted the original primary key will be NULL, if update neither will be NULL
  5. The NVL of this column is due to the NOT NULL constraint and to use as our "delete trigger"; "delete triggers" should be a value that will never appear in the column
  6. VARCHAR2 column
  7. FROM the table that will be the target of the merge;
  8. FULL [OUTER] JOIN is used to return the rows that exist in either table
  9. SELECT the merge data; this subquery will contain new rows and rows that might need updating in the merge target table
  10. data
  11. data
  12. data
  13. data
  14. data modify some rows to null values
  15. data keep some rows the same
  16. data modify the rest
  17. data
  18. data
  19. data
  20. data
  21. data delete the 69th key value (remove it from the subquery)
  22. data
  23. End of using subquery; assign table alias of "u" (again, makes sense)
  24. ON here is where (usually) primary key values are matched back to each other and influence the next two sections; what to do when things do or do not match
  25. If the condition above evaluates to TRUE then the "WHEN MATCHED THEN" section is performed
  26. UPDATE the target table
  27. SET column value
  28. SET column value
  29. I use the WHERE clause of the merge update clause to determine if the row columns have changed or not; if they have NOT changed then why have the undo and redo overhead (???)
  30. If a column cannot be NULL then a simple comparison will work
  31. If a column can be NULL then DECODE can be used on both columns in the comparison
  32. End of NOT(
  33. DELETE from the target table
  34. The WHERE clause check for the "delete trigger(s)" in (TRUNC(SYSDATE)-12345)
  35. AND check for null columns (the FULL JOIN will produce NULL values in columns without corresponding rows in the merge data)
  36. The "WHEN NOT MATCHED THEN" is performed when merge data cannot be matched to rows in the merge target
  37. So INSERT (columns)
  38. With new VALUES (from the merge subquery data)

The USING sub-query will almost always contain a complex piece of SQL to collect new and updated rows. The handle deletes of rows in the merge target table it needs to be joined to the query data with a FULL OUTER JOIN. If deletes are not needed, then the FULL JOIN is not needed. Here is a visual representation of the FULL OUTER JOIN:

ID1 X1   Y1     ID2 X2   Y2
1 data data 1 data data
2 data data 2 data data
3 data data 4 data data
4 data data 5 data data
5 data data 6 data data

After FULL JOIN

ID1  ID2  X2   Y2
1 1 data data
2 2 data data
3 NULL NULL NULL
4 4 data data
5 5 data data
NULL 6 data data

The non-primary key columns in rows that need to be deleted will be NULL. This can be used in the topmost section of the USING sub-query (via NVL) to create delete trigger data, which brings me to the DELETE mechanism.

The hardest part of the above statement was getting the DELETE clause to work. Reading the Oracle 10g Database SQL Reference helped somewhat; especially this point:

The only rows affected by this clause are those rows in the destination table that are updated by the merge operation. That is, the DELETE WHERE condition evaluates the updated value, not the original value that was evaluated by the UPDATE SET ... WHERE condition. If a row of the destination table meets the DELETE condition but is not included in the join defined by the ON clause, then it is not deleted.

This means the DELETE does not use column data in the USING clause sub-query; very important. Some column in the UPDATE must be updated for the DELETE to work. To handle this set one or more columns to values that would never appear (in combination) in the data and use those values as a delete trigger.

I always add a WHERE clause to the UPDATE to determine if anything has changed. If nothing has changed then why generate the undo and redo data? Simply compare non-null columns and then null-evaluated columns to determine if data have changed.

The INSERT clause is the same as the INSERT statement we all know and love.

One discussion I came across on the Internet was about using a UNION ALL instead of the FULL OUTER JOIN. The UNION ALL performs better (one full index scan) than the FULL JOIN (two index scans and then a UNION-ALL). UNION ALL is a set operator, however, and updated rows would be duplicated (one unique row from the merge table and one from the query data). Something could probably be written but the FULL JOIN is simple, easy to maintain and worth whatever fractional overhead (compared to the actual merge) incurred.

Friday, September 25, 2009

Drugs, doctors, dumb gripe

I heard the most interesting conversation outside the door on the examination room I was waiting in. I was there for what felt like an upper respiratory infection (or more likely remnants of) and subsequent examination. A pharmaceutical representative was talking to the doctor about a drug. A normal human would have been shocked by the conversation but with my current mindset I was able to shrug it off and not be surprised.

Why? This crap is everywhere. There is not a single organization or area that does not have these back door shenanigans.

Welcome to the world.

People pile poop on the bug drug companies and they deserve every last ounce but they also deserve credit for developing life saving and life enhancing medicines. What I find equally offensive are the people who poop on those pharmaceutical companies but support the natural, organic and alternative products. They are doing the same thing – making money off a product and potentially causing harm to individuals where medicine is needed to prevent illness or death.

What was said? Drug rep asks about drug. Doctor says there is too much conflicting information so he prescribes drug. If drug benefits patient he lets them stay on the drug. Drug rep wants to target a one year life cycle per patient for the drug. Other things were said. Mentions of the conversation getting on Internet were made. It was humorous to them.

A co-worker once said they have a drug to wake you up, a drug to keep you awake, to put you to sleep, a to make you happy, to make you sad, so you just do not care anymore, to get you hard, to make you stay hard, to make the pain go away and a drug to make you – well they have a drug for everything.

Monday, September 21, 2009

Bad mood music playlist

My current "Bad Mood Music" playlist...

  1. Exodus - "Deranged", from "Pleasures of the Flesh"
  2. Overkill - "I Hate", from "The Years Of Decay"
  3. Wrathchild America - "Surrounded By Idiots", from "3D"
  4. Anthrax - "Antisocial", from "State of Euphoria"
  5. Nailbomb - "24 Hour Bullshit", from "Point Blank"
  6. Fight - "Kill It", from "War of Words"
  7. GWAR - "America Must Be Destroyed", from "America Must Be Destroyed"
  8. Stormtroopers of Death - "Speak English or Die", from "Speak English or Die"
  9. Sacred Reich - "The American Way", from "The American Way"
  10. Corrosion of Conformity - "Vote With a Bullet", from "Blind"
  11. Megadeth - "Into The Lungs Of Hell", from "So Far, So Good... So What! [Remaster]"
  12. Megadeth - "Set The World Afire", from "So Far, So Good... So What! [Remaster]"
  13. Metallica - "Battery", from "Master of Puppets"
  14. Death Angel - "The Ultra-Violence", from "The Ultra-Violence (Remastered)"
  15. Pantera - "Mouth For War", from "Vulgar Display of Power"
  16. Marilyn Manson - "The Beautiful People", from "Antichrist Superstar"
  17. Disturbed - "Down With The Sickness", from "The Sickness"
  18. Limp Bizkit - "Break Stuff", from "significant other"
  19. Slipknot - "People = Shit", from "Iowa"
  20. Static X - "I'm With Stupid", from "Wisconsin Death Trip"
  21. Anthrax - "Chromatic Death", from "Attack of the Killer B's"
  22. Sepultura - "Attitude", from "Roots"
  23. Slayer - "War Ensemble", from "Seasons In The Abyss"
  24. Exodus - "War is my Sheppard", from "Tempo of the Damned"
  25. Pantera - "War Nerve", from "The Great Southern Trendkill"
  26. Slayer - "Exile", from "God Hates Us All"
  27. Suicidal Tendencies - "How Will I Laugh Tomorrow (Heavy Emotion Version)", from "Controlled by Hatred/Feel Like Shit"

Oracle Data Pump for data refreshes part 1

This post will be the first in a multi-part post about the Oracle data pump utility. My goal for this series of posts is to (hopefully) provide a road map for Oracle database users to use the data pump utilities to refresh one or more test/development databases from a periodic snapshot of production data.

The data pump import and export utilities were added alongside the older export and import utilities in version10g of Oracle Database. These utilities can be used to make logical backups of metadata and data in databases from the entire database down to an individual table.

The first thing I noted when doing a snapshot of a production database at work is that when enough parallel processes are given to the data pump it smokes the pants off the previous utilities. What took hours now took minutes. That made me happy and I am sure my arteries will be happy without the 4am runs to White Castle while the export chugged along. Note, I have not done an import of production data yet and there is a reason for that – the unknown.

Like any good administrator I wanted to understand this new tool before I just started restoring data to databases. What about jobs? How do I handle sequences? Unfortunately the Oracle documentation is painfully lacking in that area. The 10g documentation for the old export has a section called "considerations when exporting database objects" that explains how to handle sequences, LOB columns, synonyms, etc. It also has a lengthier section on "considerations when importing database objects" which was also helpful. The 10g and 11g documentation has examples but lacks the depth of information the previous two utilities had. The 10g documentation has more useful information about the old utilities than it does about the fancy new ones and the 11g documentation for the data pump is about the same as the 10g data pump (11g has a legacy mode to support the old utility parameters, btw). This meant I had to set up a database to test the following items that could be affected by a data refresh:

  • constraints
  • referential constraints (i.e. foreign keys)
  • indexes
  • statistics
  • triggers
  • sequences

There are other objects like dimensions and quotas but those items will wait until a later time and date.

The first major difference between the old and new utilities is the use of DIRECTORY objects in the data pump utilities. The system DBA will need to create a directory object and grant privileges to it or use the default directory object named DATA_PUMP_DIR (I recommend creating your own so you have more control over who and what and how much).So as someone with the appropriate privileges:

CREATE USER test_schema IDENTIFIED BY "testing"
PROFILE DEFAULT
DEFAULT TABLESPACE users
QUOTA UNLIMITED ON users
ACCOUNT UNLOCK;
GRANT CONNECT,DBA TO test_schema;
CREATE OR REPLACE DIRECTORY test_dp_folder AS '/u69/oraldata/testdpump';
GRANT READ,WRITE ON DIRECTORY test_dp_folder TO test_schema;

Next create tables.

--- Time periods
CREATE TABLE time_periods
AS
SELECT
time_date
, EXTRACT(YEAR FROM time_date) AS time_year
, EXTRACT(MONTH FROM time_date) AS time_month
, EXTRACT(DAY FROM time_date) AS time_day
, TO_NUMBER(TO_CHAR(time_date,'d')) AS time_day_of_week
FROM (
SELECT TRUNC(SYSDATE,'YEAR')+(LEVEL - 1) AS time_date
FROM DUAL
CONNECT BY ROWNUM BETWEEN 1 AND ADD_MONTHS(TRUNC(SYSDATE,'YEAR'),36)-TRUNC(SYSDATE,'YEAR')
);
ALTER TABLE time_periods ADD (CONSTRAINT pk_time_periods PRIMARY KEY(time_date));
--- Customer table
CREATE TABLE customers
AS
SELECT
LEVEL AS customer_id
, 'Test customer #'||TO_CHAR(LEVEL,'000000000') AS customer_name
FROM DUAL
CONNECT BY ROWNUM BETWEEN 1 AND 100;
ALTER TABLE customers ADD (CONSTRAINT pk_customers PRIMARY KEY(customer_id));
--- Purchase order table
--- A sequence
CREATE SEQUENCE seq_pk_purchase_orders;
SELECT seq_pk_purchase_orders.NEXTVAL FROM dual;
--- Table with foreign keys
CREATE TABLE purchase_orders
( po_id NUMBER
, po_date DATE
, po_cust NUMBER
, po_tag VARCHAR2(64)
, po_tot_items NUMBER
, po_tot_value NUMBER
, CONSTRAINT pk_purchase_orders PRIMARY KEY(po_id)
, CONSTRAINT fk_po_date FOREIGN KEY(po_date) REFERENCES time_periods(time_date)
, CONSTRAINT fk_po_cust FOREIGN KEY(po_cust) REFERENCES customers(customer_id)
);
--- A trigger that modifies the PO tag
CREATE OR REPLACE TRIGGER tr_po_tag
BEFORE INSERT ON purchase_orders FOR EACH ROW
BEGIN
:new.po_tag := CASE WHEN :new.po_tag IS NULL THEN 'SGPO' ELSE 'CUST '||:new.po_tag END;
END;
/

The SQL is used to insert test data into the purchase orders table

INSERT /*+ APPEND */ INTO purchase_orders
SELECT
seq_pk_purchase_orders.NEXTVAL
, TRUNC(TRUNC(SYSDATE,'YEAR')+DBMS_RANDOM.value(0,(SELECT COUNT(time_date) FROM time_periods)))
, TRUNC(DBMS_RANDOM.value(1,(SELECT COUNT(customer_id) FROM customers)))
, CASE MOD(r1,69) WHEN 0 THEN NULL ELSE DBMS_RANDOM.string('A',12) END
, CEIL(r2)
, CEIL(r2*123456)/100
FROM (
SELECT LEVEL AS r1, ABS(DBMS_RANDOM.normal) AS r2
FROM DUAL
CONNECT BY ROWNUM BETWEEN 1 AND 123
) v1
;
COMMIT;

Tuesday, September 08, 2009

Life sucks sometimes

You ever get to a point in your life where everything is getting better and you are beginning to feel happy about life and then you get smacked in the face? Well, I got slapped and punched and kicked and jabbed in the heart and spit upon all in the course of about a week.

I would love to share some opinions but to be blunt, it does not matter. The Internet is the wrong place to share such opinions.

Things are getting better and returning to normal, though. Hopefully that means I will have time to post some Oracle crap and some gripes about stuff. 

Friday, August 14, 2009

Oracle 10g database authentication

This is my attempt to get my collective knowledge of authorization in the Oracle 10g Database world in one location. The following steps and information assume you have access to a super-user (i.e. AS SYSDBA) and a database with a "USERS" tablespace.

First, create the schema owner user, read and update roles and then grant built-in roles with necessary system privileges to the users. Also grant the read role to the write role.

CREATE USER test_schema IDENTIFIED BY "t3sting"
PROFILE DEFAULT
DEFAULT TABLESPACE users
QUOTA UNLIMITED ON users
ACCOUNT UNLOCK;
CREATE USER test_user IDENTIFIED BY "t3sting"
PROFILE DEFAULT
DEFAULT TABLESPACE users
QUOTA 5M ON users
ACCOUNT UNLOCK;
CREATE ROLE test_READ NOT IDENTIFIED;
CREATE ROLE test_CRUD IDENTIFIED BY "supercomplicatedpassword";
GRANT CONNECT TO test_schema,test_user;
GRANT RESOURCE TO test_schema;
GRANT test_READ TO test_CRUD;

Next, connect as schema owner to create objects and then grant object privileges to them to the read and write roles

SQL> CONN test_schema
Enter password:
Connected.
CREATE TABLE test_table
COMPRESS
AS
SELECT
TO_CHAR(time_date,'YYYYMMDD')||'.01' AS time_id
, time_date
, EXTRACT(YEAR FROM time_year_beg_date) AS time_year
, time_year_beg_date
, ADD_MONTHS(time_year_beg_date-1,12) AS time_year_end_date
, EXTRACT(MONTH FROM time_date) AS time_month
, TRUNC(time_date,'MONTH') AS time_month_beg_date
, ADD_MONTHS(time_date-1,12) AS time_month_end_date
, TO_CHAR(time_date,'Day') AS time_day_of_week
, CASE time_date
WHEN time_year_beg_date THEN 'New Years Day'
WHEN NEXT_DAY(time_year_beg_date+30,'MON')+14 THEN 'Presidents Day'
WHEN NEXT_DAY(ADD_MONTHS(time_year_beg_date,5)-1,'MON')-7 THEN 'Memorial Day'
WHEN ADD_MONTHS(time_year_beg_date,6)+3 THEN 'Independance Day'
WHEN NEXT_DAY(ADD_MONTHS(time_year_beg_date,8)-1,'MON') THEN 'Labor Day'
WHEN ADD_MONTHS(time_year_beg_date,10)+10 THEN 'Veterans Day'
WHEN NEXT_DAY(ADD_MONTHS(time_year_beg_date,10)-1,'THU')+21 THEN 'Thanksgiving'
WHEN ADD_MONTHS(time_year_beg_date,11)+24 THEN 'Christmas'
ELSE NULL
END AS time_holiday_description
FROM (
SELECT
TRUNC(SYSDATE,'YEAR')+(LEVEL - 1) AS time_date
, TRUNC(TRUNC(SYSDATE,'YEAR')+(LEVEL - 1),'YEAR') AS time_year_beg_date
FROM DUAL
CONNECT BY ROWNUM BETWEEN 1 AND ADD_MONTHS(TRUNC(SYSDATE,'YEAR'),36)-TRUNC(SYSDATE,'YEAR')
);
Table created.

SQL> DESC test_table
Name Null? Type
----------------------------------------- -------- ----------------------------
TIME_ID VARCHAR2(11)
TIME_DATE DATE
TIME_YEAR NUMBER
TIME_YEAR_BEG_DATE DATE
TIME_YEAR_END_DATE DATE
TIME_MONTH NUMBER
TIME_MONTH_BEG_DATE DATE
TIME_MONTH_END_DATE DATE
TIME_DAY_OF_WEEK VARCHAR2(9)
TIME_HOLIDAY_DESCRIPTION VARCHAR2(16)

SQL> SELECT COUNT(*) FROM test_table;

COUNT(*)
----------
1095
GRANT SELECT ON test_table TO test_READ;
GRANT INSERT,UPDATE,DELETE ON ON test_table TO test_CRUD;

The test_user USER has CREATE SESSION system privileges so test_user can create a session and connect to Oracle and see PUBLIC objects. The test_user cannot see or access test_schema.test_table

SQL> CONN test_user
Enter password:
Connected.

SQL> DESC test_schema.test_table
ERROR:
ORA-04043: object test_schema.test_table does not exist

Authorization to schema objects can be granted: directly to the USER, indirectly through a ROLE, or through granted execute privilege on a source object (PROCEDURE, FUNCTION or PACKAGE) created with AUTHID DEFINER.

GRANT SELECT ON test_table TO test_user;

Privileges granted directly to the USER are recognized immediately

SQL> DESC test_schema.test_table
Name Null? Type
----------------------------------------- -------- ----------------------------
TIME_ID VARCHAR2(11)
TIME_DATE DATE
TIME_YEAR NUMBER
TIME_YEAR_BEG_DATE DATE
TIME_YEAR_END_DATE DATE
TIME_MONTH NUMBER
TIME_MONTH_BEG_DATE DATE
TIME_MONTH_END_DATE DATE
TIME_DAY_OF_WEEK VARCHAR2(9)
TIME_HOLIDAY_DESCRIPTION VARCHAR2(16)

SQL> SELECT DISTINCT time_holiday_description FROM TEST_SCHEMA.test_table;

TIME_HOLIDAY_DES
----------------
Veterans Day
New Years Day
Independance Day
Memorial Day
Presidents Day
Labor Day
Thanksgiving

8 rows selected.

As schema owner user revoke the privilege from the USER and then grant the read ROLE to USER test_user.

REVOKE SELECT ON test_table FROM test_user;
GRANT test_READ TO test_user;
*
ERROR at line 1:
ORA-01919: role 'TEST_READ' does not exist

Unless granted the privilege to do so, the schema owner USER cannot GRANT a ROLE to a USER; a super-user must do it.

GRANT test_READ TO test_user;

The ROLE privileges are not available until the USER reconnects or sets the ROLE

SQL> DESC test_schema.test_table
ERROR:
ORA-04043: object test_schema.test_table does not exist

SQL> SET ROLE test_READ;

Role set.

SQL> DESC test_schema.test_table
Name Null? Type
----------------------------------------- -------- ----------------------------
TIME_ID VARCHAR2(11)
TIME_DATE DATE
TIME_YEAR NUMBER
TIME_YEAR_BEG_DATE DATE
TIME_YEAR_END_DATE DATE
TIME_MONTH NUMBER
TIME_MONTH_BEG_DATE DATE
TIME_MONTH_END_DATE DATE
TIME_DAY_OF_WEEK VARCHAR2(9)
TIME_HOLIDAY_DESCRIPTION VARCHAR2(16)

SQL> UPDATE TEST_SCHEMA.test_table
SET time_holiday_description = 'Special Day'
WHERE time_id LIKE '____1107%';
UPDATE TEST_SCHEMA.test_table
*
ERROR at line 1:
ORA-01031: insufficient privileges

The test_user USER cannot update test_schema.test_table without privileges. The test_schema USER granted the privileges to the test_CRUD ROLE so a super-user can GRANT that ROLE to test_user.

GRANT test_CRUD TO test_user;

Connect as test_user and try again.

SQL> CONN test_user
Enter password:
Connected.
SQL> UPDATE TEST_SCHEMA.test_table
SET time_holiday_description = 'Special Day'
WHERE time_id LIKE '____1107%';

3 rows updated.

SQL> ROLLBACK;

Rollback complete.

Notice how test_user was able to update the table but did not need the password for the test_CRUD role? When a ROLE is granted to a USER or another ROLE it is as a DEFAULT ROLE and all default roles are applied when the session is created. The USER must be altered by a super-user to indicate what roles are default (or not default).

ALTER USER test_user DEFAULT ROLE ALL EXCEPT test_CRUD;

The test_user USER will not have the test_CRUD ROLE when it creates a session.

SQL> CONN test_user
Enter password:
Connected.

SQL> UPDATE TEST_SCHEMA.test_table
SET time_holiday_description = 'Special Day'
WHERE time_id LIKE '____1107%';

UPDATE TEST_SCHEMA.test_table
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> SET ROLE test_CRUD;
SET ROLE test_CRUD
*
ERROR at line 1:
ORA-01979: missing or invalid password for role 'TEST_CRUD'

SQL> SET ROLE test_CRUD IDENTIFIED BY "supercomplicatedpassword";

Role set.

SQL> UPDATE TEST_SCHEMA.test_table
SET time_holiday_description = 'Special Day'
WHERE time_id LIKE '____1107%';

3 rows updated.

SQL> ROLLBACK;

Rollback complete.

Source types assume the privileges of the source type definer or invoker (definer rights and invoker rights). Create a PROCEDURE as USER test_schema:

CREATE OR REPLACE PROCEDURE set_special_day AUTHID DEFINER
AS
BEGIN
UPDATE TEST_SCHEMA.test_table
SET time_holiday_description = 'Special Day'
WHERE time_id LIKE '____1107%';
COMMIT;
END;
/
GRANT EXECUTE ON set_special_day TO test_user;

Reconnect as USER test_user. When the PROCEDURE is invoked the rights and privileges of the owner are used, not the current user (invoker). A schema owner by default has privileges to all objects created in their schema.

SQL> conn test_user
Enter password: *******
Connected.

SQL> EXEC TEST_SCHEMA.set_special_day

PL/SQL procedure successfully completed.

SQL> SELECT DISTINCT time_holiday_description FROM TEST_SCHEMA.test_table;

TIME_HOLIDAY_DES
----------------
Veterans Day
New Years Day
Independance Day
Memorial Day
Presidents Day
Special Day
Labor Day
Thanksgiving

9 rows selected.

Replace the PROCEDURE as USER test_schema but with AUTHID CURRENT_USER

CREATE OR REPLACE PROCEDURE set_special_day AUTHID CURRENT_USER
AS
BEGIN
UPDATE TEST_SCHEMA.test_table
SET time_holiday_description = 'Special Day 2'
WHERE time_id LIKE '____1107%';
COMMIT;
END;
/

Reconnect as USER test_user.

SQL> CONN test_user
Enter password: *******
Connected.
SQL> EXEC TEST_SCHEMA.set_special_day
BEGIN TEST_SCHEMA.set_special_day; END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "TEST_SCHEMA.SET_SPECIAL_DAY", line 4
ORA-06512: at line 1

There are no privileges granted directly or through default roles to the current USER, test_user. Grant all roles as default to USER test_user using a super-user:

ALTER USER test_user DEFAULT ROLE ALL;

Reconnect as USER test_user.

SQL> conn test_user
Enter password: *******
Connected.

SQL> EXEC TEST_SCHEMA.set_special_day

PL/SQL procedure successfully completed.

SQL> SELECT DISTINCT time_holiday_description FROM TEST_SCHEMA.test_table;

TIME_HOLIDAY_DES
----------------
Veterans Day
New Years Day
Independance Day
Memorial Day
Presidents Day
Special Day 2
Labor Day
Thanksgiving

9 rows selected.

The confusion for me was with AUTHID DEFINER. What if USER test_user were granted temporary privileges to create objects in the test_schema schema?

GRANT DBA TO test_user;

Now create the PROCEDURE as USER test_user using the recently granted built-in DBA ROLE:

SET ROLE DBA;
CREATE OR REPLACE PROCEDURE TEST_SCHEMA.set_special_day AUTHID DEFINER
AS
BEGIN
UPDATE TEST_SCHEMA.test_table
SET time_holiday_description = 'Special Day 3'
WHERE time_id LIKE '____1107%';
COMMIT;
END;
/

Now have super-user REVOKE all the fun roles from USER test_user:

REVOKE dba, test_CRUD FROM test_user;

Reconnect as USER test_user and try to execute the PROCEDURE:

SQL> CONN test_user
Enter password: *******
Connected.
SQL> EXEC TEST_SCHEMA.set_special_day

PL/SQL procedure successfully completed.

SQL> SELECT DISTINCT TIME_HOLIDAY_DESCRIPTION FROM TEST_SCHEMA.test_table;

TIME_HOLIDAY_DES
----------------
Veterans Day
New Years Day
Independance Day
Memorial Day
Special Day 3
Presidents Day
Labor Day
Thanksgiving

9 rows selected.

Confused? I was until I looked at the owner of the procedure.

SQL> SELECT owner, authid FROM dba_procedures WHERE object_name = 'SET_SPECIAL_DAY';

OWNER AUTHID
------------------------------ ------------
TEST_SCHEMA DEFINER

When defining source types the DEFINER for AUTHID DEFINER is really the USER that is owner of the schema where the source type was created; not the USER doing the CREATE. When the test_schema USER created the PROCEDURE it by coincidence was also the owner.

Friday, July 31, 2009

Long time, no gripe (cell phones)

I meant to post this a little while ago so July would have something in it but I held off. As I age like wine into vinegar I do not feel like ranting as often. There is still the same amount of stupid in the world but if you cannot offer solutions or ideas to help resolve the issues you are ranting about then all of that typing and editing is mere therapy for your mind. It sure is not therapy for my hands; they will hurt more than my mind. However, one thing has repeatedly pissed me off for months now: cell phones.

First, let me rant about cell phone users that call or text while operating a vehicle. Pass the law now. I find very few reasons to have a conversation longer than 30 seconds while you are on the road. If you are on the phone more than a minute then pull over. This goes out to the:

  • dingbat on a cell phone that pulled out of the apartment complex in front of me causing me to throw my brakes and skid, that was one heart attack I did not need that early in the morning
  • idiot trying both text and talk while riding cowboy on a bicycle that was on a road with a 50 mph speed limit
  • douche bag that tailgated my fiancĂ©e for 30 minutes and then when traffic stopped abruptly got angry, pulled alongside her and proceeded to cuss her out while still on the damned cell phone
  • granny on I-70 driving 40 mph while on the cell phone as near collisions stacked up behind her
  • tool in front of me on the off ramp that was not paying attention when the light turned green forcing me to wait another light; my bladder wants revenge
  • dimwit not paying attention to the arrows in the Walmart parking lot that actually yelled at me for going the right direction but getting in her way (while on the cell phone the entire time)
  • bonehead at the merge close to where I live that used his yacht-sized vehicle to merge at the last second, without turn signal, less than an inch from clipping me
  • dilrods behind me, left of me and in front of me not paying attention, boxing me in, almost causing an accident

There are a few more but that is all I remember.

Second beef: cell phone commercials that portray other cell phone owners as gigantic pussies. Like that guy that goes crying in the rain as if his entire family were just fed to lions on the Serengeti. "He can't twitter, facebook or youtube." Oh, poor baby. My life is just over because I cannot tweet that I am on my phone standing in the rain somewhere because I am too stupid to find shelter. Boo hoo. Forty million children will starve today but I am in total anguish because I cannot get a status update from the teenager I am stalking on Twitter. Boo hoo. Then there is the family of losers complaining about how the other loser family members are using their cell phones to do crap they used to do on a computer at the same level of annoyance. OMG, Dad posts too much on Facebook. You poor suffering child. How awful your life must be. You are using a $100 cell phone inside a screened in California room while millions have lost their jobs and do not know if they will be able to feed their children in the future. Just awful; get that spoiled brat some Flinstones chewable Xanax, encase her in bubble wrap and roll her down a hill.

Last gripe (that I feel like typing about) – Bluetooth headset talkers that get upset because they think you are listening in on their conversation. You dumbass we have no choice but to listen to your conversation as you stand there and emote with your arms as you are talking and laughing and yelling and carrying on. I think the next time I am going to cuddle up in fetal position and just start yelling loudly "la la la la, I cannot hear you, la, la la la, la la la, not listening, la la la, trying to tune your annoying ass out, la la la."

I could go on but have probably alienated half of my audience and friends by now, although single digits have never bothered me in the past.

Solutions? I despise legislation but banning cell phone usage in vehicles except for emergency usage would be a start. Laws can be revoked or changed when they live out their usefulness. Commercials? Don't buy the product. Bluetooth blabber mouths? Maybe try to kill them with kindness and politeness and if that does not work then kill them with something else?

Friday, June 19, 2009

Afternoon at the library

I really wish I knew how to use the 1.3 mega pixel camera on my netbook. I was at a public library today, killing an hour or two waiting until my sweetheart was off work. You know how libraries are supposed to be quiet places where people can read or study or blog without interruption and annoyance. Two problems with that.

The first is a teenage kid sitting about six feet from the sign clearly stating to refrain from using cellular phones and setting them to silent. He has been on the phone on the same call for fifty minutes while also playing on a Playstation portable. So it's "hmm. Yeah. Mumble mumble. Yeah. Umm. Mumble. The game... Yeah. Um. (voice on phone is louder than him at this point). Yeah. Yeah. Muh. Yeah. Mumble. Mumble. Broken English. Mumble. A'ight. Mumble. Yeah. Yeah. No. No. That wasn't me. Mumble. Mumble. She don't know. Laugh. Mumble. Ah yes. Yeah. No. Mumble. Mumble. Yeah. Uh huh. Nuh-huh. Yeah. Mumble mumble mumble. You get bored. Mumble. I can have that. Blah blah blah. Heh heh heh." over the clackity-clack of whatever he was playing.

Second was the one year old bouncing off everything like a pinball and squealing at the top of his/her lungs. There is no parental guidance to be found. The kid will make a pretty good running back when he or she grows up.

"Mumble. Yeah. A'ight. I just wanna go. Yeah. Yeah. Mumble mumble. Yeah. Umm. Mumble. Yeah. Um. No. Yeah. Yeah. Muh. Yeah. Mumble. Mumble. Broken English. Mumble. A'ight. Mumble. Yeah. Yeah. No. No. That wasn't me. Mumble. Mumble. Whatever she do is okay. Mumble. Yeah."

I am wondering to myself. If I grab the lamp next to me and proceed to bash this individual's skull in would a) the police be called and I hauled off to jail for murder, b) I be loudly applauded until the librarian shushes everyone, or c) I silence the entire building. I can live with the last two. So those odds are 66/33 that I will be in a happier state after clubbing this dude. Tempting.

Finally. After the 64 minute mark the douche bag with the phone hung up and left. The child is getting tired and cranky but quieter if you can believe that.

Wednesday, June 10, 2009

Sleep

I have trouble getting to sleep. I have had this problem for years. It was never a problem until my new neighbors moved into the apartment behind mine and then began having sex at all hours of the night and morning. There is nothing worse than awakening from a decent sleep to the sound of a dog whimpering in heat. The situation changed as I began living with someone. She could fall asleep in minutes while it took me sometimes up to an hour. Of course, she snored. It was hard to sleep with an outboard motor next to me so I would simply escape to the relative silence of the couch with the nearby aquariums. I tried numerous things to help me get to sleep faster, here are the results.

Over-the-counter sleep aids do not work very well with me. Most are just a double dose of Benadryl (diphenhydramine). If it did work, great. If it did not or I was awakened during the night I would spend hours in a haze.

I tried herbal supplements like melatonin and Valerian root. Neither supplement helped me get to sleep. The valerian sometimes had the same effect the diphenhydramine would have. I tried Valerian in capsule form and in Sleepy Time Extra tea. I also tried zinc supplements via tablets and also through ZMA. There were no long term effects.

I tried limiting and eliminating caffeine. That would make a short term difference but nothing would happen long term (more than a couple days). Caffeine intake did not affect how long it took to get to sleep. I also tried varying my exercise and as with the caffeine, no long term changes.

I tried turkey, eating a banana, and drinking a protein shake. None of them worked.

I tried reading before bed and that did not work.

I tried different pillows including full sized, wheat husk and memory foam but there was really no change.

I tried laying flat on my back on a hard floor. I tried cooling my wrists under running water.

There was only one thing that seemed to have benefit: background noise. I had a noisy desk fan I would use to provide background noise. My girlfriend at that time hated that fan. There were times where the fan would be louder than normal or would make squeaking noises as it got older. I would have to take it apart and clean in. My current love of my life has a white noise generator that we use on the “night” setting to get a constant sound of crickets in a field or something like that.

My fiancee also has problems getting to sleep. She has taken Ambien (Zolpidem), Lunesta (Eszopiclone) and ?. She had a prescription for the Ambien and samples of the Lunesta and suggested that I try both and see what happens.

Both drugs were very effective. I could get to sleep regardless of noise from snoring or a television. I stayed asleep, and was able to awaken in time for work.

The Ambien was too effective. Apparently I received an alert on my work phone at around three in the morning, got up, acknowledged the message and then went back to sleep. I do not remember that ever taking place. Once on Ambien I could not get off Ambien without having a really bad night or two or five. Since I was borrowing from my fiancee's prescription and not remembering alerts from work I had to take the Ambien off my list.

The Lunesta was not addictive and I was able to leave the drug, have trouble falling asleep as usual, but still get sleep. The Lunesta left an awful taste in my mouth but I prefer sleep over funny tasting water any night. At this time, there is no generic equivalent for Lunesta.

I went to my physician and had him write a prescription for the Lunesta. I have been taking the Lunesta for a week now in half doses (cutting the pill in half). Sleep has been more than adequate so the drug is doing the job I am asking it to do. Eventually, I want to only use the drug from time to time when I need it (usually Sunday nights). Our feline inhabitants have slightly altered sleeping arrangements in the Grouchy household so the couch and spare bedroom are not realistic options. Eventually the spare bedroom will be available but until then I will probably continue taking the Lunesta when I feel I will need to.

Tuesday, June 09, 2009

The Oracle schema owner user

Most of this blog post is based upon this late 2006 post by Ponder Stibbons. I took those ideas and ran with it.

The usual disclaimers apply. I assume you know what a schema is and what a schema owner is. I also assume you know a little bit about Oracle SQL and Database. This post is based release 10g and will be tested on 11g. All SQL in this post can be freely used, modified, claimed as your own, whatever. All SQL here should be used as a guideline and not used verbatim.

The first steps require SYSDBA or adequate rights and create components used for all schema owners including roles, a profile and perhaps a tablespace.

/*
* Step 1: create table space in non-ASM server (you have probably already done this)
*/
CREATE SMALLFILE TABLESPACE crap_data
LOGGING
DATAFILE
'/u02/oradata/crapdb/CRAP_DATA1.dbf' SIZE 500M AUTOEXTEND ON NEXT 250M MAXSIZE 5000M
, '/u03/oradata/crapdb/CRAP_DATA2.dbf' SIZE 500M AUTOEXTEND ON NEXT 250M MAXSIZE 5000M
, '/u04/oradata/crapdb/CRAP_DATA3.dbf' SIZE 500M AUTOEXTEND ON NEXT 250M MAXSIZE 5000M
, '/u05/oradata/crapdb/CRAP_DATA4.dbf' SIZE 500M AUTOEXTEND ON NEXT 250M MAXSIZE 5000M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
/*
* Step 2: create a profile
*/
CREATE PROFILE schema_owner_profile
LIMIT CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
CONNECT_TIME UNLIMITED
IDLE_TIME UNLIMITED
SESSIONS_PER_USER UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
PRIVATE_SGA UNLIMITED
COMPOSITE_LIMIT UNLIMITED
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL;
/*
* Step 3: create roles
*/
CREATE ROLE schema_owner_role NOT IDENTIFIED;
CREATE ROLE schema_admin_role NOT IDENTIFIED;
-- or
CREATE ROLE schema_admin_role IDENTIFIED BY "30charactercomplexpassword";
/*
* Step 4: grant privileges to both roles
*/
GRANT CREATE SESSION TO schema_owner_role;
GRANT ALTER SESSION TO schema_owner_role, schema_admin_role;
/*
* The bare minimum for a schema administrator: the ability to create source and
* user types that use the "invoker rights clause"
*/
GRANT CREATE PROCEDURE TO schema_admin_role;
GRANT CREATE TYPE TO schema_admin_role;
/*
* Additional privileges
*/
--- Scheduler
GRANT CREATE JOB TO schema_owner_role, schema_admin_role;
--- Convenience
GRANT CREATE TABLE TO schema_admin_role;
GRANT CREATE CLUSTER TO schema_admin_role;
GRANT CREATE VIEW TO schema_admin_role;
GRANT CREATE SEQUENCE TO schema_admin_role;
GRANT CREATE TRIGGER TO schema_admin_role;
--- Data warehousing
GRANT CREATE MATERIALIZED VIEW TO schema_admin_role;
GRANT CREATE DIMENSION TO schema_admin_role;
--- Data cartridge
GRANT CREATE OPERATOR TO schema_admin_role;
GRANT CREATE INDEXTYPE TO schema_admin_role;
--- Restrict synonyms to database administrators ?
GRANT CREATE SYNONYM TO schema_admin_role;

The first step in the block creates a tablespace. It is usually a good idea to give each schema its own tablespace with a similar name but not necessary. The statement will look different if you are using ASM or different mount points (of course). Some articles suggest using the "USERS" tablespace and others suggest using common tablespaces. Use whatever your organization has determined to be best practice.

Step two creates a profile for all schema owner users. Ponder's post recommends not using built-in or Oracle delivered stuff. I agree. If it comes from a vendor, do not use it; copy it and then tailor it to your specific needs.

The third step is to create a role. I chose to create two roles: a default role for all sessions and an administrative role used only for administrative tasks. To do administration on objects in the schema would then require the administrative role to be set before constructive or destructive changes took place. The downside to this approach could prohibit certain GUI tools. The upside is slightly better security and one additional barrier to careless individuals that occasional accidently screw something up repeatedly. The default role is needed to create a session and might also be helpful if you are using the Oracle scheduler and defining jobs that run as the schema owner (more on that later).

The last step (four) is to grant privileges to the two roles. The default role needs to create a session. In my opinion, the roles should be able to alter their sessions (this is a potential risk). The administrative role should have the ability to create procedures and create types. Most source and user types can be defined with an "invoker rights clause" that allows the package to execute using the privileges of the user that defined the source or object or using the privileges of the user executing or using the source or object. If doing security through stored procedures, AUTHID DEFINER is usually used when defined by the schema owner to allow the procedure full DML access to any object in the schema (and then execute privileges are given to user roles). Additional privileges are for convenience and the scheduler so define what you think is needed.

Creating schema owner users is a three step process from this point forward (assuming all use the same tablespace). Create the user, grant the roles, and then set the default role.

/*
* Create the user, grant roles, set "owner" role to default
*/
CREATE USER crapbase
PROFILE schema_owner_profile
IDENTIFIED BY "30charactercomplexpassword"
DEFAULT TABLESPACE crap_data
QUOTA UNLIMITED ON crap_data
ACCOUNT LOCK;
GRANT schema_owner_role TO crapbase;
GRANT schema_admin_role TO crapbase;
ALTER USER crapbase DEFAULT ROLE schema_owner_role;
/*
* Create roles the application might use to access data in the schema
*/
CREATE ROLE crapbase_read_role NOT IDENTIFIED;
CREATE ROLE crapbase_full_role IDENTIFIED BY "30charactercomplexpassword";

The first step is to create the user. Use the profile created previously. Set the default tablespace and give unlimited quota on that tablespace else the schema owner will not be able to define objects that require persistence like tables. Most documents recommend leaving the account locked and then unlocking it when maintenance is to be performed.

The second step is to grant the two roles to the new user.

The last step is to alter the user so the administrative role is not a default role and must be set to obtain those privileges.

One optional step would be to create roles used for read only and full access to objects in the new schema. Individual users would be granted these roles as default or not as default and then the application would set the role(s). Instead of read and full, there could be a more elaborate role structure based upon your organizational requirements.

Oracle Scheduler

A schema can have jobs defined to it in addition to tables, views and procedures. Jobs can be created by the schema owner or by a user with CREATE ANY JOB privileges. Jobs created by the schema owner user or by other users in the schema are owned by the schema owner user. Jobs execute with the privileges of that user. The schema user account can be locked and jobs will continue to execute. In one of our environments, the default role has the CREATE JOB privilege so a job can create another job based upon data in other tables.

Security

The schema owner user should have a strong password that is changed regularly and should be locked unless needed to define a source or object type with AUTHID DEFINER invoker rights. If someone gets in as schema owner they have full control over all data in that schema. Strict roles should be used to limit access to schema data. Users with administrative roles should be used to create tables, indexes, etc. within the schema.

Friday, June 05, 2009

Grouchy gripe day today

I have a couple things I would like to get off my chest this morning.

One. I cannot stand the pseudo techno-intellectuals that make statements based on superior hardware and then assume everything applies downward. Windows 7 runs great on my Intel Core i7 Extreme Edition with 12 GB of DDR3 1866 memory and two Western Digital Raptors in a RAID 0 configuration. No ****? Startup time is fantastic. Well, it should be. You have hardware that God has to wait in line for and everything runs smooth and performance is fantastic. Just because your newest version of Windows or Linux runs incredibly fast on your mega box it does not mean it will perform just as well on a more standard machine given a one or two year upgrade time frame.

Two, I cannot stand web sites that cram every unnecessary Web 2.0 component on their home pages. What works well on your developer workstation with its Core 2 Quad Extreme with 16GB of memory might not work as well on my AMD 754 socket processor and 1 GB of memory or my netbook with its hamster sized screen, Atom processor and 2GB of memory. I just drug my parents kicking and screaming out of the Windows 98 world where processors were named by number and their megahertz clock speed into the land of Windows XP and processors named after sissy, woodland creatures. They do not give a rat's ass if your tag cloud rotates or if that multi-gigabyte, centralized news / ad / feature thing in the middle of your page looks good. They would be more interested in a dancing baby or Jesus.

Sorry. Today I offer zero solutions, only gripes.

Wednesday, May 27, 2009

Building a time dimension with SQL

The time dimension can be found in the majority of star-schema based data warehouses. There are literally dozens of ways to generate the values in the table at the lowest level of the dimension.

The easiest way is to simply plug the contents of the table into a spreadsheet and then import the spreadsheet into a table. Seriously... Why write all the complicated code when you can manually put it all in, show it to users, let them bitch about it, make changes, fix their frak-ups, and then cram the data into a table. The table is not built on an hourly/daily/monthly basis like other dimensions. However, if you wish to use SQL (Oracle) then maybe I can help.

If at any point during this conversation you the reader notice room for improvement, please comment.

The Table

My personal preferences for time dimensions:

  1. define columns only for the basic values and values that are too expensive to calculate within a view, do not define columns that can be easily calculated with date math or TO_CHAR functions within a view
  2. do not bother with partitioning or logging
  3. use table compression

Most time dimensions I see include columns for day of month, end of week, day in year, etc. My reasoning behind storing only the base values and complex values and then doing columns like day in year in a view is two-fold. First is I/O reduction. I would rather do the blocked read of two or three blocks and some CPU rather than do five to ten blocked reads. Second is flexibility. If someone is crawling up your ass for another group of columns so their horribly inefficient DSS actually looks feasible the nerd simply adds the column to the view.  Regardless of what you store in it, the time dimension is small compared to other dimensions and the fact table so why bother with partitioning overhead. The table can be recreated so use NOLOGGING. Compression will reduce the size by about thirty percent leading to fewer I/O requests at the expense of CPU. I always want CPUs working.

Creating Values

MERGE /*+ parallel(time_dimension,4) append */
INTO time_dimension m
USING ( -- Step 7: use the record set as part of the merge
WITH period_sf AS ( --- Step 1 - factored subquery for the various periods from operational databases
SELECT
'Craptastic' AS period_id
, TRUNC(SYSDATE,'YEAR') AS period_beg_date
, TRUNC(ADD_MONTHS(SYSDATE,12),'YEAR')-1 AS period_end_date
FROM dual
WHERE 1 = 1
)
, distinct_years_sf AS ( --- Step 2: factored subquery for all distinct years
SELECT DISTINCT TRUNC(period_beg_date,'YEAR') AS year_beg_date
FROM period_sf
UNION
SELECT DISTINCT TRUNC(period_end_date,'YEAR') AS year_beg_date
FROM period_sf
)
SELECT -- Step 6: select base values for the date, values that will enter the time dimension
TO_CHAR((v1.year_beg_date + v2.day_num),'YYYYMMDD')||'.01' AS time_id
, v1.year_beg_date + v2.day_num AS time_date
, v3.period_id AS time_period_id
, v3.period_beg_date AS time_period_beg_date
, v3.period_end_date AS time_period_end_date
, v1.year_beg_date + v2.day_num - v3.period_beg_date + 1 AS time_period_day
, CASE (v1.year_beg_date + v2.day_num)
WHEN v1.year_beg_date THEN 'New Years Day'
WHEN v1.year_presidents_date THEN 'Presidents Day'
WHEN v1.year_easter_date THEN 'Easter'
WHEN v1.year_memorial_date THEN 'Memorial Day'
WHEN v1.year_independance_date THEN 'Independance Day'
WHEN v1.year_labor_date THEN 'Labor Day'
WHEN v1.year_veterans_date THEN 'Veterans Day'
WHEN v1.year_thanksgiving_date THEN 'Thanksgiving'
WHEN v1.year_christmas_date THEN 'Christmas'
ELSE NULL
END AS time_holiday_text
FROM ( --- Step 4: generate values for each
SELECT
year_id
, year_beg_date
, year_end_date
, CASE
WHEN year_id BETWEEN 1885 AND 1970 THEN year_beg_date + 52
WHEN year_id BETWEEN 1971 AND 9999 THEN NEXT_DAY(year_beg_date + 30,'MON') + 14
ELSE NULL
END AS year_presidents_date
, CASE
WHEN year_id BETWEEN 1900 AND 2199 THEN NEXT_DAY(year_end_date - 244 + easter_value + (CASE WHEN easter_value < 5 THEN 17 ELSE -12 END),'SAT') - 34 + (CASE year_id WHEN 2079 THEN 7 ELSE 0 END)
ELSE NULL
END AS year_easter_date
, CASE
WHEN year_id BETWEEN 1882 AND 1970 THEN year_end_date - 215
WHEN year_id BETWEEN 1971 AND 9999 THEN NEXT_DAY(year_end_date - 214,'MON') - 7
ELSE NULL
END AS year_memorial_date
, CASE
WHEN year_id BETWEEN 1882 AND 9999 THEN year_end_date - 180
ELSE NULL
END AS year_independance_date
, CASE
WHEN year_id BETWEEN 1894 AND 9999 THEN NEXT_DAY(year_end_date - 122,'MON')
ELSE NULL
END AS year_labor_date
, CASE
WHEN year_id BETWEEN 1954 AND 1970 THEN year_end_date - 50
WHEN year_id BETWEEN 1971 AND 1977 THEN NEXT_DAY(year_end_date - 92,'MON') + 21
WHEN year_id BETWEEN 1978 AND 9999 THEN year_end_date - 50
ELSE NULL
END AS year_veterans_date
, CASE
WHEN year_id BETWEEN 1863 AND 1938 THEN NEXT_DAY(year_end_date - 31,'THU') - 7
WHEN year_id = 1939 THEN NEXT_DAY(year_end_date - 61,'THU') + 21
WHEN year_id = 1940 THEN NEXT_DAY(year_end_date - 61,'THU') + 14
WHEN year_id BETWEEN 1941 AND 9999 THEN NEXT_DAY(year_end_date - 61,'THU') + 21
ELSE NULL
END AS year_thanksgiving_date
, CASE
WHEN year_id BETWEEN 1870 AND 9999 THEN year_end_date - 6
ELSE NULL
END AS year_christmas_date
FROM ( --- Step 3: calculate a few values for the years in the second step
SELECT
EXTRACT(YEAR FROM year_beg_date) AS year_id
, year_beg_date
, ADD_MONTHS(year_beg_date,12) - 1 AS year_end_date
, TRUNC(MOD(EXTRACT(YEAR FROM year_beg_date) / 38 * 1440,60) / 2) AS easter_value
FROM distinct_years_sf
)
) v1
JOIN ( --- Step 5: generate numbers 0 through 366 and join to the years
SELECT (LEVEL - 1) AS day_num FROM DUAL CONNECT BY ROWNUM BETWEEN 1 and 367
) v2 ON v2.day_num BETWEEN 0 AND (v1.year_end_date - v1.year_beg_date)
LEFT JOIN period_sf v3 ON (v1.year_beg_date + v2.day_num) BETWEEN period_beg_date AND period_end_date
ORDER BY time_id
) u
ON (m.time_id = u.time_id) --- Step 8: update or insert (i.e. MERGE)
WHEN MATCHED THEN UPDATE
SET
m.time_date = u.time_date
, m.time_period_id = u.time_period_id
, m.time_period_beg_date = u.time_period_beg_date
, m.time_period_end_date = u.time_period_end_date
, m.time_period_day = u.time_period_day
, m.time_holiday_text = u.time_holiday_text
WHEN NOT MATCHED THEN INSERT
VALUES
( u.time_id
, u.time_date
, u.time_period_id
, u.time_period_beg_date
, u.time_period_end_date
, u.time_period_day
, u.time_holiday_text
)

Most time dimensions will have primary units within periods or intervals of time. Sales might have sales periods. Colleges might have semesters. Television might have quarters and sweeps weeks within them. The source for these periods could be one or more tables in an OLTP database or ODS or perhaps already in the data warehouse if using a star-snowflake hybrid schema. Each source should be a factored sub-query containing the period primary key and the dates the period starts and ends. Oracle will create a temporary table for each sub-query. For this example, I chose one period based upon the current year (table dual is available on every Oracle database).

The second step is to generate a record set from all period sub-queries with distinct years (the first date of each year). The sub-query is also factored because it could be used more than once to generate record sets based on individual years.

The next few steps create data specific for each year in the above sub-query. I do this to avoid doing repetitive calculations for all 365 or so days in each year (do you really want the server calculating when Easter occurs for each day or just do it once for each year; yeah I thought so).

Next, each year is joined to a day number, 0 through the end of the year.

Each factored period sub-query that will have values in the time dimension is then LEFT JOINed to the years plus days result set. The left join is required because there might not be overlaps in the various periods. For example: a set of sales periods existed before, during, and then after a corporate acquisition.

The columns from the result set are fed into the USING part of the MERGE statement. The results are either inserted into or used to update the time dimension. The /*+ APPEND */ hint is used to trigger a direct-path insert for table compression and the /*+ PARALLEL */ hint is optional but could speed up the process depending upon your server.

The above example is very, very simplistic. There is an entire data warehousing language and concepts documentation library that I have yet to digest. I am also certain there are better ways of doing the same thing I have done in the SQL above. I could have also used PL/SQL and loop constructs to build the dimension table. To be honest, there are more important things in life.

Feel free to comment on my SQL brain fart. Suggest improvements. Steal the code and call it your own, basking in the glory.

Wednesday, April 22, 2009

Yahoo messenger connection refused

It seems like there are other folks that have noticed connections to Yahoo Messenger are getting a "connection refused" error. I get errors in the morning all the time. Using advice from this page, I tried hard coding an IP address in place of the scs.msg.yahoo.com and it connected first time. This is not a long term solution by any means. The post also suggested doing an ipconfig /flushdns from a Windows command prompt. But as with other errors I am simply spreading the word.

Tuesday, April 21, 2009

Jackets... Playoffs...

To quote myself from Bigsoccer:

After that first period and those two strange goals it is clear this just is not meant to be...

I don't even hear Bill Murray from Caddyshack saying "Cinderella story. Outta nowhere. It looks like a mirac... It's in the goal! It's in the goal! It's in the goal!"

All I have in my head now is that "Bag of Weed" song from Family Guy this Sunday.

The very first playoff game in Nationwide Arena history was over in one minute. With enough electricity in the building to power the cities of Columbus and Detroit what was the worst thing that could happen? A Detroit goal about one minute into the game? How about another Detroit goal at the end of the first period? I am certain the City of Columbus street cleaners are probably happy. The vacuum created by all the air being let out of the arena was probably powerful enough to suck the dirt and trash off streets in a four block radius around Nationwide.

The lowest point of the evening had to be the attempted retaliation for the brutal hit by Stuart on R.J. Umberger. Commodore flops into the Detroit bench after trying to make a hit then proceeds to push a Redwing over and watch Cleary (a Redwing) handling the puck behind the goal. The Jacket player guarding the net leaves to play the puck and Cleary leaving Zetterberg wide open for the third goal as Rick Nash and Mike Commodore literally stand there and watch mere feet away from play. It would have been a sin if there was no retaliation for knocking R.J. into a game five (which is looking like it will never be played) but what the players on the ice did was even worse. They looked like staggered drunks trying to retaliate and let one more goal in while doing so.

Game four better feature one or more of the following:

  • a hard, legal hit that leaves a first or second line player on the Detroit side flat on the ice (extra points for the leg twitch)
  • a goal by Rick Nash, heck I would settle for a crushing blow by Nash on someone at this point
  • a good, legal check on a Redwing by at least six of the following players: Russell, Peca, Huselius, Chimera, Williams, Modin, Vermette, Nash, Voracek
The team needs to exceed the physicality shown toward it, not match it.