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

Did you forget to run root.sh?

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

last partition

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

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

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

anonymous cypher suites for SSL (and a 12c pitfall)

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

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

12.1.0.2 on AIX

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

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

xml to csv in powershell

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 <EMPTABLE>   <DEPT>     <DEPTNO>10</DEPTNO>                 <EMPLIST>       <ENAME>CLARK</ENAME>       <ENAME>MILLER</ENAME>       <ENAME>KING</ENAME>     </EMPLIST>   </DEPT>   <DEPT>     <DEPTNO>20</DEPTNO>     <EMPLIST>        <ENAME>SMITH</ENAME>        <ENAME>FORD</ENAME>        <ENAME>ADAMS</ENAME>        <ENAME>SCOTT</ENAME>        <ENAME>JONES</ENAME>     </EMPLIST>   </DEPT>   <DEPT>     <DEPTNO>30</DEPTNO>         <EMPLIST> […]

poor man ActiveDirectory password checker

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

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

select pdf from sqlplus

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 <<EOF |xxd -p -r >doc.pdf set pages 0 lin 17000 long 1000000000 longc 16384 select document from emp where ename=user; EOF Obviously, it could also be a […]

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 12.1.0.2.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 one […]

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

TCPS and SSLv2Hello

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

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 'ora___@% (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 of my frequent readers […]

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

in memory option

Oracle 12cR1 patchset 1 is due this month and there is a new parameter that you can set to boost your performance. It is a bit of a SET "_FAST"=true parameter. The in memory parameter is part of the sga. It is not mandatory to size it correctly, even if you do not have enough […]