You are now on my old blog. Please update your bookmarks to my new blog
http://laurentschneider.com




30 November 2006

hello world

I am setting a new blog at laurentschneider.com

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



You are now on my old blog. Please update your bookmarks to my new blog
http://laurentschneider.com




27 November 2006

Chinese Chess Swiss Champion 2006

I was honored to receive this trophy yesterday :



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



You are now on my old blog. Please update your bookmarks to my new blog
http://laurentschneider.com




22 November 2006

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



You are now on my old blog. Please update your bookmarks to my new blog
http://laurentschneider.com




17 November 2006

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.



You are now on my old blog. Please update your bookmarks to my new blog
http://laurentschneider.com




15 November 2006

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 !



You are now on my old blog. Please update your bookmarks to my new blog
http://laurentschneider.com




10 November 2006

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



You are now on my old blog. Please update your bookmarks to my new blog
http://laurentschneider.com




09 November 2006

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



You are now on my old blog. Please update your bookmarks to my new blog
http://laurentschneider.com




08 November 2006

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.



You are now on my old blog. Please update your bookmarks to my new blog
http://laurentschneider.com




03 November 2006

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



You are now on my old blog. Please update your bookmarks to my new blog
http://laurentschneider.com




02 November 2006

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



You are now on my old blog. Please update your bookmarks to my new blog
http://laurentschneider.com




01 November 2006

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'