Author: Laurent Schneider

How to convert Excel file to csv

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

Unusable index

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 […]

Do you have a partition in 2015

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 […]

SSL with PKCS12 truststore

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 […]

How to *really* send a script to the background

Let’s check this small script – #!/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.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 […]

KeepAlive socket in 12c listener

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 […] on AIX

just released today 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 is the last patch set for […]

one more stragg

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 ( […]

rowid of the last insert

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 […]

PLS-00201 in stored procedures

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 […]

Oracle on Windows

@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

import into UTF8 database

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 […]

ssl version

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 OK (100 msec) with 12cR1 : C:> tnsping (DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=SRV01)(PORT=1521))) TNS Ping Utility for 64-bit Windows: […]

TCPS and SSLv2Hello

Thanks to platform independence, the same java code work on different platforms. import java.util.Properties; import; import java.sql.*; import*; 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(“”, “keystore.jks”); props.setProperty( “”,”JKS”); props.setProperty( “”,”***”); DriverManager.registerDriver( new oracle.jdbc.OracleDriver()); Connection […]

check if using tcps part II

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 ‘[email protected]% (P%)’ then (select max(case when NETWORK_SERVICE_BANNER like ‘%TCP/IP%’ then ‘TCP’ when […]

dotNet transaction guard

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 […]

powershell goodies for Active Directory

What are my groups? PS> Get-ADPrincipalGroupMembership lsc | select -ExpandProperty “name” Domain Users oracle sybase Who is member of that group ? PS> Get-ADGroupMember oracle| select -ExpandProperty “name” Laurent Schneider Alfred E. Newmann Scott Tiger What is my phone number ? PS> (get-aduser lsc -property MobilePhone).MobilePhone +41 792134020 This works like a charm on your […]