Rman and DBGSQL message

I have not seen DBGSQL very often. But today again, a duplicate in RMAN was failing with, amoung other errors, sqlcode 911

RMAN> duplicate target database to DB02
  until time "to_date('2013-01-29_00:00:00','YYYY-MM-DD_HH24:MI:SS')"
  nofilenamecheck ;

DBGSQL:     TARGET> select 2013-01-29_00:00:00 from sys.dual
DBGSQL:        sqlcode = 911
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 02/01/2013 17:58:23
RMAN-05501: aborting duplication of target database
ORA-01861: literal does not match format string

I have not found anything useful except bug Bug 9351175 which is fixed in 11.2 (and I have, but I could workaround the problem with


Because I do like to have readable timestamp (with seconds) in my RMAN logs, I set NLS_DATE_FORMAT in my spfile

alter system set nls_date_format='YYYY-MM-DD_HH24:MI:SS';

Book review : EM12c

OCM Oracle ACE Porus Homi Havewala wrote a new book on Cloud Control

Oracle Enterprise Manager Cloud Control 12c: Managing Data Center Chaos

It is a book about Enterprise Manager, but also a book about the Chaos.

There is no separation of roles […] there is no DBA Designer (the Senior DBA) […] and no DBA Operator (the Junior DBA). The Senior […] does not even have the time […] the Junior then decides to try out different things

It is also a book about em. And about 12c. No mention of 12cR2, which is massively more performant.

I have been using 12c for about one year and multiple topics are covered. There is not much said on the Incident Manager, which seems to be powerfull but it is for me more time consuming than acknowledging the alerts in 10g/11g.

There is a huge advantage of using 12c, to deploy new agents and new targets in very large shops.

I have setup the software library in offline mode (download with pc from metalink and load in em with emcli), the offline fashion is not documented. In all the places where I have been working, an internet communication between outside world (oracle.com) and database server is banned.

Lot’s of screenshots on Exadata. Not sure where the author could try it, the domain were grayed out (why not edit to example.com with Photoshop? )

There is a lot of content (almost 400 pages), to mention only a few : Real Time ADDM, Real Application Testing, Database deployment, data masking.

At the end of book you will enjoy the case studies. Reading thru the lines, it is not always success stories, but rather expectations that em will do the job

The bank also wanted to ensure that their data center was in compliance with security best practices.

As in any IT book, you do not have to read it from page 1 to page 379, myself I have no DB Machine so I could skip the largest chapter which is on Exadata. But it is a pleasant lecture.

The book is very friendly with Oracle. I have experienced awful performance in 12cR1 and Internet Explorer, some annoying and unavoidable java exceptions in the web interface and the dumbest requirement for me was to install cygwin, bash, ssh on one windows server only to deploy the agent!

I recommend the book, it is a good book

How big was my database last month

If you backup your database at least weekly, you could check the datafile size from your backup history.

Without catalog :

select to_char(d,'"W"IW-IYYY') compl, sum(b)/1024/1024/1024*8192 GB 
  select max(DATAFILE_BLOCKS) b, trunc(completion_time,'IW') d 
  from v$backup_datafile 
  group by FILE# ,trunc(completion_time,'IW')
group by d 
order by d;

COMPL        GB
-------- ------
W30-2012   3.73
W31-2012   4.84
W32-2012   5.00
W33-2012   5.05
W34-2012   5.35
W35-2012   5.80
W36-2012   6.12
W37-2012   6.39
W38-2012    .93
W39-2012   7.02
W40-2012   7.56
W41-2012   7.72
W42-2012   7.88
W43-2012   8.08
W44-2012   8.83
W45-2012   9.03
W46-2012   9.45
W47-2012   9.61
W48-2012  10.11
W49-2012  10.29
W50-2012  10.38

The history mostly depends on control_file_record_keep_time. If you do not use an rman catalog, set it to a high value like 93 (3M) or 366 (1Y)

With the rman catalog, use the RC_ view

select DB_NAME,to_char(d,'"W"IW-IYYY') compl, sum(b)/1024/1024/1024*8192 GB 
  select DB_NAME,max(DATAFILE_BLOCKS) b, trunc(completion_time,'IW') d 
  from rc_backup_datafile 
  group by DB_NAME,FILE# ,trunc(completion_time,'IW')
group by DB_NAME,d 
order by DB_NAME,d;

-------- -------- ------
DB01     W30-2012   3.73
DB01     W31-2012   4.83
DB01     W32-2012   5.00
DB01     W33-2012   5.05
DB01     W34-2012   5.34
DB01     W35-2012   5.79
DB01     W36-2012   6.11
DB01     W37-2012   6.39
DB01     W38-2012    .93
DB01     W39-2012   7.01
DB01     W40-2012   7.56
DB01     W41-2012   7.71
DB01     W42-2012   7.87
DB01     W43-2012   8.08
DB01     W44-2012   8.82
DB01     W45-2012   9.02
DB01     W46-2012   9.44
DB01     W47-2012   9.60
DB01     W48-2012  10.10
DB01     W49-2012  10.28
DB01     W50-2012  10.37

If you need to check which table grows the most, check How big was my table yesterday. But remember, RMAN backup is free to use, AWR and the WRI$ tables require the diagnostic pack and the Enterprise edition

accent insensitive regexp

Ever wanted to find an accent insentive expression like “bébé” in a column ?

Maybe you tried to list all possible accents. But Posix has the class for you, the list of éèëê could be refered as [=e=]


not only [=e=] is easier to read and to type, but also it is more portable if you copy your scripts from DOS to UNIX and use different character sets

return code and sqlplus

Calling a shell script from within sqlplus is buggy…

I have reported bug 3798918 in (back in 2004) and bug 13349119 in because some metalink guru closed 3798918 as not reproducible.

As written in return code, host does not return the correct code

SQL> host exit 7
SQL> def _RC
DEFINE _RC             = "0" (CHAR)

If you never use _RC, you may believe you are safe. But watch this :

SQL> get foo.sh list
  1  #!/bin/sh
  2  if /bin/false
  3  then
  4    echo this is wrong
  5* fi
SQL> host ./foo.sh
this is wrong

The return code not being set is not only affecting the _RC variable, but it is also affecting all subshells !

Note this is not reproducable with SQLPLUS /NOLOG

SQL> host false
SQL> def _rc
DEFINE _RC             = "1" (CHAR)
SQL> conn x/x
ORA-01017: invalid username/password; logon denied
SQL> host false
SQL> def _rc
DEFINE _RC             = "0" (CHAR)

After my (failed or successfull) tentative to connect as x/x, it is reproducible again

How big was my table yesterday

Oracle saves a lot of information on your behalf. Whenever you get yourself an AWR reported, you access some historic tables (included in the Diagnostic Pack).

Those tables could also be accessed manually.

SELECT savtime,owner,object_name,rowcnt,blkcnt
  dba_objects o
   AND o.object_name='EMP'
   and o.object_id = W.OBJ#
ORDER BY o.owner, o.object_name, w.savtime;

----------------- -------- ----------- ---------- ----------
2012-11-06 06:49  SCOTT    EMP           13215425     120077
2012-11-13 07:28  SCOTT    EMP           12678535     120077
2012-11-20 03:15  SCOTT    EMP           12860640     120077
2012-11-27 03:19  SCOTT    EMP           13045850     120077
2012-12-04 05:41  SCOTT    EMP           13326460     120077

To increase the retention, use DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings

tnsping and instant client

Mostly when you install your instant client, you will not have tnsping handy. You could well try to copy it from a full client, but this is cumbersome to just ping your instance.

I just created one function in my .profile

whence tnsping >/dev/null 2>&1 || 
  tnsping() { 
    sqlplus -L -s x/x@$1 </dev/null | 
      grep ORA- | 
        (grep -v ORA-01017 || echo OK)

and tested it

$ tnsping db999
ORA-12154: TNS:could not resolve the connect identifier specified
$ tnsping db01
$ tnsping db02
ORA-12541: TNS:no listener

Enhancement Request : SSL listener and OEM

#em12c still does not support SSL ! Encrypting network connection (https, ssh, sftp) is common sense in today’s business.

In Enhancement Request 6512390, Created 19-Oct-2007, the customer requested support for SSL.

Most recent update : it is postponed to 13cR2 at least !

*** 09/14/12 04:04 am DISCUSSION ***As we kick off 13c release, cleaning up the ERs. Mass updating all 13%GC% ERs to fixby 13.2GC DEFER. If you want to implement ER in 13.1GC, please update the fixby to = 13.1GC, and update all the other fields as per guidelines published.

Considering the cost of oracle advanced security option (required to get ssl), the lack of ability to influence future product enhancement is disappointing

Create database link and logon trigger

Today I could not understand why I was getting ORA-1031 with create database link.

After analysis, I found out a logon trigger that changed the current schema. As Tom always says, triggers are evil…


SQL> create user u1 identified by xxx;

User created.

SQL> grant create session, create database link to u2 identified by xxx;

Grant succeeded.

SQL> create trigger evil after logon on database  begin 
  2  execute immediate 'alter session set current_schema=u1';end;
  3  /

Trigger created.

SQL> conn u2/xxx
SQL> create database link l;
create database link l
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> show user
USER is "U2"
SQL> select sys_context('USERENV','CURRENT_SCHEMA') from dual;


SQL> alter session set current_schema=u2;

Session altered.

SQL> create database link l;

Database link created.

Drop database link in another schema

Today I wrote this script :


accept owner char prompt "Enter database link owner : "
accept db_link char prompt "Enter link name : "

    job_action=>'BEGIN execute immediate ''drop database link &db_link'';END;'

I am using the scheduler to run a job as another user. The database link owner does not need to have any privilege, neither CREATE SESSION nor CREATE JOB. It could locked and expired.

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);

(X, Y, Z)
2; ;2

$ 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

2; ;2

$ 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';

SQL> alter user scott identified by tiger;

User altered.

SQL> select spare4 from user$ where name='SCOTT';

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 :


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

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;


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;


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

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

 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 RTYPE                          ANYTYPE                 OUT
 P_PARM                         VARCHAR2                IN
 P_ROWS_REQ                     NUMBER                  IN     DEFAULT

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

 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SCTX                           NCOLPIPE                IN/OUT
 P_PARM                         VARCHAR2                IN
 P_ROWS_REQ                     NUMBER                  IN     DEFAULT

 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NROWS                          NUMBER                  IN
 OUTSET                         ANYDATASET              OUT


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.


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' ;
  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;

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.


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


$ uname -s
$ 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
$ mkdir /tmp/bla
$ cd /tmp/bla
$ rm -r /tmp/bla

Wait, did it work?

$ cd /tmp/bla
$ pwd
$ 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:\>sqlplus scott/tiger@db01

SQL*Plus: Release 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 - 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

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:\>sqlplus scott/tiger@db01

SQL*Plus: Release 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 - 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.


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.


[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=
$ 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;


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 (RX1303)

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

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 ename from emp",$connection)
$reader = $command.ExecuteReader()
$a = @()
while ($reader.Read()) {
  $a = $a + $reader.GetString(0)

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)
$form.Topmost = $True
[void] $form.ShowDialog()

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

PS> $listbox.SelectedItems[0]

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).


First, the character set did not support it.

select * from v$nls_parameters where PARAMETER like '%CHARACTERSET%';

PARAMETER                      VALUE
------------------------------ ---------------
NLS_CHARACTERSET               WE8ISO8859P1

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 - 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 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select chr(128) from dual;


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 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select chr(128) from dual;


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:\>sqlplus.exe scott/tiger

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

SQL> select chr(128) from dual;


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.