fun with cron

Today I find out that my scheduler was too busy to execute all jobs in my crontab !? * * * * * (while :;do ssh example.com :; done) 59 23 19 06 * touch /tmp/bang my while loop is going to produce so much hangs on the cron deamon that it may not be […]

distinct listagg

One limitation in listagg, you cannot use DISTINCT. Okay, there are plenty of distinct-capable listagg workarounds, but to get the real listagg working, it is a bit of an headache With one listagg SELECT   DEPTNO,   LISTAGG (JOB, ',')     WITHIN GROUP (ORDER BY JOB) JOBS FROM (   SELECT DISTINCT DEPTNO, JOB  FROM EMP) GROUP BY DEPTNO;     DEPTNO […]

Index suggestion from the access advisor

Test case : create table t(x varchar2(8) primary key,   y varchar2(30)); insert into t(x,y) select   to_char(rownum,'FM00000000'),   object_name from all_objects where rownum<1e4; commit; exec dbms_stats.gather_table_stats(user,'T') One user wants to filter on x but does not do the casting properly SQL> select * from t where x=00000001; X        Y                             ——– —————————— 00000001 CON$ He received the expected data. […]

execute Sybase procedures from Oracle SQL Developer

Oracle SQL Developer provides access to data and code from other database systems than Oracle If you run Sybase procedures (or Microsoft SQL Server I suppose), you cannot use the BEGIN / DECLARE / EXEC in anonymous blocks begin   print 'HELLO' end Error starting at line : 1 in command – begin   print 'HELLO' end […]

Get the secondmax, again

Just bouncing on 2008/07/secondmax. Another way of getting secondmax would be with an ordered collection. While collection methods like (n), first, last, count are not in SQL, I used PLSQL (within SQL) WITH FUNCTION f (c sys.odcinumberlist, n NUMBER) RETURN number IS BEGIN RETURN c (n); END; SELECT   f(     CAST(       COLLECT(         CAST(           sal AS NUMBER […]

The long long route to Kerberos

If you want to single-sign-on to your database with your Windows credentials, be aware, it is hard! But the benefit is quite valuable, no more saved password on the client, central password management and user expiration, compliance to the security guidelines, and at no extra cost Landscape for my setup One PC with Windows (PC01.EXAMPLE.COM) […]

Configure ORACLE_HOME for SQL Developer

The release V4.0 of sql developer is available for download : oracle.com/technetwork/developer-tools/sql-developer/downloads The doc is there : docs.oracle.com/cd/E39885_01/index.htm And read Jeff Smith twit’s and blog I requested some time ago a 64bit Windows version with JDK on Oracle SQL Developer Exchange and once again it got accepted. Okay, I have on my PC two Oracle […]

OracleContext as top object in Active Directory

When you do expand your Active Directory schema, it is not reversible; how to decide to use the OracleContext as a top object or not? On the one hand, for tnsnames resolution, you could hide your context down in your AD structure and change the path in ldap.ora DIRECTORY_SERVER_TYPE=AD DEFAULT_ADMIN_CONTEXT="OU=Oracle,OU=Misc,DC=example,DC=com" For debugging, I set TNSPING.TRACE_LEVEL=ADMIN […]

Changing the log apply delay (DelayMins)

Whenever you change the DelayMins setting in Dataguard, you must remember it affects only logs that have not been shipped yet. DGMGRL> show database sDB01 delaymins   DelayMins = '5' DGMGRL> edit DATABASE sDB01 set property delaymins=2; Property "delaymins" updated ARC3: Archive log thread 1 sequence 3199 available in 5 minute(s) Tue Dec 03 15:34:59 2013 […]

sqlnet.ora, sqlplus.exe and tnsping.exe inconsistencies

if you use tnsping.exe and sqlplus.exe, the way the sqlnet.ora and tnsnames.ora are located differs Let’s take the following setup C:\tmp>type dir1\sqlnet.ora NAMES.DEFAULT_DOMAIN = (EXAMPLE.COM) NAMES.DIRECTORY_PATH = (TNSNAMES) C:\tmp>type dir1\tnsnames.ora db.example.com=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srv1.example.com)(PORT=1521))(CONNECT_DATA=(SID=db01))) db.example.org=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srv1.example.org)(PORT=1521))(CONNECT_DATA=(SID=db01))) C:\tmp>type dir2\sqlnet.ora NAMES.DEFAULT_DOMAIN = (EXAMPLE.ORG) NAMES.DIRECTORY_PATH = (TNSNAMES) C:\tmp>type dir2\tnsnames.ora db.example.com=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srv2.example.com)(PORT=1521))(CONNECT_DATA=(SID=db02))) db.example.org=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srv2.example.org)(PORT=1521))(CONNECT_DATA=(SID=db02))) You set TNS_ADMIN to dir1 and your current directory is dir2. […]

unreadable output file in powershell

If you redirect a not-completly-string output (like a spfile) to a file in powershell, you may not see the same in the file as in the output without redirection PS C:\> Select-String "compatible" .\spfileDB01.ora spfileDB01.ora:13:*.compatible='11.2.0.4.0' with redirection PS> Select-String "compatible" .\spfileDB01.ora > compatible.txt PS> vim -b .\compatible.txt ÿþ^M^@ ^@s^@p^@f^@i^@l^@e^@D^@B^@0^@0^@1^@.^@o^@r^@a^@:^@1^@3^@:^@*^@.^@c^@o^@m^@p^@a^@t^@i^@b^@l^@e^@=^@'^@1^@1^@.^@2^@.^@0^@.^@4^@.^@0^@'^@^M^@ ^@ With redirection and conversion to […]

multiconsumer Queue with an Oracle Type from Java

You have a multi consumer queue with a user defined type CREATE TYPE topic_message AS OBJECT(Subject VARCHAR2(30),Text VARCHAR2(80)) / BEGIN   dbms_aqadm.create_queue_table(     'topic_queue_table',     'topic_message',     Multiple_consumers=>TRUE);   dbms_aqadm.create_queue(     'toy_topic',     'topic_queue_table');   dbms_aqadm.start_queue(     'toy_topic'); END; / You create the corresponding java class with JPublisher (check the documentation of Database JPublisher User’s Guide 12c Release 1 (12.1) from which I took […]

strings larger than 4000 in 12c

Back in Oracle 7, the maximum length for VARCHAR2 was 2000. In 11gR2, it is still 4000 for varchar2/char columns and for literals. Any attempt to use something larger will produce an infamous ora-910, ora-1704 or ora-1489 error. SQL> create table t(x varchar2(5000)); create table t(x varchar2(5000))                            * ERROR at line 1: ORA-00910: specified […]

Advanced Queuing hello world

First for those who are looking for the difference between Queuing, Advanced Queuing (AQ) and Streams Advanced Queuing, there is none. There is no Beginner Queuing and Advanced Queuing was renamed to Streams advanced queuing when streams was popular and renamed back to Advanced Queuing was streams was deprecated. I am the provider, I give […]

Change background of Oracle Entreprise Manager

If you have more than one OEM and want a red status bar in your production OEM, here the proceedings Stop your cloud control emctl stop oms -all Unzip the $MW/oracle_common/modules/oracle.adf.view_11.1.1/adf-richclient-impl-11.jar in a new directory $MW/jdk16/jdk/bin/jar -xvf $MW/oracle_common/modules/oracle.adf.view_11.1.1/adf-richclient-impl-11.jar edit the picture dbd_topShadow.png (for instance) in adf/images/fusion-11.1.1.3.0/dbd_topShadow.png and copy it to META-INF/adf/images/fusion-11.1.1.3.0/dbd_topShadow.png with your favorite picture […]

Monitoring details on your explain plan

We know how to generate an explain plan. SET AUTOT ON, the good old ambulance in Toad (which is no longer an ambulance those days), the explain plan button in SQL Developer or simply EXPLAIN PLAN create table t1 partition by hash(object_id) partitions 16 as select * from dba_objects; exec dbms_stats.gather_table_stats(user, 'T1') explain plan for […]

Google Reader end of life

For those of my readers who were using Google Reader, here are my comments 1) backup your data, read this : https://support.google.com/reader/answer/3028851 2) for the web gui, there is a one-click migration to Feedly : http://feedly.com 3) you can import the OPML (Outline Processor Markup Language) file from Google Takeout into Microsoft Outlook (right click […]

ODBC 32bits for Windows 64bits

Windows-On-Windows 64-bit (WOW64) enables you to run 32bits applications in 64bits OS. You will see there is another powershell, another registry, another ODBC tool, another Oracle client. %SYSTEMROOT%\syswow64\WindowsPowerShell\v1.0\powershell.exe First, we run powershell(x86) Get-itemproperty HKLM:\SOFTWARE\ORACLE\*| Select-Object ORACLE_HOME,ORACLE_HOME_NAME ORACLE_HOME                                                 ORACLE_HOME_NAME ———–                                                 —————- C:\oracle\product\11.2.0\client_32                          client32bit_11203 Only the Oracle 32bit client is displayed cmd /c "%SYSTEMROOT%\syswow64\odbcconf.exe /a {configdsn ""Oracle in […]

Oracle ODBC hello world with powershell

Demo : cmd /c "odbcconf.exe /a {configdsn ""Oracle in OraClient11g_home1"" ""DSN=helloworld|SERVER=DB01""}" Create a helloworld data source connecting to your DB01 tns alias in your OraClient11g_home1 Oracle Home. It is easy to get the Oracle Home name with Get-itemproperty HKLM:\SOFTWARE\ORACLE\*| Select-Object ORACLE_HOME,ORACLE_HOME_NAME ORACLE_HOME                                       ORACLE_HOME_KEY ———–                                       ————— C:\oracle\product\11.1.0\client_1                 OraClient11g_home1 C:\oracle\product\11.2.0\client_1                 OraClient11g_home2 Then we create the connection (as […]

Best practice : use double quotes, even in DBMS_STATS

Whenever you create a table, it is better to use double quotes to avoid invalid identified. SQL> CREATE TABLE /XXX(x number); CREATE TABLE /XXX(x number)              * ERROR at line 1: ORA-00903: invalid table name SQL> CREATE TABLE "/XXX"(x number); Table created. Even in DBMS_STATS you should use double quotes SQL> exec dbms_stats.gather_table_stats(user,'/XXX') BEGIN dbms_stats.gather_table_stats(user,'/XXX'); […]