Laurent Schneider

Subscribe to Laurent Schneider feed
Oracle Certified Master
Updated: 9 hours 25 min ago

What is the instance name?

Mon, 2016-07-18 09:48

If your Oracle SID doesn’t match your instance name in init.ora, this is quite confusing.

Check my previous post, what is sid in oracle

In the instance_name column of the view v$instance, as well as in USERENV context, it matches the ORACLE_SID of the underlying operating system.

SQL> var ORACLE_SID varchar2(9)
SQL> set autoprint on
SQL> exec dbms_system.get_env('ORACLE_SID',:ORACLE_SID)
PL/SQL procedure successfully completed.
ORACLE_SID
------------
ORA001
SQL> select sys_context('USERENV','INSTANCE_NAME') from dual;
SYS_CONTEXT('USERENV','INSTANCE_NAME')
---------------------------------------
ORA001
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
ORA001
SQL>

This is not the same as the init.ora parameter

SQL> select name, value, description from v$parameter where name='instance_name';

NAME          VALUE     DESCRIPTION
------------- --------- ----------------------------------------
instance_name INS001    instance name supported by the instance
SQL>

The instance_name doesn’t have to match anything. It’s of relevance if you use ADR. And you probably do. Background dump dest and family are deprecated now. In your ADR docu you’ll read

{ORACLE_BASE}/diag/rdbms/{DB_UNIQUE_NAME}/{SID}/trace

But this SID is actually your init.ora instance name. And not your ORACLE_SID.

What is SID in Oracle ?

Thu, 2016-06-30 10:56

In the doc you’ll find, it’s the Oracle system identifier.

Okay, let’s imagine the following

Environment:

ORACLE_SID=ORA001

init.ora:

DB_NAME=DB001
DB_UNIQUE_NAME=UNI001
INSTANCE_NAME=INS001
SERVICE_NAMES=SVC001,SVC002
DB_DOMAIN=EXAMPLE.COM
GLOBAL_NAMES=false

database:

SQL> select * from GLOBAL_NAME;
GLO001.example.com

listener.ora:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_NAME=GLO001.EXAMPLE.COM)
      (SID_NAME=ORA001)
    )
  )

What is my SID? Actually there is more than one correct answer.

In the environment, Oracle SID is ORA001. This matches SID_NAME in listener.ora. It does not have to match database name, unique name, global name or instance_name.

$ lsnrctl services
Services Summary...
Service "GLO001.EXAMPLE.COM" has 1 instance(s).
  Instance "ORA001", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:7 refused:0
         LOCAL SERVER

As the instance is not running, I have only my listener.ora static connections.

The SERVICE_NAME is GLO001.EXAMPLE.COM and the SID is ORA001.

$ sqlplus "sys/***@
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=SRV001)
      (PORT=1521)
    )
    (CONNECT_DATA=
      (SERVICE_NAME=GLO001.EXAMPLE.COM)
    )
  )" as sysdba
Connected to an idle instance.
SQL> 
$ sqlplus "sys/***@
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=SRV001)
      (PORT=1521)
    )
    (CONNECT_DATA=
      (SID=ORA001)
    )
  )" as sysdba
Connected to an idle instance.
SQL> 

Let’s start

SQL> startup

and check my services

$ lsnrctl services
Services Summary...
Service "SVC001.EXAMPLE.COM" has 1 instance(s).
  Instance "INS001", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "SVC002.EXAMPLE.COM" has 1 instance(s).
  Instance "INS001", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "UNI001.EXAMPLE.COM" has 1 instance(s).
  Instance "INS001", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "GLO001.EXAMPLE.COM" has 1 instance(s).
  Instance "ORA001", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:13 refused:0
         LOCAL SERVER
The command completed successfully

I know have 4 service names :

  1. The global name in listener.ora
  2. the unique name in init.ora
  3. both service name in init.ora

And 2 sid

  1. The SID in listener.ora
  2. The instance name in init.ora

While we often have sid = oracle_sid = service_name = service_names = global_name = instance_name = db_name = db_unique_name, if you switch from SID to SERVICE_NAME, this could be help to identify legacy application.

If you read the doc carefully, you may have noticed the SID is no longer documented as a valid clause of CONNECT_DATA in 11g and 12c

In 10gR2 :
http://docs.oracle.com/cd/B19306_01/network.102/b14213/tnsnames.htm#i477921
Use the parameter SID to identify the Oracle8 database instance by its Oracle System Identifier (SID). If the database is Oracle9i or Oracle8, use the SERVICE_NAME parameter rather than the SID parameter.

This is probably a documentation bug, I would rephrase this as If the database is Oracle9i or Oracle8i or later.

In 11g and 12c, the comment disappeared. Oracle 8i was released last century, but SID didn’t completly disappear from tnsnames. Yet.

duplicate to a future date

Sat, 2016-05-21 08:48

If you work with large databases, you often wait way to long for the clones. Typically you want to duplicate a 10TB database to production timestamp 9am, and you start at 9am and then you wait for hours.

Is it possible to start the clone, let’s say, at midnight, and set until time 9am?

No! You’ll get

RMAN-06617: UNTIL TIME (2016-05-21 09:00:00) is ahead of last NEXT TIME in archived logs (2016-05-20 23:58:52)

But… you could start to restore the datafiles at midnight.

sqlplus sys/***@db02 as sysdba <<EOF
  alter system set db_name='DB01' scope=spfile;
  alter system set db_unique_name='DB02' scope=spfile;
  startup force nomount
EOF

rman target sys/***@db01 auxiliary sys/***@db02 <<EOF
   restore clone primary controlfile;
   alter clone database mount;

run {
   set newname for datafile  1 to
 "/db02/system01.dbf";
   set newname for datafile  2 to
 "/db02/sysaux01.dbf";
   set newname for datafile  3 to
 "/db02/undotbs1_02.dbf";
   set newname for datafile  4 to
 "/db02/users01.dbf";
   restore clone database
   ;
}
EOF

This is exactly when RMAN does when you issue a duplicate. You could use the supported RESTORE command instead of the unsupported RESTORE CLONE command. But then it’ll get a bit more complex as you need to find out the location of your backup and so on.

At 9am, you issue your duplicate, and you’ll see

skipping datafile 1; already restored to file /db02/system01.dbf
skipping datafile 2; already restored to file /db02/sysaux01.dbf
skipping datafile 3; already restored to file /db02/undotbs1_02.dbf
skipping datafile 4; already restored to file /db02/users01.dbf

You just saved nine hours &#x1f642;

column width change in 12c

Thu, 2016-02-18 12:14

In 11g I used to have 30 characters width in my dictionary

SQL> select table_name, column_name from user_tab_columns;

TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------
BONUS                          COMM
BONUS                          SAL
BONUS                          JOB
BONUS                          ENAME
DEPT                           LOC
DEPT                           DNAME
DEPT                           DEPTNO
EMP                            DEPTNO
EMP                            COMM
EMP                            SAL
EMP                            HIREDATE

TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------
EMP                            MGR
EMP                            JOB
EMP                            ENAME
EMP                            EMPNO
SALGRADE                       HISAL
SALGRADE                       LOSAL
SALGRADE                       GRADE

18 rows selected.

Which was pretty nice to read with no setting.

Now in 12c it is ugly enought to make your eyes tired

SQL> select table_name, column_name from user_tab_columns;

TABLE_NAME
------------------------------------------------------------------------------------------------------------------------
COLUMN_NAME
------------------------------------------------------------------------------------------------------------------------
DEPT
DEPTNO

DEPT
DNAME

DEPT
LOC

TABLE_NAME
------------------------------------------------------------------------------------------------------------------------
COLUMN_NAME
------------------------------------------------------------------------------------------------------------------------
EMP
EMPNO

EMP
ENAME

EMP
JOB

TABLE_NAME
------------------------------------------------------------------------------------------------------------------------
COLUMN_NAME
------------------------------------------------------------------------------------------------------------------------
EMP
MGR

EMP
HIREDATE

EMP
SAL

TABLE_NAME
------------------------------------------------------------------------------------------------------------------------
COLUMN_NAME
------------------------------------------------------------------------------------------------------------------------
EMP
COMM

EMP
DEPTNO

BONUS
ENAME

TABLE_NAME
------------------------------------------------------------------------------------------------------------------------
COLUMN_NAME
------------------------------------------------------------------------------------------------------------------------
BONUS
JOB

BONUS
SAL

BONUS
COMM

TABLE_NAME
------------------------------------------------------------------------------------------------------------------------
COLUMN_NAME
------------------------------------------------------------------------------------------------------------------------
SALGRADE
GRADE

SALGRADE
LOSAL

SALGRADE
HISAL

18 rows selected.

SQL>

This is due to a change of the width of the underlying column in the dictionary. Probably a good-news for our data modeling colleagues that it may be 128 in future.

But currently it is only 30 characters in 12c. So why not format it correctly ?

Simply add the format in $ORACLE_HOME/sqlplus/admin/glogin.sql

col TABLE_NAME for a30
col COLUMN_NAME for a30 

DISCLAIMER: it’s fine to add UNIQUE_KEY_LEVEL_NAME or REFERENCED_TRIGGER_NAME, but you may have application tables that have columns called OWNER or USER, it is probably safer to not assume they are all smaller than 30 chars, so don’t add common names.

Drop table cascade and reimport

Tue, 2016-01-19 12:26

Happy new year &#x1f642;

Today I had to import a subset of a database and the challenge was to restore a parent table without restoring its children. It took me some minutes to write the code, but it would have taken days to restore the whole database.

CREATE TABLE t1(
  c1 NUMBER CONSTRAINT t1_pk PRIMARY KEY);
INSERT INTO t1 (c1) VALUES (1);
CREATE TABLE t2(
  c1 NUMBER CONSTRAINT t2_t1_fk REFERENCES t1,
  c2 NUMBER CONSTRAINT t2_pk PRIMARY KEY);
INSERT INTO t2 (c1, c2) VALUES (1, 2);
CREATE TABLE t3(
  c2 NUMBER CONSTRAINT t3_t2_fk REFERENCES t2,
  c3 NUMBER CONSTRAINT t3_pk PRIMARY KEY);
INSERT INTO t3 (c2, c3) VALUES (2, 3);
CREATE TABLE t4(
  c3 NUMBER CONSTRAINT t4_t3_fk REFERENCES t3,
  c4 NUMBER CONSTRAINT t4_pk PRIMARY KEY);
INSERT INTO t4 (c3, c4) VALUES (3, 4);
COMMIT;

expdp scott/tiger directory=DATA_PUMP_DIR dumpfile=scott.dmp reuse_dumpfiles=y

Now what happen if I want to restore T2 and T3 ?

If possible, I check the dictionary for foreign keys from other tables pointing to T2 and T3.

SELECT constraint_name
FROM user_constraints
WHERE (r_constraint_name) IN (
    SELECT constraint_name
    FROM user_constraints
    WHERE table_name IN ('T2', 'T3'))
  AND table_name NOT IN ('T2', 'T3');

TABLE_NAME                     CONSTRAINT_NAME               
------------------------------ ------------------------------
T4                             T4_T3_FK                      

T4 points to T3 and T4 has data.

Now I can drop my tables with the cascade options

drop table t2 cascade constraints;
drop table t3 cascade constraints;

Now I import, first the tables, then the referential constraints dropped with the cascade clause and not on T2/T3.

impdp scott/tiger tables=T2,T3 directory=DATA_PUMP_DIR dumpfile=scott.dmp

impdp scott/tiger  "include=ref_constraint:\='T4_T3_FK'" directory=DATA_PUMP_DIR dumpfile=scott.dmp

It’s probably possible to do it in one import, but the include syntax is horrible. I tried there

Licensing Cloud Control

Mon, 2015-11-30 12:08

I just read the Enterprise Manager Licensing Information User Manual today. They are a lot of packs there, and you may not even know that autodiscovering targets is part of the lifecycle management pack or that blackouts are part of the diagnostic pack.

Have a look

powershell odbc sybase

Wed, 2015-10-21 07:41

Oracle goes there ==> …/oracle-odbc-hello-world-with-powershell.html

To test Sybase ODBC driver with Powershell, it’s not much different

  1. configure the DsN with odbcconf or %SYSTEMROOT%\SysWOW64\odbcconf for the 32bits drivers under Windows 64.
  2. 
    cmd /c "%SYSTEMROOT%\SysWOW64\odbcconf.exe /a {configdsn ""Adaptive Server Enterprise"" ""DSN=helloworld|database=mydb|port=25000|server=srv01""}"
    

    The name of the driver is important. It is probably also called “Adaptive Server Enterprise” on your machine.

  3. test it, same as in Oracle. If you need to test the 32bits drivers under 64bits windows, use the 32bits powershell %SystemRoot%\syswow64\WindowsPowerShell\v1.0\powershell.exe
  4. 
    $conn = New-Object data.odbc.odbcconnection
    $conn.ConnectionString = "dsn=helloworld;uid=scott;pwd=tiger"
    $conn.Open()
    (new-Object Data.Odbc.OdbcCommand("select 'Hello World'",$conn)).ExecuteScalar()
    $conn.close()
    

generate safe passwords

Fri, 2015-10-09 08:29

This is probably ambitious and I’ll start with a disclaimer, there is no such thing.

But ok, we know that system/manager isn’t

Apart from system/manager, there are hundreds of weak passwords that are commonly used and easy to guess.

On your database server, after a few tries, the account is lock. And maybe the attacker is detected by then.

So the worst passwords are the default passwords and passwords like oracle.

To enforce good passwords, we have verify functions, like ora12c_strong_verify_function in 12c, that checks for mixed case, special characters, etc. One may prefer to write his own and not disclose what it exactly checks.

In that function in rdbms admin, it states The maximum length of any DB User password is 128 bytes. but it’s 30 character in most cases.

If you have failed login attends of 10, chosing eleven as a password does not make it safe. If the attacker got’s the user metadata, you are screwed in no time. In Oracle 4, it’s clear text. In 7-10, it’s a doubled-DES unsalted with a fixed disclosed key encryption. There any dictionary attack takes milliseconds, and a 6 character password in sub-second. It’s got better in 11, where SHA1 could take weeks to years to have a 8 char password. Depending on its complexity. In 12c, generating a hash cost lot’s of cpu cycle, so it is no longer possible to test millions of password per second, even with the strongest hardware.

But to get a good password it is recommended and often required to use digit / letters / special signs / mixed case and no dictionary word.

I have made a small password generator for my reader using dbms_random.string, which generates pseudorandom string. It is best to use the cryptographically secure dbms_crypto.randombytes, but then you must still get a password that you can type. It should also be possible to use unicode if you like. And depending where you are going to use it, it is sometimes safer to not use signs like * or ‘ because, who know’s, your password may produce an error and end up in a logfile.

Okay, I wrote a small function that generates a 10-char string and verify it with the 12c strong verifier. And loop until one is good enough.

The chance that a random password is manager is pretty low, but it is probably best to check you got not only safe random, but also strong string


-- @?/rdbms/admin/catpvf
CREATE OR REPLACE FUNCTION pw (username        VARCHAR2,
                               old_password    VARCHAR2 DEFAULT NULL)
   RETURN VARCHAR2
IS
   p   VARCHAR2 (30);
   c   BOOLEAN := FALSE;
   i   NUMBER := 0;
BEGIN
   WHILE NOT c AND i < 1000
   LOOP
      p := DBMS_RANDOM.string ('P', 10);
      i := i + 1;
      BEGIN
         c := sys.ora12c_strong_verify_function (username, p, old_PASSWORD);
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;
   END LOOP;
   RETURN p;
END;
/

SELECT pw ('SCOTT', 'TIGER') FROM DUAL;

#gA~82NxBv

This could well be a good initial expired password for your user. Later the user will find something easier to remember

Generate 11g password hash

Thu, 2015-10-01 10:14

An easy way to generate a value string from the ssl is to use openssl

Let’s take a random salt of ABCDEFGHIJ. The length of 10 is important.

The hexadecimal representation is -41-42-43-44-45-46-47-48-49-4A-


$ echo "SafePassw0rDABCDEFGHIJ\c" | openssl dgst -sha1
(stdin)= 47cc4102144d6e479ef3d776ccd9e0d0158842bb

With this hash, I can construct my value


SQL> create user testuser identified by values 'S:47CC4102144D6E479EF3D776CCD9E0D0158842BB4142434445464748494A';

User created.

SQL> grant create session to testuser;

Grant succeeded.

SQL> conn testuser/SafePassw0rD
Connected.

If you prefer PL/SQL over shell, use DBMS_CRYPTO


SQL> exec dbms_output.put_line('S:'||dbms_crypto.hash(utl_raw.cast_to_raw('SafePassw0rDABCDEFGHIJ'),dbms_crypto.HASH_SH1)||utl_raw.cast_to_raw('ABCDEFGHIJ'))
S:47CC4102144D6E479EF3D776CCD9E0D0158842BB4142434445464748494A

PL/SQL procedure successfully completed.

In 12c there is also a “T” String. According to the doc
The cryptographic hash function used for generating the 12C verifier is based on a de-optimized algorithm involving PBKDF2 and SHA-512.

delete all data

Tue, 2015-09-29 09:53

How do you delete all data? The simplistic approach would be to truncate all tables


SQL> select table_name from user_tables;
TABLE_NAME
----------
T1
SQL> truncate table t1;
Table truncated.

You cannot truncate if you have referential integrity constraints.


SQL> truncate table t2;
ORA-02266: unique/primary keys in table 
  referenced by enabled foreign keys

Ok, let’s disable the RIC


SQL> select table_name, constraint_name
  from user_constraints
  where constraint_type='R';
TAB CONSTRAINT
--- ----------
T3  SYS_C00107
SQL> alter table t3 disable constraint SYS_C00107;
Table altered.
SQL> truncate table t2;
Table truncated.
SQL> truncate table t3;
Table truncated.

You cannot truncate cluster tables


SQL> truncate table t4;
ORA-03292: Table to be truncated is part of a cluster

Cluster tables could be dropped with TRUNCATE CLUSTER.


SQL> select cluster_name from user_clusters;
CLUSTER_NAME                  
------------
C                             
SQL> truncate cluster c;
Cluster truncated.

The code above doesn’t work with Partitioned cluster (12.1.0.2) because it was not properly implemented at the time of the writing.
Check Bug 20284579 : CAN NOT QUERY DYNAMIC CLUSTER PARTITIONS

For reference partitioning, it is not possible to disable the foreign key


SQL> alter table t6 disable constraint fk;
ORA-14650: operation not supported for 
  reference-partitioned tables

In 12c, if the foreign key is defined with ON DELETE CASCADE, you can truncate cascade the parent.


SQL> select table_name, REF_PTN_CONSTRAINT_NAME
  from user_part_tables 
  where partitioning_type='REFERENCE';
TAB REF
--- ---
T6  FK 
SQL> select r_constraint_name, delete_rule 
  from user_constraints 
  where constraint_name='FK';
R_CON DELETE_RULE
----- -----------
PK    CASCADE    
SQL> select table_name
  from user_constraints 
  where constraint_name='PK';
TAB
---
T5 
SQL> truncate table t5 cascade;
Table truncated.

But if one of child or child-child table is using reference partitioning without the ON DELETE CASCADE, then the parent or grand-parent could not be truncated. And truncate cascade for reference partitioning is not documented (yet).

But there is very nice alternative to TRUNCATE called is DELETE &#x1f642;


SQL> select table_name, REF_PTN_CONSTRAINT_NAME
  from user_part_tables 
  where partitioning_type='REFERENCE';
TAB REF
--- ---
T8  FK 
SQL> select r_constraint_name, delete_rule 
  from user_constraints 
  where constraint_name='FK';
R_CON DELETE_RULE
----- -----------
PK    NO ACTION  
SQL> select table_name
from user_constraints 
where constraint_name='PK'
TAB
---
T7 
SQL> truncate table t7 cascade;
ORA-14705: unique or primary keys referenced by enabled foreign keys in table "SCOTT"."T8"
SQL> truncate table t8;
Table truncated.
SQL> delete from t7;
2 rows deleted

To get the tables in the right order, parent tables after children, you can do some hierarchical query and then order by rownum desc, a construct I’m using for the first time I confess. Note the leaf tables are truncable.


select c_owner owner, child table_name   
FROM 
  (
    SELECT 
      p_OWNER, parent, nvl(c_owner, a.owner) c_owner,
      nvl(child, a.table_name ) child
    FROM 
    (
      SELECT 
        PT.OWNER P_owner, pt.table_name parent, 
        pt2.owner c_owner, pt2.table_name child
      FROM all_part_tables pt
      JOIN all_constraints c
      ON pt.OWNER = c.owner
        AND PT.TABLE_NAME = c.table_name
        AND c.constraint_type = 'P'
        AND c.status = 'ENABLED'
      JOIN all_constraints r
      ON r.r_owner = c.owner
        AND r.r_constraint_name = c.constraint_name
        AND r.constraint_type = 'R'
        AND r.status = 'ENABLED'
      JOIN all_part_tables pt2
      ON r.owner = pt2.owner
        AND r.constraint_name = pt2.REF_PTN_CONSTRAINT_NAME
        AND pt2.partitioning_type = 'REFERENCE'
    ) t
    RIGHT JOIN all_tables a 
    ON child = table_name and a.owner = T.c_OWNER
  )
where connect_by_isleaf=0  
CONNECT BY parent = PRIOR child and p_owner=PRIOR c_owner
start with parent is null 
order by rownum desc;

OWNER TAB
----- ---
SCOTT T10 
SCOTT T9

Note the query above is very slow. If dictionary-performance is an issue, maybe we could delete all tables and catch exceptions and loop until all tables are empty


SQL> delete from t9;
ORA-02292: integrity constraint (SCOTT.F10) violated - child record found
SQL> delete from t10;
ORA-02292: integrity constraint (SCOTT.F11) violated - child record found
SQL> delete from t11;
1 row deleted.
SQL> delete from t9;
ORA-02292: integrity constraint (SCOTT.F10) violated - child record found
SQL> delete from t10;
1 row deleted.
SQL> delete from t11;
0 row deleted.
SQL> delete from t9;
1 row deleted.
SQL> delete from t10;
0 row deleted.
SQL> delete from t11;
0 row deleted.
SQL> delete from t9;
0 row deleted.
SQL> delete from t10;
0 row deleted.
SQL> delete from t11;
0 row deleted.

If you have close to zero reference-partitioning table, this approach will be more efficient.

tracefile tim to readable date format

Wed, 2015-09-23 07:25

In trace file, the time is recorded, this could be used to measure time between two timestamps

But how do you convert 31796862227375 to a human format?

This is how I proceeded :

SQL> oradebug SETMYPID
Statement processed.
SQL> oradebug tracefile_name
/u01/log/oracle/diag/rdbms/db01/DB01/trace/DB01_ora_32440740.trc
SQL> alter session set 
  nls_date_format='YYYY-MM-DD_HH24:MI:SS' 
  sql_trace=true;
Session altered.
SQL> select '&_DATE','Start' from dual;
2015-09-23_13:17:50 Start
SQL> select '&_DATE','End' from dual;
2015-09-23_13:18:38 End
SQL> alter session set sql_trace=false;
Session altered.

I use the sqlplus variable _date and waited about a minute between the select’s.

Now let’s look at the trace

PARSING IN CURSOR #4859519800 tim=31796814530524
select '2015-09-23_13:17:50','Start' from dual

PARSING IN CURSOR #4859511384 tim=31796862227375 
select '2015-09-23_13:18:38','End' from dual

Note the tim=

Between 13:17:50 and 13:18:38 I have 48 seconds; the difference between 31796862227375 and 31796814530524 is 47696851, roughly 48 millions of microseconds.

So the time on this specific version of Oracle is in microseconds.

Now if I substracted 31796862227375 from 2015-09-23_13:17:50 and I get 2014-09-20_12:50:08 on this specific instance.

So to convert tim= to human readable date, I simply add tim microseconds to Sep 20, 2014, 12:50:08.

job_name cannot be null

Wed, 2015-09-02 02:23


exec dbms_scheduler.create_job(job_name=>null,job_type=>'PLSQL_BLOCK',job_action=>'BEGIN NULL; END;')
ORA-27451: JOB_NAME cannot be NULL
ORA-06512: at "SYS.DBMS_ISCHED", line 146
ORA-06512: at "SYS.DBMS_SCHEDULER", line 288
ORA-06512: at line 1

This sounds like a proper error message. A bit less obvious is the drop_job message


SQL> exec dbms_scheduler.drop_job(job_name=>null)
ORA-20001: comma-separated list invalid near
ORA-06512: at "SYS.DBMS_UTILITY", line 236
ORA-06512: at "SYS.DBMS_UTILITY", line 272
ORA-06512: at "SYS.DBMS_SCHEDULER", line 743
ORA-06512: at line 1

comma-separated list invalid near what?

Ok, why would you create an empty job? Obviously you wouldn’t. But remember job_name could be a very long expression that won’t fit in your VARCHAR2(30) variable.


SQL> begin 
  dbms_scheduler.create_job(job_name=>
'                  "SCOTT"                    '||
'                     .                       '||
'             "JOB10000000000000000000001"    ',
    job_type=>'PLSQL_BLOCK',
    job_action=>'BEGIN NULL; END;');
end;
/

PL/SQL procedure successfully completed.

SQL> exec dbms_scheduler.drop_job('scott.job10000000000000000000001')

PL/SQL procedure successfully completed.

If you use drop job in the exception clause without catching the exception of the exception, it could lead to this ORA-20001 if job name is null

For exception handling, we could improve


BEGIN
  CREATE JOB 
  RUN JOB
  DROP JOB
EXCEPTION
  WHEN OTHERS THEN
    DROP JOB
    output message
    RAISE
END

into

BEGIN
  CREATE JOB 
  RUN JOB
  DROP JOB
EXCEPTION
  WHEN OTHERS THEN
    BEGIN
      DROP JOB
    EXCEPTION 
      WHEN IS_RUNNING
         sleep
      WHEN OTHERS
         output message
    END LOOP
    output message
    RAISE
END