Oracle Certification Portal

On the certification portal, you can now view your certification. This replace the original Sun Certmanager (used to be and it provides you an interface to publish your information to third parties. Both Sun and Oracle Certifications are visible. If you publish your information, your recipient will receive a link that will show […]

shutdown timeout

I do not like shutdown abort (see this post). I always use shutdown immediate and it always work… well almost always. Today I discovered a 9iR2 new feature : shutdown timeout ! Shutdown Timeout If all events blocking the shutdown do not occur within one hour, the shutdown operation aborts with the following message: ORA-01013: […]

Dynamic number of columns

I used to believe you cannot have a dynamic number of columns. Today Tom referenced Anton on asktom. It leaded me there, back in time, 2006, on the OTN forums Difficult to write an article on this without copy-pasting most of Anton code, so just read it on the link above. Then you will […]

grant select on sys tables

I prefer to use a powerful named user with dba rather than sys. It is more conform to the security policies in place regarding accounting of administrator operations. Very occasionaly, my user get ORA-1031 insufficient privileges even if I have the dba role. Amoung others, I have “PURGE DBA_RECYCLEBIN” and DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE update, 2012-07-24 For purge […]

American = fast

I had the incredible behavior of having the same query running 10x faster in sqlplus depending on two different PCs. After analysis, I realised the super fast pc was American… at least in nls_lang Slow client: PC setup in German, NLS_LANG is set to GERMAN_SWITZERLAND.WE8MSWIN1252 C:\>set NLS_LANG=GERMAN_SWITZERLAND.WE8MSWIN1252 C:\>sqlplus scott/tiger@db01 SQL*Plus: Release Production on Fr […]

What does # mean in sqlplus?

The script used to be shutdown abort it has been replaced by #shutdown abort shutdown immediate Let’s try ! SQL> #shutdown abort ORACLE instance shut down. SQL> shutdown immediate ORA-01012: not logged on sqlplus just silently ignored the # symbol and executed the first statement. Thanks to Maxim comment, here is a new case to […]

[Windows] Email of current user

I hate having to type my email address, so I created a long one-liner to do the trick of getting my email from Exchange and copying it in my clipboard powershell -noprofile -command “$o=New-Object DirectoryServices.DirectorySearcher; $o.SearchRoot=New-Object DirectoryServices.DirectoryEntry;$o.Filter=’samaccountname=’+$ENV:USERNAME;write-host ($o.FindOne().Properties.mail)” | clip save this as “C:\WINDOWS\E.BAT” Then, when you have to enter your email in a form […]

My first .NET gui in Powershell

I managed to interface Oracle and a GUI via powershell. First, load the Oracle and the .NET assemblies [void] [Reflection.Assembly]::LoadFile(“C:\oracle\product\11.2.0\client_1\ODP.NET\bin\2.x\Oracle.DataAccess.dll”) [void] [Reflection.Assembly]::LoadWithPartialName(“Drawing”) [void] [Reflection.Assembly]::LoadWithPartialName(“Windows.Forms”) Now, let’s retrieve EMP in a powershell array. I hope one of my reader will advise me on a better way 🙂 $connection=New-Object Oracle.DataAccess.Client.OracleConnection(“Data Source=DB01; User Id=scott; password=tiger”) $ $command=new-object Oracle.DataAccess.Client.OracleCommand(“select […]

EURO symbol, sqlplus, cmd.exe and various issues

One customer reported a not-correctly displayed Euro Symbol (€) in the database from sqlplus (msdos). Why? First, the character set did not support it. select * from v$nls_parameters where PARAMETER like ‘%CHARACTERSET%’; PARAMETER VALUE —————————— ————— NLS_CHARACTERSET WE8ISO8859P1 NLS_NCHAR_CHARACTERSET AL16UTF16 If you are still using WE8ISO8859P1, consider migrating to WE8MSWIN1252 using csalter sqlplus “/ as […]

Toad 11.5 is out

The latest Toad is now in production, 11.5, get it from If you have an old license key, 9.6 or older, it may complain at installation time, just ignore. It will be fine at run time. Enhanced TAB browsing experience, nicer and more visible colors for your connection (production=red…), read-only connections. Currently it still […]

How to get rid of corrupted blocks without a backup

First, you identify the blocks in alert log or with db verify $ dbv BLOCKSIZE=8192 file=sysaux01.dbf DBV-00201: Block, DBA 12629823, marked corrupt for invalid redo application … DBVERIFY – Verification complete Total Pages Examined : 131072 Total Pages Processed (Data) : 69691 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 28669 Total Pages […]

weekinmonth in powershell starts on Sunday…

This April month is full of surprises! Not only I did not have the opportunity to trick my colleagues on Sunday April first, but I had some bug of my code due to the start of the week. [int](get-date).dayofweek and (Get-WmiObject Win32_LocalTime).dayofweek both refer Sunday as day 0. PS> get-date Wednesday, 11. April 2012 18:31:52 […]

[alert] AIX Posix Timezone issue

Maybe you did get or you will get an issue with the date command in AIX. expected behavior, Linux $ TZ=NZST-12NZDT,M10.1.0/2,M3.3.0/3 date Sat Mar 17 00:14:54 NZDT 2012 $ TZ=Pacific/Auckland date Sat Mar 17 00:14:58 NZDT 2012 unexpected behavior, AIX $ TZ=Pacific/Auckland date Sat Mar 17 00:15:50 GMT+13:00 2012 $ TZ=NZST-12NZDT,M10.1.0/2,M3.3.0/3 date Fri Mar 16 […]

to ADO or to ODP ?

I just read on System.Data.OracleClient Namespace that System.Data.OracleClient are deprecated. The types in System.Data.OracleClient are deprecated. The types are supported in version 4 of the .NET Framework but will be removed in a future release. Microsoft recommends that you use a third-party Oracle provider. The alternative is to use ODP (from Oracle) instead of ADO […]

on Windows Management Instrumentation

I have a bit versed into powershell over the last months, I just cannot stop discovering new gems. Ex : Win32 Classes Self-explanatory examples : PS> (get-wmiobject win32_processor).currentclockspeed 3292 PS> (get-wmiobject win32_operatingsystem).version 5.1.2600 PS> (get-wmiobject win32_proxy).ProxyServer PS> (get-wmiobject win32_proxy).ProxyPortNumber 8080 it goes on and on and on… the ways those things are accessible from […]

scott.emp in Sybase

As part of my dba job, I have a few Sybase DBs around. I created the well known Oracle Scott tables to play with $ isql -U sa -P “” 1> sp_addlogin “scott”,”tiger7″ 2> go Password correctly set. Account unlocked. New login created. (return status = 0) 1> create database lsc01 2> go CREATE DATABASE: […]

This system is for the use of authorized users only.

How to bypass the login banners? There is actually more than one banner to bypass. One of the them is the message of the day banner, commonly located in /etc/motd. Typically friendly, example in AIX **************************************************************************** * * * * * Welcome to AIX Version 6.1! * * * * * * Please see the […]

The new metalink interface

I just started using the New My Oracle Support User Interface It is a zero-flash interface with more capabilities than the “html” interface we used the previous years. It still provides about the same browser experience as the flash one. Have a try… according to Oracle : “it will eventually replace the Flash version” To […]