In Oracle Universal Installer and OPatch User’s Guide it is documented that The first Oracle home is named the “DEFAULT_HOME” and registers itself in the Windows NT Registry.

Remember, NT means New Technology 🙂

There is apparently a Home Selector that is a part of the installation software, maybe something like D:\oracle\product\11.2.0\client_1\bin\selecthome.bat. Sometimes. Not sure

But there is no DEFAULT HOME in the registry.

PS C:\> gci HKLM:\SOFTWARE\ORACLE

Hive: HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE

Name Property
---- --------
KEY_agent12c1 ORACLE_HOME : D:/oracle\core\12.1.0.1.0
ORACLE_HOME_NAME : agent12c1
ORACLE_GROUP_NAME : Oracle - agent12c1
NLS_LANG : AMERICAN_AMERICA.WE8MSWIN1252
KEY_oracle_sysman_db_12_1_0_2_ ORACLE_HOME : D:\oracle\plugins\oracle.sysman.db.discovery.plugin_12.1.0.2.0
0_discovery_Home0 ORACLE_HOME_NAME : oracle_sysman_db_12_1_0_2_0_discovery_Home0
ORACLE_GROUP_NAME : Oracle - oracle_sysman_db_12_1_0_2_0_discovery_Home0
KEY_oracle_sysman_emas_12_1_0_ ORACLE_HOME : D:\oracle\plugins\oracle.sysman.emas.discovery.plugin_12.1.0.2.0
2_0_discovery_Home0 ORACLE_HOME_NAME : oracle_sysman_emas_12_1_0_2_0_discovery_Home0
ORACLE_GROUP_NAME : Oracle - oracle_sysman_emas_12_1_0_2_0_discovery_Home0
KEY_oracle_sysman_oh_12_1_0_1_ ORACLE_HOME : D:\oracle\plugins\oracle.sysman.oh.agent.plugin_12.1.0.1.0
0_agent_Home0 ORACLE_HOME_NAME : oracle_sysman_oh_12_1_0_1_0_agent_Home0
ORACLE_GROUP_NAME : Oracle - oracle_sysman_oh_12_1_0_1_0_agent_Home0
KEY_oracle_sysman_oh_12_1_0_1_ ORACLE_HOME : D:\oracle\plugins\oracle.sysman.oh.discovery.plugin_12.1.0.1.0
0_discovery_Home0 ORACLE_HOME_NAME : oracle_sysman_oh_12_1_0_1_0_discovery_Home0
ORACLE_GROUP_NAME : Oracle - oracle_sysman_oh_12_1_0_1_0_discovery_Home0
KEY_OraClient11g_home1 ORACLE_HOME : D:\oracle\product\11.2.0\client_1
ORACLE_HOME_NAME : OraClient11g_home1
ORACLE_GROUP_NAME : Oracle - OraClient11g_home1
ORACLE_BUNDLE_NAME : Enterprise
NLS_LANG : AMERICAN_AMERICA.WE8MSWIN1252
OLEDB : D:\oracle\product\11.2.0\client_1\oledb\mesg
ORACLE_HOME_KEY : SOFTWARE\ORACLE\KEY_OraClient11g_home1
MSHELP_TOOLS : D:\oracle\product\11.2.0\client_1\MSHELP
SQLPATH : D:\oracle\product\11.2.0\client_1\dbs
KEY_OraGtw11g_home1 ORACLE_HOME : D:\oracle\product\11.2.0\tg_1
ORACLE_HOME_NAME : OraGtw11g_home1
ORACLE_GROUP_NAME : Oracle - OraGtw11g_home1
NLS_LANG : AMERICAN_AMERICA.WE8MSWIN1252
ORACLE_BUNDLE_NAME : Enterprise
MSHELP_TOOLS : D:\oracle\product\11.2.0\tg_1\MSHELP
SQLPATH : D:\oracle\product\11.2.0\tg_1\dbs
ORACLE_HOME_KEY : SOFTWARE\ORACLE\KEY_OraGtw11g_home1
KEY_sbin12c1 ORACLE_HOME : D:\oracle\sbin
ORACLE_HOME_NAME : sbin12c1
ORACLE_GROUP_NAME : Oracle - sbin12c1
ODP.NET
remexecservicectr remaining_time : 120000
SYSMAN


How do I set my Oracle Home?

Actually if you enter a command like “lsnrctl start”, the OS will search in the PATH for lsnrctl and determines the Oracle Home name accordingly.

Therefore, the only thing you must do to change your default Oracle Home is to set the PATH environment variable. Only then your LSNRCTL START will find the right binary and right parameter file to start your listener.

When building a materialized view, you may want to postpone the loading to a later phase, for instance you install a new release, and the refresh happends every night.

BUILD DEFERRED allow you to build an empty materialized view and refresh it later. But this may still takes ages.


SQL> create materialized view mv1 build deferred as select count(*) c from emp,emp,emp,emp,emp,emp,emp;

Materialized view created.

Elapsed: 00:00:17.28
SQL> select * from mv1;

no rows selected


No data collected, but still a long time (17sec here, but much worst in real life)

A workaround is to use ON PREBUILT TABLE on an empty table, just add a few WHERE 1=0 in your subqueries

SQL> create table mv1 as select * from (select count(*) c from emp,emp,emp,emp,emp,emp,emp where 1=0) where 1=0;

Table created.

Elapsed: 00:00:00.04
SQL> create materialized view mv1 on prebuilt table as select count(*) c from emp,emp,emp,emp,emp,emp,emp;

Materialized view created.

Elapsed: 00:00:00.15
SQL> select * from mv1;

no rows selected

Elapsed: 00:00:00.00


Much faster !

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 11.2.0.2/aix), but I could workaround the problem with


unset NLS_DATE_FORMAT


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


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

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
from
(
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
from
(
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;



DB_NAME COMPL GB
-------- -------- ------
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

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=]


SELECT * FROM TAB WHERE REGEXP_LIKE(C,'b[[=e=]]b[[=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

Calling a shell script from within sqlplus is buggy…

I have reported bug 3798918 in 10.1.0.2 (back in 2004) and bug 13349119 in 11.2.0.3 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
ERROR:
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

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
FROM sys.WRI$_OPTSTAT_TAB_HISTORY w,
dba_objects o
WHERE
o.owner='SCOTT'
AND o.object_name='EMP'
and o.object_id = W.OBJ#
ORDER BY o.owner, o.object_name, w.savtime;


SAVTIME OWNER OBJECT_NAME ROWCNT BLKCNT
----------------- -------- ----------- ---------- ----------
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

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 | 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
OK
$ tnsping db02
ORA-12541: TNS:no listener


#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

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…

DEMO:

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>
SQL> conn u2/xxx
Connected.
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;

SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
-----------------------------------------
U1

SQL> alter session set current_schema=u2;

Session altered.

SQL> create database link l;

Database link created.


Today I wrote this script :

drop_database_link.sql

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

begin
dbms_scheduler.create_job(
job_name=>'&owner..drop_database_link',
job_type=>'PLSQL_BLOCK',
job_action=>'BEGIN execute immediate ''drop database link &db_link'';END;'
);
dbms_scheduler.run_job('&owner..drop_database_link',false);
dbms_lock.sleep(2);
dbms_scheduler.drop_job('&owner..drop_database_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.

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 !

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

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 :

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.

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…

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 !

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

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.

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 😉

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 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/tiger@db01

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!

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


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 …

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!

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 */


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 🙂

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


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]


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

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
-
€