Laurent Schneider

If you only use the Advanced Security Option for SSL, you may not need to pay for it !

License 11.2
When used with Oracle Real Application Clusters, Oracle Advanced Security SSL/TLS is included.

But also
License 11.1
Network encryption (native network encryption and SSL/TLS) and strong authentication services (Kerberos, PKI, and RADIUS) are no longer part of Oracle Advanced Security and are available in all licensed editions of all supported releases of the Oracle database.

If SSL/TLS is no longer part of Advanced Security, what is then Oracle Advanced Security SSL/TLS ?

The release V4.0 of sql developer is available for download : oracle.com/technetwork/developer-tools/sql-developer/downloads
The doc is there : docs.oracle.com/cd/E39885_01/index.htm

And read Jeff Smith twit’s and blog

I requested some time ago a 64bit Windows version with JDK on Oracle SQL Developer Exchange and once again it got accepted.

Okay, I have on my PC two Oracle homes : one for 32 bits and one for 64 bits. The 32 bits is first in PATH.

When I start SQL Developer, I could not use the OCI thick driver which is required when connecting via LDAP or TNSNAMES. SQL Developer was failing with error :
ocijdbc11.dll: Can't load IA 32-bit .dll on a AMD 64-bit platform

Apart from changing the path in the Environment variables or in .bat file, it is possible to add the following line in sqldeveloper/bin/sqldeveloper.conf (where dbhome_2 is a 64bits home):
AddVMOption -Djava.library.path=C:\oracle\product\12.1.0\dbhome_2\bin

If you need to connect to Sybase ASE (or MSSQL), download the driver here : http://sourceforge.net/projects/jtds and add it as Datasbase/Third-Party Driver

For MySQL go there : http://dev.mysql.com/downloads/connector/j/ and download the Platfrom Independant ZIP file.

When you do expand your Active Directory schema, it is not reversible; how to decide to use the OracleContext as a top object or not?

On the one hand, for tnsnames resolution, you could hide your context down in your AD structure and change the path in ldap.ora


DIRECTORY_SERVER_TYPE=AD
DEFAULT_ADMIN_CONTEXT="OU=Oracle,OU=Misc,DC=example,DC=com"


For debugging, I set TNSPING.TRACE_LEVEL=ADMIN and TNSPING.TRACE_DIRECTORY=C:\TEMP


PS> TNSPING DB01
Used LDAP adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(
PROTOCOL=TCP)(HOST=SRV01)(PORT=1521)))(CONNECT_DATA=(SID=DB01)))
OK (20 msec)

nnflrne1: Quering the directory for dn: cn=DB01,cn=OracleContext,
OU=Oracle,OU=Misc,DC=example,DC=com
nnflqbf: Search: Base: cn=DB01,cn=OracleContext,OU=Oracle,OU=Misc,
DC=example,DC=com; Scope: 0; filter: (objectclass=*) returns 0x0
nnflgne: DN : cn=DB01,cn=OracleContext,OU=Oracle,OU=Misc,
DC=example,DC=com
nsc2addr: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.2)
(PORT=1521))(CONNECT_DATA=(SID=DB01)))


So far so good; but on the other hand, it prevents you from using connection identifier like DB01.EXAMPLE.COM


PS> TNSPING DB01.EXAMPLE.COM
TNS-03505: Failed to resolve name

nnflfdn: Turning simplified name DB01.EXAMPLE.COM into a dn.
nnflfdn: The resulting dn is cn=DB01,cn=OracleContext,
dc=EXAMPLE,dc=COM
nnflrne1: Quering the directory for dn: cn=DB01,cn=OracleContext,
dc=EXAMPLE,dc=COM
nnflqbf: Search: Base: cn=DB01,cn=OracleContext,dc=EXAMPLE,
dc=COM; Scope: 0; filter: (objectclass=*) returns 0x20
nnfun2a: address for name "DB01.EXAMPLE.COM" not found


This no longer works. Your database domain name must match your Active Directory domain name and your object must be a top object domain

Platform guide for Windows : Oracle Context is the top-level Oracle entry in the Active Directory tree

It is probably wiser to follow this recommendation.

Also new in 11gR2 is NAMES.LDAP_AUTHENTICATE_BIND=TRUE, which removes the need of allowing anonymous ldap bind in AD

Whenever you change the DelayMins setting in Dataguard, you must remember it affects only logs that have not been shipped yet.


DGMGRL> show database sDB01 delaymins
DelayMins = '5'

SYSDATE SEQUENCE#
------------------- ----------
2013-12-03_15:38:00 3199


The old logs are not affected. Let’s wait until the latest Delay=5 got applied.


Tue Dec 03 15:40:02 2013
Media Recovery Log /u01/app/oracle/admin/DB01/arch/DB01_1_3199_827686279.arc
Media Recovery Log /u01/app/oracle/admin/DB01/arch/DB01_1_3200_827686279.arc
Media Recovery Log /u01/app/oracle/admin/DB01/arch/DB01_1_3201_827686279.arc
Media Recovery Log /u01/app/oracle/admin/DB01/arch/DB01_1_3202_827686279.arc
Media Recovery Log /u01/app/oracle/admin/DB01/arch/DB01_1_3203_827686279.arc
Media Recovery Log /u01/app/oracle/admin/DB01/arch/DB01_1_3204_827686279.arc
Media Recovery Log /u01/app/oracle/admin/DB01/arch/DB01_1_3205_827686279.arc


All files which had a delay=2 were “pending” apply. Now we got the apply=2 behavior

Same if you increase the value


DGMGRL> edit DATABASE sDB01 set property delaymins=30;
Property "delaymins" updated

ARC3: Archive log thread 1 sequence 3224 available in 2 minute(s)
Tue Dec 03 15:47:22 2013


Here again, the old logs are not affected, we need to wait until the last delay=2 got applied to get a delay=30 behavior.

While you cannot change the delay, there is still a way to workaround the problem.

If you want to immediately increase log to 30 minutes, turn off applying for half an hour.


DGMGRL> edit DATABASE sDB01 set state='APPLY-OFF';
Succeeded.
-- coffee break
DGMGRL> edit DATABASE sDB01 set state='APPLY-ON';
Succeeded.


If you want to decrease log from 30 to 2 minutes right now and immediately apply the old logs which have reached this threshold, use sqlplus

ARC1: Archive log thread 1 sequence 3253 available in 30 minute(s)
Tue Dec 03 16:01:26 2013
ARC3: Archive log thread 1 sequence 3254 available in 2 minute(s)
Tue Dec 03 16:01:37 2013

DGMGRL> edit DATABASE sDB01 set state='APPLY-ON';
Succeeded.


I wrote on delay standby failover here : here

Monday I wrote on tnsping.exe inconsistencies. Actually there is one good thing in having Oracle Client on Windows looking in the current directory first : you can set one tnsnames for a specific shortcut ! It is quite a viable alternative to .bat files with set TNS_ADMIN=path.

Demo :

First I create a small EXE in C#

HelloWorld.cs:

using System;
using System.Threading;
using Oracle.DataAccess.Client;

class HelloWorld
{
static void Main() {
OracleConnection connection=
new OracleConnection("Data Source=DB01; User Id=scott; password=tiger");
try {
connection.Open();
Console.WriteLine("Msg: " + (new OracleCommand(
"select * from global_name",connection)).ExecuteScalar());
connection.Close();
} catch(Exception e) {
Console.WriteLine("Exception Occured :{0}",e.Message);
} finally {
connection.Dispose();
}
Thread.Sleep(5000);
}
}


Compile

C:\Windows\Microsoft.NET\Framework\v4.0.*\csc.exe /R:C:\oracle\product\11.2.0\client_1\odp.net\bin\4\Oracle.DataAccess.dll HelloWorld.cs


Create a specific tnsnames and sqlnet

Tnsnames.ora

DB01.example.com=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=DB01)(PORT=1521)))(CONNECT_DATA=(SID=DB01)))


SQLNET.ora

NAMES.DIRECTORY_PATH=TNSNAMES
NAMES.DEFAULT_DOMAIN=EXAMPLE.COM


test

C:\TEMP> HelloWorld
Msg: DB01.EXAMPLE.COM


To create a desktop icon with the correct WorkingDirectory with powershell

PS> $ws = New-Object -comObject WScript.Shell
PS> $desktop = [Environment]::GetFolderPath("Desktop")
PS> $s = $ws.createshortcut($desktop+"\HelloWorld.lnk")
PS> $s.TargetPath = "C:\TEMP\HelloWorld.exe"
PS> $s.WorkingDirectory = "C:\TEMP"
PS> $s.Save()


if you use tnsping.exe and sqlplus.exe, the way the sqlnet.ora and tnsnames.ora are located differs

Let’s take the following setup


C:\tmp>type dir1\sqlnet.ora
NAMES.DEFAULT_DOMAIN = (EXAMPLE.COM)
NAMES.DIRECTORY_PATH = (TNSNAMES)

C:\tmp>type dir2\tnsnames.ora
db.example.com=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srv2.example.com)(PORT=1521))(CONNECT_DATA=(SID=db02)))
db.example.org=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srv2.example.org)(PORT=1521))(CONNECT_DATA=(SID=db02)))


You set TNS_ADMIN to dir1 and your current directory is dir2.

Let’s try TNSPING.EXE first

C:\tmp>cd dir2

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srv2.example.com)(PORT=1521))(CONNECT_DATA=(SID=db02)))
OK (0 msec)

TNSPING.EXE is using the sqlnet.ora in %TNS_ADMIN% directory (EXAMPLE.COM domain) and the tnsnames.ora in the current directory (db02)

Let’s try with sqlplus

C:\tmp>cd dir2

GLOBAL_NAME
-------------------------------------------------
DB02.EXAMPLE.ORG


SQLPLUS.EXE is using the sqlnet.ora in the current directory (EXAMPLE.ORG) and the tnsnames.ora in the current directory (db02)

This does not reproduce on Linux

If you redirect a not-completly-string output (like a spfile) to a file in powershell, you may not see the same in the file as in the output

• without redirection
  
PS C:\> Select-String "compatible" .\spfileDB01.ora
spfileDB01.ora:13:*.compatible='11.2.0.4.0'

• with redirection
  
PS> Select-String "compatible" .\spfileDB01.ora > compatible.txt
PS> vim -b .\compatible.txt
ÿþ^M^@
^@s^@p^@f^@i^@l^@e^@D^@B^@0^@0^@1^@.^@o^@r^@a^@:^@1^@3^@:^@*^@.^@c^@o^@m^@p^@a^@t^@i^@b^@l^@e^@=^@'^@1^@1^@.^@2^@.^@0^@.^@4^@.^@0^@'^@^M^@
^@

• With redirection and conversion to ascii
  
  PS> Select-String "compatible" .\spfileDB01.ora |
  Out-File -Encoding ASCII .\compatible.txt

  PS> vim .\compatible.txt

  spfileDB01.ora:13:*.compatible='11.2.0.4.0'


With Out-File (instead of >), you can specify the encoding, which does the trick

You can execute it a command without password from the commande line


$ sudo -l
User lsc may run the following commands on this host:
(root) NOPASSWD: /usr/local/bin/helloworld
$ sudo /usr/local/bin/helloworld
Hello World!


Now you try to run it via cron and you get

sudo: sorry, you must have a tty to run sudo


The message is clear, you need a terminal.

Either you edit your sudoers files to disable requiretty, or you just get yourself a terminal.

Maybe you tried to assign a pseudo terminal with ssh -t, but you may get an error if ssh has no local tty

Pseudo-terminal will not be allocated because stdin is not a terminal.


Don’t despair, read man ssh

Multiple -t options force tty allocation, even if ssh has no local tty.


Let’s try

* * * * * ssh -t -t sudo /usr/local/bin/helloworld >> /tmp/txt


This should work, providing you configured ssh keys 🙂

You have a multi consumer queue with a user defined type


CREATE TYPE topic_message AS OBJECT(Subject VARCHAR2(30),Text VARCHAR2(80))
/
BEGIN
dbms_aqadm.create_queue_table(
'topic_queue_table',
'topic_message',
Multiple_consumers=>TRUE);
dbms_aqadm.create_queue(
'toy_topic',
'topic_queue_table');
dbms_aqadm.start_queue(
'toy_topic');
END;
/


You create the corresponding java class with JPublisher (check the documentation of Database JPublisher User’s Guide 12c Release 1 (12.1) from which I took this example). JPublisher does need to be installed, it is already in your Oracle Home.


CLASSPATH=$ORACLE_HOME/jdbc/lib/ojdbc5.jar ### for java 5
CLASSPATH=$CLASSPATH:$ORACLE_HOME/sqlj/lib/translator.jar
CLASSPATH=$CLASSPATH:$ORACLE_HOME/sqlj/lib/runtime12.jar
CLASSPATH=$CLASSPATH:$ORACLE_HOME/jlib/jta.jar
CLASSPATH=$CLASSPATH:$ORACLE_HOME/rdbms/jlib/aqapi.jar
CLASSPATH=$CLASSPATH:$ORACLE_HOME/rdbms/jlib/jmscommon.jar
CLASSPATH=$CLASSPATH:.

java oracle.jpub.Doit -user=scott/tiger -sql=toy_topic:ToyTopic


SCOTT.TOPIC_MESSAGE
SCOTT.TOY_TOPIC
Note: /u01/app/oracle/java/ToyTopic.java uses unchecked or unsafe operations.
Note: Recompile with -Xlint:unchecked for details.


Now I can publish and receive from that queue


import java.util.Properties;
import java.sql.*;
import javax.jms.*;
import oracle.jms.*;

m = topic.receiveNoWait("ToyLand");
System.out.println("ToyFactory receive " + m.getSubject()
+ " " + m.getText());
m = topic.receiveNoWait("ToyFactory");
System.out.println("ToyFactory receive " + m.getSubject()
+ " " + m.getText());
m = topic.receiveNoWait("ToyFactory");
}
}


Try it :

$ javac Test.java
$ java Test
Message broadcasted: scooby doo lights out
Message broadcasted: dalmatian solve the puzzle
ToyParty receive scooby doo lights out
ToyParty receive dalmatian solve the puzzle
ToyFactory receive dalmatian solve the puzzle
ToyFactory receive scooby doo lights out


Back in Oracle 7, the maximum length for VARCHAR2 was 2000. In 11gR2, it is still 4000 for varchar2/char columns and for literals. Any attempt to use something larger will produce an infamous ora-910, ora-1704 or ora-1489 error.


SQL> create table t(x varchar2(5000));
create table t(x varchar2(5000))
*
ERROR at line 1:
ORA-00910: specified length too long for its datatype

select 'x'||
*
ERROR at line 1:
ORA-01489: result of string concatenation is too long


In 12c it is possible to extends the varchar2 to 32k.

This is not the default and it is controlled by max_string_size

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade quiet
ORACLE instance started.
Database mounted.
Database opened.
SQL> alter system set max_string_size=extended;
System altered.
SQL> @?/rdbms/admin/utl32k
SQL> shu immediate
Database closed.
Database dismounted.
SQL> startup quiet
ORACLE instance started.
Database mounted.
Database opened.


Now we can create, insert and select longer strings.


SQL> create table t(x varchar2(6000));
Table created.
SQL> desc t
Name Null? Type
--------------------- -------- ----------------
X VARCHAR2(6000)
SQL> insert into t values ('-
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx-
... 100 times
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
1 row created.
SQL> insert into t values (
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'||
... 100 times
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
1 row created.


Oracle 8i came with a bunch of issue (cannot index a varchar2(4000) column) because the datatype was really increased to 4000. In 12c, however, it is silently using clob.

SQL> select TABLE_NAME,COLUMN_NAME,SEGMENT_NAME from user_lobs where table_name='T';

TABLE_NAME COLUMN_NAM SEGMENT_NAME
---------- ---------- ------------------------------
T X SYS_LOB0000022083C00001$$


It also works for NVARCHAR2 and RAW (who wasn’t increased to 4000). But not for CHAR.

This is a smart move to provide larger text fields to developers. On the other hand, why limit it to 32k if it is a clob? VARCHAR(MAX) in SQL Server does not have such a limitation.

SQL> create table t(x varchar2(32768));
create table t(x varchar2(32768))
*
ERROR at line 1:
ORA-00910: specified length too long for its datatype

SQL> select 'x'||
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'||
... 999 times
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'||
'x' from dual;
select
*
ERROR at line 1:
ORA-01489: result of string concatenation is too long


First for those who are looking for the difference between Queuing, Advanced Queuing (AQ) and Streams Advanced Queuing, there is none. There is no Beginner Queuing and Advanced Queuing was renamed to Streams advanced queuing when streams was popular and renamed back to Advanced Queuing was streams was deprecated.

I am the provider, I give you messages to reads. I do not need to wait you to read the first one before I can send you the next one.

You are the receiver, you read them one by one, whenever you have time.

First a few grants

SQL> grant execute on dbms_aq to scott;
Grant succeeded.
SQL> grant execute on dbms_aqadm to scott;
Grant succeeded.
SQL> grant execute on dbms_aqin to scott;
Grant succeeded.


The queue table and queue

SQL> EXEC dbms_aqadm.create_queue_table('QT', 'SYS.AQ$_JMS_TEXT_MESSAGE')
PL/SQL procedure successfully completed.
SQL> EXEC dbms_aqadm.create_queue('Q','QT')
PL/SQL procedure successfully completed.
SQL> EXEC dbms_aqadm.start_queue('Q')
PL/SQL procedure successfully completed.


You start listening, if there is nothing to read, you wait

SQL> set serverout on
SQL> DECLARE
2 dequeue_options DBMS_AQ.DEQUEUE_OPTIONS_T;
3 message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
4 message_handle RAW (16);
5 msg SYS.AQ$_JMS_TEXT_MESSAGE;
6 BEGIN
7 DBMS_AQ.dequeue (
8 queue_name => 'Q',
9 dequeue_options => dequeue_options,
10 message_properties => message_properties,
11 payload => msg,
12 msgid => message_handle);
13 DBMS_OUTPUT.PUT_LINE(msg.TEXT_VC);
14 COMMIT;
15 END;
16 /

It is waiting

I write a first message.

SQL> DECLARE
2 enqueue_options DBMS_AQ.ENQUEUE_OPTIONS_T;
3 message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
4 message_handle RAW (16);
5 msg SYS.AQ$_JMS_TEXT_MESSAGE;
6 BEGIN
7 msg := SYS.AQ$_JMS_TEXT_MESSAGE.construct;
8 msg.set_text('HELLO PLSQL WORLD !');
9 DBMS_AQ.ENQUEUE (
10 queue_name => 'Q',
11 enqueue_options => enqueue_options,
12 message_properties => message_properties,
13 payload => msg,
14 msgid => message_handle);
15 COMMIT;
16 END;
17 /

PL/SQL procedure successfully completed.


Now you receive it !

HELLO PLSQL WORLD !
PL/SQL procedure successfully completed.
SQL>


I write one from Java using Java Message Service (JMS)

import java.util.Properties;
import java.sql.*;
import javax.jms.*;
import oracle.jms.*;

public class JMS {
public static void main(String argv[]) throws JMSException, SQLException {
String url = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=SRV01)(Port=1521))(CONNECT_DATA=(SID=DB01)))";
Properties props = new Properties();
props.setProperty("user", "SCOTT");
props.setProperty("password", "tiger");
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
java.sql.Connection conn = DriverManager.getConnection(url, props);
QueueConnection qconn = AQjmsQueueConnectionFactory.createQueueConnection(conn);
QueueSession qsess = qconn.createQueueSession(true, 0);
Queue q = qsess.createQueue("Q");
QueueSender qsend = qsess.createSender(q);
TextMessage msg;
msg = qsess.createTextMessage("TEST JAVA");
qsend = qsess.createSender(q);
qsend.send(msg);
qsess.commit();
}
}


$ CLASSPATH=$ORACLE_HOME/jdbc/lib/ojdbc5.jar
$ CLASSPATH=$CLASSPATH:$ORACLE_HOME/jlib/jta.jar
$ CLASSPATH=$CLASSPATH:$ORACLE_HOME/xdk/lib/xmlparserv2.jar
$ CLASSPATH=$CLASSPATH:$ORACLE_HOME/rdbms/jlib/xdb.jar
$ CLASSPATH=$CLASSPATH:$ORACLE_HOME/rdbms/jlib/aqapi.jar
$ CLASSPATH=$CLASSPATH:$ORACLE_HOME/rdbms/jlib/jmscommon.jar
$ CLASSPATH=$CLASSPATH:.
$ export CLASSPATH
$ javac JMS.java
$ java JMS

ojdbc5 is for java5

Now you read the next message (and you do not have to wait, there is one in the queue)

SQL> /
TEST JAVA
PL/SQL procedure successfully completed.


Read more in the Advanced Queuing User’s Guide

If you have more than one OEM and want a red status bar in your production OEM, here the proceedings

Stop your cloud control

emctl stop oms -all


Unzip the $MW/oracle_common/modules/oracle.adf.view_11.1.1/adf-richclient-impl-11.jar in a new directory

$MW/jdk16/jdk/bin/jar -xvf $MW/oracle_common/modules/oracle.adf.view_11.1.1/adf-richclient-impl-11.jar

edit the picture dbd_topShadow.png (for instance) in adf/images/fusion-11.1.1.3.0/dbd_topShadow.png and copy it to META-INF/adf/images/fusion-11.1.1.3.0/dbd_topShadow.png with your favorite picture editor

recreate the jar

$MW/jdk16/jdk/bin/jar -cvf $MW/oracle_common/modules/oracle.adf.view_11.1.1/adf-richclient-impl-11.jar *


restart your OEM

emctl start oms

and empty your browser cache

You should see red

We know how to generate an explain plan. SET AUTOT ON, the good old ambulance in Toad (which is no longer an ambulance those days), the explain plan button in SQL Developer or simply EXPLAIN PLAN


create table t1 partition by hash(object_id) partitions 16 as select * from dba_objects;
exec dbms_stats.gather_table_stats(user, 'T1')
explain plan for
select /*+ PARALLEL(x, 4) */ * from t1 x, t1 y
where x.object_name = y.object_name and x.owner != y.owner


Explain plan writes in the PLAN_TABLE and could be displayed with

SQL> select * from table(dbms_xplan.display)

3 - access("X"."OBJECT_NAME"="Y"."OBJECT_NAME")
filter("X"."OWNER"<>"Y"."OWNER")


But if you want to display progress on a currently running query, use DBMS_SQLTUNE (or Oracle Enterprise Manager SQL Monitoring):


SQL> set lin 150 longc 150 long 1000000;
SQL> select DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_plan_hash_value
=>2344570521) from dual;

SQL Plan Monitoring Details (Plan Hash Value=2344570521)
==================================================================
| Id | Operation | Rows | Cost | Rows |
| | | (Estim) | | (Actual) |
==================================================================
| -> 0 | SELECT STATEMENT | | | 271 |
| -> 1 | PX COORDINATOR | | | 271 |
| 2 | PX SEND QC (RANDOM) | 18287 | 202 | 615 |
| 3 | HASH JOIN | 18287 | 202 | 538 |
| 4 | PX RECEIVE | 19219 | 44 | 14481 |
| 5 | PX SEND HYBRID HASH | 19219 | 44 | 19219 |
| 6 | STATISTICS COLLECTOR | | | 19219 |
| 7 | PX BLOCK ITERATOR | 19219 | 44 | 19219 |
| 8 | TABLE ACCESS FULL | 19219 | 44 | 19219 |
| 9 | BUFFER SORT | | | 1316 |
| 10 | PX RECEIVE | 19219 | 158 | 14481 |
| 11 | PX SEND HYBRID HASH | 19219 | 158 | 19219 |
| 12 | PARTITION HASH ALL | 19219 | 158 | 19219 |
| 13 | TABLE ACCESS FULL | 19219 | 158 | 19219 |
==================================================================


The small -> sign shows you where it is and display some actual (not estimates) info.

If I run it again :

==================================================================
| Id | Operation | Rows | Cost | Rows |
| | | (Estim) | | (Actual) |
==================================================================
| -> 0 | SELECT STATEMENT | | | 6451 |


For the same query, we see some progress (6451 rows now).

Check you have licensed the appropriate tuning options before using DBMS_SQLTUNE

I want to avoid cleartext password on my filesystem

I encrypt my password with a secret key

echo tiger | openssl enc -aes-256-cbc -k MyKey -out $HOME/myconfig


Whenever I call a script, I pass the secret key

sqlplus scott/$(openssl enc -d -aes-256-cbc -k MyKey -in $HOME/myconfig)


Of course you still need the key {MyKey}, but unless you have both the file AND the key, it is safe

As 10g introduced MODEL, which is mostly used to impress your colleagues but seldom used in production, 12c has a new syntax, MATCH_RECOGNIZE

I gave it a first try to recognize trends in EMPs salaries over hire date.


SELECT ename, hiredate, sal, trend
FROM emp
MATCH_RECOGNIZE (
ORDER BY hiredate
MEASURES CLASSIFIER () AS TREND
ALL ROWS PER MATCH
PATTERN (FIRST * Better * Worst * Same *)
DEFINE FIRST AS ROWNUM = 1,
Better AS Better.sal > PREV (sal),
Same AS Same.sal = PREV (sal),
Worst AS Worst.sal < PREV (sal));

ENAME HIREDATE SAL TREND
---------- ---------- ---------- ------------------------------
SMITH 1980-12-17 800 FIRST
ALLEN 1981-02-20 1600 BETTER
WARD 1981-02-22 1250 WORST
JONES 1981-04-02 2975 BETTER
BLAKE 1981-05-01 2850 WORST
CLARK 1981-06-09 2450 WORST
TURNER 1981-09-08 1500 WORST
MARTIN 1981-09-28 1250 WORST
KING 1981-11-17 5000 BETTER
JAMES 1981-12-03 950 WORST
FORD 1981-12-03 3000 BETTER
MILLER 1982-01-23 1300 WORST
SCOTT 1987-04-19 3000 BETTER
ADAMS 1987-05-23 1100 WORST


I just discovered a best price for my book :

Which is obviously more money than I will ever get from my editor 🙁

For those of my readers who were using Google Reader, here are my comments

1) backup your data, read this : https://support.google.com/reader/answer/3028851
2) for the web gui, there is a one-click migration to Feedly : http://feedly.com
3) you can import the OPML (Outline Processor Markup Language) file from Google Takeout into Microsoft Outlook (right click on RSS Feeds –> import OPML).

Maybe you want to forget about RSS and switch to Google+ or Twitter or read orafaq or orana or install your own aggregator on your web server (e.g. tiny rss)

Windows-On-Windows 64-bit (WOW64) enables you to run 32bits applications in 64bits OS.

You will see there is another powershell, another registry, another ODBC tool, another Oracle client.


%SYSTEMROOT%\syswow64\WindowsPowerShell\v1.0\powershell.exe

First, we run powershell(x86)


Get-itemproperty HKLM:\SOFTWARE\ORACLE\*| Select-Object ORACLE_HOME,ORACLE_HOME_NAME

ORACLE_HOME ORACLE_HOME_NAME
----------- ----------------
C:\oracle\product\11.2.0\client_32 client32bit_11203

Only the Oracle 32bit client is displayed

cmd /c "%SYSTEMROOT%\syswow64\odbcconf.exe /a {configdsn ""Oracle in client32bit_11203"" ""DSN=helloworld32|SERVER=DB01""}"


We registered ODBC with a wow64 configurator (odbcconf) or assistant (odbcad32).

$conn = New-Object Data.Odbc.OdbcConnection
$conn.ConnectionString= "dsn=helloworld32;uid=scott;pwd=tiger;"
$conn.open()
(new-Object Data.Odbc.OdbcCommand("select 'Hello World' from dual",$conn)).ExecuteScalar()
$conn.close()


For the 64 bits version, it boils down to the same as odbc 32 bit on 32 bit os


%SYSTEMROOT%\system32\WindowsPowerShell\v1.0\powershell.exe

$conn = New-Object Data.Odbc.OdbcConnection
$conn.ConnectionString= "dsn=helloworld64;uid=scott;pwd=tiger;"
$conn.open()
(new-Object Data.Odbc.OdbcCommand("select 'Hello World' from dual",$conn)).ExecuteScalar()
$conn.close()


Do not get confused by Windows32 and WOW64.

Windows32 is the default windows system directory with a bad name from upon a time where 32 meant something very big (compared to 16bit software); and WOW64 which is a special directory to run 32bits windows application on a 64bits os.

Note: odbcconf will be removed in future release, prefer Add-OdbcDsn

Demo :

cmd /c "odbcconf.exe /a {configdsn ""Oracle in OraClient11g_home1"" ""DSN=helloworld|SERVER=DB01""}"


Create a helloworld data source connecting to your DB01 tns alias in your OraClient11g_home1 Oracle Home.

It is easy to get the Oracle Home name with

Get-itemproperty HKLM:\SOFTWARE\ORACLE\*| Select-Object ORACLE_HOME,ORACLE_HOME_NAME

ORACLE_HOME ORACLE_HOME_KEY
----------- ---------------
C:\oracle\product\11.1.0\client_1 OraClient11g_home1
C:\oracle\product\11.2.0\client_1 OraClient11g_home2


Then we create the connection (as we did in ADO or ODP) :

$conn = New-Object Data.Odbc.OdbcConnection
$conn.ConnectionString= "dsn=helloworld;uid=scott;pwd=tiger;"
$conn.open()
(new-Object Data.Odbc.OdbcCommand("select 'Hello World' from dual",$conn)).ExecuteScalar()
$conn.close()


Note: odbcconf will be removed in future release, prefer Add-OdbcDsn

If you rename a file with alter database on primary, it will not occur on standby. If you want to rename it on standby to, you need to do it manually

1. cancel the recovery (set dg_broker_start to false and restart db in mount status if you use dataguard)
2. set the standby file management to manual alter system set standby_file_management=manual
3. move the file with OS commands as you did on the primary
4. rename the file with alter database rename file ‘old’ to ‘new’
5. reenable automatic standby file management and dg broker and restart the recovery (start db in mount status if you use dataguard)

Whenever you create a table, it is better to use double quotes to avoid invalid identified.

SQL> CREATE TABLE /XXX(x number);
CREATE TABLE /XXX(x number)
*
ERROR at line 1:
ORA-00903: invalid table name

Table created.


Even in DBMS_STATS you should use double quotes


SQL> exec dbms_stats.gather_table_stats(user,'/XXX')
BEGIN dbms_stats.gather_table_stats(user,'/XXX'); END;

PL/SQL procedure successfully completed.


It is also a good practice to not use table name like “/XXX”, “FROM” or “ROWID”. But if you use dynamic SQL, be sure your code does not bug on invalid identifier.

It is pretty seldom that Oracle introduces new reserved words, as it breaks code, so if you do

CREATE TABLE MYTABLE(x number);

you can be pretty sure that neither MYTABLE nor X will be reserved in 12c or 13c…

Did you know you can make your backup at least twice faster with a single line ?

Demo :

RMAN> backup as compressed backupset database;
Starting backup at 2013-06-05_13:08:01
...
Finished backup at 2013-06-05_13:13:59


6 minutes for a compressed backup on a NAS with 24 Channels and 100Gb of raw data. Not bad. But look at this !


RMAN> configure compression algorithm 'low';
new RMAN configuration parameters are successfully stored
RMAN> backup as compressed backupset database;
Starting backup at 2013-06-05_14:06:09
...
Finished backup at 2013-06-05_14:08:29
RMAN> configure compression algorithm clear;
RMAN configuration parameters are successfully reset to default value


By configuring this magic parameter, it is now more than twice faster ! This is incredible !

Go to your cashier and grab some coins to get this amazing advanced compression option !

For my readers who are preparing the ocm 11g exam, the environment just changed (From 13th May 2013 onwards)

Instead of using OEM 10g, you will be using OEM 11g.

The upgrade exam is still using OEM 10g and DB 11gR1 (!) but I did not care installing OEM 10g and I prepared with OEM 11g.

Ref: p_exam_id:11GOCM

I wrote about not using DAY OF MONTH and DAY OF WEEK simultanously in how to cron

The correct method is to use
15 14 15 05 * /tmp/run-my-job

But… I wrote this five years ago. Hmmm ! Not that correct then since it would run every year 😉

Ok, periodically I check for jobs are scheduled to run a specific date only
$ crontab -l|awk '$1!~/#/&&$3*$4'
15 14 15 05 * /tmp/run-my-job


I have 9 more days to remove this before it runs for the fifth time 🙂

To delete large number of rows, for instance rows with date until 2010, you can issue this simple statement.


SQL> DELETE FROM T WHERE C commit;


This is perfectly fine. The table remains online, other users are not much affected (maybe they will not even notice the lower IO performance).

It will generate quite a lot of UNDO, and you will need enough space for archivelog and a large undo tablespace and a large undo retention setting (to prevent ORA-01555 snapshot too old).

If your table is like 100G big, you do it during week-end, you have 500Gb Undo and 250G free space in your archive destination, you will be fine. Well. Maybe.

There are workarounds where you create a new table then rename etc… but this is not the scope of this post and you will need to validate your index / foreign keys / online strategy with the application guys.

Another way to decrease runtime pro statement and undo requirement pro statement (but increase overall elapsed time) is to divided it chunks, for instance to delete 100’000’000 rows each night during 10 days.


SQL> DELETE FROM T WHERE C commit;


Or if you want to delete in much smaller chunks to accomodate your tiny undo tablespace, you could try


BEGIN
LOOP
DELETE FROM T WHERE C