strings larger than 4000 in 12c

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

SQL> select 'x-
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx-
... 100 times 
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx-
' from dual;

select 'x-
       *
ERROR at line 1:
ORA-01704: string literal too long

SQL> select 'x'||
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'||
... 100 times 
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'||
'x' from dual;

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

Advanced Queuing hello world

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

Change background of Oracle Entreprise Manager

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

emred

Monitoring details on your explain plan

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)

PLAN_TABLE_OUTPUT
--------------------------------
Plan hash value: 2344570521

---------------------------------------------------------------
| Id  | Operation                  | Name     | Rows  | Bytes |
---------------------------------------------------------------
|   0 | SELECT STATEMENT           |          | 18287 |  3500K|
|   1 |  PX COORDINATOR            |          |       |       |
|   2 |   PX SEND QC (RANDOM)      | :TQ10002 | 18287 |  3500K|
|*  3 |    HASH JOIN               |          | 18287 |  3500K|
|   4 |     PX RECEIVE             |          | 19219 |  1839K|
|   5 |      PX SEND HYBRID HASH   | :TQ10001 | 19219 |  1839K|
|   6 |       STATISTICS COLLECTOR |          |       |       |
|   7 |        PX BLOCK ITERATOR   |          | 19219 |  1839K|
|   8 |         TABLE ACCESS FULL  | T1       | 19219 |  1839K|
|   9 |     BUFFER SORT            |          |       |       |
|  10 |      PX RECEIVE            |          | 19219 |  1839K|
|  11 |       PX SEND HYBRID HASH  | :TQ10000 | 19219 |  1839K|
|  12 |        PARTITION HASH ALL  |          | 19219 |  1839K|
|  13 |         TABLE ACCESS FULL  | T1       | 19219 |  1839K|
---------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   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;

DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_PLAN_HASH_VALUE=>2344570521) 
----------------------------------------------------------------
SQL Monitoring Report

SQL Text
------------------------------
select /*+ PARALLEL(x, 4) */ * from t1 x, t1 y where 
x.object_name = y.object_name and x.owner != y.owner

Global Information
------------------------------
 Status              :  EXECUTING
 Instance ID         :  1
 Session             :  SCOTT (25:10369)
 SQL ID              :  0dpj0fxm2gf81
 SQL Execution ID    :  16777216
 Execution Started   :  08/12/2013 14:48:26
 First Refresh Time  :  08/12/2013 14:48:26
 Last Refresh Time   :  08/12/2013 14:48:59
 Duration            :  34s
 Module/Action       :  SQL*Plus/-
 Service             :  SYS$USERS
 Program             :  sqlplus.exe
 Fetch Calls         :  19

DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_PLAN_HASH_VALUE=>2344570521)
----------------------------------------------------------------

Global Stats
=================================================
| Elapsed |   Cpu   |  Other   | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls |  Gets  |
=================================================
|    0.25 |    0.13 |     0.12 |    19 |    752 |
=================================================

Parallel Execution Details (DOP=4 , Servers Allocated=8)
========================================================
|      Name      | Type  | Server# | Elapsed |   Cpu   |
|                |       |         | Time(s) | Time(s) |
========================================================
| PX Coordinator | QC    |         |    0.08 |    0.03 |
| p000           | Set 1 |       1 |    0.03 |    0.02 |
| p001           | Set 1 |       2 |    0.03 |    0.02 |
| p002           | Set 1 |       3 |         |         |
| p003           | Set 1 |       4 |    0.03 |    0.02 |
| p004           | Set 2 |       1 |    0.02 |    0.01 |
| p005           | Set 2 |       2 |    0.03 |    0.02 |
| p006           | Set 2 |       3 |    0.03 |    0.00 |
| p007           | Set 2 |       4 |    0.02 |    0.00 |
========================================================

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

encrypt with openssl

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

One more obscure syntax

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                         

Google Reader end of life

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)

ODBC 32bits for Windows 64bits

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

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

ORACLE_HOME                             ORACLE_HOME_NAME
-----------                             ----------------
C:\oracle\product\11.2.0\client_64      client64bit_11203

cmd /c "%SYSTEMROOT%\system32\odbcconf.exe /a {configdsn ""Oracle in client64bit_11203"" ""DSN=helloworld64|SERVER=DB01""}"

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

Oracle ODBC hello world with powershell

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()

how to rename datafiles on standby

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)

Best practice : use double quotes, even in DBMS_STATS

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

SQL> CREATE TABLE "/XXX"(x number);

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;

*
ERROR at line 1:
ORA-20001: /XXX is an invalid identifier
ORA-06512: at "SYS.DBMS_STATS", line 23829
ORA-06512: at "SYS.DBMS_STATS", line 23880
ORA-06512: at line 1

SQL> exec dbms_stats.gather_table_stats(user,'"/XXX"')

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…

Rman backup compression

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 !

use cron to schedule a job only once

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 :)

Delete one billion row

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<DATE '2011-01-01';
1'000'000'000 rows deleted 
Elapsed: 23:45:22.01
SQL> 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<DATE '2011-01-01' AND ROWNUM<=100000000;
100'000'000 rows deleted 
Elapsed: 04:11:15.31
SQL> 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<DATE '2011-01-01' AND ROWNUM <= 1000;
    EXIT WHEN SQL%ROWCOUNT = 0;
    COMMIT;
   END LOOP;
END;
/

This will run longer than a single transaction, but it is quite usefull if your undo tablespace is too small. Also if you abort it (CTRL-C or kill session), you will not lose all progresses (but you lose on integrity/atomicity) and your KILL SESSION will not last for ever. With a single transaction, your session may be marked as killed for hours/days…

When v$session_longops is not long enough

With large table scans, sometimes the estimated total work is far beyond reality


SQL> select message from v$session_longops where target='SCOTT.EMP';

MESSAGE
------------------------------------------------------------
Table Scan:  SCOTT.EMP: 7377612 out of 629683 Blocks done

The total work is the Oracle estimation :


SQL> select blocks from dba_tables where table_name='EMP';

    BLOCKS
----------
    629683

This may differ quite a lot from the segment size, for instance if the table is not very often analyzed :


SQL> select blocks, sysdate, last_analyzed from dba_tables where table_name='EMP';

    BLOCKS SYSDATE             LAST_ANALYZED
---------- ------------------- -------------------
    629683 2013-04-21_09:21:47 2007-10-13_21:40:58

SQL> select blocks from dba_segments where segment_name='EMP';

    BLOCKS
----------
   7749888

I have customized my very long ops query to deal with very long waits.


col target for a20
set lin 150 pages 40000 termout off
alter session set nls_currency='%';
col PCT_DONE for 990.00L jus r
col time_remaining for 999999

select
  lo.target,lo.sofar,seg.blocks,
  lo.ELAPSED_SECONDS*seg.blocks/lo.sofar-lo.ELAPSED_SECONDS TIME_REMAINING,
  100*lo.sofar/seg.blocks PCT_DONE
from
  dba_segments seg,
  v$session_longops lo
where
  lo.units='Blocks'
  and lo.totalwork>0 and (lo.time_remaining>0 or lo.time_remaining is null)
  and regexp_substr(lo.target,'[^.]+') = seg.owner
  and regexp_substr(lo.target,'[^.]+$') = seg.segment_name
/

Generate Microsoft Office Documents from command line

In previous posts (e.g. Export to Excel) I wrote about using HTML format to export to Excel.

Let’s do it for real, let’s dive into the .xls file format and learn how to generate dynamic excel from Unix!

1) create one time your excel file manually. With graphs, colors, sounds, up to you. Or Word, Powerpoint or whatever (minimum MS Office 2007)
excel-screenshot

2) save as excel 2007 or later format (.xlsx)
this is called the Office Open XML format. It is neither OpenOffice nor OpenSource. It is XML and license restriction may apply.

3) transfer the excel file to your favorite platform

4) unzip the excel file (yes, you read it correctly, unzip the .xlsx file)


$ unzip /tmp/DynamicExcel.xlsx
Archive:  /tmp/DynamicExcel.xlsx
  inflating: [Content_Types].xml
  inflating: _rels/.rels
  inflating: xl/_rels/workbook.xml.rels
  inflating: xl/workbook.xml
  inflating: xl/styles.xml
  inflating: xl/worksheets/sheet2.xml
  inflating: xl/worksheets/_rels/sheet1.xml.rels
  inflating: xl/worksheets/_rels/sheet2.xml.rels
  inflating: xl/drawings/_rels/drawing1.xml.rels
  inflating: xl/theme/theme1.xml
  inflating: xl/worksheets/sheet1.xml
  inflating: xl/drawings/drawing2.xml
  inflating: xl/charts/chart1.xml
  inflating: xl/drawings/drawing1.xml
  inflating: xl/sharedStrings.xml
  inflating: docProps/core.xml
  inflating: docProps/app.xml

5) now substitute the data with some script output (for instance select * from v$backup_redologs). Here I am substituing all datas from row r=2


cd xl/worksheets

tr -d '\r' < sheet1.xml | sed 's,<row r="2".*,,' > head
sqlplus -s -L / as sysdba <<'EOF' > body
set feed off pages 0 lin 2000 longc 2000 long 2000
SELECT XMLELEMENT (
          "row",
          xmlattributes ((rownum+1) AS "r",
                         '1:2' AS "spans",
                         '0.2' AS "x14ac:dyDescent"),
          XMLELEMENT ("c",
                      xmlattributes ('A' || (rownum+1) AS "r", '1' AS "s"),
                      XMLELEMENT ("v", d-date '1899-12-30')),
          XMLELEMENT ("c",
                      xmlattributes ('B' || (rownum+1) AS "r", '2' AS "s"),
                      XMLELEMENT ("v", c)))
          x
  FROM (  SELECT TRUNC (next_time, 'DD') d, COUNT (*) c
            FROM v$backup_redolog
           WHERE next_time BETWEEN TRUNC (SYSDATE - 90)
                               AND TRUNC (SYSDATE) - 1 / 86400
        GROUP BY TRUNC (next_time, 'DD')
        ORDER BY 1);
EOF
tr -d '\r' < sheet1.xml | sed -n 's,.*</sheetData>,</sheetData>,p' > tail
cat head body tail | tr -d '\n' > sheet1.xml
rm head body tail

6) recreate zip file


$ cd ../..
$ zip -r /tmp/DynamicExcel2.xlsx *
  adding: [Content_Types].xml (deflated 78%)
  adding: docProps/ (stored 0%)
  adding: docProps/core.xml (deflated 51%)
  adding: docProps/app.xml (deflated 53%)
  adding: _rels/ (stored 0%)
  adding: _rels/.rels (deflated 60%)
  adding: xl/ (stored 0%)
  adding: xl/_rels/ (stored 0%)
  adding: xl/_rels/workbook.xml.rels (deflated 71%)
  adding: xl/workbook.xml (deflated 42%)
  adding: xl/styles.xml (deflated 56%)
  adding: xl/worksheets/ (stored 0%)
  adding: xl/worksheets/sheet2.xml (deflated 45%)
  adding: xl/worksheets/_rels/ (stored 0%)
  adding: xl/worksheets/_rels/sheet1.xml.rels (deflated 39%)
  adding: xl/worksheets/_rels/sheet2.xml.rels (deflated 39%)
  adding: xl/worksheets/sheet1.xml (deflated 81%)
  adding: xl/drawings/ (stored 0%)
  adding: xl/drawings/_rels/ (stored 0%)
  adding: xl/drawings/_rels/drawing1.xml.rels (deflated 39%)
  adding: xl/drawings/drawing2.xml (deflated 58%)
  adding: xl/drawings/drawing1.xml (deflated 61%)
  adding: xl/theme/ (stored 0%)
  adding: xl/theme/theme1.xml (deflated 79%)
  adding: xl/charts/ (stored 0%)
  adding: xl/charts/chart1.xml (deflated 85%)
  adding: xl/sharedStrings.xml (deflated 22%)

7) Check it

DynamicExcel2.xlsx

Default Oracle Home in Windows

In Oracle Universal Installer and OPatch User’s Guide it is documented that The first Oracle home is named the “DEFAULT_HOME” and registers itself in the Windows NT Registry.

Remember, NT means New Technology :)

There is apparently a Home Selector that is a part of the installation software, maybe something like D:\oracle\product\11.2.0\client_1\bin\selecthome.bat. Sometimes. Not sure

But there is no DEFAULT HOME in the registry.

PS C:\> gci HKLM:\SOFTWARE\ORACLE

    Hive: HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE

Name                           Property
----                           --------
KEY_agent12c1                  ORACLE_HOME       : D:/oracle\core\12.1.0.1.0
                               ORACLE_HOME_NAME  : agent12c1
                               ORACLE_GROUP_NAME : Oracle - agent12c1
                               NLS_LANG          : AMERICAN_AMERICA.WE8MSWIN1252
KEY_oracle_sysman_db_12_1_0_2_ ORACLE_HOME       : D:\oracle\plugins\oracle.sysman.db.discovery.plugin_12.1.0.2.0
0_discovery_Home0              ORACLE_HOME_NAME  : oracle_sysman_db_12_1_0_2_0_discovery_Home0
                               ORACLE_GROUP_NAME : Oracle - oracle_sysman_db_12_1_0_2_0_discovery_Home0
KEY_oracle_sysman_emas_12_1_0_ ORACLE_HOME       : D:\oracle\plugins\oracle.sysman.emas.discovery.plugin_12.1.0.2.0
2_0_discovery_Home0            ORACLE_HOME_NAME  : oracle_sysman_emas_12_1_0_2_0_discovery_Home0
                               ORACLE_GROUP_NAME : Oracle - oracle_sysman_emas_12_1_0_2_0_discovery_Home0
KEY_oracle_sysman_oh_12_1_0_1_ ORACLE_HOME       : D:\oracle\plugins\oracle.sysman.oh.agent.plugin_12.1.0.1.0
0_agent_Home0                  ORACLE_HOME_NAME  : oracle_sysman_oh_12_1_0_1_0_agent_Home0
                               ORACLE_GROUP_NAME : Oracle - oracle_sysman_oh_12_1_0_1_0_agent_Home0
KEY_oracle_sysman_oh_12_1_0_1_ ORACLE_HOME       : D:\oracle\plugins\oracle.sysman.oh.discovery.plugin_12.1.0.1.0
0_discovery_Home0              ORACLE_HOME_NAME  : oracle_sysman_oh_12_1_0_1_0_discovery_Home0
                               ORACLE_GROUP_NAME : Oracle - oracle_sysman_oh_12_1_0_1_0_discovery_Home0
KEY_OraClient11g_home1         ORACLE_HOME        : D:\oracle\product\11.2.0\client_1
                               ORACLE_HOME_NAME   : OraClient11g_home1
                               ORACLE_GROUP_NAME  : Oracle - OraClient11g_home1
                               ORACLE_BUNDLE_NAME : Enterprise
                               NLS_LANG           : AMERICAN_AMERICA.WE8MSWIN1252
                               OLEDB              : D:\oracle\product\11.2.0\client_1\oledb\mesg
                               ORACLE_HOME_KEY    : SOFTWARE\ORACLE\KEY_OraClient11g_home1
                               MSHELP_TOOLS       : D:\oracle\product\11.2.0\client_1\MSHELP
                               SQLPATH            : D:\oracle\product\11.2.0\client_1\dbs
KEY_OraGtw11g_home1            ORACLE_HOME        : D:\oracle\product\11.2.0\tg_1
                               ORACLE_HOME_NAME   : OraGtw11g_home1
                               ORACLE_GROUP_NAME  : Oracle - OraGtw11g_home1
                               NLS_LANG           : AMERICAN_AMERICA.WE8MSWIN1252
                               ORACLE_BUNDLE_NAME : Enterprise
                               MSHELP_TOOLS       : D:\oracle\product\11.2.0\tg_1\MSHELP
                               SQLPATH            : D:\oracle\product\11.2.0\tg_1\dbs
                               ORACLE_HOME_KEY    : SOFTWARE\ORACLE\KEY_OraGtw11g_home1
KEY_sbin12c1                   ORACLE_HOME       : D:\oracle\sbin
                               ORACLE_HOME_NAME  : sbin12c1
                               ORACLE_GROUP_NAME : Oracle - sbin12c1
ODP.NET
remexecservicectr              remaining_time : 120000
SYSMAN

How do I set my Oracle Home?

Actually if you enter a command like “lsnrctl start”, the OS will search in the PATH for lsnrctl and determines the Oracle Home name accordingly.

Therefore, the only thing you must do to change your default Oracle Home is to set the PATH environment variable. Only then your LSNRCTL START will find the right binary and right parameter file to start your listener.

BUILD DEFERRED takes ages

When building a materialized view, you may want to postpone the loading to a later phase, for instance you install a new release, and the refresh happends every night.

BUILD DEFERRED allow you to build an empty materialized view and refresh it later. But this may still takes ages.


SQL> create materialized view mv1 build deferred as select count(*) c from emp,emp,emp,emp,emp,emp,emp;

Materialized view created.

Elapsed: 00:00:17.28
SQL> select * from mv1;

no rows selected

No data collected, but still a long time (17sec here, but much worst in real life)

A workaround is to use ON PREBUILT TABLE on an empty table, just add a few WHERE 1=0 in your subqueries


SQL> create table mv1 as select * from (select count(*) c from emp,emp,emp,emp,emp,emp,emp where 1=0) where 1=0;

Table created.

Elapsed: 00:00:00.04
SQL> create materialized view mv1 on prebuilt table as select count(*) c from emp,emp,emp,emp,emp,emp,emp;

Materialized view created.

Elapsed: 00:00:00.15
SQL> select * from mv1;

no rows selected

Elapsed: 00:00:00.00

Much faster !

Rman and DBGSQL message

I have not seen DBGSQL very often. But today again, a duplicate in RMAN was failing with, amoung other errors, sqlcode 911


RMAN> duplicate target database to DB02
  until time "to_date('2013-01-29_00:00:00','YYYY-MM-DD_HH24:MI:SS')"
  nofilenamecheck ;

DBGSQL:     TARGET> select 2013-01-29_00:00:00 from sys.dual
DBGSQL:        sqlcode = 911
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 02/01/2013 17:58:23
RMAN-05501: aborting duplication of target database
ORA-01861: literal does not match format string

I have not found anything useful except bug Bug 9351175 which is fixed in 11.2 (and I have 11.2.0.2/aix), but I could workaround the problem with


unset NLS_DATE_FORMAT

Because I do like to have readable timestamp (with seconds) in my RMAN logs, I set NLS_DATE_FORMAT in my spfile


alter system set nls_date_format='YYYY-MM-DD_HH24:MI:SS';

Book review : EM12c

OCM Oracle ACE Porus Homi Havewala wrote a new book on Cloud Control

Oracle Enterprise Manager Cloud Control 12c: Managing Data Center Chaos

It is a book about Enterprise Manager, but also a book about the Chaos.

There is no separation of roles [...] there is no DBA Designer (the Senior DBA) [...] and no DBA Operator (the Junior DBA). The Senior [...] does not even have the time [...] the Junior then decides to try out different things

It is also a book about em. And about 12c. No mention of 12cR2, which is massively more performant.

I have been using 12c for about one year and multiple topics are covered. There is not much said on the Incident Manager, which seems to be powerfull but it is for me more time consuming than acknowledging the alerts in 10g/11g.

There is a huge advantage of using 12c, to deploy new agents and new targets in very large shops.

I have setup the software library in offline mode (download with pc from metalink and load in em with emcli), the offline fashion is not documented. In all the places where I have been working, an internet communication between outside world (oracle.com) and database server is banned.

Lot’s of screenshots on Exadata. Not sure where the author could try it, the domain were grayed out (why not edit to example.com with Photoshop? )

There is a lot of content (almost 400 pages), to mention only a few : Real Time ADDM, Real Application Testing, Database deployment, data masking.

At the end of book you will enjoy the case studies. Reading thru the lines, it is not always success stories, but rather expectations that em will do the job

The bank also wanted to ensure that their data center was in compliance with security best practices.

As in any IT book, you do not have to read it from page 1 to page 379, myself I have no DB Machine so I could skip the largest chapter which is on Exadata. But it is a pleasant lecture.

The book is very friendly with Oracle. I have experienced awful performance in 12cR1 and Internet Explorer, some annoying and unavoidable java exceptions in the web interface and the dumbest requirement for me was to install cygwin, bash, ssh on one windows server only to deploy the agent!

I recommend the book, it is a good book

How big was my database last month

If you backup your database at least weekly, you could check the datafile size from your backup history.

Without catalog :


select to_char(d,'"W"IW-IYYY') compl, sum(b)/1024/1024/1024*8192 GB 
from 
(
  select max(DATAFILE_BLOCKS) b, trunc(completion_time,'IW') d 
  from v$backup_datafile 
  group by FILE# ,trunc(completion_time,'IW')
) 
group by d 
order by d;


COMPL        GB
-------- ------
W30-2012   3.73
W31-2012   4.84
W32-2012   5.00
W33-2012   5.05
W34-2012   5.35
W35-2012   5.80
W36-2012   6.12
W37-2012   6.39
W38-2012    .93
W39-2012   7.02
W40-2012   7.56
W41-2012   7.72
W42-2012   7.88
W43-2012   8.08
W44-2012   8.83
W45-2012   9.03
W46-2012   9.45
W47-2012   9.61
W48-2012  10.11
W49-2012  10.29
W50-2012  10.38

The history mostly depends on control_file_record_keep_time. If you do not use an rman catalog, set it to a high value like 93 (3M) or 366 (1Y)

With the rman catalog, use the RC_ view


select DB_NAME,to_char(d,'"W"IW-IYYY') compl, sum(b)/1024/1024/1024*8192 GB 
from 
(
  select DB_NAME,max(DATAFILE_BLOCKS) b, trunc(completion_time,'IW') d 
  from rc_backup_datafile 
  group by DB_NAME,FILE# ,trunc(completion_time,'IW')
) 
group by DB_NAME,d 
order by DB_NAME,d;


DB_NAME  COMPL        GB
-------- -------- ------
DB01     W30-2012   3.73
DB01     W31-2012   4.83
DB01     W32-2012   5.00
DB01     W33-2012   5.05
DB01     W34-2012   5.34
DB01     W35-2012   5.79
DB01     W36-2012   6.11
DB01     W37-2012   6.39
DB01     W38-2012    .93
DB01     W39-2012   7.01
DB01     W40-2012   7.56
DB01     W41-2012   7.71
DB01     W42-2012   7.87
DB01     W43-2012   8.08
DB01     W44-2012   8.82
DB01     W45-2012   9.02
DB01     W46-2012   9.44
DB01     W47-2012   9.60
DB01     W48-2012  10.10
DB01     W49-2012  10.28
DB01     W50-2012  10.37

If you need to check which table grows the most, check How big was my table yesterday. But remember, RMAN backup is free to use, AWR and the WRI$ tables require the diagnostic pack and the Enterprise edition

accent insensitive regexp

Ever wanted to find an accent insentive expression like “bébé” in a column ?

Maybe you tried to list all possible accents. But Posix has the class for you, the list of éèëê could be refered as [=e=]


SELECT * FROM TAB WHERE REGEXP_LIKE(C,'b[[=e=]]b[[=e=]]')

not only [=e=] is easier to read and to type, but also it is more portable if you copy your scripts from DOS to UNIX and use different character sets

return code and sqlplus

Calling a shell script from within sqlplus is buggy…

I have reported bug 3798918 in 10.1.0.2 (back in 2004) and bug 13349119 in 11.2.0.3 because some metalink guru closed 3798918 as not reproducible.

As written in return code, host does not return the correct code


SQL> host exit 7
SQL> def _RC
DEFINE _RC             = "0" (CHAR)

If you never use _RC, you may believe you are safe. But watch this :


SQL> get foo.sh list
  1  #!/bin/sh
  2  if /bin/false
  3  then
  4    echo this is wrong
  5* fi
SQL> host ./foo.sh
this is wrong

The return code not being set is not only affecting the _RC variable, but it is also affecting all subshells !

Note this is not reproducable with SQLPLUS /NOLOG


SQL> host false
SQL> def _rc
DEFINE _RC             = "1" (CHAR)
SQL> conn x/x
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> host false
SQL> def _rc
DEFINE _RC             = "0" (CHAR)

After my (failed or successfull) tentative to connect as x/x, it is reproducible again

How big was my table yesterday

Oracle saves a lot of information on your behalf. Whenever you get yourself an AWR reported, you access some historic tables (included in the Diagnostic Pack).

Those tables could also be accessed manually.


SELECT savtime,owner,object_name,rowcnt,blkcnt
FROM sys.WRI$_OPTSTAT_TAB_HISTORY w,
  dba_objects o
WHERE 
   o.owner='SCOTT'
   AND o.object_name='EMP'
   and o.object_id = W.OBJ#
ORDER BY o.owner, o.object_name, w.savtime;


SAVTIME           OWNER    OBJECT_NAME     ROWCNT     BLKCNT
----------------- -------- ----------- ---------- ----------
2012-11-06 06:49  SCOTT    EMP           13215425     120077
2012-11-13 07:28  SCOTT    EMP           12678535     120077
2012-11-20 03:15  SCOTT    EMP           12860640     120077
2012-11-27 03:19  SCOTT    EMP           13045850     120077
2012-12-04 05:41  SCOTT    EMP           13326460     120077

To increase the retention, use DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings

tnsping and instant client

Mostly when you install your instant client, you will not have tnsping handy. You could well try to copy it from a full client, but this is cumbersome to just ping your instance.

I just created one function in my .profile


whence tnsping >/dev/null 2>&1 || 
  tnsping() { 
    sqlplus -L -s x/x@$1 </dev/null | 
      grep ORA- | 
        (grep -v ORA-01017 || echo OK)
  }

and tested it


$ tnsping db999
ORA-12154: TNS:could not resolve the connect identifier specified
$ tnsping db01
OK
$ tnsping db02
ORA-12541: TNS:no listener

Enhancement Request : SSL listener and OEM

#em12c still does not support SSL ! Encrypting network connection (https, ssh, sftp) is common sense in today’s business.

In Enhancement Request 6512390, Created 19-Oct-2007, the customer requested support for SSL.

Most recent update : it is postponed to 13cR2 at least !

*** 09/14/12 04:04 am DISCUSSION ***As we kick off 13c release, cleaning up the ERs. Mass updating all 13%GC% ERs to fixby 13.2GC DEFER. If you want to implement ER in 13.1GC, please update the fixby to = 13.1GC, and update all the other fields as per guidelines published.

Considering the cost of oracle advanced security option (required to get ssl), the lack of ability to influence future product enhancement is disappointing

Create database link and logon trigger

Today I could not understand why I was getting ORA-1031 with create database link.

After analysis, I found out a logon trigger that changed the current schema. As Tom always says, triggers are evil…

DEMO:


SQL> create user u1 identified by xxx;

User created.

SQL> grant create session, create database link to u2 identified by xxx;

Grant succeeded.

SQL> create trigger evil after logon on database  begin 
  2  execute immediate 'alter session set current_schema=u1';end;
  3  /

Trigger created.

SQL>
SQL> conn u2/xxx
Connected.
SQL> create database link l;
create database link l
                     *
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> show user
USER is "U2"
SQL> select sys_context('USERENV','CURRENT_SCHEMA') from dual;

SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
-----------------------------------------
U1

SQL> alter session set current_schema=u2;

Session altered.

SQL> create database link l;

Database link created.

Drop database link in another schema

Today I wrote this script :

drop_database_link.sql


accept owner char prompt "Enter database link owner : "
accept db_link char prompt "Enter link name : "

begin
  dbms_scheduler.create_job(
    job_name=>'&owner..drop_database_link',
    job_type=>'PLSQL_BLOCK',
    job_action=>'BEGIN execute immediate ''drop database link &db_link'';END;'
  );
  dbms_scheduler.run_job('&owner..drop_database_link',false);
  dbms_lock.sleep(2);
  dbms_scheduler.drop_job('&owner..drop_database_link');
end;
/

I am using the scheduler to run a job as another user. The database link owner does not need to have any privilege, neither CREATE SESSION nor CREATE JOB. It could locked and expired.

ORA-01722: invalid number and sql loader

Your manager asked you to load an Excel file in a table. Ok, you look at the header, create a basic table with meaningfull datatype, you open Excel and save as csv, you load your data. But then you get ORA-1722. What happened ?

Ok, let’s do it


create table t(x number not null, y number, z number not null);


LOAD DATA
INFILE *
INTO TABLE T
TRUNCATE
FIELDS TERMINATED BY ';'
(X, Y, Z)
BEGINDATA
1;1;1
2; ;2
3;3;3


$ sqlldr scott/tiger control=foo.ctl
$ vi foo.log
...
Record 2: Rejected - Error on table T, column Y.
ORA-01722: invalid number

Here it is pretty eye-popping, but you probably have 10 years of market data to load with hundreds of columns and most of the columns are empty or/and obsolete.

The thing is, Excel did put a space for your “number” datatype, space is not a valid number !


SQL> select to_number(' ') from dual;
select to_number(' ') from dual
                 *
ERROR at line 1:
ORA-01722: invalid number

A workaround is for each nullable numeric column to specify nullif column=blank


LOAD DATA
INFILE *
INTO TABLE T
TRUNCATE
FIELDS TERMINATED BY ';'
(X, Y NULLIF Y=BLANKS, Z)
BEGINDATA
1;1;1
2; ;2
3;3;3

$ sqlldr scott/tiger control=foo.ctl
$ vi foo.log
...
Table T:
  3 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.

I hope I will remember this next time I am sqlloading from Excel into Oracle !

Oracle Certified Master