Author: Laurent Schneider

Almost a decade ago I wrote about su in sqlplus. This 10gR2 “new” feature allows delegation à la sudo. By checking the DBA_USERS in 12c I found PROXY_ONLY_CONNECT. According to Miguel Anjo, there is a secret syntax for allowing only the proxy user. SQL> ALTER USER app_user PROXY ONLY CONNECT; SQL> CONNECT app_user/xyz ERROR:ORA-28058: login […]

Depending the files, you may use different signs for comments, typically # hash // slash slash /* slash-star star-slash */ : column — dash dash The latest is used in sql and pl/sql, but : CREATE TABLE t (x NUMBER) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY data_pump_dir ACCESS PARAMETERS ( FIELDS TERMINATED BY ‘;’ […]

#excel to #csv in #powershell (New-Object -ComObject Excel.Application).Workbooks.Open("c:x.xlsx").SaveAs("c:x.csv",6) — laurentsch (@laurentsch) February 13, 2015 One-liner to convert Excel to CSV (or to and from any other format). There is a bug 320369 if you have excel in English and your locale is not America. Just change your settings to us_en before conversion.

After table maintenance, like move or split partition, underlying indexes are marked as unusable. This boils down to segment reorganisation, not dictionary change. For instance : CREATE TABLE t(x NUMBER) PARTITION BY RANGE(x) (PARTITION p1 VALUES LESS THAN (MAXVALUE)); CREATE INDEX i ON t (x); INSERT INTO t VALUES (1); ALTER TABLE T SPLIT PARTITION […]

Not easy to detect, and depending on the product (agent/database), it may have only limited side effects. Like external jobs not running, operating systems statistics not collected. But it is not always easy to diagnose. For instance if you patch from OMS 12cR2 to 12cR3, and you run the root.sh only in 12cR2, they are […]

I just added a few features to WordPress, please report any bug and test your comments here. Thanks

if you really need to quickly find the latest partition per table, I have written this little gem WITH FUNCTION d (b BLOB, len number) RETURN DATE IS d DATE; BEGIN IF DBMS_LOB.SUBSTR (b, 1, 1) = hextoraw(’07’) and len=83 THEN DBMS_STATS.convert_raw_value (DBMS_LOB.SUBSTR (b, 12, 2), d); ELSE d := NULL; END IF; RETURN d; […]

You need to check the high_value from dba_tab_partitions. Or you you could metadata. With metadata, it is not a long, it either clob or clob-xml. SELECT t.table_name, MAX ( TO_DATE ( REGEXP_SUBSTR ( EXTRACT ( (VALUE (x)), ‘HIBOUNDVAL’).getStringVal (), ‘ \d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}’), ‘YYYY-MM-DD HH24:MI;:SS’)) high_value FROM user_tables t, TABLE ( XMLSEQUENCE ( EXTRACT ( xmltype […]

Many many moons ago I vaguely remember having a similar issue with java keystore / truststore and microsoft certificates stores. When you start using SSL for your listener, you could potentially face a large number of issues amoung your toolsets. In my opinion, the most disastrous one is that you cannot monitor your database with […]

If you configure your listener for encryption only, you do not really need authentication. It works pretty fine until 11.2.0.2, I wrote multiple posts on ssl. You add SSL_CLIENT_AUTHENTICATION=FALSE to your server sqlnet.ora and listener.ora and specify an “anon” cipher suite in your client. You do not need to validate the certificate, so a default […]

Let’s check this small script – foo.sh #!/bin/sh echo foo.1:`date` | tee $HOME/tmp/foo.txt sleep 3 echo foo.2:`date` | tee -a $HOME/tmp/foo.txt $ $HOME/tmp/foo.sh foo.1:Thu Nov 27 17:34:53 CET 2014 foo.2:Thu Nov 27 17:34:56 CET 2014 Very obvious, I write to the console, wait three seconds, then write to the console. Ok, let’s take another script […]

A not uncommon issue with firewalls and listeners are timeouts. Your production database may be behind a firewall, you may connect from a remote location, even your Windows workstation may have some firewall activated, possibly you use ssh tunnels or TCPS. All those occasionally lead to timeouts and connection abortion, for instance ORA-03113 end-of-file on […]

just released today http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html only in Enterprise Edition at the moment, and now available on HPUX, zLinux and AIX #oracle12c #db12102 is out for a bunch of platform #aix #os390 #hpux #zlinux — laurentsch (@laurentsch) November 17, 2014 This is the first and last patchset for 12cR1 #oracle 12.1.0.2 is the last patch set for […]

select to_char( sum( power(100,rownum-1)* deptno ), ‘FM99G99G99G99G99’, ‘NLS_NUMERIC_CHARACTERS=,;’ ) deptlist from dept DEPTLIST ————— 40;30;20;10 I also wrote about distinct listagg. The same applies for sum distinct. select to_char( sum(power(1e3,d-1)*deptno), ‘FM999G999G999’, ‘NLS_NUMERIC_CHARACTERS=,;’ ) deptsum, to_char( sum(distinct power(1e2,d-1)*deptno), ‘FM99G99G99’, ‘NLS_NUMERIC_CHARACTERS=,;’ ) deptsumdist, to_char( sum(power(1e1,d-1)), ‘FM9G9G9’, ‘NLS_NUMERIC_CHARACTERS=,;’ ) deptcount, to_char( sum(power(1e4,c-1)*comm), ‘FM9999G9999G9999G9999G9999’, ‘NLS_NUMERIC_CHARACTERS=,;’ ) commlist from ( […]

Powershell is very strong with XML, to convert an XML document to something flat like a CSV file, it is incredibly powerfull. Let’s take a file called emp.xml 10 CLARK MILLER KING 20 SMITH FORD ADAMS SCOTT JONES 30 ALLEN WARD MARTIN BLAKE TURNER JAMES To get all employees, it is awfully easy ([xml](gc emp.xml)).EMPTABLE.DEPT.EMPLIST.ENAME […]

To have the same users in multiple databases and no single sign on is quite a nightmare for password expiration, synchronisation and validation. You probably were discouraged by the long long route to kerberos, where the 11.2.0.2 bugs are fixed in 11.2.0.4, the 12.1 bugs are fixed in 12.2. And lot’s of system changes that […]

If you look for the last insert result, check returning into. Is it identity column, the rowid, any expression, get it back SQL> var r varchar2(24) SQL> var x number SQL> var d varchar2(30) SQL> insert into t values (default) returning rowid,x,sysdate into :r,:x,:d; 1 row created. SQL> print R ——————– AAAaFTAAIAAAAILAAD X ———- 6 […]

When you grant table access thru a role, you cannot use that role in a stored procedure or view. create role r; create user u1 identified by ***; grant create procedure, create session to u1; create user u2 identified by ***; grant create procedure, create session, r to u2; conn u1/*** create procedure u1.p1 is […]

In the past, most of my customers skipped R1 releases. That is, 8.1.7 -> 9.2 -> 10.2 -> 11.2. SAP does the same. For the very first time SAP plans to go to 12.1.0.2 + some PSU in spring 2015. But only to avoid running out of support and without any fancy feature like Multitenant […]

sqlplus 10gR2 and later allows you to select from a BLOB. If you use linux, you could convert the hex output to binary with xxd sqlplus -s scott/tiger

I just learnt a new syntax. With 12.1.0.2, you can dive into your JSON document within sql, it looks a bit unusual to me SQL> CREATE TABLE t (b CLOB CHECK (b IS JSON)); Table created. SQL> insert into t(b) values (‘{“c”:{“d”:{“e”:1}}}’); 1 row created. SQL> select a.b.c.d.e from t a; C ———————- 1 Largely […]

@mkratoch is speaking at 10am UGF2633: Managing Oracle 12c on Windows Again, 12cR1ps1 came before AIX and HPUX. SQL> select dbms_utility.port_string, version from v$instance; PORT_STRING VERSION ——————– —————– IBMPC/WIN_NT64-9.1.0 12.1.0.2.0

A common error when you import single-byte characters (e.g. iso8859p1 or mswin1252) into multi-bytes databases (e.g. utf8) is ORA-12899: value too large for column. The root cause is the default semantics in a database being BYTE SQL> select VALUE, ISDEFAULT from v$parameter where NAME=’nls_length_semantics’ VALUE ISDEFAULT ——- ——— BYTE TRUE It means, one char equals […]

setup -J-Duser.country=FR -Duser.language=fr It works also on UNIX runInstaller -J-Duser.country=FR -J-Duser.language=fr Documented in Sap Note : 1431796

I wrote about ssl version in jdbc thin yesterday The default version also no longer works for the thick client with 12c client and 11g Server. With 11gR2 : C:> tnsping (DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=SRV01)(PORT=1521))) TNS Ping Utility for 64-bit Windows: Version 11.2.0.4.0 OK (100 msec) with 12cR1 : C:> tnsping (DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=SRV01)(PORT=1521))) TNS Ping Utility for 64-bit Windows: […]

Thanks to platform independence, the same java code work on different platforms. import java.util.Properties; import java.security.Security; import java.sql.*; import javax.net.ssl.*; public class KeyStore { public static void main(String argv[]) throws SQLException { String url=”jdbc:oracle:thin:@(DESCRIPTION=”+ “(ADDRESS=(PROTOCOL=TCPS)(Host=SRV01)(“+ “Port=1521))(CONNECT_DATA=(SID=DB01)))”; Properties props = new Properties(); props.setProperty(“user”, “scott”); props.setProperty(“password”, “tiger”); props.setProperty(“javax.net.ssl.trustStore”, “keystore.jks”); props.setProperty( “javax.net.ssl.trustStoreType”,”JKS”); props.setProperty( “javax.net.ssl.trustStorePassword”,”***”); DriverManager.registerDriver( new oracle.jdbc.OracleDriver()); Connection […]

in your current session, as written there, check sys_context(‘USERENV’, ‘NETWORK_PROTOCOL’) in another session, you could grab some hints out of the network service banner. Do the maths, when it is not-not using ssl, it probably is… select sid,program, case when program not like ‘ora___@% (P%)’ then (select max(case when NETWORK_SERVICE_BANNER like ‘%TCP/IP%’ then ‘TCP’ when […]

#db12cR2 release announced for 2016, Doc ID 742060.1 — laurentsch (@laurentsch) September 5, 2014

Some settings matter when using dbms_metadata. define large clobs set long 1000000 large long columns set longchunksize 32000 long lines set linesize 32000 no trailing spaces set trimspool on no header set heading off no page size set pages 0 no page feed (^L) set newpage none no start of page set embedded on no […]

also with ODP in 12c, you can check the commit outcome as in jdbc let’s create a table with a deferred primary key create table t (x number primary key deferrable initially deferred); Here an interactive Powershell Demo PS> [Reflection.Assembly]::LoadFile(“C:\oracle\product\12.1.0\dbhome_1\ODP.NET\bin\4\Oracle.DataAccess.dll”) GAC Version Location — ——- ——– True v4.0.30319 C:\Windows\Microsoft.Net\assembly\GAC_64\Oracle.DataAccess\v4.0_4.121.1.0__89b483f429c47342\Oracle.DataAccess.dll I first load the assembly. Some […]

Getting the logical transaction id in 12c will greatly simplify your error handling and enhance your business continuity in your application. In 11g and below, your java code use to look like try { insert into… } catch () { error_handling() } but one probably assumed the insert failed when it was committed (e.g. database […]