Today I needed to create an audit statement with a lot of users who are excepted from auditing. It looks somewhat like : audit policy <policy_name> except <comma separated list of users> For my requirement all Oracle Maintained account should be excepted from auditing. Following statement made it easy: spool policy_on.sql select ‘audit policy […]
Continue ReadingDistribution of values in a column
Distribution of data For calculating the right execution-plan Oracle often needs the distribution of values for a column. For an outlier value a index can easily be used. For a value that is present in half the rows a index might be less effective. For a similar exercise I needed the distribution of values myself. […]
Continue ReadingCreating an ACL
A very simple example of creating an ACL for a principal and a host: Creating the ACLS (qith the associated principal) itself. exec dbms_network_acl_admin.create_acl( acl=>’print_permissions.xml’, description=>’Gebruikt voor Print functionaliteit’, principal=>’APEX_230100′, is_grant=>TRUE, privilege=>’connect’); Associating a host to the ACL: exec dbms_network_acl_admin.assign_acl( acl=>’print_permissions.xml’, host=>’machine-name.domain.domain’, lower_port=>8010, upper_port=>8010); This is a starting point. You can go from here … […]
Continue ReadingDB2 : Cost Calculation 3 : index only revisited
Index Why In the DB2 : Cost Calculation 2 : index only post I noticed something upsetting, well, at least it was upsetting to me. We looked at the explain plan for looking up 1 value by an index. The index had nlevel=3. So there is a root-node, some intermediate-nodes and finally finally some leaf-nodes. […]
Continue ReadingDB2 Row generator
Sometimes you need a mechanism to generate rows. For example when you need to create a demonstration table . Recursive CTE’s (Common Table Expression) might be your friend WITH engine (rownum) AS (select 1 as rownum from sysibm.sysdummy1 union all select rownum + 1 AS rownum from engine where rownum<10) SELECT rownum FROM engine; […]
Continue Reading$SQLPATH vs $ORACLE_PATH
Every time I install a new oracle machine it hits me: Why is login.sql not executed when I start a sqlplus session. So here is a quick reminder for myself: sqlplus is not looking in $SQLPATH for login.sql ….. it is looking in $ORACLE_PATH So set $ORACLE_PATH and you will be fine. This […]
Continue ReadingRecursive SQL in DB2
Problem Playing around in DB2 I wanted to know how to create a hiearchical query. This because I wanted to create view for explain plans, a bit resembling the explain plans which oracle provides. First step is understanding how to make the recursive SQL’s The setup: drop table relations; create table relations (id int, parent […]
Continue ReadingSession statistics
You can have a look at v$sesstat (joined with v$staname) to see the value of some statistics at this moment. What is a little more difficult to see is how statistics change over time. So I wrote a script for it. What it does: Gather all statistics, of one or all sessions, and put them […]
Continue Reading