Author Archives

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

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

SPARC Supercluster

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

connect by and recursive with (part 2)

According to the doc
The subquery_factoring_clause now supports recursive subquery
factoring (recursive WITH), which lets you query hierarchical data.
This feature is more powerful than CONNECT BY in that it
provides depth-first search and breadth-first search, and supports
multiple recursive branches. A new search_clause and cycle_clause
let you specify an ordering for the rows and mark cycles in the
recursion
As written there […]