Skip navigation.

Laurent Schneider

Syndicate content
Oracle Certified Master
Updated: 13 hours 22 min ago

How to *really* send a script to the background

Thu, 2014-11-27 10:55

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 that would call this script in the background using &

bar.sh


#!/bin/sh
echo bar.1:`date`
$HOME/tmp/foo.sh &
echo bar.2:`date`


$ $HOME/tmp/bar.sh
bar.1:Thu Nov 27 17:36:32 CET 2014
bar.2:Thu Nov 27 17:36:32 CET 2014
$ 
foo.1:Thu Nov 27 17:36:32 CET 2014
foo.2:Thu Nov 27 17:36:35 CET 2014

bar is printing the date, calling foo in the background, then printing the date, then it returns to you, and foo is still running.

BUT this is only in a relative background …

Let’s try this


$ time $HOME/tmp/bar.sh > /dev/null

real    0m0.01s
user    0m0.00s
sys     0m0.00s

So it takes no time to run bar you believe ?

Let’s try, for instance, over ssh (or cron or whatever)


$ time ssh localhost $HOME/tmp/bar.sh > /dev/null
real    0m3.81s
user    0m0.01s
sys     0m0.01s

running bar suddenly waits 3 seconds for foo to finish.

To be sure the script is sent to the farest background, you need to close the file descriptors, stdin, stdout, stderr

I rewrote it as

baz.sh


#!/bin/sh
echo bar.1:`date`
$HOME/tmp/foo.sh <&- >&- 2>&- &
echo bar.2:`date`


$ time ssh localhost $HOME/tmp/baz.sh >/dev/null
real    0m0.44s
user    0m0.00s
sys     0m0.00s

Now the script baz is immediately finished and does not wait for foo to complete

KeepAlive socket in 12c listener

Fri, 2014-11-21 06:50

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 communication channel, ORA-03135: connection lost contact, TNS-12547 Lost contact.

The good news is that Oracle 12c now implements Socket Options (see man setsockopt), as documented in Net admin new features and more nicely in Note 1591874.1
dcd visualized

I made until now a positive experience with this keepalive behavior, especially with SSL listener. The default value for SQLNET.EXPIRE_TIME is 0, so you must set it to a non-zero value first, the recommended value is 10 (minutes).

12.1.0.2 on AIX

Mon, 2014-11-17 04:22

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 Release 12.1.

— laurentsch (@laurentsch) October 9, 2014

one more stragg

Thu, 2014-11-13 04:01


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 (
  select comm, deptno, 
    dense_rank() over (order by deptno) d, 
    dense_rank() over (order by comm) c 
  from emp);   

DEPTSUM      DSUMDIST COUNT COMMLIST
------------ -------- ----- -------------------
180;100;030  30;20;10 6;5;3 1400;0500;0300;0000

xml to csv in powershell

Wed, 2014-11-12 11:59

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>
       <ENAME>ALLEN</ENAME>
       <ENAME>WARD</ENAME>
       <ENAME>MARTIN</ENAME>
       <ENAME>BLAKE</ENAME>
       <ENAME>TURNER</ENAME>
       <ENAME>JAMES</ENAME>
    </EMPLIST>
  </DEPT>
</EMPTABLE>

To get all employees, it is awfully easy
([xml](gc emp.xml)).EMPTABLE.DEPT.EMPLIST.ENAME


CLARK
MILLER
KING
SMITH
FORD
ADAMS
SCOTT
JONES
ALLEN
WARD
MARTIN
BLAKE
TURNER
JAMES

Now I actually want to have each employee together with his department. I create an object for each department add the ename and the deptno


$d=([xml](gc emp.xml)).EMPTABLE.DEPT | % { 
  foreach ($i in $_.EMPLIST.ENAME) {
    $o = New-Object Object
    Add-Member -InputObject $o -MemberType NoteProperty -Name DEPTNO -Value $_.DEPTNO
    Add-Member -InputObject $o -MemberType NoteProperty -Name ENAME -Value $i
    $o
  }
}
$d


DEPTNO     ENAME
------     -----
10         CLARK
10         MILLER
10         KING
20         SMITH
20         FORD
20         ADAMS
20         SCOTT
20         JONES
30         ALLEN
30         WARD
30         MARTIN
30         BLAKE
30         TURNER
30         JAMES

This could be convert to multiple format.

HTML
$d|ConvertTo-HTML

DEPTNO ENAME 10 CLARK 10 MILLER 10 KING 20 SMITH 20 FORD 20 ADAMS 20 SCOTT 20 JONES 30 ALLEN 30 WARD 30 MARTIN 30 BLAKE 30 TURNER 30 JAMES

CSV
$d|ConvertTo-CSV


"DEPTNO","ENAME"
"10","CLARK"
"10","MILLER"
"10","KING"
"20","SMITH"
"20","FORD"
"20","ADAMS"
"20","SCOTT"
"20","JONES"
"30","ALLEN"
"30","WARD"
"30","MARTIN"
"30","BLAKE"
"30","TURNER"
"30","JAMES"

JSON
$d|ConvertTo-JSon


[
    {
        "DEPTNO":  "10",
        "ENAME":  "CLARK"
    },
    {
        "DEPTNO":  "10",
        "ENAME":  "MILLER"
    },
    {
        "DEPTNO":  "10",
        "ENAME":  "KING"
    },
    {
        "DEPTNO":  "20",
        "ENAME":  "SMITH"
    },
    {
        "DEPTNO":  "20",
        "ENAME":  "FORD"
    },
    {
        "DEPTNO":  "20",
        "ENAME":  "ADAMS"
    },
    {
        "DEPTNO":  "20",
        "ENAME":  "SCOTT"
    },
    {
        "DEPTNO":  "20",
        "ENAME":  "JONES"
    },
    {
        "DEPTNO":  "30",
        "ENAME":  "ALLEN"
    },
    {
        "DEPTNO":  "30",
        "ENAME":  "WARD"
    },
    {
        "DEPTNO":  "30",
        "ENAME":  "MARTIN"
    },
    {
        "DEPTNO":  "30",
        "ENAME":  "BLAKE"
    },
    {
        "DEPTNO":  "30",
        "ENAME":  "TURNER"
    },
    {
        "DEPTNO":  "30",
        "ENAME":  "JAMES"
    }
]

Or even to xml with ($d|ConvertTo-XML).OuterXml

It is so lightening fast that you could process pretty large files (millions of lines) in just a few seconds

poor man ActiveDirectory password checker

Mon, 2014-11-10 07:45

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 won’t be welcome by your sysadmins / winadmins.

Okay, to partly cover the password expiration issue, you could check in a profile function that the password is the one from AD.

Firstly, without SSL


CREATE OR REPLACE FUNCTION pw_function_AD
(username varchar2,
 password varchar2,
 old_password varchar2)
RETURN boolean IS
  sess raw(32);
  rc number;
BEGIN
  sess := DBMS_LDAP.init(
    'example.com',dbms_ldap.PORT);
  rc := DBMS_LDAP.simple_bind_s(
    sess, username||'@example.com', 
    password);
  rc := DBMS_LDAP.unbind_s(sess);
  RETURN(TRUE);
EXCEPTION
  WHEN OTHERS THEN
    rc := DBMS_LDAP.unbind_s(sess);
    raise;
END;
/
GRANT EXECUTE ON pw_function_ad TO PUBLIC;
CREATE PROFILE AD LIMIT 
  PASSWORD_VERIFY_FUNCTION pw_function_AD;
ALTER PROFILE AD LIMIT 
  PASSWORD_LIFE_TIME 30;
ALTER PROFILE AD LIMIT 
  PASSWORD_REUSE_MAX UNLIMITED;

alter user lsc profile AD;

When the password expires, the user must change it to its AD Password.

If I try with a dummy password, the profile will reject this


SQL> conn lsc/pw1
ERROR:
ORA-28001: the password has expired

Changing password for lsc
New password:anypassword
Retype new password:anypassword
ERROR:
ORA-28003: password verification for 
  the specified password failed
ORA-31202: DBMS_LDAP: LDAP client/server 
  error: Invalid credentials. 
  80090308: LdapErr: DSID-0C0903A9, 
  comment: AcceptSecurityContext error, 
    data 52e, v1db1
Password unchanged
Warning: You are no longer connected to ORACLE.

I need to enter my Windows password


SQL> conn lsc/pw1
ERROR:
ORA-28001: the password has expired

Changing password for lsc
New password: mywindowspassword
Retype new password: mywindowspassword
Password changed
Connected.

Secondly, with SSL.

Maybe simple bind without SSL is not possible (check http://support.microsoft.com/kb/935834). And for sure it is better to not send unencrypted plain text password over the network.

Create a wallet with password with the ROOT Certification Authority that signed your AD. You probably could download this in your trusted root certification authorities in Internet Explorer.

Internet Explorer – Tools – Internet Options – Content – Certificates – Trusted root.

Then you create a ewallet.p12 with orapki. No need for user certificate and no need for single-sign-on. Only import the trusted root (and intermediaries if applicable).

Here is the modified code


CREATE OR REPLACE FUNCTION pw_function_AD
(username varchar2,
 password varchar2,
 old_password varchar2)
RETURN boolean IS
  sess raw(32);
  rc number;
BEGIN
  sess := DBMS_LDAP.init(
    'example.com',dbms_ldap.SSL_PORT);
  rc := DBMS_LDAP.open_ssl(
    sess, 'file:/etc/wallet/MSAD', 
    'welcome1', 2);
  rc := DBMS_LDAP.simple_bind_s(
    sess, username||'@example.com', 
    password);
  rc := DBMS_LDAP.unbind_s(sess);
  RETURN(TRUE);
EXCEPTION
  WHEN OTHERS THEN
    rc := DBMS_LDAP.unbind_s(sess);
    raise;
END;
/

If you get SSL Handshake, be prepared, it could be anything! Check your wallet, your certificate, your permission, your wallet password.

One step further could be to expire users as soon as they change their password in AD or when they expire there.

For instance with powershell goodies for active directory


PS> (Get-ADuser lsc -properties PasswordLastSet).PasswordLastSet

Montag, 6. Oktober 2014 08:18:23

PS> (Get-ADuser king -properties AccountExpirationDate).AccountExpirationDate

Mittwoch, 16. Juli 2014 06:00:00

And in the database


SQL> SELECT ptime FROM sys.user$ 
  WHERE name ='LSC';

PTIME
-------------------
2014-11-10_10:33:08

If PTIME is less than PasswordLastSet or if AccountExpirationDate is not null, expire the account.

In conclusion : if you do not want to use Kerberos, nor Oracle “OctetString” Virtual Directory ovid nor Oracle Internet directory oid, this workaround may help to increase your security by addressing the “shared” and “expired” accounts problematic

There an additional hidden benefit. You could set up a self-service password reset function and send a generated expired password per mail, that the user won’t be able to change without its AD password

rowid of the last insert

Fri, 2014-11-07 06:42

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

D
--------------------------------
2014-11-07_13:33:03

It is documented in the SQL Reference. No need for PL/SQL here. I it very usefull if you use sequences too.


SQL> insert into t(x) values (s.nextval) returning x into :x;

1 row created.

SQL> print x

         X
----------
         2

PLS-00201 in stored procedures

Mon, 2014-10-13 03:36

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 begin null; end; 
/

grant execute on u1.p1 to r;

conn u2/***

create procedure u2.p2 is begin u1.p1; end; 
/

sho err procedure u2.p2

Errors for PROCEDURE U2.P2:

L/COL ERROR
----- -------------------------------------------
1/26  PL/SQL: Statement ignored
1/26  PLS-201: identifier U1.P1 must be declared

However, If i run it in an anonymous block, it works


declare
  procedure p2 is 
  begin 
    u1.p1; 
  end; 
begin
  p2; 
end; 
/

PL/SQL procedure successfully completed.

But this only works when my role is active. If my role is no longer active, then it obviously fails.


set role none;

declare 
  procedure p2 is 
  begin 
    u1.p1; 
  end; 
begin 
  p2; 
end; 
/
ERROR at line 1:
ORA-06550: line 4, column 5:
PLS-00201: identifier 'U1.P1' must be declared
ORA-06550: line 4, column 5:
PL/SQL: Statement ignored

It is all written in the doc,

All roles are disabled in any named PL/SQL block (stored procedure, function, or trigger) that executes with definer’s rights

I knew the behavior but not the reason behind it. Thanks to Bryn for bringing me so much knowledge on plsql.

#OOW14, #DB12c, #BikeB4OOW(!) at 14:45 today, #CON8269: "Doing PL/SQL from SQL: Correctness and Performance" http://t.co/A9BKqrKygv

— Bryn Llewellyn (@BrynLite) September 29, 2014

to R1 or to R2

Thu, 2014-10-09 07:00

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 or in Memory.

#oracle 12.1.0.2 is the last patch set for Release 12.1.

— laurentsch (@laurentsch) October 9, 2014

12.1.0.2, which is not available on AIX yet, will be the last patchset of 12cR1. It is the first and only patchset for that release. It is actually more than a patchset, as it introduced in memory database and JSON in the database.

The next release is expected beginning of 2016 on Linux. 11.2.0.4 patching ends January 2018.

Should I I go to an already aborted release or should I buy extended support for 11.2.0.4 until 2018 ?

Probably I will go both ways, depending on the applications.

select pdf from sqlplus

Wed, 2014-10-08 06:48

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 sound, a video or an image !

select a.b.c.d.e from t a

Tue, 2014-09-30 07:37

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 inspired from Tom Kyte session

Oracle on Windows

Sun, 2014-09-28 05:31

@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

Thu, 2014-09-25 04:41

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 byte. But after conversion, one char is larger than one byte and does not fit any longer.

single-byte


SQL> select VALUE 
  from nls_database_parameters 
  where parameter='NLS_CHARACTERSET';
VALUE
-------------
WE8MSWIN1252
SQL> create table t(x char(1));
Table created.
SQL> insert into t values ('é');
1 row created.
SQL> commit;
Commit complete.
$ expdp scott/tiger dumpfile=t.dmp tables=t
. . exported "SCOTT"."T"            1 rows

multi-byte


SQL> select VALUE 
  from nls_database_parameters 
  where parameter='NLS_CHARACTERSET';
VALUE
-----------
UTF8
$ impdp scott/tiger dumpfile=t.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type 
  TABLE_EXPORT/TABLE/TABLE_DATA
ORA-02374: conversion error loading table 
  "SCOTT"."T"
ORA-12899: value too large for column X 
  (actual: 2, maximum: 1)
ORA-02372: data for row: X : 0X'E9'
. . imported "SCOTT"."T"            0 out of 1 rows

How do I import my data?

1) import the metadata


$ impdp scott/tiger dumpfile=t.dmp content=metadata_only
Processing object type TABLE_EXPORT/TABLE/TABLE

2) change the char_used of the column(s) from (B)yte to (C)har

SQL> select 
  column_name, char_used, data_length, data_type 
  from user_tab_columns 
  where table_name='T' and char_used='B';
COLUMN_NAME  C DATA_LENGTH DATA_TYPE
------------ - ----------- ---------
X            B           1 CHAR
SQL> alter table t modify x char(1 char);
Table altered.

3) import the data

$ impdp scott/tiger dumpfile=t.dmp content=data_only
Processing object type 
  TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."T"            1 rows

4) check

SQL> select x, length(x), lengthb(x) from t;
X  LENGTH(X) LENGTHB(X)
- ---------- ----------
é          1          2

My column has now a length of one char and two bytes.

change Oracle installer language in Windows

Wed, 2014-09-24 07:47


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

ssl version

Tue, 2014-09-16 04:59

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: Version 12.1.0.1.0
TNS-12560: TNS:protocol adapter error

in trace file I see


ntzgsvp: no SSL version specified - using default version 0
ntzdosecneg: SSL handshake failed with error 29048.
ntzCreateConnection: returning NZ error 29048 in result structure
ntzCreateConnection: failed with error 542
nserror: nsres: id=0, op=65, ns=12560, ns2=0; nt[0]=29048, nt[1]=542, nt[2]=0; ora[0]=29048, ora[1]=0, ora[2]=0

I could not see this as a documented change yet, but if you force ssl_version to be 3.0, both client versions works


C:> tnsping (DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=SRV01)(PORT=1521)))
TNS Ping Utility for 64-bit Windows: Version 12.1.0.1.0
OK (80 msec)
C:> find "version" tnsping.trc sqlnet.ora

---------- TNSPING.TRC
nlstddp_dump_ptable:   ssl_version = 3.0
ntzGetStringParameter: found value for "ssl_version" configuration parameter: "3.0"

---------- SQLNET.ORA
ssl_version=3.0

TCPS and SSLv2Hello

Mon, 2014-09-15 08:19

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 conn = 
      DriverManager.getConnection(url, props);
    ResultSet res = conn.prepareCall("select "+
       "sys_context('USERENV','NETWORK_PROTOCOL"+
       "') txt from dual").
         executeQuery();
    res.next();
    System.out.println("PROTOCOL: "+
      res.getString("TXT"));
  }
}

The code above perfectly works with Linux and Windows.

Okay, in AIX you will get IllegalArgumentException SSLv2Hello at com.ibm.jsse2.sb.a if you don’t add


props.setProperty("oracle.net.ssl_version","3.0");

The default does not work with the Oracle AIX client. Just set it to 1.0 and 3.0 and you will be a bit less plateform-dependent

check if using tcps part II

Thu, 2014-09-11 11:31

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 NETWORK_SERVICE_BANNER like '%Bequeath%' 
      then 'BEQUEATH'
when NETWORK_SERVICE_BANNER like '%IPC%' 
      then 'IPC'
when NETWORK_SERVICE_BANNER like '%SDP%' 
      then 'SDP'
when NETWORK_SERVICE_BANNER like '%NAMED P%' 
      then 'Named pipe'
when NETWORK_SERVICE_BANNER is null 
      then 'TCPS' end)
    from V$SESSION_CONNECT_INFO i 
    where i.sid=s.sid) end protocol
  from v$session s;

       SID PROGRAM         PROTOCOL
---------- --------------- --------
       415 sqlplus(TNS V1- BEQUEATH
       396 sqlplus(TNS V1- IPC     
         6 Toad            TCP     
         9 Toad            TCPS    
         1 oracle(DIAG)            
       403 Toad            TCP     

12cR2

Sun, 2014-09-07 23:49

#db12cR2 release announced for 2016, Doc ID 742060.1

— laurentsch (@laurentsch) September 5, 2014

Configure your Oracle Net Client for AD LDAP Signing

Mon, 2014-08-25 12:14

If you don’t know about the “Require Signing” option, read this http://support.microsoft.com/kb/935834

LDAP is a cool alternative to tnsnames.ora. If your MSAD is highly available and you have friends there, you could go down that route : tnsnames and active directory

Amoung others, the AD “Require Signing” option prevents the ldap clients from sending clear text passwords to the Microsft Active Directory Server.

Since 11gR2, NAMES.LDAP_AUTHENTICATE_BIND=TRUE, enable authenticated bind. If you turn on “Require Signing” on the ActiveDirectory, you will need to use SSL. Unsigned search queries (tnsping/sqlplus/…) will be rejected.

Therefore the default port 389 won’t do the trick any more.


C:\> nslookup

> set type=all
> _ldap._tcp.dc._msdcs.example.com

_ldap._tcp.dc._msdcs.example.com     SRV service location:
          priority       = 0
          weight         = 100
          port           = 389
          svr hostname   = msad01.example.com

You must hardcode the SSL port in your ldap.ora


DIRECTORY_SERVERS=example.com:389:636
DIRECTORY_SERVER_TYPE = AD
DEFAULT_ADMIN_CONTEXT = "OU=Oracle,OU=Test,DC=example,DC=com"

And that’s not it! You need to have a wallet
So in your sqlnet.ora


WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=C:\oracle\WALLET)))
NAMES.DIRECTORY_PATH= (LDAP)
NAMES.LDAP_AUTHENTICATE_BIND=TRUE

And in your wallet, you need the root certificates of your Active Directory server. If you do not where to download them, try
1) Internet Explorer – Tools – Internet Options – Contents – Certificates
2) Double click on your Trusted certicate “Example.com Root CA”
3) Details – Copy to file – Base64 – Save

You may need intermediate “issuer” authorities too. Check with your AD Admin if you are in doubt.

Now create your wallet


mkdir c:\oracle\wallet
orapki wallet create -wallet c:\oracle\WALLET -auto_login -pwd welcome1
orapki wallet add -wallet c:\oracle\WALLET -pwd welcome1 -trusted_cert -cert c:\oracle\WALLET\ExampleComIssuingCA.cer
orapki wallet add -wallet c:\oracle\WALLET -pwd welcome1 -trusted_cert -cert c:\oracle\WALLET\ExampleComRootCA.cer
orapki wallet display -wallet c:\download\WALLET 

That’s all you need


C:\> tnsping DB01
OK (10 msec)

The best tools to debug are
1) tnsping. Increase the trace level in sqlnet.ora


TNSPING.TRACE_LEVEL = ADMIN
TNSPING.TRACE_DIRECTORY = C:\temp
DIAG_ADR_ENABLED=off

Without SSL and Require Signing, tnsping was using the SNNFL cache (DNS?):


Adding parameter DIRECTORY_SERVERS=MSAD01.example.com discovered from SNNFL into cache

With SSL and DIRECTORY_SERVERS, you should get


Inserted value DIRECTORY_SERVSSL=example.com:636 at index 0 into NLPA_CACHE
Inserted value DIRECTORY_SERVERS=example.com:389 at index 0 into NLPA_CACHE

2) ldapsearch
the one in %ORACLE_HOME%\bin


ldapsearch -d 2147483647 -b "OU=Oracle,OU=Test,DC=example,DC=com" -h example.com -p 636 -D "CN=Laurent Schneider,OU=Users,DC=example,DC=com" -w MyWindowsPW -W file:c:/oracle/WALLET -P welcome1 -s sub "(&(objectclass=*)(cn=DB01))" orclNetDescString 

3) ldp
A Microsoft LDAP gui

Once you get it, you will feel a lot more secure :)

DBMS_METADATA.GET_DDL in sqlplus

Wed, 2014-08-20 10:10

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 tabulator (^T)

set tab off  

no feedback (n rows returned)

set feedback off  

no echo

set echo off

Per default you get no terminator


exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true)

When running the spooled code, allow blank lines


set sqlblanklines on

Get rid of &


set define off

Get rid of a leading #


set sqlprefix off

Get rid of . on a single line


set blockterminator OFF

To get rid of a trailing -, you may use set lin bigger than set longc and trimsp off, but I could not make it bug yet


CREATE TABLE T(x number default -
1);

would give 1 instead of -1 when run in sqlplus. But METADATA translates it to

  CREATE TABLE "SCOTT"."T"
   (    "X" NUMBER DEFAULT -
1
   )

So just forget about trailing dash for now.

Now try


SQL> set SQLBL ON SQLPRE OFF DEF OFF BLO OFF
SQL> create view v as select '
  2
  3  .
  4  #?
  5  &_date
  6  ' x from dual;

View created.
SQL> set long 1000000 longc 32000 lin 32000 trims on hea off pages 0 newp none emb on tab off feed off echo off
SQL> exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true)
SQL> select dbms_metadata.get_ddl('VIEW','V') from dual;

  CREATE OR REPLACE FORCE VIEW "SCOTT"."V" ("X") AS
  select '

.
#?
&_date
' x from dual;

Now you are safer to use dynamic sql in sqlplus. But hardly ever 100% safe.

Plenty of useful transformation parameters there to get rid of storage, tablespace and others.