Author: Laurent Schneider

I just learnt a neat trick from Oracle Support. How do you see the current value of NLS_LANG in SQLPLUS ? HOST is not the right answer. E.g.: Unix: SQL> host echo $NLS_LANG AMERICAN_SWITZERLAND Windows: SQL> HOST ECHO %NLS_LANG% %NLS_LANG% The correct setting is revealed by @.[%NLS_LANG%] E.g.: Unix: SQL> @.[$NLS_LANG] SP2-0310: unable to open […]

I posted earlier about sending html mail. Here an example on how to send -fake- excel per mail echo “select * from emp;”| sqlplus -M “HTML ON” -s scott/tiger| uuencode emp.xls| mail [email protected] May this be of some help to my readers !

Your business partner wants to receive some daily mail with an sql query output in it. It does not need to be ultra-fancy, but some colors and titles would not hurt. Here is the report in SQL: select dname, sum(sal) from emp join dept using (deptno) group by rollup(dname); Ok, let’s do the report within […]

Advertisment found on http://www.firefoxsupport.net/software-download/advanced-oracle-sql-programming-the-expert-guide-to-writing-complex-queries.html

I wrote about tracking ddl changes with a trigger there : track ddl changes Another option is to use auditing. A new and cool alternative is to use enable_ddl_logging (11gR2). This will track all ddl’s in the alert log ALTER SYSTEM SET enable_ddl_logging=TRUE Then later you issue create table t(x number) and you see in […]

What is the time offset of the current date in Unix? perl -e ‘ $t=time; @l=localtime($t); @g=gmtime($t); $d=$l[2]-$g[2]+($l[1]-$g[1])/60; $gd=$g[3]+$g[4]*31+$g[5]*365; $ld=$l[3]+$l[4]*31+$l[5]*365; if($gd$ld){$d-=24} print ($d.”\n”)’ 2 Am I in summer (DST)? perl -e ‘if((localtime)[8]){print”yes”}else{print “no”}’ yes

You got an internal error and want to create a zip of all relevant files. First, let’s generate an internal error. I found a quick way to generate an ora-600 or an ora-700 (which is a harmless ora-600 in 11g, read 737878.1) on oradeblog SQL> oradebug unit_test dbke_test dde_flow_kge_soft foo bar baz Statement processed. Now […]

I had fun answering a question about random on the technical forums. What is in your opinion the boolean value of DBMS_RANDOM.VALUE=DBMS_RANDOM.VALUE? Or, how many rows would select * from dual where dbms_random.value=dbms_random.value; return? It is wrong to assume the function will be evaluated twice. The short answer would be : do not rely on […]

There is a new command in 11g to display the current events, which is oradebug eventdump. For instance : SQL> alter session set events ‘10046 trace name context forever,level 12:942 trace name ERRORSTACK level 3’; SQL> oradebug setmypid Statement processed. SQL> oradebug eventdump session sql_trace level=12 942 trace name ERRORSTACK level 3 Read metalink note […]

I recently wrote on table reorg and rebuild index Rule number one : you cannot rebuild a partitioned index in whole. You need to rebuild each individual (sub-)partition Rule number two : to rebuild an iot, move the table instead of trying to rebuild the underlying index Rule number three : a LOB index is […]

EZCONNECT is the easy connect protocol, available in 10g, whenever you want to connect to a database without tnsnames and without ldap. $ grep -iw directory_path $TNS_ADMIN/sqlnet.ora names.directory_path=EZCONNECT $ sqlplus scott/tiger@//srv01:1521/db01 connect to server srv01 on port 1521 for service db01 HOSTNAME was the old-fashion way to connect to a database, where hostname = sid […]

It is a very common challenge for a dba to create scripts that work on every version. How do you return NULL if a column does not exists? Imagine I have a view that returns the table_name, column_name and retention_type of my LOBS. SQL> create table t1(c clob) lob(c) store as (retention); Table created. SQL> […]

Before you start reading : do not rebuild all your indexes and reorganize all your tables every Sunday morning. One day you may find one of your table missing or one index invalid. Ok, let’s take a case where table reorg and index rebuild is good. One of your table was never cleaned up, it […]

I am not sure whether some tuning guy at Oracle decided to ignore any SELECT statement after execute immediate to save time doing nothing. exec execute immediate ‘select 1/0 from dual connect by level

I wrote about powershell [xml] yesterady : xml and powershell Let’s see how to use XPATH expressions in Powershell John Jack With the [xml] datatype, we create a navigator : (([xml](GC foo.xml)).psbase.createnavigator().evaluate( ‘//emplist/emp[@no=”1″]/ename/text()’ ))|%{$_.Value} John I have not been seduced by a Microsoft product since ages, but I must say I felt with love in […]

I wrote about the unix command-line utility xmllint there : extract xml from the command line Let’s do the same exercice in Powershell PS> gc foo.xml John Jack Simply use [xml] datatype ! (([xml](GC foo.xml)).emplist.emp|Where{$_.no-eq”1″}).ename John Powershell rules!

If you want to get rid of self signed certificate, and the annoying security warnings in your browser, here is how to do it in 2 easy steps 1) create a new wallet in [OMS]/sysman/wallet/console.servername/, either with owm (gui) or with orapki (command line) 2) restart opmn http server opmnctl stopproc process-type=HTTP_Server opmnctl startproc process-type=HTTP_Server […]

I had a very serious issue last Friday with errors as weird as ORA-00322: log name of thread num is not current copy. After a clone from Prod to Test, the prod crashed. Both databases are located on the same server (I am not a virtualization fanatic) and clone from prod to test have been […]

I just upgraded my blog to wordpress 3

Fuadar recently wrote : Grid Control 10.2.0.5 AIX Alert … Basically, if you have an 10g oms Server (any OS / any release) and aix agents (any release), and according to Note 1171558.1, communication between [10g] Oracle Management Service and [AIX] Management Agents will break due to a default self-signed certificate expiring in 31 Dec […]

patch 10098816 is available for download

I was amazed by this oneliner in stackoverflow. First, let me introduce you my old foe, ORA-04068 : Session 1: SQL> CREATE OR REPLACE PACKAGE P AS 2 X NUMBER;Y NUMBER;END; 3 / Package created. SQL> exec P.X := 1 PL/SQL procedure successfully completed. Session 2: SQL> CREATE OR REPLACE PACKAGE P AS 2 X […]

I just install Oracle Enterprise Linux on my new notebook. I wanted to check how far could I improve the performance of a count(*) SQL> drop table big_emp; table big_emp dropped. 258ms elapsed SQL> create table big_emp as with l as(select 1 from dual connect by level alter table big_emp add primary key(empno) table big_emp […]

Oracle buys Sun was an exciting accouncement 20 months ago. What did change in the Solaris/Oracle Database world? First, Oracle delivered Exadata on Sun Hardware (x86_64). Second, they delivered Exadata on Sun Solaris Operating System (x86_64). But now, they announced a combination of software and hardware that will run Oracle database faster than anything ever […]

This Toad 11 beta new feature made me so happy ! Not only you can specify a color for your connection (ex: red=prod, green=devl), and this color is much more visible than it was in previous version of TOAD, but now you can set your connection to be read-only. For any reason, bored, ill, under […]

I wrote about the new defaults in 10gR2 more than 5 years ago FAILED_LOGIN_ATTEMPTS default to 10 in 10gR2 This new DEFAULT increases the security by preventing the users from trying millions of different passwords. However the locking of application users is noticeably decreasing the database availability 🙁 My recommendation : – create a separate […]

A user mentioned one call is faster in test than in prod. And the table has the same size. How could we tune the production to make it quick? user007@PROD> select count(*) from t; COUNT(*) ———- 19832501 Elapsed: 00:03:05.00 Let’s try in test : user007@TEST> select count(*) from t select count(*) from t * ERROR […]

patch 10098816 is available for download

I just wrote one of my first powershell script yesterday, it has a pretty nice syntax actually, and no need to download anything like cygwin or other unix-like shell to your PC. PS> $stmt = “set hea off`n” PS> $stmt += “select ‘hello world’ from dual;” PS> $res = ($stmt | sqlplus -s scott/tiger) PS> […]

Historically and semantically, SQL Developer is a developer tool. There is very little comparison with TOAD for the dba. But… Oracle is working on this! In the latest release, 3.0 EA1, you will be able to see Tablespaces, Redo logs and controlfile, finally. Still no session browser, but I am delighted that the database administrators […]

I already wrote about jdbc hello world and listener with tcps. Let’s combine both technologies ! TCPS.java import java.util.Properties; import java.security.Security; import java.sql.*; import javax.net.ssl.*; public class TCPS { public static void main(String argv[]) throws SQLException { String url = “jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(Host=dbsrv001)(Port=12345))(CONNECT_DATA=(SID=DB01)))”; Properties props = new Properties(); props.setProperty(“user”, “scott”); props.setProperty(“password”, “tiger”); props.setProperty(“javax.net.ssl.trustStore”,”cwallet.sso”); props.setProperty(“javax.net.ssl.trustStoreType”,”SSO”); Security.addProvider(new oracle.security.pki.OraclePKIProvider()); DriverManager.registerDriver(new […]