list targets

As correctly pointed out by dhoogfr , the proper way to list targets on an agent is to use list target


$ emctl config agent listtargets
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
[srv01.example.com, host]
[srv01.example.com:3872, oracle_emd]
[agent13c2_2_srv01.example.com_3830, oracle_home]
[DB01.example.com, oracle_database]
[OraDB12Home1_14_srv01.example.com_743, oracle_home]
[DB01_srv01.example.com_CDBROOT, oracle_pdb]
[DB01_srv01.example.com_PDB01, oracle_pdb]
[LISTENER001_srv01.example.com, oracle_listener]

Not really easy to parse, and it does not contain all information. Let’s imagine I want to get the TARGET_NAME out of my SID? hard…

What is actually emctl doing ? It is parsing the targets.xml with perl. Oracle wrote a module, called ias::simpleXPath, that helps parsing the file.


$AGENT_HOME/perl/bin/perl -l -I$AGENT_HOME/sysman/admin/scripts -Mias::simpleXPath -e '
  foreach $t(
    simpleXPathQueryForNodes(
      "targets.xml","Targets/Target")){
    print 
      "[".($t->{"attributes"}->{"NAME"}).
      ", ".($t->{"attributes"}->{"TYPE"}).
      "]"}'

ias::simpleXPath is a wrapper for XML::Parser. XML::Parser is a supported perl that is included in the agent home. So no need to install your own perl modules for this purpose!

back to by example, if I want to get the target name for my SID DB01


$AGENT_HOME/perl/bin/perl -l -MXML::Parser -e '
  $xmlfile = "targets.xml";
  die "Cannot find file $xmlfile"
    unless -f $xmlfile;
  $parser = new XML::Parser;
  $parser->setHandlers(
    Start => \&startElement,
    End => \&endElement);
  $parser->parsefile($xmlfile);
  sub startElement {
    ( $parseinst, $element, %attrs ) = @_;
    if ($element eq "Target") {
      $tn=$attrs{"NAME"};
      $tt=$attrs{"TYPE"};
    }
    if ($element eq "Property" &&
        $attrs{"NAME"} eq "SID" ) {
      $sid=$attrs{"VALUE"};
    }
  }
  sub endElement {
    ( $parseinst, $element ) = @_;
    if ($element eq "Target"){
      if (
        lc $sid eq lc "DB01"
      ) {
        print $tn . ":" . $tt;
      }
      $sid="";
    }
  }
'
DB01.example.com:oracle_database

This could be useful, for instance if you want to start a blackout


emctl start blackout db01_black DB01.example.com:oracle_database

For listener, you could retrieve the LsnrName for your listener LISTENER001


$AGENT_HOME/perl/bin/perl -l -MXML::Parser -e '
  $xmlfile = "targets.xml";
  die "Cannot find file $xmlfile"
    unless -f $xmlfile;
  $parser = new XML::Parser;
  $parser->setHandlers(
    Start => \&startElement,
    End => \&endElement);
  $parser->parsefile($xmlfile);
  sub startElement {
    ( $parseinst, $element, %attrs ) = @_;
    if ($element eq "Target") {
      $tn=$attrs{"NAME"};
      $tt=$attrs{"TYPE"};
    }
    if ($element eq "Property" &&
        $attrs{"NAME"} eq "LsnrName" ) {
      $lsn=$attrs{"VALUE"};
    }
  }
  sub endElement {
    ( $parseinst, $element ) = @_;
    if ($element eq "Target"){
      if (
        lc $lsn eq lc "LISTENER001"
      ) {
        print $tn . ":" . $tt;
      }
      $lsn="";
    }
  }
'
LISTENER001_srv01.example.com:oracle_listener

Which you could also blackout before rebooting.

The parser is not limited to Entreprise Manager targets, you could use it for oraInventory/ContentsXML/inventory.xml or whatever files.

There are plenty of other mean to read xml, from the database, xmllint, powershell.

sid and pluggable

I wrote about SID there. (CONNECT_DATA=(SID=DB01)) is undocumented since Oracle 11gR1 and breaks things with multi-tenant / single-tenant.

You create a container CDB01 and a pluggable DB01, you can connect with


sqlplus "scott/tiger@
  (description=(address=
    (host=srv01)(port=1521)(protocol=tcp))
    (connect_data=(service_name=DB01.example.com)))"

But one of your application has hardcoded SID=DB01


sqlplus "scott/tiger@
  (description=(address=
    (host=srv01)(port=1521)(protocol=tcp))
    (connect_data=(sid=DB01)))"
ORA-12514: TNS:listener does not currently know of 
    service requested in connect descriptor

How do you do the trick?

  • Remove the domain name in the pluggable
  • 
    alter session set container=DB01;
    alter system set db_domain='' 
      container=current 
      scope=spfile;
    

  • You use the listener parameter USE_SID_AS_SERVICE
  • listener.ora

    
    USE_SID_AS_SERVICE_LISTENER = on
    

    You restart the listener and the database.

    
    sqlplus "scott/tiger@
      (description=(address=
        (host=srv01)(port=1521)(protocol=tcp))
        (connect_data=(sid=DB01)))"
    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.2.0.1.0
    

    You just connected with SID to a pluggable database

default listener port

Long time ago, Maxime Yuen registered 1521 for nCube License Manager.

By googling I found : Ellison cleans house at nCube, and since them 1521 has been used as a default port for Oracle. Still, you’ll see nCube in IANA.ORG service names port numbers and in /etc/services the nCube name. I don’t know which one came first, Oracle using 1521 or Larry investing in nCube, but I am pretty sure it’s related 😉

$ curl https://www.iana.org/assignments/
  service-names-port-numbers/service-names
  -port-numbers.txt | grep 1521
ncube-lm 1521 tcp nCube License Manager 
  [Maxine_Yuen] [Maxine_Yuen]
ncube-lm 1521 udp nCube License Manager 
  [Maxine_Yuen] [Maxine_Yuen]
$ grep -w 1521 /etc/services
ncube-lm 1521/tcp # nCube License Manager
ncube-lm 1521/udp # nCube License Manager
$ netstat -a | grep ncube
tcp 0 0 *.ncube-lm *.* LISTEN

Later, still long time ago, Oracle officially registered 2483 and 2484 (tcps) for the listener communication, as documented on Recommended Port Numbers :
This port number may change to the officially registered port number of 2483 for TCP/IP and 2484 for TCP/IP with SSL.

Still, as of Oracle 12c Release 2, port 1521 is recommended.

Now, another question : do you really want to use port 1521?

On the one hand, it could be good for a hacker to know listener runs on 1521 and ssh on port 22. This is configurable of course.

On the other hand, you better use that is assigned to Oracle. RFC 6335 defines 1024-49151 as User Ports, and 49152-65535 as the Dynamic and/or Private
Ports (aka ephemeral). Remember, if a port is used before you start your listener, your listener won’t start.

Remember every network connection keeps a port busy. So if you start a network client from your database server to another server, ssh, sqlnet, mail, whatever, dns, then your port 1028 or 57313 may be busy for a client connection. Which will prevent your listener from starting. If you use port 9999, you could look on IANA and ask the owner if he plans anything on that port.

Very often, most ports are unused when you start the listener. If you find an unused port in the private range, 49152-65535, you may name it in /etc/services.

Very often I see database servers with more than one listener. Obviously, you cannot run more than one listener on port 1521. There are some case where you want different listener with different sqlnet.ora or different Oracle version. But this render consolidation (e.g. Multitenant) more painful.

The discussion on which port to use is obviously far beyond Oracle. There are gazillions of TCP/UDP servers running in the digital world and less than 65535 ports. For sure you cannot have all them on IANA.ORG, right?

In most cases, stick to Oracle recommendation, use port 1521.

Single-Tenant over bequeath connections

If you follow Oracle recommendation to use SingleTenant CDB instead of Non-CDB, then a lot of things will break.

I won’t go into details, but basically, in this new architecture, you have one core container, called CDB$ROOT, which is the default container where you connect to if you connect locally


sqlplus / as sysdba
SQL> select cdb from v$database;
CDB
---
YES
SQL> select sys_context('USERENV','CON_NAME') from dual;
SYS_CONTEXT('USERENV','CON_NAME')
---------------------------------
CDB$ROOT

SQL> select con_id, NAME from V$CONTAINERS

    CON_ID NAME
---------- ----------
         1 CDB$ROOT
         2 PDB$SEED
         3 ST01

Then you’ll soon realise, you can no longer do what you used to do


SQL> create user u identified by u;
create user u identified by u
            *
ERROR at line 1:
ORA-65096: invalid common user or role name

Some scripts still run in the root container. SHUTDOWN ABORT, ALTER SYSTEM SWITCH LOGFILE. Doing a full backup or clone probably won’t hurt you much. Relatively..

But now let’s imagine I have a very simple and very old script to lock scott


vintage.sh:
  echo "alter user scott account lock;"|
    sqlplus -s / as sysdba

This won’t work. I need to lock scott in the SingleTenant container ST01.

I could do this in the container


SQL> alter session set container=ST01;
  Session altered.
SQL> alter user scott account lock;
  User altered.

So fine, so good. NOW : how do I make this work without changing the script ?

Remember, non-cdb database, as they were used in Oracle 11, 10, 9, 8, 7 … are now deprecated. Remember, cdb is recommended. Now face it : it’ll break your dba scripts.

As a production dba, I don’t want to rewrite all the existing scripts. Some are ultra-old and used by people who did not write them.

One method for my script would be to change the container in a login script.


echo "alter session set container=ST01;" > /tmp/login.sql
export ORACLE_PATH=/tmp
vintage.sh
  Session altered.
  User altered.  

(ORACLE_PATH in latest 12.1 and in 12.2, SQL_PATH in older release)

However, if my script must work with both CDB and non-CDB, I need to set the container in only this case.

In my login.sql, I first tried to implement some plsql logic, but alter session set container is not working (aka working with limitation) with execute immediate.

As well, I don’t want my script to break Oracle 11.

So I decide to do some sqlplus magic with defined variable.


set ver off feed off
-- 1) check if the column v$database.cdb exists
col column_name new_v cdb nopri
def cdb=null
select column_name from dba_tab_columns 
where owner='SYS' and 
table_name='V_$DATABASE' and column_name='CDB';
-- 2) if cdb is YES, then select a dynamic statement using V$PDB 
col pdb new_v pdb nopri
def pdb="null stmt from dual where 1=0"
select 
'''set container="''||name||''"'' stmt from v$pdbs where name!=''PDB$SEED'''
  pdb 
from v$database 
where &cdb='YES';
-- 3) get a dynamic alter session statement. I use a dummy flagger for non-cdb
col stmt new_val stmt nopri
def stmt="SET FLAGGER=OFF"
select &pdb;
-- 4) alter session
alter session &stmt;
set feed 6
col column_name clear
col stmt clear
col pdb clear
undef cdb
undef stmt
undef pdb
del

Now I run my script


11g: ./vintage.sh
User altered.

12c-non-cdb: ./vintage.sh
User altered.

12cR2-single-tenant: ./vintage.sh
User altered.

DISCLAIMER: you shouldn’t use a global login.sql and you should know that secretly fixing old scripts may have side effects. Test, test and retest your code
DISCLAIMER 2: my frequent readers surely wonder if this statement generating a statement generating a statement is for an obfuscation contest

Legacy users get ORA-01017 in 12.2

The default case insensitive string disappeared in 12cR2, let’s call it the 10G string in this post, but it was the same since Oracle 7 at least. It was introduced in V5 or V6 to replace clear-text passwords.

What’s happening then with my ultra-old-accounts?

You could well set a new password (or the same password again) to each account to be migrated in 11g/12cR1 before moving to 12cR2.

If nobody knows the password and nobody can change it because it is hardcoded in the application and neither easy to read (hidden / obfuscated /encrypted) nor to change, then, you are in TROUBLE ! This is documented in Note 2075401.1

First disclaimer : it is a good thing to achieve a better security. SHA1 and SHA2 are a lot better than the oldstyle-longly-hacked-unsalted-case-insensitive-homemade-algorythm. SHA3 has been published in 2015 and it not used in Oracle 12cR2 yet. SHA2 is a bit older (2001) but still recommended. SHA1 is oldish (1995) and no-longer-recommended, collision has been detected. Read more on wikipedia or crypto101

SHA-1 was a really huge improvement when introduced in 11gR1. The old self-made algorythm has been a torture for Oracle Security team. It has been published on Internet. Extremly powerfull password cracker can find your “not-too-long” password in notime. In 11g, Oracle removed the 10g String from the DBA_USERS view. I wrote about this here. It remained on the base table, USER$ until 12cR2. Now Oracle completly removed it by default in 12cR2. 10 years after SHA1 was introduced in 11gR1.

Still. You are the dba. You want to migrate your database not to chase passwords.

You could edit your sqlnet.ora to allow 10g strings.


SQLNET.ALLOWED_LOGON_VERSION_SERVER=11

This works


SQL> sho parameter sec_case_sensitive_logon
NAME                      VALUE
------------------------- -----
sec_case_sensitive_logon  FALSE

SQL> CREATE USER "U" IDENTIFIED BY 
     VALUES 'DC6F2B33D359A95B';
User created.
SQL> grant create session to u;
Grant succeeded.
SQL> conn u/abcdefg@pdb01
Connected.
SQL> conn u/AbCdEfG@pdb01
Connected.

If you have SQLNET.ALLOWED_LOGON_VERSION_SERVER=11, then you could keep the same setting of sec_case_sensitive_logon as in 11g. I recommmend the default (true).

But, that’s it ? Wellllllll… not sure.

In 12.1

 
SQL> select dbms_metadata.get_ddl('USER','U') from dual
DBMS_METADATA.GET_DDL('USER','U')
----------------------------------
   CREATE USER "U" IDENTIFIED BY 
     VALUES 'DC6F2B33D359A95B'

Let’s try in 12.2


SQL> select dbms_metadata.get_ddl('USER','U') from dual
DBMS_METADATA.GET_DDL('USER','U')
------------------------------------
CREATE USER "U" IDENTIFIED BY VALUES 
  'S:0000000000000000000000000000000
00000000000000000000000000000'

While this is a perfectly working syntax, and IDENTIFIED BY VALUES is not supported *. So if create that user, then, obviously, the 10G string is lost.

Well, unsupported feature then? Hmm, yes. You should never have used identified by values.

Ok, so if I refresh my Test database with Production data, how can I save test passwords? You can’t. At least not in a supported way by using identified by values.

You could something like :


SQL> select 'alter user "'||name||
       '" identified by values '''||
       password||''';' txt
     from user$, v$instance 
     where version > '12.2' 
     and spare4 is null  
     and type# = 1
     and regexp_like(PASSWORD,'[A-F0-9]{16}');

txt
-----------------------------------
alter user "U" identified by values
   'DC6F2B33D359A95B';

This may work. In 12.2.0.1. Maybe not in 13. Maybe not in 12.2.0.1.0PSU July. It’s not supported. If it does not work, it is NOT-A-BUG.

The SHA1 was introduced 10 years ago in Oracle 11gR1. If you have not changed your password in ten years, and you don’t know how many employees and ex-employees know this password, and it is case-insensitive, and its “pseudo-hashing-algorythm” has been hacked for maybe two decades, yet, I can only warmly recommend to change those accounts passwords !

Again: so if I refresh my Test database with Production data, how can I save test passwords ?
If I were you I would design a better system for login. For human users, use global users and an Identity solution, like Oracle Universal directory. For technical account, build yourself a tool that generate a random password, and update the user and credentials, something like


select
substr(
REGEXP_REPLACE(
UTL_RAW.cast_to_varchar2(
SYS.DBMS_CRYPTO.RANDOMBYTES (1024)
) ,'[^!#-~]'
),
1,
20
) PW
from dual;
PW
--------------------
%K0w(^%UN.B82Yjjfu{?

And use it to reset your technical user and to configure your application credentials.

* Note 554605.1: the 'IDENTIFIED BY VALUES' clause on a CREATE/ALTER USER statement is not officially documented, and is intended purely for internal

remote transaction timeout

If you access one table via database link and the row is locked, you may get a timeout


SQL> update emp@l set sal=sal+1
where ename='SCOTT';
1 row updated.

          SQL> update emp@l set sal=sal+2 
          where ename='SCOTT';
          update emp@l set sal=sal+2 
          *
          ERROR at line 1:
          ORA-02049: timeout: distributed 
          transaction waiting for lock
          ORA-02063: preceding line from L

          Elapsed: 00:01:00.00

SQL> sho parameter distr
NAME                        VALUE
--------------------------- -------
distributed_lock_timeout    60

This timeout (default 60 seconds) could be tuned, maybe to 300 seconds, if you are doing huge remote transactions

But what if you don’t want to wait one minute to get an exception? Lock the row before update then !


          SQL> select ename, sal from emp@l
          where ename='SCOTT' 
          for update wait 2;
          select ename, sal from emp@l
          *
          ERROR at line 1:
          ORA-30006: resource busy; 
          acquire with WAIT timeout expired
          ORA-02063: preceding line from L
          Elapsed: 00:00:02.01

If you want to wait only 2 seconds and not one minute, or even NOWAIT (0 second), then lock the row first. Depending on your application, waiting one minute to get an exception may be unacceptable

Monitor audit_file_dest !

Until 11.2, audit_file_dest used to remain small with default settings and reasonably sized and active database. Suddenly, in 12c, you will sooned or later get ORA-09925: Unable to create audit trail file.

At that point, no more connection is possible to the database, it is a complete loss of service.

Why suddenly in 12c ? This is because the default for audit_sys_operations changed to true. In 11g, you used to get an 1K file each time you connect as sysdba. So a few tousands sysdba connections a weeks, a few mega, no worries.


Mon Mar 27 14:08:01 2017 +02:00
LENGTH : '155'
ACTION :[7] 'CONNECT'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[0] ''
STATUS:[1] '0'

Suddenly in 12c, you get plenty files that are many Mb. For instance for AUTOTASK jobs, every single select is dumped to the filesystem. A single week-end of an quiet database may generate 1Gb of *.aud files of DBMS_SCHEDULER.

Those DB001_j000_12345_20170327140802123456789.aud files are highly useless and annoying.


LENGTH : '641'
ACTION :[490] 'select /*+  no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl no_substrb_pad  */ substrb(dump("PERIOD_END_TIME",16,0
,64),1,240) val,
                      rowidtochar(rowid) rwid from "SYS"."WRP$_REPORTS_TIME_BANDS" t where rowid in (chartorowid('AABJ58AADAAAMsrAAA'),chartorowid('AABJ58AADAAAMsrAAB'),chartorowid('AABJ58AADAAAMsrAAC'),chartorowid('AABJ58A
ADAAAMssAAA')) order by "PERIOD_END_TIME"'
DATABASE USER:[3] 'SYS'
PRIVILEGE :[4] 'NONE'
CLIENT USER:[0] ''
CLIENT TERMINAL:[7] 'UNKNOWN'
STATUS:[1] '0'

Once your audit_file_dest is getting full, your database stops, so better delete those *_j00*_* and *_m00*_* quickly enough!

connect / as sysoper generates ORA-1017

Today I had the issue that I could not login as sysoper on one database, despite being in the dba and oper groups.

The problem is that the osoper group was -probably- not selected during installation

e.g. in 12c /AIX
it may be called differently on your system


$ sqlplus -L -s / as sysoper
ERROR:
ORA-01017: invalid username/password; logon denied
$ awk '/H.17.*, 3/,/End/{print}' $ORACLE_HOME/rdbms/lib/config.s
        .csect  H.17.NO_SYMBOL{RO}, 3
        .string ""
# End   csect   H.17.NO_SYMBOL{RO}

The string is empty. Let’s change this to oper or dba. Stopping any processes using that home is strongly recommended before relink.


$ vi $ORACLE_HOME/rdbms/lib/config.s
        .string "dba"
$ relink
$ sqlplus -s -L / as sysoper
sho user
USER is "PUBLIC"
quit
$

old jdbc driver generates ORA-28040

I read on note 401934.1 that Oracle 10gR2 jdbc clients are still supported with Oracle 12c.

I have an application using an oracle10gr2 jdbc, and connection to 12c generates ORA-28040.

Connection to 11gR2 works like a charm.

O12.java


import java.util.Properties;
import java.sql.*;

public class O12 {
  public static void main(String argv[]) throws
      SQLException {
    Properties props = new Properties();
    props.setProperty("user", "scott");
    props.setProperty("password", "tiger");
    DriverManager.registerDriver(new 
      oracle.jdbc.driver.OracleDriver());
    Connection conn = DriverManager.getConnection(
      argv[0], props);
    System.out.println("JDBC Version: "+ 
      conn.getMetaData().getDriverVersion());
    conn.close();
  }
}

compile


javac -cp ojdbc14.jar O12.java

test with jdbc 10.2.0.2 db 11.2.0.4


java -cp ojdbc14.jar:. O12 jdbc:oracle:thin:@DB11204:1521:DB11204
JDBC Version: 10.2.0.2.0

test with jdbc 10.2.0.2 db 12.1.0.2


java -cp ojdbc14.jar:. O12 jdbc:oracle:thin:@DB12102:1521:DB12102
Exception in thread "main" java.sql.SQLException: ORA-28040: No matching authentication protocol

The easy solution of course is to update the driver. Even without recompile it worked.

test with jdbc 11.2.0.1 db 12.1.0.2


java -cp ojdbc5.jar:. O12 jdbc:oracle:thin:@DB12102:1521:DB12102
JDBC Version: 11.2.0.1.0

Before upgrading the db server to 12c, check 10g jdbc jar’s are upgraded

Restricted sqlplus and sql_script

Yesterday I wrote about execute sql script from plsql. Connor then posted a 12c new feature that I overviewed

If you are still considering the 11g approach with sqlplus, you should read about restricted sqlplus

If you run

sqlplus -L -s -R 3 scott/tiger@db01

lot’s of OS-command are disabled

SQL> get /etc/passwd
SP2-0738: Restricted command "get" not available
SQL> spool /etc/oratab
SP2-0738: Restricted command "spool" not available
SQL> host reboot
SP2-0738: Restricted command "host" not available

There is also a Product User Profile. I never saw any customer using this. I doubt it is very safe.

You could disable some commands


SQL> insert into system.PRODUCT_USER_PROFILE
  (PRODUCT,USERID,ATTRIBUTE,CHAR_VALUE)
  values 
  ('SQL*Plus','SCOTT','ROLLBACK','DISABLED');

Which prevents SCOTT from doing rollabck


SQL> rollback;
SP2-0544: Command "rollback" disabled in Product User Profile

but it doesn’t resist Rock & Roll


SQL> roll & rock
Rollback complete.

If you have 12c, go have a look at the doc for SQL_SCRIPT jobs

execute sql script from pl/sql

When you want to run a SQL script, you rather use sqlplus. If you want to run it within a PL/SQL stored procedure, you are screwed. You could redo the logic of sqlplus, this is what any decent IDE and code runnner does, and it’s a pile of work and you will get lot’s of bugs…

Another approach is to use sqlplus.

Wait! calling sqlplus from plsql? Well… why not. For instance via external job

  1. create a table with sql scripts
  2. create table t(id number primary key, text clob);
    insert into t(id, text) values (
      42, 'update emp set sal=sal*2 /* XMas gift */;'
    );
    

  3. create a shell script, /tmp/ddl, that spool and executes T.text content
  4. #!/bin/ksh
    ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1
    export ORACLE_HOME
    $ORACLE_HOME/bin/sqlplus -s -L scott/tiger <<EOF
      set hea off lin 2000 pages 0 trims on
      spo /tmp/$1.sql
      select text from t where id=$1;
      spo off
      @/tmp/$1
    EOF
    

  5. create a UNIX credential
  6. begin 
      dbms_scheduler.create_credential(
        'C','USER01','PW');
    end;
    /
    

  7. create and run an external job
  8. begin
      DBMS_SCHEDULER.CREATE_JOB(
        job_name             => 'J',
        job_type             => 'EXECUTABLE',
        number_of_arguments  => 1,
        job_action           => '/tmp/ddl',
        credential_name      => 'C');
      DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
        'J', 1, '42');
      DBMS_SCHEDULER.run_job('J');
    end;
    /
    

CREATE EXTERNAL JOB privilege is powerfull and your DBA won’t like it 😉

ANNOUNCEMENT: 12cR2 documentation is available

The doc docs.oracle.com/database/122 is available. Check new features and stay tuned.

Okay, just 12 for today : HIGH compressed index, case insensitive database with bound collation, partitioned external tables, AL32UTF8 default for new database, listagg overflow, VALIDATE_CONVERSION, approx_percentile, json_exists, flashback pluggable database, the SHARING clause, RAC streched clusters site definition and INACTIVE_ACCOUNT_TIME in profile.

Some I like more than others 🙂

Okay, one more : SQLPLUS HISTORY !

To shrink or to move

If you delete most of the rows in one table, you probably want to regain that space, to minimize IO and fragmentation.

If you delete most of the rows in most of the tables, you probably also want to resize the datafile to reduce storage and backup space.

In this case, you could move your data in a new tablespace

alter table t move tablespace newts;

I wrote about this here : 2006/08/tablespace-maintenance-tasks.html

There is also an option to SHRINK. In oldish (pre-12cR2) releases, this had the advantage of being an online operation. In 12.2, include the online keyword.

SHRINK requires ROW MOVEMENT. I don’t like the idea of having the rowid’s changing. You have also a bunch of restrictions, amoung others on materialized view fast refreshes and index organized tables.

I am impatient to get this 12cR2. According to the doc, during an alter table move, transactions run uninterrupted on the table

OTN Appreciation Day : Partition your table online !

#ThanksOTN @oraclebase

No, I am not talking about DBMS_REDEFINITION, where you get a kind of online feeling.

No, I don’t want to rename my table, rename my foreign keys, my primary key, my not-null-constraints, recreate my referential integrity, recompile my triggers.

I just want to partition a non-partitioned table.

ALTER TABLE EMP 
  MODIFY 
  PARTITION BY RANGE(HIREDATE)
  INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
  (PARTITION P1 VALUES LESS THAN (DATE '1980-01-01'))
  ONLINE;

This is going to save me a lot of Saturday work 🙂

You need 12.2 to run this.

What is the instance name?

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 ?

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

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 🙂

column width change in 12c

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.

powershell odbc sybase

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

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

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

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 🙂


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

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


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

DELETE is faster than TRUNCATE

Truncate is useful in some serial batch processing but it breaks the read-write consistency, generates stranges errors and results for running selects, and it needs DROP ANY TABLE when run over a table that you do not own.

But also, DELETE is faster in the following test case.

In 12c, you could have over one million partition in a table, but for the sake of the universe, I’ll try with 10000.


SQL> create table scott.t(x) 
  partition by range(x) 
  interval(1) 
  (partition values less than (0)) 
  as 
  select rownum 
  from dual 
  connect by level<10001;
SQL> select count(*) from scott.t;

  COUNT(*)
----------
     10000

The 10K rows table is created, each row is its partition


SQL> delete scott.t;

10000 rows deleted.

Elapsed: 00:00:04.02
SQL> rollback;

Rollback complete.

Not tuned or parallelized or whatever. It took 4 seconds for 10’000 rows. If you have one billion rows, it is doable in a few hours. But you better do it in chunks then.

Anyway, let’s truncate


SQL> truncate table scott.t;

Table truncated.

Elapsed: 00:05:19.24

Five minutes !!! to truncate that tiny table.

If you have one million partitions and underlying indexes and lobs, it will probably failed with out of memory errors after hours and a large impact on the dictionary, sysaux, undo.

The dictionary changes are here very slow.

permission issue due to one role

Most permissions issues are due to a missing role or privilege.

But in the following test case you need to revoke the right to get more privileges.


create table tt(x number);
create view v as select * from tt;
create role rw;
grant all on v to rw;

I’ve created a read-write role on a view. The owner of the role is the DBA, but the owner of the view is the application. Next release, the role may prevent an application upgrade


SQL> create or replace view v as select * from dual;
ORA-01720: grant option does not exist for 'SYS.DUAL'

Ok, if I drop the role, it works


SQL> drop role r;
Role dropped.
SQL> create or replace view v as select * from dual;
View created.

It is not always a good thing to grant privileges on a view, when you are not the owner of that view