Do you shu or do you spo?

When I quit ftp command line, I do not type quit, nor bye, because it is too long. I rather type “by”. Does it sound strange and meaningless to abbreviate “bye” in “by” ? Well, I have a few favorites SQL> set lin 999 SQL> rollb SQL> spo f SQL> shu SQL> spo off

lock system, restrict dbsnmp

An unlocked user is a security problem. Currently, all my unlocked users have only CREATE SESSION as system privilege, evtl ALTER SESSION. Except SYS, SYSTEM and DBSNMP To minimize this security problem, I implemented the following strategy on my test system. 1) delete password file, set remote_login_passwordfile=NONE, O7_DICTIONARY_ACCESSIBILITY=FALSE 2) alter user SYSTEM account lock; 3a) […]

the forums I use

on otn : forums.oracle.com 1) SQL and PL/SQL 2) Database General 3) iSQL*Plus 4) Documentation Feedback when otn is down or too slow, I read the metalink forums. Especially SQL*Plus and Oracle PL/SQL. If necessary, I post questions on the DBA Administration forum. to post questions/comments specific to security, I go to Pete Finnigan’s Oracle […]

new metalink interface

I just discovered that new interface today. Hopefully, my old bookmarks still work. I did not find the “Tar” button. Well, it is now named “Service Request”. I tried the ORA-600 lookup tool, Doc id 153788.1 but it does not work today, both Firefox and Explorer failed. MS Explorer reports a Java Script error. document.forms.0.tool_type.value […]

sys_connect_by_path in 8i or the danger to use undocumented parameters…

I have been posting on metalink technical forum about a query that I run against all my test databases but did not work in production. as it simpliest form select sys_connect_by_path(dummy,’:’) from dual connect by 1=2; well, there is nothing wrong with this query. I tried it on 8i, 9iR2, 10gR1 10gR2 and it worked […]

oracle10gR2 on suse10

I prefer and recommend using Oracle on a supported version, like Suse Entreprise 9, because the installation is fair. The Installer does complain on SLES9 what is missing. Ok, I just received a brand new notebook, I decided to go to Suse 10, and, Ô Miracle, the installation was pretty easy! I surely installed C++ […]

row generators performance

I wrote a few generators, and listed some existant in http://laurentschneider.blogspot.com/2005/08/pivot-table.html I decided to test them note that this is not a “good” test, it is simply an overview. I am doing a count(*), another operation may be better in one or worst in another one. all_objects is so slow (6 seconds for 50K rows) […]

Tom Day 3 : read consistency, transparent data encryption, dbms_advanced_rewrite

Day 3 concludes the Tom workshop in Switzerland. We started with read consistency and write consistency, with an interesting example were a single row update could make a big job run thrice slower, because of the write consistency. Once again, Tom insist on saying that you must understand Oracle to write applications. Tom demonstrates a […]

Tom Kyte Day 2

Today I asked : “Is it the responsability of the developper to create the table structure?” The answer was something like that : “You have four kind of persons. – You have the Oracle6 DBA, who says always NO – You have the Developer, who does not care about database – You have the DBA/Developer, […]

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 […]

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)(HOST=dbsrv85a.ex.zkb.ch)(PORT=10061)(QUEUESIZE=200)) )) PASSWORDS_LISTENER_LSC61 = 1234567890ABCDEF this 64bit encrypted string can be used in 9i to stop the listener $ lsnrctl LSNRCTL […]

the sqlplus settings I like

It is monday, I am going to give a list of settings I like in sqlplus set lin 32767 trimsp on tab off extends the linesize and avoid line breaks. I use it before SPOoling. But it is annoying before DESCribing. Trimspool is necessary to avoid spaces at the end of the line. Set tab […]

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 […]