hello world

I am setting a new blog at laurentschneider.com

Please update your feeds to point to
http//laurentschneider.com/feed

Chinese Chess Swiss Champion 2006

I was honored to receive this trophy yesterday :



More results under http://www.sxv.ch/news_2006.php

Restrict network access to listener

If I have a limited number of db clients which are authorized to access my listener, I can restrict access to my listener by setting only two parameters in sqlnet.ora.


TCP.VALIDNODE_CHECKING = yes
TCP.INVITED_NODES = (dbclient001,chltlxlsc1)


chltlxlsc1 is my db server, I include it in the list, it is required to start the listener locally.

From dbclient001, I can connect :

$ sqlplus scott/tiger@lsc02

SQL*Plus: Release 10.1.0.4.2 - Production on Wed Nov 22 09:47:43 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning option
JServer Release 9.2.0.8.0 - Production


From dbclient002, I cannot connect

$ sqlplus scott/tiger@lsc02

SQL*Plus: Release 10.1.0.4.2 - Production on Wed Nov 22 09:48:26 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

ERROR:
ORA-12547: TNS:lost contact


backup your blog!

You surely have not missed the "Amazing November 2006 Blog Catastrophe" of Mark Rittman.

I use blogger and by typing "backup blogger" in google, I found : answer=41447

It let you change the formatting of your blog to have all your posts (max 999) and all your comments in one page. If you do not have your own ISP but use blogspot.com, then it will replace your blog by this not-fancy page. But just for the time to save it on your local disk and then you restore your template. Surely less than 5 minutes , unless you surf with a 100 bps or 480 bps underwater accoustic modem like this one :


I have now a dump of my blog. The size of the html file is 440k right now.

pivot table

First I apologize for the confusion with previous post of mines, where I defined row generators as pivot table.

Here I talking about transposing rows into columns PIVOT and transposing columns into rows UNPIVOT

Ok, back in 2003, I had a data model were all attributes were stored in same table (a miracle of java tools generating data models)

select person.name,
  property.type,
  property.value
from person, property
where 
  person.id=property.person;

NAME TYPE     VALUE
---- -------- ------
John gender   male
Mary category junior
Mary gender   female

for datawarehousing purpose, I had to get the attributes, if set, as a column, so I started with outer joining for each attribute (they were plenty, not just two)

select name, 
  gender.value gender, 
  category.value category
from person,
  property gender,
  property category
where
  person.id = gender.person(+) 
  and gender.type(+)='gender'
  and person.id = category.person(+)
  and category.type(+)='category';

NAME GENDER CATEGO
---- ------ ------
Mary female junior
John male

By using the Tom Kyte method described on asktom, I could have used aggregation.

select name,
  max(decode(type,'gender',value)) gender,
  max(decode(type,'category',value)) category
from person , property
where person.id = property.person (+)
group by name;

NAME GENDER CATEGO
---- ------ ------
John male
Mary female junior

To do the opposite, I posted once in a forum

select deptno,
  decode(x,1,'DNAME','LOC') type,
  decode(x,1,dname,loc) value
from dept,
 (select 1 x from dual union all
  select 2 from dual);

    DEPTNO TYPE  VALUE
---------- ----- --------------
        10 DNAME ACCOUNTING
        20 DNAME RESEARCH
        30 DNAME SALES
        40 DNAME OPERATIONS
        10 LOC   NEW YORK
        20 LOC   DALLAS
        30 LOC   CHICAGO
        40 LOC   BOSTON

Well, in the next generation database, this is going to be easier, maybe.

With the introduction of pivot keyword, the following should work

select name, type, value
from person , property
pivot (max(value) 
for type in (
  'gender' as gender,
  'category' as category))
where person.id = property.person (+);

and with the unpivot keyword

select * 
from dept 
unpivot (value 
for type in (
  dname as 'DNAME',
  loc as 'LOC'));

It would be interesting to compare the execution plans !

track comments

This is a goodie : you can monitor comments on a blog article. Then all the new comments can be sent by mail or RSS feed.

Check http://co.mments.com, you can then add a button to your browser to monitor an article for comments

How To Add The Domain Name Of The Host To Name Of The Agent

I have been looking for this note for ages : Metalink note 295949.1

Now I know how to rename my targets in Grid Control! the trick is to stop the agent
AH/emctl stop agent
to remove the upload and state files/subdirectories

cd AH/sysman/emd
find state upload recv agntstmp.txt lastupld.xml protocol.ini -type f -exec rm {} \;
rm -r state/*
to remove the agent in the grid

col TARGET_TYPE for a17
col TARGET_NAME for a60
set lin 100 pages 0 emb on newp none head on autop on
select TARGET_TYPE,TARGET_NAME
from MGMT$TARGET
where TARGET_TYPE='oracle_emd'
order by TARGET_NAME;
var target_name varchar2(40)
prompt Enter the target_name to delete
exec :target_name:='&TARGET_NAME';if (:target_name is not null) then mgmt_admin.cleanup_agent(:target_name); end if

to edit the targets.xml
vi AG/sysman/emd/targets.xml ### Get the source of the traditional vi
to rename your targets (listener, host, db),




and restart your agent
AH/emctl start agent
hth

sqldeveloper 1.1 evaluation available for download

check Oracle SQL Developer 1.1 Evaluation Release is Now Available!

PS: there is now a RPM for Linux, which I installed on my SLES 10 notebook with SUN JDK 1.6.

difference between two dates

How can i get the difference in days between two dates, d1 and d2 ?
-
for example :

with t as (select
  to_date('2000-02-01','YYYY-MM-DD') d1,
  to_date('2000-01-23','YYYY-MM-DD') d2
  from dual)
select d1,d2,d2-d1
from t;
D1         D2              D2-D1
---------- ---------- ----------
01.02.2000 23.01.2000         -9

How can i get the difference in hours:minutes:seconds between two dates, d1 and d2 ?
Use the interval datatype. To have a format different than the default +000000000 00:00:00.000000, use extract

with t as (select
  to_date('2000-02-01 11:22:02',
    'YYYY-MM-DD HH24:MI:SS') d1,
  to_date('2000-01-23 12:00:03',
    'YYYY-MM-DD HH24:MI:SS') d2
  from dual)
select d1,d2,case
  when d1    extract(day from ((d2 - d1)
      day to second))*24 +
    extract(hour from ((d2 - d1)
      day to second))||':'||
    extract(minute from ((d2 - d1)
      day to second))||':'||
    extract(second from ((d2 - d1)
      day to second))
  when d1>d2 then
    '-'||
    (extract(day from ((d1 - d2)
      day to second))*24 +
    extract(hour from ((d1 - d2)
      day to second)))||':'||
    extract(minute from ((d1 - d2)
      day to second))||':'||
    extract(second from ((d1 - d2)
      day to second))
  when d1=d2 then
    '0:0:0'
  end "D2-D1"
from t
/
D1                  D2                  D2-D1
------------------- ------------------- ----------
01.02.2000 11:22:02 23.01.2000 12:00:03 -215:21:59

alias oraver 2.0

Thanks to an anonymous comment in my post yesterday, I can now provide a more flexible version of my alias, which do not require the database to be running nor the sysdba privilege


$ alias oraver
oraver='echo '\''ORACLE_SID VERSION %CPU RSZ VSZ START_TIME'\'';awk -F: '\''/^[^ *#]/{print "printf \042%-9s %11s %5s %8s %8s %s\\n\042",$1,"$(ORACLE_HOME="$2,$2"/bin/sqlplus -v 2>/dev/null|cut -d\047 \047 -f3) $(ps -eo pcpu,rsz,vsz,start_time,args|sed -n \042s/ [o]ra_pmon_"$1".*//p\042)"}'\'' /etc/oratab |sh'
$ oraver

ORACLE_SID    VERSION  %CPU      RSZ      VSZ START_TIME
LSC01      10.2.0.2.0   0.0    12184   508448 10:09
LSC02       9.2.0.8.0   0.0     8420   303320 10:10
LSC03       9.2.0.8.0

Well, it is based on the sqlplus version, which is not necessarly the same as database version, like 8.1.7.3 does show 8.1.7.0, but in most of the recent versions, it should be ok

ps parameters may not work on all os, you can use -ef if you prefer

ps -ef |grep pmon alternative

I just wrote a new alias to check if the databases are up and running. I added the version and a dash for non-running database. Needed is /etc/oratab + sysdba access to the database.

Here it is :

awk -F: '/^[^*# ]/{system("echo \042select \047+ "$1" \011\047||version from v\\$instance;\042|ORACLE_SID="$1" ORACLE_HOME="$2" "$2"/bin/sqlplus -s \042/ as sysdba\042 2>/dev/null|grep \042^+\042||echo \042- "$1"\042")}' /etc/oratab

+ LSC01         10.2.0.2.0
+ LSC02         9.2.0.8.0
- LSC03

Or, as an alias :

alias oraver='awk -F: '\''/^[^*# ]/{system("echo \042select \047+ "$1" \011\047||version from v\\$instance;\042|ORACLE_SID="$1" ORACLE_HOME="$2" "$2"/bin/sqlplus -s \042/ as sysdba\042 2>/dev/null|grep \042^+\042||echo \042- "$1"\042")}'\'' /etc/oratab'