2017/06/22

Not for APEX guru: Select All/Deselect All in APEX 5

Recently I realized that I need to implement Select All/Deselect All feature for some dynamically generated checklist. Google search overwhelmed me with a vast number of inconvenient and unclear instructions mostly applicable to early APEX versions. Nothing worked for me. And also zero good documentation, so pity.

But the solution does exist. Even more, it is pretty elegant, I mean its implementation of course.

So, it is very simple: got to Packaged Applications and install "CheckList Manager". Page 77 contains all you need, just explore it and replicate in your application.

Enjoy.

2017/06/04

How to load big text file into CLOB via SQL*Plus

From time to time a need to load a long text file with XML or even long PL/SQL script arises. And the only tool available is SQL*Plus.

As it appeared, it is very simply, if "include script" feature is used.
So, put your long text into some file, long_plsql.sql for example.
Here are steps to load it:

create table script_storage (
script_name varchar2(100),
script_content clob)
;

declare
  l_script clob := 
q'{
@@long_plsql.sql
}';
begin
  delete from script_storage where script_name='SCRIPT1';
  insert into script_storage (script_name,script_content) values
  ('SCRIPT1',l_script);
end;
/

and that's it!

Of course, if the file is longer than 32767 bytes, you will get an error:
PLS-00172: string literal too long.

But it can be easily overcome. Just divide the long text into parts shorter than 32767 bytes with the comment like this (usable for PL/SQL):
--}'||q'{
it will make Oracle concatenate big text from shorter parts. 

2017/04/11

SQL Monitoring Report History does contain predicate information

It can be extracted pretty easily using the following queries:

insert into plan_table
  (statement_id, plan_id, timestamp, remarks, operation, options, 
   object_node, object_name, object_alias, id, depth, position, 
   cost, cardinality, bytes, partition_start, partition_stop, 
   partition_id, distribution, cpu_cost, io_cost, temp_space, 
   access_predicates, filter_predicates, qblock_name)
  select
   r.key1 statement_id, 1 plan_id, sysdate timestamp, 
   'sql monitor history report' remarks,
   OPERATION, OPTIONS, OBJECT_NODE, OBJECT_NAME, OBJECT_ALIAS, 
   ID, DEPTH, POSITION, COST, CARDINALITY, BYTES, PARTITION_START,
   PARTITION_STOP, PARTITION_ID, DISTRIBUTION, CPU_COST, IO_COST, 
   TEMP_SPACE, ACCESS_PREDICATES, FILTER_PREDICATES, QBLOCK_NAME
    from dba_hist_reports r,
         dba_hist_reports_details d,
         xmltable('/report/sql_monitor_report/plan/operation' 
                  PASSING xmlparse(document d.report) COLUMNS 
                  OPERATION path '@name'
                 ,options path '@options'
                 ,OBJECT_NODE path 'node'
                 ,OBJECT_NAME path 'object'
                 ,object_alias path 'object_alias'
                 ,id path '@id'
                 ,depth path '@depth'
                 ,POSITION path '@pos'
                 ,cost path 'cost'
                 ,CARDINALITY path 'card'
                 ,bytes path 'bytes'
                 ,PARTITION_START PATH 'partition/@start'
                 ,PARTITION_STOP path 'partition/@stop'
                 ,PARTITION_ID path 'partition/@id'
                 ,DISTRIBUTION path 'distrib'
                 ,cpu_cost path 'cpu_cost'
                 ,io_cost path 'io_cost'
                 ,TEMP_SPACE path 'TEMP_SPACE'
                 ,ACCESS_PREDICATES path 'predicates[@type="access"]'
                 ,FILTER_PREDICATES path 'predicates[@type="filter"]'
                 ,QBLOCK_NAME path 'qblock'
                 ) x1
   WHERE r.component_name = 'sqlmonitor'
     and r.REPORT_ID = d.REPORT_ID
     and r.key1 = 'dhpn35zupm8ck'
     and r.report_id = 70954;
select * from table(dbms_xplan.display());


Just put relevant SQL_ID and REPORT_ID.


PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------
| Id  | Operation                              | Name    | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |         |       |       |   233 (100)|
|   1 |  SORT ORDER BY                         |         |     2 |   146 |   233   (1)|
|   2 |   NESTED LOOPS                         |         |     2 |   146 |   232   (0)|
|   3 |    NESTED LOOPS                        |         |    46 |   146 |   232   (0)|
|*  4 |     TABLE ACCESS BY INDEX ROWID BATCHED| TYPE$   |    46 |  1012 |   230   (0)|
|   5 |      INDEX FULL SCAN                   | I_TYPE2 |  4565 |       |     2   (0)|
|*  6 |     INDEX RANGE SCAN                   | I_OBJ3  |     1 |       |     1   (0)|
|*  7 |    TABLE ACCESS BY INDEX ROWID         | OBJ$    |     1 |    51 |     1   (0)|
---------------------------------------------------------------------------------------

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

   4 - filter(BITAND("T"."PROPERTIES",8388608)=8388608)
   6 - access("O"."OID$"="T"."TVOID")
       filter("O"."OID$" IS NOT NULL)
   7 - filter(SYSDATE@!-"O"."CTIME">.0007)




BTW, active SQL Monitor History Report also shows predicates on "Plan" tab with "Tabular" view turned on.

2017/03/14

Flashback to Guaranteed Restore Points with Flashback Logging Disabled does not require redo logs.


As it appeared, flashback to Guaranteed Restore Points with Flashback Logging Disabled does not require redo logs.

Indeed, restore point is made during database is mounted, and it does not need any recovery.
Otherwise, Oracle issues an error:

RMAN> shutdown abort

Oracle instance shut down

RMAN> startup mount

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area     415236096 bytes

Fixed Size                     2924976 bytes
Variable Size                272633424 bytes
Database Buffers             134217728 bytes
Redo Buffers                   5459968 bytes

RMAN> create restore point rp3 guarantee flashback database;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 03/14/2017 18:39:38
ORA-38784: Cannot create restore point 'RP3'.
ORA-38714: Instance recovery required.


Here is an experiment. The state of my database before that was the following. There was a cold RMAN backup, several guaranteed restore points and some changes made between them also stored in archive redo logs.
During the experiment, I just created one more backup and deleted all obsolete files. All archived redo logs happened to be obsolete. At the and I'm still able to flashback to any guaranteed restore points.

RMAN> backup as compressed backupset database;

Starting backup at 14-MAR-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=/u04/app/oracle/oradata/db12r102m/pdb1/example01.dbf
input datafile file number=00009 name=/u04/app/oracle/oradata/db12r102m/pdb1/sysaux01.dbf
input datafile file number=00008 name=/u04/app/oracle/oradata/db12r102m/pdb1/system01.dbf
input datafile file number=00010 name=/u04/app/oracle/oradata/db12r102m/pdb1/SAMPLE_SCHEMA_users01.dbf
channel ORA_DISK_1: starting piece 1 at 14-MAR-17
channel ORA_DISK_1: finished piece 1 at 14-MAR-17
piece handle=/u04/app/oracle/fast_recovery_area/DB12R102M/46C0EDA68AC32521E0530100007FF7AE/backupset/2017_03_14/o1_mf_nn                                                                                                          ndf_TAG20170314T175412_ddj4k4q6_.bkp tag=TAG20170314T175412 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/u04/app/oracle/oradata/db12r102m/sysaux01.dbf
input datafile file number=00001 name=/u04/app/oracle/oradata/db12r102m/system01.dbf
input datafile file number=00004 name=/u04/app/oracle/oradata/db12r102m/undotbs01.dbf
input datafile file number=00006 name=/u04/app/oracle/oradata/db12r102m/users01.dbf
channel ORA_DISK_1: starting piece 1 at 14-MAR-17
channel ORA_DISK_1: finished piece 1 at 14-MAR-17
piece handle=/u04/app/oracle/fast_recovery_area/DB12R102M/backupset/2017_03_14/o1_mf_nnndf_TAG20170314T175412_ddj4mj0f_.                                                                                                          bkp tag=TAG20170314T175412 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:15
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/u04/app/oracle/oradata/db12r102m/pdbseed/sysaux01.dbf
input datafile file number=00005 name=/u04/app/oracle/oradata/db12r102m/pdbseed/system01.dbf
channel ORA_DISK_1: starting piece 1 at 14-MAR-17
channel ORA_DISK_1: finished piece 1 at 14-MAR-17
piece handle=/u04/app/oracle/fast_recovery_area/DB12R102M/46C09FC3D5701FE6E0530100007F94C7/backupset/2017_03_14/o1_mf_nn                                                                                                          ndf_TAG20170314T175412_ddj4qq55_.bkp tag=TAG20170314T175412 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
Finished backup at 14-MAR-17

Starting Control File and SPFILE Autobackup at 14-MAR-17
piece handle=/u04/app/oracle/fast_recovery_area/DB12R102M/autobackup/2017_03_14/o1_mf_s_938627615_ddj4sj3c_.bkp comment=                                                                                                          NONE
Finished Control File and SPFILE Autobackup at 14-MAR-17


RMAN> delete obsolete redundancy 1;

using channel ORA_DISK_1
Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           1      14-MAR-17
  Backup Piece       1      14-MAR-17          /u04/app/oracle/fast_recovery_area/DB12R102M/46C0EDA68AC32521E0530100007FF7AE/backupset/2017_03_14/o1_mf_nnndf_TAG20170314T174036_ddj3qod4_.bkp
Backup Set           2      14-MAR-17
  Backup Piece       2      14-MAR-17          /u04/app/oracle/fast_recovery_area/DB12R102M/backupset/2017_03_14/o1_mf_nnndf_TAG20170314T174036_ddj3vwsn_.bkp
Backup Set           3      14-MAR-17
  Backup Piece       3      14-MAR-17          /u04/app/oracle/fast_recovery_area/DB12R102M/46C09FC3D5701FE6E0530100007F94C7/backupset/2017_03_14/o1_mf_nnndf_TAG20170314T174036_ddj40qz5_.bkp
Archive Log          1      14-MAR-17          /u04/app/oracle/fast_recovery_area/DB12R102M/archivelog/2017_03_14/o1_mf_1_38_ddj4794g_.arc
Backup Set           4      14-MAR-17
  Backup Piece       4      14-MAR-17          /u04/app/oracle/fast_recovery_area/DB12R102M/autobackup/2017_03_14/o1_mf_s_938626762_ddj426rf_.bkp
Archive Log          2      14-MAR-17          /u04/app/oracle/fast_recovery_area/DB12R102M/archivelog/2017_03_14/o1_mf_1_39_ddj489c1_.arc
Archive Log          3      14-MAR-17          /u04/app/oracle/fast_recovery_area/DB12R102M/archivelog/2017_03_14/o1_mf_1_40_ddj4dr1h_.arc
Archive Log          4      14-MAR-17          /u04/app/oracle/fast_recovery_area/DB12R102M/archivelog/2017_03_14/o1_mf_1_40_ddj4gw4l_.arc
Archive Log          6      14-MAR-17          /u04/app/oracle/fast_recovery_area/DB12R102M/archivelog/2017_03_14/o1_mf_1_39_ddj4gwn1_.arc
Archive Log          5      14-MAR-17          /u04/app/oracle/fast_recovery_area/DB12R102M/archivelog/2017_03_14/o1_mf_1_41_ddj4gwft_.arc
Backup Set           5      14-MAR-17
  Backup Piece       5      14-MAR-17          /u04/app/oracle/fast_recovery_area/DB12R102M/autobackup/2017_03_14/o1_mf_s_938627596_ddj4hf1o_.bkp

Do you really want to delete the above objects (enter YES or NO)? y
...
Deleted 11 objects


RMAN> alter database open;

Statement processed

RMAN> list copy;

specification does not match any datafile copy in the repository
specification does not match any control file copy in the repository
specification does not match any archived log in the repository

RMAN> list backup;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6       Full    189.07M    DISK        00:01:09     14-MAR-17
        BP Key: 6   Status: AVAILABLE  Compressed: YES  Tag: TAG20170314T175412
        Piece Name: /u04/app/oracle/fast_recovery_area/DB12R102M/46C0EDA68AC32521E0530100007FF7AE/backupset/2017_03_14/o1_mf_nnndf_TAG20170314T175412_ddj4k4q6_.bkp
  List of Datafiles in backup set 6
  Container ID: 3, PDB Name: PDB1
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  8       Full 2137619    14-MAR-17 /u04/app/oracle/oradata/db12r102m/pdb1/system01.dbf
  9       Full 2137619    14-MAR-17 /u04/app/oracle/oradata/db12r102m/pdb1/sysaux01.dbf
  10      Full 2137619    14-MAR-17 /u04/app/oracle/oradata/db12r102m/pdb1/SAMPLE_SCHEMA_users01.dbf
  11      Full 2137619    14-MAR-17 /u04/app/oracle/oradata/db12r102m/pdb1/example01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7       Full    405.53M    DISK        00:02:11     14-MAR-17
        BP Key: 7   Status: AVAILABLE  Compressed: YES  Tag: TAG20170314T175412
        Piece Name: /u04/app/oracle/fast_recovery_area/DB12R102M/backupset/2017_03_14/o1_mf_nnndf_TAG20170314T175412_ddj4mj0f_.bkp
  List of Datafiles in backup set 7
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 2137631    14-MAR-17 /u04/app/oracle/oradata/db12r102m/system01.dbf
  3       Full 2137631    14-MAR-17 /u04/app/oracle/oradata/db12r102m/sysaux01.dbf
  4       Full 2137631    14-MAR-17 /u04/app/oracle/oradata/db12r102m/undotbs01.dbf
  6       Full 2137631    14-MAR-17 /u04/app/oracle/oradata/db12r102m/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8       Full    157.07M    DISK        00:00:44     14-MAR-17
        BP Key: 8   Status: AVAILABLE  Compressed: YES  Tag: TAG20170314T175412
        Piece Name: /u04/app/oracle/fast_recovery_area/DB12R102M/46C09FC3D5701FE6E0530100007F94C7/backupset/2017_03_14/o1_mf_nnndf_TAG20170314T175412_ddj4qq55_.bkp
  List of Datafiles in backup set 8
  Container ID: 2, PDB Name: PDB$SEED
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  5       Full 1824920    23-JAN-17 /u04/app/oracle/oradata/db12r102m/pdbseed/system01.dbf
  7       Full 1824920    23-JAN-17 /u04/app/oracle/oradata/db12r102m/pdbseed/sysaux01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
9       Full    17.20M     DISK        00:00:02     14-MAR-17
        BP Key: 9   Status: AVAILABLE  Compressed: NO  Tag: TAG20170314T175838
        Piece Name: /u04/app/oracle/fast_recovery_area/DB12R102M/autobackup/2017_03_14/o1_mf_s_938627615_ddj4sj3c_.bkp
  SPFILE Included: Modification time: 10-MAR-17
  SPFILE db_unique_name: DB12R102M
  Control File Included: Ckp SCN: 2137631      Ckp time: 14-MAR-17

RMAN> shutdown immediate

using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down

RMAN> startup mount

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area     415236096 bytes

Fixed Size                     2924976 bytes
Variable Size                272633424 bytes
Database Buffers             134217728 bytes
Redo Buffers                   5459968 bytes

RMAN> flashback database to restore point rp2;

Starting flashback at 14-MAR-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK


starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished flashback at 14-MAR-17

RMAN> alter database open resetlogs;

Statement processed

RMAN>

2017/01/18

MERGE statement can fail with ORA-00001: unique constraint violated

How can MERGE statement fail with ORA-00001: unique constraint violated if all other things have been done right? I mean both source and target tables have primary key constraints and all columns from that constraints are mentioned in ON clause.
There is a tiny window of possibility. Suppose the first session inserts a row into source table with commit, then the second session starts long-running MERGE into the target table. While it is running, the first session inserts the same row into target table and commits it. When the second session reaches that new row, it gets ORA-00001.
When the second session started MERGE statement, the first thing it did was that it outer-joined the source and target tables. At that moment questioned row existed in source table, but did not exist in target table (or invisible due to uncommited transaction), so, MERGE decided to put the row into insert branch of MERGE statement.

Here is a test-case for reproducing this effect.

First session:
--preparation
drop table t1 purge;
drop table t2 purge;

create table t1 (
 OBJECT_ID     NUMBER,
 OBJECT_NAME   VARCHAR2(128),
 OBJECT_TYPE   VARCHAR2(23),
 filler        char(1000) default 'X'
 );

insert into t1 select OBJECT_ID,OBJECT_NAME,OBJECT_TYPE, 'Y' from all_objects where rownum < 5000;
create table t2 as select * from t1 where 1=2;
alter table t1 add constraint t1_pk primary key (object_id);
alter table t2 add constraint t2_pk primary key (object_id);

--start of the experiment
insert into t1 (object_id, object_name, object_type) values (100000,'test_obj', 'test_type');
commit;
insert into t2 (object_id, object_name, object_type) values (100000,'test_obj', 'test_type');

Second session:
merge into t2 trg
 using t1 src
 on (trg.object_id=src.object_id)
 when matched then update set
 trg.object_name=src.object_name
 when not matched then insert
 (trg.object_id,trg.object_name, trg.object_type)
 values

 (src.object_id,src.object_name,src.object_type);

First session:
commit;

Second session:
merge into t2 trg
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.T2_PK) violated

2017/01/12

Swallowing exceptions

Have you heard about Oracle can hide some exceptions raised from user code?
One well-known situation is described here.

But there is one more case I've just found.
Try this code:

create or replace procedure test1 is
begin
  raise no_data_found;
end;
/
begin test1; end;
/
begin
      dbms_scheduler.create_job(job_name        => 'T1',
                                job_type        => 'STORED_PROCEDURE',
                                job_action      => 'TEST1',
                                enabled         => TRUE,
                                start_date      => systimestamp,
                                auto_drop       => true);
END;
/
select ACTUAL_START_DATE, LOG_DATE,STATUS,ERRORS from user_scheduler_job_run_details where job_name='T1';

The final query shows STATUS='SUCCEEDED' even though call of test1() raises a no_data_found exception.

Btw, if job is defined slightly differently

  job_type        => 'PLSQL_BLOCK',
  job_action      => 'BEGIN raise no_data_found; end;',

all works as expected, i.e. STATUS='FAILED'.

Checked on 11.2.0.3/4 and 12.1.0.2.

UPD, see MOS doc 1331778.1 for more details.

2016/12/13

How many times is pl/sql function called

Recently, a query caught my eye with some strange overuse of resources (LIO in that case). As usual, there were several calls of pretty complex pl/sql user-defined functions. Also an analytic function row_numbe() was used there to eliminate possible row duplicates. The query was similar to the following one:

select id, val
  from (select id, val, row_number() over (partition by id order by val) as n
    from (select id,
                 tc1$p1.test1(id) as val
            from tc1$t1
         )
       ) where n=1;

As it appeared, for the table tc1$t1, which contains N unique values of id column, the function tc1$p1.test1(id) is called  almost 2*N times.

It is not clear what is going on behind the scene. For example, if I use trace event 10032 to get some sorting statistics, I can see no correlation between the number of comparisons ("Total number of comparisons performed" statistic) and the number of function calls while adding some duplicates or changing clustering of the data in tc1$t1 (loading the table with different order by clause).

For those who hits this issue, a very simple workaround exists: just wrap function call with subquery from dual like in the following example:

select id, val
  from (select id, val, row_number() over (partition by id order by val) as n
    from (select id,
                 (select tc1$p1.test1(id) from dual) as val
            from tc1$t1
         )
       ) where n=1;

Here is a script to reproduce the described behavior. I've checked it on 11.2.0.4 and 12.1.0.2, the output was the same:

===========================================================
How many times is user-defined PL/SQL function (UDF) called
for table tc1$t1 with                   100000 rows in it
===========================================================
#1 Using analytic function
===========================================================
Counter (start): 0
  1  select id, val
  2    from (select id, val, row_number() over (partition by id order by val) as n
  3      from (select id,
  4                   tc1$p1.test1(id) as val
  5              from tc1$t1
  6           )
  7*        ) where n=1
Counter (end), UDF function calls: 200040
===========================================================
#2 Without analytic function
Counter (start): 0
  1  SELECT id,
  2         tc1$p1.test1(id) AS val
  3*   FROM tc1$t1
Counter (end), UDF function calls: 100000
===========================================================
#3 WA for analytic function
Counter (start): 0
  1  select id, val
  2    from (select id, val, row_number() over (partition by id order by val) as n
  3      from (select id,
  4                   (select tc1$p1.test1(id) from dual) as val
  5              from tc1$t1
  6           )
  7*        ) where n=1
Counter (end), UDF function calls: 100000
===========================================================