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.

Thursday, October 29, 2009

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 bag 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. (I think Ozzy Osbourne also said this)...

Monday, September 21, 2009

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;

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.

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.

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.

Tuesday, March 31, 2009

ORA-01839 using an INTERVAL

One of our application developers had a program that was been active and usually working for years but for some reason does not work issuing a cursor not open error every once in a while. It has been a source of anger for a while because the application will magically start working a day later without any changes to the application or host system. The source of the problem: an error occurred at cursor open due to date arithmetic using an INTERVAL and that error was not handled.

Shame on someone for not handling the error.

Regarding the INTERVAL logic, if you add or subtract a month interval from a date that results in the day being outside the number of days in the month (going from March with 31 to April with 30 for example) the statement will throw an error.

The developer is better off using the Oracle function ADD_MONTHS which does not throw an error. If the application needs a value of the first of the month if 31 days then the application should probably be subtracting days instead of months. If the warranty expires in 90 days then add 90 days. If the warranty expires in three months then unless there is some complex logic in the contract it should end on June 30 and not July 1 (just assuming).
SELECT SYSDATE FROM DUAL;
SYSDATE
---------
31-MAR-09

SELECT SYSDATE - INTERVAL '2' MONTH FROM DUAL;
SYSDATE-I
---------
31-JAN-09

SELECT SYSDATE - INTERVAL '4' MONTH FROM DUAL;
ORA-01839: date not valid for month specified

SELECT ADD_MONTHS(SYSDATE,-4) FROM DUAL;
ADD_MONTH
---------
30-NOV-08
The INTERVAL logic has been discussed elsewhere including a comment from Tom Kyte about the pure ANSI implementation. I document it here to spread the word and hopefully keep others from banging their heads into a frothy pulp over silly crap like this. Standards; gotta love them.

Monday, March 30, 2009

Recession garden

I do not remember which station I heard this on but their news teaser mentioned something about what to do for your "recession garden".

A what?

So because the United States is in a recession I am supposed to grow my own food in my own personal recession garden?

A what?

Do these people know how hard it is to maintain a garden large enough to supplement the store bought food supply a family requires to survive? Do they know how expensive it is? Hmm, I just lost my job and I am tapping my cash reserves to stay inside my house and keep my family fed. Wait, I got it. I will make a recession garden.

First I have no problem whatsoever with home gardens. If you truly want fresh tomatoes and other vegetables right out of the ground or off the vine you really cannot beat a home garden. Well that is not true. There are plenty of local farmers with farm markets where all sorts of freshness are available. I would rather help keep them in business because they serve a greater need.

Second, do these people live in Ohio? Residential soil is better suited for making pottery than growing things. You will likely need to borrow or rent a machine to grind up and process the soil and more than likely you will need to purchase a decent amount of top soil to grow stuff in.

Lastly, seed and chemicals for growth and parasite control are not free. Sanitization will also be required. Are there stray cats in the neighborhood? If so, you just built them another litter box so be prepared for that.

What is next? Recession Chia herb gardens? Recession barn so we can raise our own meat?

Wednesday, February 25, 2009

ORA-01578 and related heart attacks

Holy ****! We get this heart attack inducing error message in our production system:
ORA-01578: ORACLE data block corrupted (file # 4, block # 1510490)
ORA-01110: data file 4: '/u16/app/oracle/oradata/database/main_effin.dbf
ORA-26040: Data block was loaded using the NOLOGGING option
Within seconds we are hitting Metalink and Google and Oracle forums and Burleson Consulting's sites and opening up service requests. It's not looking good. We call friends. "I've never seen that one before!" F***! We're so f*****. We consider running dbv. We consider preparing for a full restore. We data pump the table into a file on a different file system. We drop the table. We recreate the table. S**!, error when trying to create the table from the import. Drop the table again. Import the table. Both of us have to pee so bad we could extinguish an active volcano.

The Oracle finally calls back.

No problem. We see this a number of times each month.

Here's the deal.

The table and index are set to full redo logging but the CLOB in that table is not. A couple months ago we had to do a full restore of our production database. This part gets confusing because it's being related second hand but the block got marked as LOGGING because of the restore even though the LOB options are set to not generate full REDO logging. When someone tried to update that table Oracle as a safety measure marked the block as read only until measures were taken to check it. The "corruption" in this case wasn't corruption as in your data is fooggegated and there is some crap lost somewhere. The best thing to do is data pump export, drop, data pump import (what we were trying to do while freaking out before calm, cool, collected Mr. Oracle called). He also explained that generating REDO for LOBs is painfully expensive so there is a case for NOT generating REDO for them if the data are insignificant and/or can be recreated.

So here I am, bladder emptied. It felt so good it feels like I'm still taking that leak. My heart rate has returned to close to normal.

I really need to win the lottery...

Wednesday, January 07, 2009

The Chinese buffet

The girlfriend and I have made a few trips to a Chinese buffet recently. Those eateries are a wonderful example of what is wrong with people and why both of us stay hidden from society as a whole.

One of the easiest groups of people to get upset at are the wasteful slobs that pile a plate full of food, eat maybe half of it, then go back for another overflowing plate of mostly the same stuff still on the original plate. If the goal is to sample everything then take reasonable portions of stuff you are not sure if you will like, more of stuff you do like. The same ideals apply to those who want to feed until gorged and no longer mobile under their own locomotion. Americans waste an ungodly amount of food every year; enough to make world hunger probably go away or diminish to the level of "now what were you complaining about again?"

Then there are the food thieves. They are the ones that will secretly package and hide food in their garb or handbags during the consumption process. One for my gut, one for my purse. Two for my gut, one for my handbag. I understand it is about getting your money's worth but walking into a buffet with a Gucci wallet then walking out looking like you were about to go camping or were just enlisted into the military is another story.

For some strange reason a growing number of groups of people believe the buffet gives them a pass to exclude all manners and parenting from their public pie-hole stuffing. The other night there were two youths texting over their cell phones; so the missus and I had to endure twenty minutes of phone sound effects at the highest volume. Why? A group of loud, obnoxious cretins have waddled forth from their front porch couches to continue their "oh so important" 24 hour cell phone conversations. Yet if I were to go over, politely ask them to shut their noise holes and turn their ringer volumes down from a level that can be heard from space, I'd probably be shot. If I were to make Swiss cheese of their skulls, I'd go to jail after a thunderous round of applause. Also included in that group are the parents that just let their kids run feral like they were at a McDonalds. It's not a McDonalds. I understand the lack of oxygen due to not taking adequate breaths during your face stuffing might be part of the problem but when the floor looks like the badlands of a World War 1 trench war then you should be in jail and your kids should be in a compound somewhere.

Another group of people that aggravate me are the people that go to eat only crab legs. If you have seen them in action, you know who I am talking about. They usually travel in groups of two and ask to sit as close or with a direct line of sight to the pan containing the crab legs. The first thing these sloths do is empty that pan of crab legs. When the employees bring out a fresh pan of crab legs the stop what they are doing, swallow what is in or within the orbit around their mouths, then go back up to empty the pan of crab legs. Why by courteous and leave a few legs behind when you can shovel the entire catch onto your plate then shuffle back to the safety of the pile of shell carcasses and litter surrounding your table. I always pray that there will be two competing tribes of crab leg mooches so I can take humor from the situation as they jockey for position and circle the other buffet foods with an empty plate waiting for the crab legs to come out. It is like some sick game of musical chairs, just with food. It makes me feel a little better to think that a group of hardened men in Alaska didn't risk their lives in the dangerous, freezing oceans so two cellulite filled meat bags could gorge themselves on the cheap.

When I go to the Chinese buffet it is usually because I want a cheap variety of Oriental cuisine. I love steamed dumplings, green beans, and the California rolls with wasabi and ginger slices. I usually get a little bit of everything. I leave a clean plate. I eat quietly. If I see someone eyeing a dish I'm in the process of emptying I'll ask if they would like some or wait until a fresh batch comes out. I usually don't let the masses get to me but the last trip to the buffet just didn't sit right with me; too much rudeness and waste.