Return NULL if the column does not exist

It is a very common challenge for a dba to create scripts that work on every version.

How do you return NULL if a column does not exists?

Imagine I have a view that returns the table_name, column_name and retention_type of my LOBS.


SQL> create table t1(c clob) lob(c) store as (retention);

Table created.

SQL> create table t2(c clob) lob(c) store as (pctversion 10);

Table created.

SQL> create or replace force view v as select table_name,
  column_name,retention_type from user_lobs;

View created.

SQL> select * from v where table_name in ('T1','T2');
TAB COL RETENTION_TYPE
--- --- --------------
T1  C   YES
T2  C   NO

Let’s imagine I try to run this on an antique version of Oracle


SQL> select version from v$instance;
VERSION
-----------------
11.2.0.1.0

SQL> create table t1(c clob) lob(c) store as (retention);

Table created.

SQL> create table t2(c clob) lob(c) store as (pctversion 10);

Table created.

SQL> create or replace force view v as select table_name,column_name,retention_type from user_lobs;

Warning: View created with compilation errors.

SQL> select * from v where table_name in ('T1','T2');
select * from v where table_name in ('T1','T2')
              *
ERROR at line 1:
ORA-04063: view "SCOTT.V" has errors

Obviously the RETENTION_TYPE did not exist in that version.

Let’s default this to NULL !


SQL> create or replace function retention_type return varchar2 is 
  begin return null; end;
/

Function created.

SQL> select * from v where table_name in ('T1','T2');
TAB COL RETENTION_TYPE
--- --- --------------
T1  C
T2  C

Very simple workaround, is not it?

On table reorg and index rebuild

Before you start reading : do not rebuild all your indexes and reorganize all your tables every Sunday morning. One day you may find one of your table missing or one index invalid.

Ok, let’s take a case where table reorg and index rebuild is good.

One of your table was never cleaned up, it grew to 100000000 rows over the last 5 years and you need only the last 2 weeks.

One of your task will be to create a job to clean up your table on a weekly basis to delete rows older than 14 days. This is beyond the scope of this post.

Now you have deleted more than 99% of your rows and you want to reorganize your table and rebuild the index, to gain disk space and performance.

Here is the demo


SQL> DROP TABLE t1;

Table dropped.

SQL> 
SQL> CREATE TABLE t1
  2  (
  3    r     NUMBER,
  4    txt   VARCHAR2 (4000),
  5    y     NUMBER
  6  );

Table created.

SQL> 
SQL> CREATE INDEX i1
  2    ON t1 (r);

Index created.

SQL> 
SQL> INSERT INTO t1
  2    WITH t
  3         AS (    SELECT *
  4             FROM DUAL
  5       CONNECT BY LEVEL < 1001)
  6    SELECT ROWNUM r, LPAD ('X', 100, '.') txt, MOD (ROWNUM, 2) y
  7      FROM t, t;

1000000 rows created.

SQL> 
SQL> DROP TABLE t2;

Table dropped.

SQL> 
SQL> CREATE TABLE t2
  2  (
  3    r     NUMBER,
  4    txt   VARCHAR2 (4000),
  5    y     NUMBER
  6  )
  7  PARTITION BY HASH (r)
  8    (PARTITION T2_P1);

Table created.

SQL> 
SQL> CREATE INDEX i2
  2    ON t2 (r)
  3    LOCAL (PARTITION i2_p1);

Index created.

SQL> 
SQL> INSERT INTO t2
  2    WITH t
  3         AS (    SELECT *
  4             FROM DUAL
  5       CONNECT BY LEVEL < 1001)
  6    SELECT ROWNUM r, LPAD ('X', 100, '.') txt, MOD (ROWNUM, 2) y
  7      FROM t, t;

1000000 rows created.

SQL> 
SQL> DROP TABLE t3;

Table dropped.

SQL> 
SQL> CREATE TABLE t3
  2  (
  3    r     NUMBER,
  4    txt   VARCHAR2 (4000),
  5    y     NUMBER
  6  )
  7  PARTITION BY RANGE (r)
  8    SUBPARTITION BY HASH (r)
  9       SUBPARTITION TEMPLATE (SUBPARTITION s1 )
 10    (PARTITION T3_P1 VALUES LESS THAN (maxvalue));

Table created.

SQL> 
SQL> CREATE INDEX i3
  2    ON t3 (r)
  3    LOCAL (PARTITION i3_p1
  4        (SUBPARTITION i3_p1_s1));

Index created.

SQL> 
SQL> INSERT INTO t3
  2    WITH t
  3         AS (    SELECT *
  4             FROM DUAL
  5       CONNECT BY LEVEL < 1001)
  6    SELECT ROWNUM r, LPAD ('X', 100, '.') txt, MOD (ROWNUM, 2) y
  7      FROM t, t;

1000000 rows created.

SQL> 
SQL> COMMIT;

Commit complete.

SQL> 
SQL>  SELECT segment_name,
  2          segment_type,
  3          partition_name,
  4          sum(bytes),
  5          count(*)
  6     FROM user_extents
  7    WHERE segment_name IN ('T1', 'T2', 'T3', 'I1', 'I2', 'I3')
  8  group by
  9    segment_name,
 10          segment_type,
 11          partition_name
 12  ORDER BY segment_name, partition_name;

SEGMENT_NA SEGMENT_TYPE       PARTITION_     SUM(BYTES)       COUNT(*)
---------- ------------------ ---------- -------------- --------------
I1         INDEX                             16,777,216             31
I2         INDEX PARTITION    I2_P1          16,777,216             31
I3         INDEX SUBPARTITION I3_P1_S1       16,777,216             31
T1         TABLE                            134,217,728             87
T2         TABLE PARTITION    T2_P1         134,217,728             16
T3         TABLE SUBPARTITION T3_P1_S1      134,217,728             16

I created 3 tables, T1, T2 which is partitioned, T3 which is subpartitioned. There is a slight difference in the number of extents between partitioned and non-partitioned table, but this ASSM, so it is fine.


SQL> DELETE FROM t1
  2       WHERE r > 1;

999999 rows deleted.

SQL> 
SQL> COMMIT;

Commit complete.

SQL> 
SQL> DELETE FROM t2
  2       WHERE r > 1;

999999 rows deleted.

SQL> 
SQL> COMMIT;

Commit complete.

SQL> 
SQL> DELETE FROM t3
  2       WHERE r > 1;

999999 rows deleted.

SQL> 
SQL> COMMIT;

Commit complete.

SQL> 
SQL>  SELECT segment_name,
  2          segment_type,
  3          partition_name,
  4          sum(bytes),
  5          count(*)
  6     FROM user_extents
  7    WHERE segment_name IN ('T1', 'T2', 'T3', 'I1', 'I2', 'I3')
  8  group by
  9    segment_name,
 10          segment_type,
 11          partition_name
 12  ORDER BY segment_name, partition_name;

SEGMENT_NA SEGMENT_TYPE       PARTITION_     SUM(BYTES)       COUNT(*)
---------- ------------------ ---------- -------------- --------------
I1         INDEX                             16,777,216             31
I2         INDEX PARTITION    I2_P1          16,777,216             31
I3         INDEX SUBPARTITION I3_P1_S1       16,777,216             31
T1         TABLE                            134,217,728             87
T2         TABLE PARTITION    T2_P1         134,217,728             16
T3         TABLE SUBPARTITION T3_P1_S1      134,217,728             16

I deleted the completed table but one row, however the size of the table and the number of extents did not change.


SQL> ALTER TABLE t1 MOVE;

Table altered.

SQL> 
SQL> ALTER INDEX I1 REBUILD;

Index altered.

SQL> 
SQL> ALTER TABLE t2 MOVE PARTITION T2_P1;

Table altered.

SQL> 
SQL> ALTER INDEX I2 REBUILD PARTITION I2_P1;

Index altered.

SQL> 
SQL> ALTER TABLE t3 MOVE SUBPARTITION T3_P1_S1;

Table altered.

SQL> 
SQL> ALTER INDEX I3 REBUILD SUBPARTITION I3_P1_S1;

Index altered.

SQL> 
SQL>  SELECT segment_name,
  2          segment_type,
  3          partition_name,
  4          sum(bytes),
  5          count(*)
  6     FROM user_extents
  7    WHERE segment_name IN ('T1', 'T2', 'T3', 'I1', 'I2', 'I3')
  8  group by
  9    segment_name,
 10          segment_type,
 11          partition_name
 12  ORDER BY segment_name, partition_name;

SEGMENT_NA SEGMENT_TYPE       PARTITION_     SUM(BYTES)       COUNT(*)
---------- ------------------ ---------- -------------- --------------
I1         INDEX                                 65,536              1
I2         INDEX PARTITION    I2_P1              65,536              1
I3         INDEX SUBPARTITION I3_P1_S1           65,536              1
T1         TABLE                                 65,536              1
T2         TABLE PARTITION    T2_P1           8,388,608              1
T3         TABLE SUBPARTITION T3_P1_S1        8,388,608              1

Now I have reorganized my tables and rebuilt my indexes.

The sized dropped to 64K or 8M and the fragmentation disappeard as the number of extents dropped to 1.

Note you cannot rebuild a whole partitioned index (ORA-14086) nor reorganize a whole partitioned table (ORA-14511). You need to loop through each partition or subpartition.

EXECUTE IMMEDIATE ‘SELECT’ does not execute anything

I am not sure whether some tuning guy at Oracle decided to ignore any SELECT statement after execute immediate to save time doing nothing.

exec execute immediate 'select 1/0 from dual connect by level<9999999999999'

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

But it is really annoying… and not documented as far as I know.

Imagine I want to increase all my sequences by 1000


SQL> create sequence s;

Sequence created.

SQL> select s.nextval from dual;

   NEXTVAL
----------
         1

SQL> begin
  2    for f in (select sequence_name n from user_sequences)
  3    loop
  4      execute immediate
  5        'select '||f.n||'.nextval from dual connect by level<=1000';
  6    end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> select s.currval from dual;

   CURRVAL
----------
         1

Hmm, it does not work. Does SELECT work at all? Yes when it is a SELECT INTO :-)


SQL> drop sequence s;

Sequence dropped.

SQL> create sequence s;

Sequence created.

SQL> select s.nextval from dual;

   NEXTVAL
----------
         1

SQL> declare
  2    type t is table of number index by pls_integer;
  3    c t;
  4  begin
  5    for f in (select sequence_name n from user_sequences)
  6    loop
  7      execute immediate
  8        'select '||f.n||'.nextval from dual connect by level<=1000'
  9        bulk collect into c;
 10    end loop;
 11  end;
 12  /

PL/SQL procedure successfully completed.

SQL> select s.currval from dual;

   CURRVAL
----------
      1001

I wonder in which version this optimization/bug was introduced…

xml and powershell : using XPATH

I wrote about powershell [xml] yesterady : xml and powershell

Let’s see how to use XPATH expressions in Powershell


<emplist>
  <emp no="1">
    <ename>John</ename>
  </emp>
  <emp no="2">
    <ename>Jack</ename>
  </emp>
</emplist>

With the [xml] datatype, we create a navigator :

(([xml](GC foo.xml)).psbase.createnavigator().evaluate(
'//emplist/emp[@no="1"]/ename/text()'
))|%{$_.Value}

John

I have not been seduced by a Microsoft product since ages, but I must say I felt with love in this goody much more than in perl, cygwin, or whatever python, dos, java, vb…

It is simply great to use on the command line and can do my work.

1:0 for Microsoft

Use your own wallet for EM

If you want to get rid of self signed certificate, and the annoying security warnings in your browser, here is how to do it in 2 easy steps

1) create a new wallet in [OMS]/sysman/wallet/console.servername/, either with owm (gui) or with orapki (command line)
2) restart opmn http server

opmnctl stopproc process-type=HTTP_Server
opmnctl startproc process-type=HTTP_Server 

Later, to access your Enterprise Manager Grid Control homepage, access the Apache server and not the oms upload server


opmnctl status -l 
HTTP_Server http1:7779,http2:7201,https1:4445,https2:1159,http3:4890

https1 is Apache (4445) and https2 is Upload (1159)

So the url will be https://yourserver.dom.com:4445/em

Check note 1278231.1

RMAN duplicate does change your DB_NAME !

I had a very serious issue last Friday with errors as weird as ORA-00322: log name of thread num is not current copy. After a clone from Prod to Test, the prod crashed. Both databases are located on the same server (I am not a virtualization fanatic) and clone from prod to test have been done by most of my dba readers.

What did change in 11g ?

Incredibly, in 11g, rman issues the following statement before restore

sql clone "alter system set  db_name = ''PROD'' ...
restore clone primary controlfile...

This is probably related to the capability of cloning a database without connecting to the target database.

At the end of the clone, rman is setting back the db_name to TEST and recreate the TEST controlfile

sql statement: alter system set  db_name = ''TEST'' ...
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "TEST" ...
...
LOGFILE
GROUP 1 ('/.../TEST/redo1.dbf')...

So what’s wrong with this? Howcome could a clone from prod to test screw up the prod db???

Simple, the RMAN job did not complete

1) set new name, restore prod controlfile to test
2) restore issue, for instance ORA-19870: error while restoring backup piece archive1234
3) RMAN-03002: failure of Duplicate Db command

At this point, the complete restore was finished, we restored the missing archivelog, recover and open resetlog.
What happened then???
At this point, remember you still have the prod controlfile (and the prod db_name), so by doing an alter resetlogs, the production redologs will get overwritten without notice !

This is a fairly important change that could really hurt if you are cloning two databases on the same server.

In case you are trying to save a failed database clone, make sure you check db_name and also v$logfile before doing an alter database resetlogs!!!

[alert] Oracle agents on AIX may not work in 2011 with OMS10g

Fuadar recently wrote : Grid Control 10.2.0.5 AIX Alert

Basically, if you have an 10g oms Server (any OS / any release) and aix agents (any release), and according to Note 1171558.1, communication between [10g] Oracle Management Service and [AIX] Management Agents will break due to a default self-signed certificate expiring in 31 Dec 2010.

There is more than one way to solve this

1) you upgrade your oms to 11g. Good luck to do this before end of year…

2) You upgrade your oms to 10.2.0.5, apply patch 10034237 on your oms, create a new certificate, resecure all your agents. Pretty heavy stuff I promise.

3) You use a Third Party Certificate. This may work. I have not tested this for you.

4) You switch from https to http… this is of course not an acceptable workaround as the connection between the agent and the oms will be unsecure, but it may save your Silvester Party.

  • allow both secure and unsecure connections to the oms
  • on all your OMS instances

    
    opmnctl stopall
    emctl secure unlock
    opmnctl startall

  • switch all your agents to http
  • On all your AIX hosts with an agent installed

    
    emctl unsecure agent -omsurl http://omsserver:4890/em/*

    You can find the port for unsecure in your oms server in OMSHOME/sysman/config/emoms.properties under oracle.sysman.emSDK.svlt.ConsoleServerPort.

Happy holidays !


How to solve ORA-4068

I was amazed by this oneliner in stackoverflow.

First, let me introduce you my old foe, ORA-04068 :
Session 1:

SQL> CREATE OR REPLACE PACKAGE P AS 
  2  X NUMBER;Y NUMBER;END;
  3  /

Package created.

SQL> exec P.X := 1

PL/SQL procedure successfully completed.

Session 2:

SQL> CREATE OR REPLACE PACKAGE P AS 
  2  X NUMBER;Z NUMBER;END;
  3  /

Package created.

Session 1:

SQL> exec P.X := 2
BEGIN P.X := 2; END;

*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "SCOTT.P" has been invalidated
ORA-04065: not executed, altered or dropped package "SCOTT.P"
ORA-06508: PL/SQL: could not find program unit being called: "SCOTT.P"
ORA-06512: at line 1

Changing the package in session 2 did invalidate the package variable in session 1.

And the PRAGMA that saves the world : PRAGMA SERIALLY_REUSABLE

Session 1:

SQL> CREATE OR REPLACE PACKAGE P AS 
  2  PRAGMA SERIALLY_REUSABLE;X NUMBER;Y NUMBER;END;
  3  /

Package created.

SQL> exec P.X := 1

PL/SQL procedure successfully completed.

Session 2:

SQL> CREATE OR REPLACE PACKAGE P AS 
  3  PRAGMA SERIALLY_REUSABLE;X NUMBER;Z NUMBER;END;
  2  /

Package created.

Session 1:

SQL> exec P.X := 2

PL/SQL procedure successfully completed.

Oh yes!

make count(*) faster

I just install Oracle Enterprise Linux on my new notebook.

I wanted to check how far could I improve the performance of a count(*)


SQL> drop table big_emp;

table big_emp dropped.
258ms elapsed

SQL> create table big_emp as 
  with l as(select 1 from dual connect by level<=3000) 
  select rownum empno,ename,job,mgr,hiredate,sal,comm,deptno from emp,l,l

table big_emp created.
330,390ms elapsed

SQL> alter table big_emp add primary key(empno)

table big_emp altered.
481,503ms elapsed

SQL> alter system flush buffer_cache

system flush altered.
2,701ms elapsed

SQL> alter system flush shared_pool
system flush altered.
137ms elapsed

SQL> select count(*) from big_emp
COUNT(*)               
---------------------- 
126000000              

9,769ms elapsed

SQL> select count(*) from big_emp
COUNT(*)               
---------------------- 
126000000              

8,157ms elapsed

SQL> alter table big_emp drop primary key

table big_emp altered.
905ms elapsed

SQL> alter table big_emp add primary key(empno) 
  using index (
    create index big_i on big_emp(empno) 
    global partition by hash(empno) 
    partitions 16 parallel 16)

table big_emp altered.
974,300ms elapsed

SQL> alter system flush buffer_cache

system flush altered.
601ms elapsed

SQL> alter system flush shared_pool

system flush altered.
140ms elapsed

SQL> select count(*) from big_emp

COUNT(*)               
---------------------- 
126000000              

5,201ms elapsed

SQL> select count(*) from big_emp

COUNT(*)               
---------------------- 
126000000              

2,958ms elapsed

As it is on a notebook, I suppose the benefit of partitioning is not as good as you could get on your server with lots of fast disks and lot’s of CPUs, but I am pretty happy with the results.

It is still counting 126 Million rows in less than 3 seconds :-)

Thanks for the very kind sponsor of the notebook !

SPARC Supercluster

Oracle buys Sun was an exciting accouncement 20 months ago.

What did change in the Solaris/Oracle Database world?

First, Oracle delivered Exadata on Sun Hardware (x86_64).
Second, they delivered Exadata on Sun Solaris Operating System (x86_64).

But now, they announced a combination of software and hardware that will run Oracle database faster than anything ever before.

I am happy to read Oracle is still investing on R&D on the Sparc processors server line !

This version of TOAD provides READONLY access!

This Toad 11 beta new feature made me so happy !

Not only you can specify a color for your connection (ex: red=prod, green=devl), and this color is much more visible than it was in previous version of TOAD, but now you can set your connection to be read-only.

For any reason, bored, ill, under pressure, tired, you may screw up your database one of those days.

I feel so good that I can now setup my prod connection to be read-only. It is REALLY cool.

Setting the connection read-only is a TOAD feature. As it is in beta it may not be 100% bullet-proof. Mostly it allows you to do only SELECT in the editor, even if you are logged in as SYSTEM or SYS.

Anything comparable in SQL Developer?

[FUN] How to tune select count(*) from t?

A user mentioned one call is faster in test than in prod. And the table has the same size. How could we tune the production to make it quick?


user007@PROD> select count(*) from t;
  COUNT(*)
----------
  19832501

Elapsed: 00:03:05.00

Let’s try in test :


user007@TEST> select count(*) from t
select count(*) from t
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

Elapsed: 00:00:00.16

Wow, 00:00:00.16! This is a much quicker response time. Probably the ORA-942 was catched by the application and the user did not realise it…

Next time someone ask you to tune a count(*), just revoke access to the table to optimize the response time :twisted:

SQL developer for dba

Historically and semantically, SQL Developer is a developer tool. There is very little comparison with TOAD for the dba.

But… Oracle is working on this! In the latest release, 3.0 EA1, you will be able to see Tablespaces, Redo logs and controlfile, finally.

Still no session browser, but I am delighted that the database administrators are not getting forgotten !

Thanks Surachart for spreading the news!

jdbc ssl

I already wrote about jdbc hello world and listener with tcps.

Let’s combine both technologies !
TCPS.java

import java.util.Properties;
import java.security.Security;
import java.sql.*;
import javax.net.ssl.*;

public class TCPS {
  public static void main(String argv[]) throws SQLException {
    String url = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(Host=dbsrv001)(Port=12345))(CONNECT_DATA=(SID=DB01)))";
    Properties props = new Properties();
    props.setProperty("user", "scott");
    props.setProperty("password", "tiger");
    props.setProperty("javax.net.ssl.trustStore","cwallet.sso");
    props.setProperty("javax.net.ssl.trustStoreType","SSO");
    Security.addProvider(new oracle.security.pki.OraclePKIProvider());
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
    Connection conn = DriverManager.getConnection(url, props);
    ResultSet res = conn.
      prepareCall("select 'Hello SSL World' txt from dual").
      executeQuery();
    res.next();
    System.out.println(res.getString("TXT"));
  }
}

I have an auto-login wallet (cwallet.sso) with the trusted certificate from the server.

There are a few jar’s to use:

$ CLASSPATH=$ORACLE_HOME/jdbc/lib/ojdbc5.jar
$ CLASSPATH=$CLASSPATH:$ORACLE_HOME/jlib/oraclepki.jar
$ CLASSPATH=$CLASSPATH:$ORACLE_HOME/jlib/osdt_cert.jar
$ CLASSPATH=$CLASSPATH:$ORACLE_HOME/jlib/osdt_core.jar
$ CLASSPATH=$CLASSPATH:.
$ export CLASSPATH
$ javac TCPS.java
$ java TCPS
Hello SSL World

Greatly inspired by Jean de Lavarene’s white paper : SSL With Oracle JDBC Thin Driver

How to check if I have a pending transaction?

Pretty straightforward, check if dbms_transaction.step_id is null!

SQL> select dbms_transaction.step_id from dual;

        STEP_ID
---------------

SQL> insert into t values (1);

1 row created.

SQL> select dbms_transaction.step_id from dual;

        STEP_ID
---------------
114352430549782

SQL> commit;

Commit complete.

SQL> select dbms_transaction.step_id from dual;

        STEP_ID
---------------

SQL> insert into t values (2);

1 row created.

SQL> select dbms_transaction.step_id from dual;

        STEP_ID
---------------
124248035235852

SQL> rollback;

Rollback complete.

SQL> select dbms_transaction.step_id from dual;

        STEP_ID
---------------

on my bookshelf

I just started a new job as a dba and placed some books on my desk.

Applied Mathematics for Database Professionals by Lex de Haan and Toon Koppelaars
SQL Design Patterns by Vadim Tropashko
Beginning Oracle Database 11g Administration by Iggy Fernandez
Expert Oracle Database Architecture by Tom Kyte
Oracle 11g New Features by Brian Carr, John Garmany, V. J. Jain, Steve Karam and Lutz Hartmann
Advanced Oracle SQL Programming my book…

I have not worked as an 11g dba before, I will surely learn a lot in those books (and giveaway my book) !

11.2.0.2

I have read on Surachart Opun’s blog that 11.2.0.2 is available, but also that the patchset is now a full installation.

In the past, if you wanted to have 9.2.0.8 or 10.2.0.5, you needed to install both the base version, 9.2.0.1 or 10.2.0.1 and the patchset, 9.2.0.8 or 10.2.0.5. Very often the patchset itself was bigger and took more time than the base database installation.
According to Note 1189783.1, it is now possible to install 11.2.0.2 without having to install both 11.2.0.1 + a patchset.

if you are installing Oracle Database on a system without an existing Oracle Database installation, then you can simply install the release 11.2.0.2 patch set

I like this :-)

last access time of a file

I was reading http://blogs.oracle.com/myoraclediary and there was a command about printing the modification details of a file.

In Linux / Cygwin “stat” exists as a command

$ stat /etc/hosts
Access: 2010-08-25 15:20:49.782522200 +0200
Modify: 2010-08-18 14:04:25.868114200 +0200
Change: 2010-08-18 14:04:26.072413100 +0200

Or use the one-liner perl below


### st_atime;         /* Time of last access */
$ perl -e 'use POSIX;[-f"/etc/hosts"]&&print ctime((stat(_))[8])'
Wed Aug 25 15:20:08 2010
### st_mtime;         /* Time of last data modification */
$ perl -e 'use POSIX;[-f"/etc/hosts"]&&print ctime((stat(_))[9])'
Wed Jun 10 11:36:40 2009
### st_ctime;         /* Time of last file status change */
$ perl -e 'use POSIX;[-f"/etc/hosts"]&&print ctime((stat(_))[10])' 
Wed Aug 25 01:00:07 2010

OCE Solaris Network Admin

I read Paul Sorensen blog : Sun certifications will be renamed next week.

For instance the Sun Certified Network Administrator will be Oracle Certified Expert, Oracle Solaris 10 Network Administrator.

But to get the OCE Solaris title, you need to upgrade your certification […] to receive an Oracle certification title (check more details on the Oracle Certification website here).

I never upgraded my Sun credentials, I am a Solaris 2.6 system admin and Solaris 7 network admin, but this equivalency with OCE surprised me.

There are even two OCM Java certifications : Oracle Certified Master, Java EE 5 Enterprise Architect, originally Sun Certified Enterprise Architect (SCEA) and Oracle Certified Master, Java SE6 Developer, originally Sun Certified Java Developer (SCJD)

Also, since a few months, there is an upgrade to the 9i OCM DBA certification : 11g OCM Upgrade. I hope I can do this one :)

Do you know the ORA- nonerrors?

In one of my script, I am checking at the end for any ORA- error. And if I have any ORA- error, I quit with an error.

So far so good.

Also when I run a report from the shell, I do set the sqlplus settings I like and I expect the script to receive the output with no ORA- error at the beginning.

But watch this !

$ echo "set feed off hea off newp none
conn scott/tiger
select * from dual;"|sqlplus /nolog
ERROR:
ORA-28002: the password will expire within 10 days

X

My password will expire in 10 days. And this screw up my day :(

Hardcoding SYSDATE

I see TRUNC(SYSDATE) in the code very often. Is this good or bad?

Over my past two years as a developer, I can tell you it is pretty bad. What does TRUNC(SYSDATE) mean? It is today’s date.

But when does today starts and ends? And at the time of the writing, it is already tomorrow in Thailand.

We could argue about using CURRENT_DATE instead of sysdate, but it does not solve everything. Maybe you want your day to end at 10:30pm and initialize for the next day after some post processing. Maybe you want some components running in different timezones. Maybe you want to rollback and rollforward the date in your test systems…

Also the bad news, if your report hardcodes SYSDATE, kind of SELECT SUM(AMT) FROM T WHERE EXPIRY_DATE>TRUNC(SYSDATE), you will not be able to run it tomorrow in case it fails today.

No, I think that you would better store the date in a table and update it daily.

So you will have SELECT SUM(T.AMT) FROM T, TRADINGDAY WHERE T.EXPIRY_DATE>TRADINGDAY.TRADINGDAY and a daily job running UPDATE TRADEDAY SET TRADINGDAY=TRADINGDAY+1;.

You may want to consider the performance overhead of reading from a table instead of using the very performant SYSDATE function…

On Express Edition

If you want to start with developing free software on old technology, you can download the Oracle Database Express Edition.

Actually, Oracle 10g is more than 5 years old and it is the only version available as Express Edition, 11g has not been released at the time of the writing, and no patch has been released, not even for the worst bugs or security breaches.

Most probably you will not want to run this for your sensitive data.

If you are serious about Oracle Development, you could download the Enterprise Edition for free, and get the Personal Edition on Oracle Shop for support and patches.

Last Friday 13th August, a news/rumor (not an official announcement) was posted on the opensolaris forum, apparently OpenSolaris has been cancelled in favor of an Oracle Solaris 11 Express. Not sure if it will come in the same format as the database XE, but this does not sound to please the community.

After the Oracle sues Google, it does not make Oracle very popular in the opensource community at the very moment

YMMV

to cvs or to subversion

First surprise, after migration, the size of my subversion folder is double the size of my cvs folder. With a bunch of 2Gb disks shared amoung dozens of unix persons, and regular reminders the current usage reached 100%, you will feel the pain of having each developers doublesizing its home directory…
The reason is a .svn/test-base directory containing a duplicate of your local copy.
The benefit there is that it reduces network usage, for instance when diffing with the working version.

Second surprise, not sure if it is a generic issue or related to the one above, a full checkout take ages :( .

svn and cvs command line interfaces almost have the same options, a bit like vi and vim -vi is my favorite- but the “tagging” massively differs. The is no cvs tag command in subversion, you use a different syntax.
cvs :


$ cd /home/lsc/CVS/proj1/source/pkg
$ cvs tag MYTAG helloworld.pkb helloworld.pks

now enjoy the power of subversion! [updated as I found the –parents option]

$ cd /home/lsc/SVN/proj1/source/pkg
$ svn copy --parents helloworld.pkb http://myserver.domain.com/svn/REPOS1/proj1/tags/MYTAG/proj1/source/pkg -m "my first svn tag"
$ svn copy --parents helloworld.pks http://myserver.domain.com/svn/REPOS1/proj1/tags/MYTAG/proj1/source/pkg -m "my first svn tag"

I always loved the cvs rename command

$ cvs rename pkg1.pkb pkg2.pkb
cvs [rename aborted]: Remote server does not support rename

Joke apart, in CVS I used to physically logon to the server and manually move the pkg1.pkb,v to pkg2.pkb,v
Renaming works in subversion :

$ svn move pkg1.pkb pkg2.pkb
A         pkg2.pkb
D         pkg1.pkb

Update: One more annoyance, you cannot checkout a single file :(
subversion faq

extract xml from the command line

I just discovered this morning this cool utility in my /bin directory : xmllint

You can use it to extract values from your xml files within your shell scripts


$ cat foo.xml
<emplist>
  <emp no="1">
    <ename>John</ename>
  </emp>
  <emp no="2">
    <ename>Jack</ename>
  </emp>
</emplist>
$ echo 'cat //emplist/emp[@no="1"]/ename/text()'|
  xmllint --shell foo.xml |
  sed -n 3p
John

I like this !

connect by and recursive with (part 2)

According to the doc
The subquery_factoring_clause now supports recursive subquery
factoring (recursive WITH), which lets you query hierarchical data.
This feature is more powerful than CONNECT BY in that it
provides depth-first search and breadth-first search, and supports
multiple recursive branches. A new search_clause and cycle_clause
let you specify an ordering for the rows and mark cycles in the
recursion

As written there and there, recursive with has more capabilities. It is also ANSI and implemented in DB2, MS SQL Server. CONNECT BY is an Oracle oddity. But does recursive with perfoms as well as connect by?

  1. Create a big emp
  2. 
    create table big_emp as
    with t(empno,mgr) as
    (select 1, null from dual
    union all 
    select empno+1,trunc(dbms_random.value(1+empno/10,empno))
    from t 
    where empno<100000)
    select * from t
    

  3. execution plan
  4. CONNECT BY

    
    select empno,mgr
    from big_emp
    connect by mgr = prior empno
    start with mgr is null;
    
    E M 
    - -
    1 -  
    2 1 
    4 2 
    6 2 
    8 6 
    
    Operation                 Object    Rows Time Cost   Bytes
    ------------------------- ------- ------ ---- ---- -------
    SELECT STATEMENT                       3    3  185      78
    CONNECT BY WITH FILTERING
    TABLE ACCESS FULL         BIG_EMP      1    1   61      10
    HASH JOIN                              2    2  122      46
    CONNECT BY PUMP
    TABLE ACCESS FULL         BIG_EMP 100000    1   61 1000000
    

    recursive WITH

    
    with e(empno,mgr) as (
    select empno, mgr 
    from big_emp 
    where mgr is null
    union all
    select f.empno,f.mgr 
    from big_emp f, e 
    where e.empno=f.mgr)
    select empno,mgr
    from e;
    
    E M
    - -
    1 -  
    2 1 
    3 1 
    4 2 
    5 3 
    ...
    
    Operation                 Object    Rows Time Cost   Bytes
    ------------------------- ------- ------ ---- ---- -------
    SELECT STATEMENT                       3    3  183      78
    VIEW                                   3    3  183      78
    UNION ALL (RECURSIVE WITH) BREADTH FIRST
    TABLE ACCESS FULL BIG_EMP              1    1   61      10
    HASH JOIN                              2    2  122      46
    RECURSIVE WITH PUMP
    TABLE ACCESS FULL BIG_EMP         100000    1   61 1000000
    

    In this particular simple case, it seems CONNECT BY have a 1% higher cost.

  5. execution time
  6. CONNECT BY

    
    select sum(mgr)
    from 
    (
    select empno,mgr
    from big_emp
    connect by mgr = prior empno
    start with mgr is null
    )
    
    SUM(MGR) 
    ------------
    2745293877 
    
    1 rows returned in 0.73 seconds 
    

    recursive with

    
    with e(empno,mgr) as (
    select empno, mgr 
    from big_emp 
    where mgr is null
    union all
    select f.empno,f.mgr 
    from big_emp f, e 
    where e.empno=f.mgr)
    select sum(mgr)
    from e;
    
    SUM(MGR) 
    ------------
    2745293877 
    
    1 rows returned in 1.24 seconds 
    

Honestly I am not surprised that CONNECT BY is faster, CONNECT BY has been in the Oracle database forever and has been massively tuned in 8.1.7.4.

And then one day you find, ten years have got behind you.

Oracle Certified Master