Tom Kyte Day 1

Day one was quite interesting! We learnt a lot of staff about tuning approach, I have got confirmation that most of the “WE KNOW THAT, IT HAS ALWAYS BEEN SO” were maybe one day true, but are no longer, for example “separate index and tables”, or, delightfull, “you must periodically reorganise your tables”. I also… Continue reading Tom Kyte Day 1


I just tried today to limit power of rman : REVOKE ALTER SESSION, CREATE DATABASE LINK FROM RECOVERY_CATALOG_OWNER; It seems I can still do a backup… probably those privilege are not needed by rman, maybe just inherited from Connect in an older released !?

encrypted listener password

There a few major changes in the database administration and the database security between 9i and 10g. In 9i, I used to grep in the listener.ora to find out the password. LISTENER_LSC61 = (DESCRIPTION=(ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)( )) PASSWORDS_LISTENER_LSC61 = 1234567890ABCDEF this 64bit encrypted string can be used in 9i to stop the listener $ lsnrctl LSNRCTL… Continue reading encrypted listener password

add_years and years_between

I just wrote those two functions add_years and years_between they work similary to add_months and months_between, with the exception of leap years. there is exactly 1 year between 28-feb-2003 and 28-feb-2004 there is 1.00273224 year (1+1/366) between 28-feb-2003 and 29-feb-2004 there is 0.99726776 year (1-1/366) between 29-feb-2004 and 28-feb-2005 there is exactly 1 year between… Continue reading add_years and years_between

Categorized as Blogroll, sql

dinner with Tom Kyte in Zurich

Next wednesday, Dec 7th, starts the Tom Kyte workshop in Zurich. In the evening we will have a dinner in Zurich. Drop me a comment if you intend to come !

ansi literals

the first time I saw ansi literals was in June 2004 in a post on otn forum by alex goodmann. I just cannot stop using them, it is so handy! Whenever I specify a date (or a timestamp or even a time), with Ansi Date, I do not rely on the NLS parameters, nor I… Continue reading ansi literals

Categorized as Blogroll, sql

Welcome to Switzerland

I will attend Tom Kyte 3-days session in Zurich in 2 weeks. I am immensely impatient to follow his workshop ! I hope palindnilap will be there too 😉 I invited him to come to my home to go to Tom Workshop, and to have a game of xiangqi on the brand new wooden board… Continue reading Welcome to Switzerland

post from palindnilap

I just answered a question about the data dictionary on It is all about the dictionary views. How many tables should I know in the dictionary ? SQL> select count(*) from dict; 1857 Well, that’s too much. But I can remove the GV$ view, which contain the instance for RAC, and the DBA_ ALL_… Continue reading post from palindnilap

Categorized as Blogroll, dba

oracle voyage worm

I wrote a mini script to protect my customer from being attacked by an “oracle voyage worm” variant : revoke CREATE DATABASE LINK from CONNECT; revoke ALL on SYS.UTL_FILE from PUBLIC; revoke ALL on SYS.UTL_HTTP from PUBLIC; revoke ALL on SYS.UTL_SMTP from PUBLIC; revoke ALL on SYS.UTL_TCP from PUBLIC; grant EXECUTE on SYS.UTL_FILE to XDB;… Continue reading oracle voyage worm hidden parameters

In order to get a clean database configuration, I add the following two hidden parameters in my parameter file. Do not hurl that loud, I hear you from here! Well, as I said already about the _pga_max_size, I never recommend using hidden parameters when you can do the same without. However, I am going… Continue reading hidden parameters

Categorized as Blogroll, dba

no more tnsnames

with netca, it is easy to configure your sqlnet.ora to use LDAP instead of tnsnames.ora. The ldap.ora and sqlnet.ora are updated… than it works, sqlplus user@db is correctly looking in the ldap oracle content

ldap day 2

what can I do with LDAP? what is the difference between LDAP and Oracle Internet Directory? Well, there is quite a lot of interresting documents, pictures and faq on otn : OTN Directory homepage Directory Admin guide Identity Management ReferenceLDAP is a directory server, the info are stored in an Oracle database. When you… Continue reading ldap day 2

ldap server

My ldap server is up and running on my notebook with SLES9. Next, next, next, install. That is it. Oracle Application Server creates a database and start the Oracle Internet Directory – understand LDAP server – automatically. It can then be configured with the web interface.

Details regarding the 10g DBA OCM requirements will be available in December

Well, when 10g certification came out, it said : “will be available in late 2004”. In March 2005, I contacted Oracle University in Germany. They said : keep watching Later, it said : “will be available in mid 2005”. In Open World, I met the certification principal, who meant it will start in December… Continue reading Details regarding the 10g DBA OCM requirements will be available in December

Chinese Chess

In case you like this game and live in Zurich, I will be playing the swiss championship tomorrow.

Categorized as game


what is a hierarchy? I enjoy reading the wikipedia definition : In the doc the hierarchy is as a parent-child connection, CONNECT BY PRIOR defines the relationship. However, it is possible to have under certain circumstances to connect to a child, regardsless of the parent. This is no longer a practical relation. Sterile variant… Continue reading hierarchy

Categorized as Blogroll, sql

linux 64bits 10gR2 is out

one fresh download today oracle database 10g download I heard that grid control 10g release 2 may be available only in 2006. Quite annoying, since the repository cannot be on a 10gR2 db… just waiting

restore to a new host : nid++

Great challenge today: restore to a new host from a closed noarchivelog backup on tape library. In oracle 8i and before, the only way to rename a database was to recreate the controlfile. In 9i, I could change it with nid, in 10gR2, I should never have a reason again to recreate the controlfile, because… Continue reading restore to a new host : nid++

Categorized as Blogroll, dba


I just remembered the woman who asked Lawrence Ellison why you need at least three days to get a qualified support representative when you open an iTar. Since a few days, I have been wondering why a non-dba user was not able to do sqlplus in 10gR2. Well, I saw the bug 4516865 on metalink… Continue reading metalink

Categorized as Blogroll


Ref: part 1 I reported this lack of documentation on Here is my test case (take care, it will create a new db!) : SQL> startup force quiet nomount; ORACLE instance started. SQL> create database controlfile reuse extent management local default tablespace users default temporary tablespace temp undo tablespace undotbs1; Database created. SQL> @?/rdbms/admin/catalog… Continue reading FAILED_LOGIN_ATTEMPTS part 2

pivot table part 3

one more try with model, available 10gR1 select * from (select extract(year from hiredate) h, count(*) c from emp group by extract(year from hiredate)) model dimension by (h) measures (c) rules( c[FOR h FROM 1980 to 1990 INCREMENT 1] = case when c[CV()] is present then c[CV()] else 0 end) order by h; H C… Continue reading pivot table part 3

Categorized as Blogroll, sql

pivot table part 2

One more try with 10gR2 select to_number(column_value) HIREDATE, count(decode(to_number(extract(year from hiredate)), to_number(column_value), 1)) COUNT from emp,xmltable(‘for $i in 1980 to 1990 return $i’ ) group by to_number(column_value) order by to_number(column_value) / 1980 1 1981 10 1982 1 1983 0 1984 0 1985 0 1986 0 1987 2 1988 0 1989 0 1990 0 pivot table… Continue reading pivot table part 2

Categorized as Blogroll, sql

Last day

Very intense last day. In the morning I waked up late, and just went to OTN lounge. Well, I meet Puschitz, which speaks german too because he is austrian, and also Wim. I then ran to Moscone South for my XQuery session. Very interresting indeed… In the afternoon, pure DBA staff : 1) shared memory… Continue reading Last day