Laurent Schneider

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 !

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 do specify a format.

I simply use

date '2000-01-01'

I often use Jan 1st, 2000 as an anonymous date.
For avg(txndate), I can use

date '2000-01-01' +
avg(txndate-date '2000-01-01')

Other nice literals are timestamps

timestamp 
'2000-01-01 00:00:00.000000000 Etc/GMT+0'
timestamp '2000-01-01 00:00:00'

less usefull, because unsupported as oracle datatype

time '00:00:00.000000000 +00:00'
time '00:00:00'

also intervals

interval '1' day

one more I want to mention

q'[let's quote this]'

all this I found by reading the doc, more than once !

First snowman this year in Zürich
picture privacy

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 I received yesterday.

I just answered a question about the data dictionary on forums.oracle.com. 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_ and USER_ have (almost) the same structure.

SQL> select count(*)
from dict
where table_name not like ‘GV$%’
and table_name not like ‘ALL%’
and table_name not like ‘DBA%’ ;
712

Anyway, who knows the 712 views by heart? Hopefully, there is one called DICTIONARY, which helps !

Coming back to the post, palindnilap wants to see which columns of a view are mapped to which column of a table. A quick look at ALL_VIEWS could do the trick, but than you will need to “understand” the query to see which view.column maps to which table.column. What’s more, ALL_VIEWS.TEXT is a long. Arghh!

if you have a view that contains all columns from a table, you could use ALL_DEPENDENCIES to see on which table it is based.

On my first answer, I pointed out that ALL_UPDATABLE_COLUMNS may reveal that a view column belongs to a table if the column is updatable.

My last try was to use the ACCESS_PREDICATES to get the column physically accessed.

SQL> select * from v02 where employee=123456;

no rows selected

SQL> select
max(substr(ACCESS_PREDICATES,1,instr(ACCESS_PREDICATES,’=')-1))
from v$sql_plan
where ACCESS_PREDICATES like ‘%=123456′;
“EMPNO”

here we see EMPLOYEE is actually named “EMPNO” in the based table. It could be done with explain plan and PLAN_TABLE too.

Tom Kyte is coming to Zurich next month!
http://www.digicomp.ch/tomkyte

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;
grant EXECUTE on SYS.UTL_HTTP to MDSYS;
grant EXECUTE on SYS.UTL_HTTP to ORDPLUGINS;
@?/rdbms/admin/utlrp

Than, in OEM 10g, check for policy violations.

I added a few grants to special oracle internal users, to avoid invalid objects, which is also a policy violation in OEM… OEM will report a violation if those accounts are not locked and expired

I just found in my RSS feed that a new agent has been released. 10.1.0.4 for AIX5L. Well, if you go to the OEM download page, you will see only 10.1.0.2 version for AIX, HPUX, Solaris Sparc. But by clicking on the link, for example
Grid Control (10.1.0.2)
for AIX5L Based Systems, you will find a link OEM 10g Grid Control Management Agent Release 1, from there I finally discovered that 10.1.0.4 agent download.

Well, I already had 10.1.0.4, but it was a 10.1.0.2 installation patched with 10.1.0.4. So I prefer installing directly 10.1.0.4 agent.

Not trivial to found, but hopefully, I subscribed to RSS feeds on Newest Downloads

In order to get a clean database configuration, I add the following two hidden parameters in my 10.2.0.1 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 to communicate those parameters, and the metalink notes referencing them.
You will need them to have a cleaner 10.2.0.1 installation

_kgl_large_heap_warning_threshold=33554432
This parameter prevent Heap size 2800K exceeds notification threshold errors in the alert log and user trace dumps.
Note: 330239.1 Bugs: 4286095, 4390265

__dg_broker_service_names='’
In case you do not use dataguard but you do use local_listener parameter.
this parameter prevents pmon from registering a <DB_NAME>_XPT.<DOMAIN_NAME> service in the listener.
Thread: 611575.993 Bug: 4632635

Probably all this will be fixed in 10.2.0.2

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

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 10.1.0.4.2 database.

When you download application server (about 2Gb), you get a fully functionnal 10.1.0.4.2 database preconfigured and an ldap server running.

To start the admin tool, just type oidadmin in the command line.

The password to use is the same as ias_admin and the username is orcladmin

My ldap server is up and running on my notebook with SLES9.

Next, next, next, install. That is it.

Oracle Application Server creates a 10.1.0.4.2 database and start the Oracle Internet Directory - understand LDAP server - automatically.

It can then be configured with the web interface.

As requested, I will do some swiss geography today

I am now working in my company, LC Systems, in Thurgau. Thurgau is a large canton in switzerland. There is also Zurich, where I do live, with over 1 million people, Basel, which is divided in two half-cantons, Basel-City and Basel-Campaign, and Bern, where the governement resides, and also Geneva, where I was born, and quite famous because of the many international organisation like the United Nations, the World Health Organisation, the International Labor Organisation and a few others.

Well, yesterday I was in Frauenfeld, which is the biggest city of Thurgau, with more than 20′000 people. No no, it is no joke, I did not mean 20 millions, it is no mistake of mine! I had a beer with a collegue. When we arrived in the pub, he first met a school friend at the table next to our. Than the people at our table hear about that and asked he would not be the son of Rolf (of course he was), and on the other table he met an other school friend. Nothing to do with the big city where you never know the people when you go to restaurant. Anyway, after a few gallons of refreshment, a policeman stopped me because I was walking on the road. Incredible. Of course my friend knew him, because his previous girlfriend lost his bag one day and he already stopped him because he was ridding mountain bike on a road where traffic is prohibided. I just had to laugh. This guy was not even working! He was just going home with his sport car, and found that, as a policeman, he was allowed to make me a comment! What a Moron !

Anyway, I am now in my office, with a beautifoul seesight. The office is located about 5 meters next to the lake of Constance (that is why my company is called LC, Lake of Constance), and I can see Germany from here when the weather is better than today. The lake is actually located between Germany, Austria and Switerland. It is quite big, about 540 km2. The biggest here is the lake of Geneva. Located between France and Switzerland. The largest 100% swiss is the lake of Neuchâtel.

In our small country, one of the smallest in Europa -apart from micro-country like Vatican or Monaco- we have the largest lakes and some of the highest mountains of Europa.

I feel that I must publish a picture today :

Well, I just googled to find this one. I may remove the link above one day.

Ok, today I am trying to install Oracle Internet Directory (LDAP). I finished unzipping the files and will start installing soon.

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 oracle.com/education. Later, it said : “will be available in mid 2005″.
In Open World, I met the certification principal, who meant it will start in December and upgrade from 9i ocm will start in January and will be available in all oracle universities which give RAC course. Fine.
When I came back I saw: “will be available in October”. Today I saw: “will be available in December”.

Such a move like OCM is very intense, I want to plan at least 2-3 weeks full time to prepare for OCM. I need to go to my boss and say : “this year I will go for 10g ocm”. But what a pity if the certification is always delayed… planned for 2004, no info why it did not take place in 2005, I also fair that there is no guarantee for 2006 in switzerland, as they will surely start in the United States - but do not ask why…

Keep cool, be patient

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

what is a hierarchy?
I enjoy reading the wikipedia definition :

http://en.wikipedia.org/wiki/Hierarchy

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 :

SQL> select * from dept connect by 1=2;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

Fertile variant :

SQL> select * from dept connect by 1=1

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        10 ACCOUNTING     NEW YORK
        10 ACCOUNTING     NEW YORK
        10 ACCOUNTING     NEW YORK
        10 ACCOUNTING     NEW YORK
        10 ACCOUNTING     NEW YORK
        10 ACCOUNTING     NEW YORK
        10 ACCOUNTING     NEW YORK
        10 ACCOUNTING     NEW YORK
        10 ACCOUNTING     NEW YORK
        10 ACCOUNTING     NEW YORK
        10 ACCOUNTING     NEW YORK
...

the connect by does defines a true or a false connection. when true, everyone is your parent and everyone is your child. If false, you are the parent, but you have no child.

connect by level

It is the second time I got an answer like : it is a bug in the documentation.

First was about single table sort hashed cluster, when the doc does not say that you need to use order by to sort the rows.
Ref: tom kyte blog : order in the court This is just a warning that unless your query has an ORDER BY, you have no reason to anticipate the data being returned to you in any kind of sorted order whatsoever
Ref: the documentation 10gR2 Admin Guide : Given the sort order of the data, the following query would return the call records for a specified hash key by oldest record first.

SELECT * WHERE telephone_number = 6505551212;

Tom assumes it is a bug in the doc. But still it is sorting the rows as described in the doc.

The second example is about a Mikito Harakiri hack. In my opinion, it is not legal to use connect by without prior. And it has never been since Oracle 7. I do not have Oracle 6 or Oracle 5 doc, so if you have it, drop me a mail if it was different.
Ref: asktom Can there be an infinite DUAL?

select level from dual connect by level < :N

Ref: the doc 10gR2 SQL Ref In a hierarchical query, one expression in condition must be qualified with the PRIOR operator to refer to the parent row
Ref: the doc 7.3.4 SQL Ref connect by clause : some part of the condition must use the PRIOR operator to refer to the parent row

Well, I am deeply disappointed about those answers. In the first example, a sorted hash cluster is a special case. Why is the doc wrong? If you select from a sorted single table hash cluster, you get your result sorted. There could be limit case (like nls_sort), where the sorting algorythm could be different than the one you would get by using “order by”, and specifying an “order by” will not necessarly dicrease the performance. But the data IS sorted as stated in the doc.

In the second case, Tom is using an illegal construct. In Oracle 7, 8 and 8i, it was generating an ORA-1436

SCOTT@LSC69/7.3.4.5
SQL> select * from 
(select level l from dual 
connect by level < 10);
ERROR:
ORA-01436: CONNECT BY loop in user data

SCOTT@LSC65/8.1.7.4
SQL> select * from 
(select level l from dual 
connect by level < 10);
ERROR:
ORA-01436: CONNECT BY loop in user data

I would tend to argue that the fact that it works in 9i is bug.

Most of the questions could be answered in the doc. Sometimes, quite often actually, I send feedback to the OTN documentation forum. But only when I can prove there is something wrong. And I have never see a correction in the doc. I urged OTN team to take care of my feedback. But never I would say : the doc is wrong. Unless I can prove it.

Nevertheless, the documentation is not open-source. It is not a collection of customer statements. It is a reference and everything what is stated in the doc is supported, and something that would not be stated in the doc will probably not be supported by Metalink.

I once suggested in asktom

select stragg(distinct ename) from emp;

to sort the ename’s. It DOES sort the rows. But it is not WRITTEN in the doc that udag(distinct n) does sort the rows, so I cannot pretend it will. You probably would not be able to find an counter-example, but, since it is not stated in the doc, I gave me beaten on that one.

I hope I have correctly explained my point of view :
The ultimate oracle book is oracle documentation. Go to tahiti.oracle.com to start reading

I just want to clarify something today : I do not like the connect by level select * from dual connect by level select * from dual connect by level

Oracle Enterprise Manager Downloads: oem download

this is a great announcement!