ORA-01722: invalid number and sql loader

Your manager asked you to load an Excel file in a table. Ok, you look at the header, create a basic table with meaningfull datatype, you open Excel and save as csv, you load your data. But then you get ORA-1722. What happened ?

Ok, let’s do it

create table t(x number not null, y number, z number not null);


LOAD DATA
INFILE *
INTO TABLE T
TRUNCATE
FIELDS TERMINATED BY ';'
(X, Y, Z)
BEGINDATA
1;1;1
2; ;2
3;3;3


$ sqlldr scott/tiger control=foo.ctl
$ vi foo.log
...
Record 2: Rejected - Error on table T, column Y.
ORA-01722: invalid number

Here it is pretty eye-popping, but you probably have 10 years of market data to load with hundreds of columns and most of the columns are empty or/and obsolete.

The thing is, Excel did put a space for your “number” datatype, space is not a valid number !


SQL> select to_number(' ') from dual;
select to_number(' ') from dual
*
ERROR at line 1:
ORA-01722: invalid number

A workaround is for each nullable numeric column to specify nullif column=blank

LOAD DATA
INFILE *
INTO TABLE T
TRUNCATE
FIELDS TERMINATED BY ';'
(X, Y NULLIF Y=BLANKS, Z)
BEGINDATA
1;1;1
2; ;2
3;3;3

$ sqlldr scott/tiger control=foo.ctl
$ vi foo.log
...
Table T:
3 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

I hope I will remember this next time I am sqlloading from Excel into Oracle !

old-hash, SHA-1, SHA-2/512

Until pretty recently, only the dubious unsalted proprietary algorithm was available to store Oracle passwords. A bunch of tool where at the time able to decode any 6-8 characters in no time, and the rainbow approach was to precalculate all possibles passwords for a specific user.

Those time are not really for away, only starting at Oracle 11g, you could have salted/case sensitive passwords. Salted means that Scott may have many different passwords keys for tiger.


SQL> select spare4 from user$ where name='SCOTT';
SPARE4
----------------------------------------------------------------
S:96A5FF65BFF84D7AAC6F8F00879881E8506FE57F555E5BA2927B606DC4F1

SQL> alter user scott identified by tiger;

User altered.

SQL> select spare4 from user$ where name='SCOTT';
SPARE4
----------------------------------------------------------------
S:AE23FB94A462C44A75040CE3BA731E3EF08C4A270F5940491045CBCEF63C

Some users may have only the 10g version (password not changed after migrating to 11g), some may have the 11g version of both, and -who knows- some may have already have SHA-2/512 passwords. SHA2 has many advantages. The chance that 2 passwords provides exactly the same string are much lower than in SHA1 (collision) and it performs twice faster on 64 bits servers.


SQL> select username, password_versions from dba_users where username like 'U_;
USERNAME PASSWORD
------------------------------ --------
U1 10G
U2 11G
U3 10G 11G
U4 12C

Probably you never saw this unless you are in beta 12. But actually it is documented in the 11gR2 Documentation.

12C if a new SHA-2 based SHA-512 hash exists

Oracle Certification Portal

On the certification portal, http://certview.oracle.com you can now view your certification. This replace the original Sun Certmanager (used to be certmanager.net/sun) 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 your credentials :

Credentials

Credential Title Certification Date
Oracle Database 11g Administrator Certified Master 19-SEP-12
Oracle Database SQL Certified Expert 19-JUL-07
Certified Expert Oracle Database 10g Real Application Clusters Administrator 27-JUN-07
Oracle Application Server 10g Administrator Certified Associate 28-APR-06
Oracle Database 10g Administrator Certified Professional 14-SEP-05
Oracle9i Database Administrator Certified Master 05-FEB-04
Oracle9i Database Administrator Certified Professional 18-FEB-03
Oracle8i Database Administrator Certified Professional 19-DEC-02
Sun Certified Network Administrator for Solaris 8 08-AUG-02
Oracle8 Database Administrator Certified Professional 17-JAN-02
Sun Certified System Administrator for Solaris 2.6 22-MAY-00

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: user requested cancel of current operation.

Ok, if and only if I am getting this ORA-1013, I shutdown abort, startup, shutdown immediate.

It is very unusual that a shutdown immediate does not terminate in 1 hour, and hard to reproduce. For this test case, I am doing a shutdown normal

1) make sure you have at least one other session open
2) shutdown normal
3) wait about 60 minutes (defined in _shutdown_completion_timeout_mins, not a supported parameter to change)

SQL> shutdown normal
ORA-01013: user requested cancel of current operation
SQL>

Now we received a ORA-1013 (but I did not use CTRL-C). The instance is now half-stopped, most sessions and background processes like MMON, CJQ, SMCO are already dead and it is probably a good idea to restart it properly. Maybe with startup force and shutdown immediate.

SQL> startup force
ORACLE instance started.

Total System Global Area 1069252608 bytes
Fixed Size 2166160 bytes
Variable Size 658510448 bytes
Database Buffers 402653184 bytes
Redo Buffers 5922816 bytes
Database mounted.
Database opened.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

This is all documented :
After ORA-01013 occurs, you must consider the instance to be in an unpredictable state…If subsequent SHUTDOWN commands continue to fail, you must submit a SHUTDOWN ABORT

I am extremly happy to have discovered this, because it will still allow a shutdown abort after one hour of shutdown immediate, which is an extremly rare case, but it is an enhancement for my backup scripts.

OCM 11g upgrade

Last Friday I went to Munich for THE upgrade exam. I have a few recommendations based on my preparation.

DISCLAIMER: no recommendation is based on the exam

Check you have a similar environment. You will get 11gR1 database and 10g entreprise manager. Personnaly I did my preparation on 11gR2 and 11g/12c em, I did not find useful to prepare on some oldish version. You should have some Linux / Unix around. It does not really matter if you use AIX, HPUX, Solaris or Linux.

I bought myself a keyboard with German Layout (the one with ß, Ä and Ö) to gain speed

Read each topic carefully and get proficiency:
– Database:
I know the meaning of the initialisation parameters. In need, I can quickly check one in the Reference
I am familiar with the sql syntax and have read the SQL Reference more than once

– Network configuration :
I know how to configure sqlnet.ora, tnsnames.ora and listener.ora without looking at the documentation.
I have carefully tested all possible parameters in Net Service Guide

– ADR :
I know how to set up the ADR location in the database and network.
I know how to use the command line utility, documented in utilities -> adrci

– RMAN :
I know how to configure RMAN with the configure statement. I am efficient in doing backups and restores
I have read the RMAN Reference carefully.

– Dataguard :
I am confident with the dgmgrl utility and with oem to configure and monitor dataguard.
I have read the Dataguard Broker Guide

– Materialized view
I have read and tested the examples in the Datawarehouse Guide -> Materialized views

– Secure Lob
I have played with lobs as basicfile and securefiles to check the differences and new features. I have read the secure files guide

– Streams
Streams is a very complex product. I have attended last year a five days course in Paris to only realise it is utterly tricky to maintain and debug, it case of errors.
I know where to look at if the capture, propagation or apply fail. Mostly in alert log, but also in the DBA views. I know how to restart the different processes. I am confident with both the OEM and PL/SQL Packages DBMS_STREAMS_*.
I have read the Streams Concept and Streams Replication Administration

– Resource manager
I know both the GUI and command line. I have read Admin guide -> Resource Manager

– Advisors and baselines
A very valuable resource to read is the 2 Days Perf Guide. It may sound strange to prepare an OCM exam with a 2-Days guide, but it is an awesome lecture.
To get deeper and learn the API usage, read Performance Tuning -> Optimizing SQL Statements

– Replay a capture workload
I learnt both the PL/SQL and EM usage in the Real Application Testing User Guide

Almost all the other topics are self explanatory and almost trivial once you know the syntax by heart…

I spent over one year preparing this exam and if you browse my recent posts, you will find detailled example that somehow relate to miscellaneous findings
Check mount option in linux
Transport tablespace over db links
Fast start failover
On star transformation
my first ADR package

For those of my readers who are on this way, good luck!

PS: no, I do not know the result yet…

Difference between Paris and Zurich

When I was a child, I used to go skiing in the alps, and occasionaly cross the borders. I remember that late in the season (Eastern skiing) restaurants were already empty in France when we had lunch, because our neithbough countries introduced summertime before us.

It is a long way back, namely summers 1976 to ’80. In 1975 and before, neither of us had day light saving. In 1981 and later, we both had it.

Ok, I just had an issue with a wrong date in a customer application. Somehow our database is set with POSIX format, let’s say +01:00 and +02:00, derived from CET/CEST unix timezone (TZ=CET-1CEST,M3.5.0,M10.5.0)

Due to some obscure multiple conversions, dates for summer 1976-80 are wrong, so we sent birthday cards too early to our customers…

SQL> select to_char(cast(
timestamp '1979-08-01 00:00:00 CET'
as timestamp with local time zone),
'YYYY-MM-DD') from dual;

TO_CHAR(CAST(TIMESTAMP'1979-08-0100:00:
---------------------------------------
1979-08-01

but if set my session timezone to Europe/Zurich, which is currently equivalent to CET, I got discrepancies

SQL> alter session set time_zone='Europe/Zurich' ;

Session altered.

SQL> select to_char(cast(
timestamp '1979-08-01 00:00:00 CET'
as timestamp with local time zone),
'YYYY-MM-DD') from dual;

TO_CHAR(CAST(TIMESTAMP'1979-08-0100:00
--------------------------------------
1979-07-31

A good reason to specify the time zone name correctly in your create database statement !

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
https://forums.oracle.com/forums/message.jspa?messageID=1297717#1297717

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 see the magic :


SQL> select * from table( NColPipe.show( 'test', 3 ) );

test1 test2 test3
---------- ---------- ----------
row: 1 row: 1 1
row: 2 row: 2 2
row: 3 row: 3 3

SQL> desc NColPipe
Name Null? Type
----------------------------------------- -------- ----------------------------
L_PARM VARCHAR2(10)
ROWS_REQUESTED NUMBER
RET_TYPE ANYTYPE
ROWS_RETURNED NUMBER

METHOD
------
STATIC FUNCTION ODCITABLEDESCRIBE RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
RTYPE ANYTYPE OUT
P_PARM VARCHAR2 IN
P_ROWS_REQ NUMBER IN DEFAULT

METHOD
------
STATIC FUNCTION ODCITABLEPREPARE RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SCTX NCOLPIPE OUT
TI ODCITABFUNCINFO IN
P_PARM VARCHAR2 IN
P_ROWS_REQ NUMBER IN DEFAULT

METHOD
------
STATIC FUNCTION ODCITABLESTART RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SCTX NCOLPIPE IN/OUT
P_PARM VARCHAR2 IN
P_ROWS_REQ NUMBER IN DEFAULT

METHOD
------
MEMBER FUNCTION ODCITABLEFETCH RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NROWS NUMBER IN
OUTSET ANYDATASET OUT

METHOD
------
MEMBER FUNCTION ODCITABLECLOSE RETURNS NUMBER

The function is returning ANYDATASET and implemeting ODCITABLEDESCRIBE. This is all clean documented code.

Read more:
Data Cartridge Developer’s Guide – Using Pipelined and Parallel Table Functions – Describe Method
Sometimes it is not possible to define the structure of the return type from the table function statically … You can implement a ODCITableDescribe() routine

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 dba_recyclebin, you probably should purge tables individually
exec for f in(select*from dba_recyclebin where owner!='SYS' and type='TABLE')loop execute immediate 'purge table "'||f.owner||'"."'||f.object_name||'"';end loop;

For DBMS_STREAMS_AUTH, what I am actually missing, is the GRANT OPTION on some documented dba views and dbms package. So I could safely grant the grant option to my user for all sys objects that have been granted to DBA, PUBLIC and any other roles.

Kind of

create table scott.t as
select distinct owner,table_name,privilege
from dba_tab_privs t
where privilege not in ('USE','DEQUEUE') and owner='SYS' ;
begin
for f in(select * from scott.t) loop
execute immediate
'grant '||f.privilege||' on "'||f.owner||'"."'
||f.table_name||'" to scott with grant option';
end loop;
end;
/

It is better to not select from dba_tab_privs directly, as executing immediate while opening the cursor may have unexpected side effects.

This may help you to increase your security by reducing your connections as sys.

remove the current directory

Can I remove the current directory?

I used to believe you cannot.

Solaris:

$ uname -s
SunOS
$ mkdir /tmp/bla
$ cd /tmp/bla
$ rm -r /tmp/bla
rm: Cannot remove any directory in the path of the current working directory
/tmp/bla
$

AIX:

$ uname -s
AIX
$ mkdir /tmp/bla
$ cd /tmp/bla
$ rm -r /tmp/bla
rm: Cannot remove the current directory /tmp/bla.
$

Today I did a rm that I expected to fail, but …


$ uname -s
Linux
$ mkdir /tmp/bla
$ cd /tmp/bla
$ rm -r /tmp/bla
$

Wait, did it work?

$ cd /tmp/bla
$ pwd
/tmp/bla
$ cd /tmp/bla
$ ls -lad /tmp/bla
ls: /tmp/bla: No such file or directory
$ cd /tmp/bla
$

Somehow I am still there, in /tmp/bla, but /tmp/bla has been removed. What a strange operating system 😉

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/[email protected]

SQL*Plus: Release 11.2.0.3.0 Production on Fr Jul 6 10:30:25 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Verbunden mit:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set autot on exp
SQL> select job,count(*) FROM emp group BY job order by job;

JOB COUNT(*)
--------- ----------
ANALYST 2
CLERK 4
MANAGER 3
PRESIDENT 1
SALESMAN 4

Ausführungsplan
----------------------------------------------------------
Plan hash value: 2389703825

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 40 | 5 (40)| 00:00:01 |
| 1 | SORT ORDER BY | | 5 | 40 | 5 (40)| 00:00:01 |
| 2 | HASH GROUP BY | | 5 | 40 | 5 (40)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 112 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------

Now let’s try with the PC setup with american_america

C:\>set NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252

C:\>sqlplus scott/[email protected]

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jul 6 10:31:57 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set autot on exp
SQL> select job,count(*) FROM emp group BY job order by job;

JOB COUNT(*)
--------- ----------
ANALYST 2
CLERK 4
MANAGER 3
PRESIDENT 1
SALESMAN 4

Execution Plan
----------------------------------------------------------
Plan hash value: 637087546

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 40 | 4 (25)| 00:00:01 |
| 1 | SORT GROUP BY | | 5 | 40 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 112 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------

The “cost” and “plan” changed. Why this? Maybe an optimizer bug, but it is related to NLS_SORT, which is GERMAN for nls_lang=GERMAN_SWITZERLAND and BINARY for nls_lang=AMERICAN_AMERICA.

Binary!

How to quit crontab -e without overwritting cron

Imagine my crontab

* * * * * /usr/bin/date > /tmp/foo

I am writing the date to /tmp/foo every minute
$ cat /tmp/foo
Thu Jul 5 08:45:01 CEST 2012

Now I want to view my crontab in my EDITOR (vi).


$ crontab -e

I do not quit yet.

In the meantime, my colleague modify the crontab.


* * * * * /usr/bin/date > /tmp/bar

Later, I quit vi with :q!

O Surprise, the crontab is * * * * * /usr/bin/date > /tmp/foo again

According to the doc :

When you finish creating entries and exit the file, the crontab command
copies it into the /var/spool/cron/crontabs directory

Even if you did not make change, you overwrite the content of your crontab !

If you want to exit your crontab editor really without overwritting the crontab, you need to kill yourself.

Ex:

CTRL-Z
[2] + Stopped (SIGTSTP) crontab -e
$ kill %2
$
[2] + Stopped (SIGTTOU) crontab -e

Thanks to Colin comment, I realized I could not kill with kill, let’s kill with -9

$ kill -9 %2
$
[2] + Killed crontab -e

On deferred segment creation and truncate

One year ago, I wrote about a side effect of deferred segment creation and drop tablespace :
on deferred segment creation

Today I discoved one more side effect :

In the good old days (I read once that you are old as soon as you start talking about the good old days) and according to the doc :
You cannot truncate the parent table of an enabled foreign key constraint. You must disable the constraint before truncating the table


SQL> alter session set deferred_segment_creation=false;

Session altered.

SQL> create table t1(x number primary key);

Table created.

SQL> create table t2(x number references t1);

Table created.

SQL> truncate table t1;
truncate table t1
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

This however does not apply if you have deferred segment creation and empty tables

SQL> alter session set deferred_segment_creation=true;

Session altered.

SQL> create table t1(x number primary key);

Table created.

SQL> create table t2(x number references t1);

Table created.

SQL> truncate table t1;

Table truncated.

Table truncated. Ok, what’s the point in truncating an empty table …

Check mount option in linux

I did not find a clean way to check the mount option in Linux.

For instance wsize=32768

On AIX, I simply type “mount” and see the mount option…

For some reasons, my Linux does not show me the complete mount options !


$ mount
precision:/nfsserver on /nfsclient type nfs (rw,bg,addr=127.0.0.2)
$ grep nfsclient /proc/mounts
precision:/nfsserver /nfsclient nfs rw,vers=3,rsize=32768,wsize=32768,hard,proto=tcp,timeo=600,retrans=2,sec=sys,addr=precision 0 0

To lookup my NFS mount was using the mount option specified by Oracle for creating a tablespace that uses NFS, I had to grep in /proc… unreal!

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 explain the sql prefix #

SQL> sho sqlpre
sqlprefix "#" (hex 23)
SQL> select
2 #prompt hello world
hello world
2 * from dual;

D
-
X

While within (or outside of) an sqlplus block, you can tell sqlplus to immediately run a sqlplus statement

The correct syntaxes to put comment are documented in Placing Comments in Scripts

SQL> remark shutdown abort
SQL> rem shutdown abort
SQL> -- shu abort
SQL> /* shutdown abort */

xhost+ security hole part 2

Five years ago I wrote xhost+ is a huge security hole, I turned out red this morning when my neighbour sent me a smiley via X.

Do I really want everyone to have full access to my screen? No, I don’t. And I don’t do xhost+.

So why did it happen to me ???

I am using X-Window Attachmate aka Reflection X. And in this tool, according to the doc, the default X policy is unrestricted. This is in my opinion a huge flaw in the security design. Make sure you always change this to something more secure.

In Reflection X Manager Settings, Category Security, choose for instance User-based security and Prompt. Configuring X Cookies is probably more cumbersome.

Then when you or someone else will start an XTERM on your desktop, you will get a nice dialog box :

[Reflection X]
Client could not successfully authenticate itself to Reflection X server. Would you like Reflection X to connect to this client as an UNTRUSTED client ? Client originated from 192.168.0.1 (RX1303)
[Yes][No]

Ok, I have to click one more button, but at least I can deny access to my screen 🙂

[Hello World] run c# from powershell

How to run csharp code from command line with powershell. One line

PS> add-type 'public class c{public const string s="hello world";}';[c]::s
hello world

You can also execute visual basic directly from powershell.

PS> Add-Type -language visualbasic 'public class v
>> public const s as string = "Hello World"
>> end class'
>>
PS> [v]::s
Hello World

and also jscript

PS> add-type -language jscript -name j -memberdefinition 'class x{public const s="hello world";}'
PS> [j]::s
hello world

[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 or document or login screen,
[⊞+R] [e] [enter] [CTRL+V]
4 keystrokes (+ 2 mod)

Explanation :

C:\>powershell -noprofile
Windows PowerShell
Copyright (C) 2009 Microsoft Corporation. All rights reserved.

PS C:\> $o=New-Object DirectoryServices.DirectorySearcher;
### new object to browse Microsoft AD
PS C:\> $o.SearchRoot=New-Object DirectoryServices.DirectoryEntry;
### the base dn
PS C:\> $o.Filter='samaccountname='+$ENV:USERNAME;
### the search filter, your Username
PS C:\> $o.FindOne().Properties.mail
### find one (not necessarly 100% safe) and print the mail property
[email protected]

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")
$connection.open()
$command=new-object Oracle.DataAccess.Client.OracleCommand("select ename from emp",$connection)
$reader = $command.ExecuteReader()
$a = @()
while ($reader.Read()) {
$a = $a + $reader.GetString(0)
}
$connection.close()

last, let’s create a simple window (a Form) with a list (a List box) where you can select an item with a doubleclick.

Initialize the list with the array from EMP

$form = New-Object Windows.Forms.Form
$form.Text = "Select employee !"
$form.Size = New-Object Drawing.Size(640,480)
$form.StartPosition = "CenterScreen"
$listbox = New-Object Windows.Forms.ListBox
$listbox.Location = New-Object Drawing.Point(10,10)
$listbox.Size = New-Object Drawing.Size(620,460)
$listbox.Items.AddRange($a)
$listbox.Add_DoubleClick({$form.Close();})
$form.Controls.Add($listbox)
$form.Topmost = $True
$form.Add_Shown({$form.Activate()})
[void] $form.ShowDialog()

Show the result (or use it in your powershell scripts)

PS> $listbox.SelectedItems[0]
SCOTT

Pretty cool! No compiler needed, directly run from the powershell prompt

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 sysdba" @?/rdbms/admin/csminst
csscan "'sys/sys as sysdba'" full=y tochar=we8mswin1252 array=1024000 process=5
sqlplus "/ as sysdba" @?/rdbms/admin/csalter.plb

It is not always that straight forward, check output from csscan (scan.*) carefully before running csalter.

Ok, now retry

H:\>set NLS_LANG=american_america.we8pc850

H:\>sqlplus.exe scott/tiger

SQL*Plus: Release 11.1.0.6.0 - Production on Thu May 10 11:28:01 2012

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select chr(128) from dual;

C
-

Not good!

Obviously, the PC850 client character is not good enough. Let’s switch to mswin1252 on the client.


H:\>chcp 1252
Active code page: 1252

H:\>set NLS_LANG=american_america.we8mswin1252

H:\>sqlplus.exe scott/tiger

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select chr(128) from dual;

C
-
Ç

Well, what’s missing now? The font ! Let’s change it from “Raster Fonts” to “Lucida Console”. Either by clicking on the command com properties, or even dynamically with that gem (tested on XP) !


H:\>type Lucida.cs
using System;
using System.Runtime.InteropServices;

public class Lucida
{
const int STD_OUT_HANDLE = -11;

[DllImport("kernel32.dll", SetLastError = true)]
static extern int SetConsoleFont(IntPtr hOut, uint dwFontSize);

[DllImport("kernel32.dll", SetLastError = true)]
static extern IntPtr GetStdHandle(int dwType);

public static void Main()
{
SetConsoleFont(GetStdHandle(STD_OUT_HANDLE), 6);
}
}

H:\>csc Lucida.cs
Microsoft (R) Visual C# 2010 Compiler version 4.0.30319.1
Copyright (C) Microsoft Corporation. All rights reserved.
H:\>Lucida

H:\>sqlplus.exe scott/tiger

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select chr(128) from dual;

C
-

Toad 11.5 is out

The latest Toad is now in production, 11.5, get it from http://toadfororacle.com.

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 requires a 32bit clients, even when running on a 64bit Operating System.

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 Failing (Index): 0
Total Pages Processed (Other): 15755
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 16957
Total Pages Marked Corrupt : 9
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 3220271881 (11.3220271881)

For the dba number, identify the block

def dba=12585405
col block_id new_v block_id
col file_id new_v file_id
select dbms_utility.data_block_address_block(&dba) block_id,
dbms_utility.data_block_address_file(&dba) file_id from dual;

BLOCK_ID FILE_ID
---------- ----------
2493 3

From the block_id/file_id, identify the segment

col owner new_v table_owner
col segment_name new_v segment_name
select owner,segment_name,segment_type from dba_extents where file_id=&file_id and &BLOCK_ID between block_id and block_id + blocks - 1;
OWNER
------------------------------
SEGMENT_NAME
-----------------------------------
SEGMENT_TYPE
------------------
SYS
SYS_IL0000008786C00008$$
LOBINDEX

If it is a lob, identify the column and data_type

select tablespace_name,owner, table_name, column_name, data_type from dba_lobs join
dba_tab_columns using (owner, table_name, column_name) where segment_name =
'&segment_name' and owner='&table_owner';
TABLESPACE_NAME OWNER
------------------------------ ------------------------------
TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------
DATA_TYPE
--------------------------------------------------------------------------------
SYSAUX SYS
WRI$_DBU_FEATURE_USAGE FEATURE_INFO
CLOB

If you are lucky, you will find a useless segment that you will just drop. Or maybe you will be able to move all segments in another tablespace and drop the tablespace with the corrupt blocks.

If you are pretty unlucky like me today, you will find sys segments in system or sysaux.

Either you export all users data and import them again in a new database (but this means downtime), or you start moving the segments in another tablespace. Or dropping and recreating them.
Check Tablespace maintenance tasks

Once dropped or moved or emptied, you may still see the corrupted blocks. Do not forget to purge the recyclebin, either with PURGE DBA_RECYCLEBIN or PURGE TABLESPACE tbs1 USER usr1

Even then the corruption may belong to no more segment but still appear in dbverify. One workaround is to fill the tablespace (check it does not extend) with a dummy table

create table t(x number, y varchar2(4000) default lpad('x',4000,'x')) tablespace tbs1;

exec while true loop insert into t(x) select rownum r from dual connect by level<10000;commit;end loop exec while true loop insert into t(x) select rownum r from dual connect by level<100;commit;end loop exec while true loop insert into t(x,y) select rownum r,'x' from dual;commit;end loop exec while true loop insert into t(x,y) values (null,null);commit;end loop drop table t;

Run dbv again and again until you get completly rid of errors. If you drop and recreate sys objects, or even if you simply move them out of the sys tablespace, dictionary corruption and ora-600 is possible. But well, you had corruption anyway ...

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
PS C:\svn> [int](get-date).dayofweek
3
PS C:\svn> (Get-WmiObject Win32_LocalTime).dayofweek
3

Consistently and unfortunately for me, the week does start on Sunday. And not on Monday.

To get week ending on Sunday, lets add 6 for Monday (5 for Tuesday, and so on) to dayofweek and get the 7-modulo

PS> (6+(get-date).dayofweek)%7
2

Ok, now Mon=0, Tue=1, Wed=2, etc…

Substract it from day of month to truncate to Monday

PS> (get-date).day - (6+(get-date).dayofweek)%7
9

First day of current week is Monday 9th

Now add 5 to get the first Day of month between 0 and 6
Divide by 7…
add 1 to get first week=1 (and not 0).

Truncate

PS> [math]::floor(((get-date).day - (6+(get-date).dayofweek)%7 + 5)/7)+1
3

Which slicely differs from weekinmonth when 1st of month is Sunday !

PS> (Get-WmiObject Win32_LocalTime).weekinmonth
2

Pfeww…

[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 23:15:52 NZST 2012

The consequence : date, and all other unix commands like ls, who, ps that display the date in human readable format, and all programs that use ctime are affected

$ TZ=NZST-12NZDT,M10.1.0/2,M3.3.0/3 perl -e 'use POSIX;print ctime(time)'
Fri Mar 16 23:19:51 2012

Reference and link to the fixes : www-01.ibm.com/support/docview.wss?uid=isg3T1013017

Powershell and dates

I wrote about unix timestamp i powershell.

I wrote : It is chockingly easy !
but I should have written : … it is not correct 🙁


PS> ./perl -e "print time.'`n'"
1331454753
PS> get-date -u %s
1331458358.05694

there is about 3605 seconds difference. 1 hour for Europe/Zurich and 5 seconds to type on a slow keyboard…

Even more confusing


PS> get-date "1970-01-01 00:00:00 +00:00" -u %s
3600

Ok, why that? this is related to the Kind

PS> (get-date "1970-01-01 00:00:00 +00:00").kind
Local

A Unix timestamp of Kind Local is useless. Don’t use this.

Unix timestamp should only refers to Utc.


PS> get-date
Sonntag, 11. März 2012 10:47:35
PS> get-date ((get-date).touniversaltime()) -u %s
1331459257.78432

still it is very confusing to using the unixformat in Windows

PS> get-date -u %c__%Z__%s
So Mrz 11 10:55:05 2012__+01__1331463305.85197
PS> get-date ((get-date).touniversaltime()) -u %c__%Z__%s
So Mrz 11 09:55:46 2012__+01__1331459746.07913

the %s does not work as expected with local (as it refers to a local 1970-01-01) and %Z does not work at all with utc (it sould be +00

OK, let’s switch to the Microsoft time. You count the time as the number of tenth of microseconds (or ticks) since 1 Jan 0001. Again it may differ if you do not use utc.

Let’s define constant $c as number of ticks until 1970-01-01, in utc

PS> $c = (get-date "1970-01-01 00:00:00 +00:00").touniversaltime().ticks
PS> $c
621355968000000000

Let’s convert it to a human readable format

PS C:\> New-Object datetime($c)

Donnerstag, 1. Januar 1970 00:00:00

So far so good.

Lets convert 1331461119 back and forth

PS> (New-Object datetime($c + 1331461119 * 10000000)).tolocaltime()

Sonntag, 11. März 2012 11:18:39

PS> (New-Object datetime($c + 1331461119 * 10000000))

Sonntag, 11. März 2012 10:18:39
PS> ( (get-date "2012-03-11 10:18:39 +00:00").touniversaltime().ticks - $c ) / 10000000
1331461119
PS> ( (get-date "2012-03-11 11:18:39 +01:00").touniversaltime().ticks - $c ) / 10000000
1331461119

BTW, what does Oracle think of the number of ticks of current date ?


PS> $d=36217;"select to_char(date '0001-01-01' + $d,'YYYY-MM-DD') from dual;"|sqlplus -s -L scot/tiger;New-Object datetime($d*864000000000)

TO_CHAR(DA
----------
0100-02-28

Sonntag, 28. Februar 0100 00:00:00

PS C:\> $d=36218;"select to_char(date '0001-01-01' + $d,'YYYY-MM-DD') from dual;"|sqlplus -s -L scot/tiger;New-Object datetime($d*864000000000)

TO_CHAR(DA
----------
0100-02-29

Montag, 1. März 0100 00:00:00

Apparently Microsoft does not have a leap year in 100AD.

Ok, whatelse to say about date?

There is one more format that is commonly used in Microsoft world, the so-called DMTF or Distributed Management Task Force. Let’s have a quick look with 1-JAN-2000.

PS C:\> [System.Management.ManagementDateTimeConverter]::TodmtfDateTime((get-date "2000-01-01 00:00:00"))
20000101000000.000000+060
PS C:\> [System.Management.ManagementDateTimeConverter]::TodmtfDateTime((get-date "2000-01-01 00:00:00 +00:00").touniversaltime())
20000101000000.000000+000
PS C:\> [System.Management.ManagementDateTimeConverter]::ToDateTime('20000101000000.000000+060')
Samstag, 1. Januar 2000 00:00:00
PS C:\> [System.Management.ManagementDateTimeConverter]::ToDateTime('20000101000000.000000+000').touniversaltime()
Samstag, 1. Januar 2000 00:00:00

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 (from Microsoft). More powerful of course.

ADO (within powershell):

PS C:\> [Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient")

GAC Version Location
--- ------- --------
True v2.0.50727 C:\WINDOWS\assembly\GAC_32\system.data.oracleclient\2.0.0.0__b77a5c561934e089\system.data.orac...

PS C:\> $connection=New-Object DATA.OracleClient.OracleConnection("Data Source=DB01;User Id=scott;Password=tiger")
PS C:\> $connection.Open()
PS C:\> (new-Object DATA.OracleClient.OracleCommand("select 'Hello World' from dual",$connection)).ExecuteScalar()
Hello World
PS C:\> $connection.Close()

ODP :
PS C:\> [Reflection.Assembly]::LoadFile("C:\oracle\product\11.1.0\client_1\ODP.NET\bin\2.x\Oracle.DataAccess.dll")

GAC Version Location
--- ------- --------
True v2.0.50727 C:\WINDOWS\assembly\GAC_32\Oracle.DataAccess\2.111.6.0__89b483f429c47342\Oracle.DataAccess.dll

PS C:\> $connection=New-Object Oracle.DataAccess.Client.OracleConnection("Data Source=DB01; User Id=scott; password=tiger")
PS C:\> (new-object Oracle.DataAccess.Client.OracleCommand("select 'helloworld' from dual",$connection)).executescalar()
helloworld
PS C:\> $connection.close()
PS C:\> $connection.dispose()

Read more : Oracle Data Provider for .NET Developer’s Guide

PS: also possible

PS> $dataset = New-Object Data.dataset
PS> (New-Object Oracle.DataAccess.Client.OracleDataAdapter("select * from emp",$connection)).fill($dataset)
14
PS> ($dataset.tables[0]|where{ $_.empno -eq 7902 }).ename
FORD

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
proxy.example.com
PS> (get-wmiobject win32_proxy).ProxyPortNumber
8080

it goes on and on and on… the ways those things are accessible from the prompt in interactive command line mode or within a script is amazing me

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: allocating 1536 logical pages (6.0 megabytes) on disk
'data_dev' (1536 logical pages requested).
Database 'lsc01' is now online.
1> use lsc01
2> go
1> sp_adduser scott
2> go
New user added.
(return status = 0)
1> grant create table to scott
2> go
1> exit
$ isql -U scott -P tiger7 -D lsc01
1> create table dept(deptno int constraint pk_dept primary key, dname varchar(14), loc varchar(13))
2> go
1> insert into dept values(10,'ACCOUNTING','NEW YORK')
2> go
(1 row affected)
1> insert into dept values(20,'RESEARCH','DALLAS')
2> go
(1 row affected)
1> insert into dept values(30,'SALES','CHICAGO')
2> go
(1 row affected)
1> insert into dept values(40,'OPERATIONS','BOSTON')
2> go
(1 row affected)
1> create table emp(empno int constraint pk_emp primary key, ename varchar(10), job varchar(9), mgr int null, hiredate date, sal float, comm float null, deptno int constraint fk_dept references dept)
2> go
1> insert into emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,null,20)
2> go
(1 row affected)
1> insert into emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30)
2> go
(1 row affected)
1> insert into emp values (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30)
2> go
(1 row affected)
1> insert into emp values (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20)
2> go
(1 row affected)
1> insert into emp values (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30)
2> go
(1 row affected)
1> insert into emp values (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30)
2> go
(1 row affected)
1> insert into emp values (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10)
2> go
(1 row affected)
1> insert into emp values (7788,'SCOTT','ANALYST',7566,'1987-07-13',3000,NULL,20)
2> go
(1 row affected)
1> insert into emp values (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10)
2> go
(1 row affected)
1> insert into emp values (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30)
2> go
(1 row affected)
1> insert into emp values (7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20)
2> go
(1 row affected)
1> insert into emp values (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30)
2> go
(1 row affected)
1> insert into emp values (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20)
2> go
(1 row affected)
1> insert into emp values (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10)
2> go
(1 row affected)
1> select ename,dname,d.deptno,empno from dept d left join emp e on (d.deptno=e.deptno)
2> go
ename dname deptno empno
-------------------- ---------------------------- ----------- -----------
KING ACCOUNTING 10 7839
CLARK ACCOUNTING 10 7782
MILLER ACCOUNTING 10 7934
FORD RESEARCH 20 7902
SCOTT RESEARCH 20 7788
JONES RESEARCH 20 7566
ADAMS RESEARCH 20 7876
SMITH RESEARCH 20 7369
WARD SALES 30 7521
BLAKE SALES 30 7698
ALLEN SALES 30 7499
JAMES SALES 30 7900
TURNER SALES 30 7844
MARTIN SALES 30 7654
NULL OPERATIONS 40 NULL

(15 rows affected)
1> commit
2> go

Sql file to download : emp-dept.sql

There are obviously a few differences, the password is associated with a login that belongs to the “db server”, and the user is created in the database “lsc01” and associated to the login. In Sybase, one database server (typically one Unix Process), contains more than one database. There is also a master database, which is as sensitive as your system tablespace. Still in the official Sybase administrator course, they already teach you how to manually update the dictionary…

There is no varchar2 or number, and the default for a column is not null. The equivalent of DESC EMP is sp_help emp, but it is more verbose. The columns are case-sensitive, create table EMP is not the same as create table emp.

Not all “ANSI” code will work, like DATE ‘2000-01-01’, FULL OUTER JOIN, USING and all those Oracle “ANSI” things are not meant “COMPATIBLE WITH SYBASE” but more “CONFORM TO ANSI”

ok, that said, it was pretty straightforward

Unix timestamp in powershell

I was a bit playing with powershell. I wanted to know the week of month, day of weeks, and other gems. It is chockingly easy !

Unix Timestamp :
PS> get-date -uformat %s
1329322194.18894

To get the day of week as number, you can chose between [int](get-date).dayofweek and get-date -uformat %u

What does uformat stand for ? Unix Format ! This is probably why powershell is so good, they integrated all unix and .net goodies in one tool 🙂

Ok, one more, get the week of month :
PS> (Get-WmiObject Win32_LocalTime).weekinmonth
3