An ORA-07445 troubleshooting case

When somebody adopts fresh Oracle releases, she or he will certainly hit some fresh bug, which no one has registered or described before.
And there is no choice except trying to dig deeply into available trace information to get some clue of how to deal with this situation. I mean a dev team is not going to wait until SR will be resolved in some indefinite future.

So, it was a huge query with more than 5 page downs, and it failed to work. MOS search gave nothing for ORA-07445 first argument as well as Google search.
But still, it is not the time for giving up. Oracle kernel error stack actually can provide a lot of interesting stuff.

Here is an excerpt from ORA-07445 incident trace file:

----- Call Stack Trace -----
calling              call     entry                
location             type     point                
-------------------- -------- -------------------- 
skdstdst()+45        call     kgdsdst()            
                                                   
                                                   
...
__sighandler()       call     sslsshandler()       
                                                   
                                                   
                                                   
kkoordi()+670        signal   __sighandler()       
                                                   
                                                   
kkocnp()+279         call     kkoordi()            
                                                   
                                                   
kkooqb()+1882        call     kkocnp()             
                                                   
                                                   
kkoqbc()+2438        call     kkooqb()             
                                                   
                                                   
apakkoqb()+182       call     kkoqbc()             
                                                   
                                                   
apaqbdDescendents()  call     apakkoqb()           
+488                                               
                                                   
apaqbd()+135         call     apaqbdDescendents()  
                                                   
                                                   
kkqcbyGetCost()+749  call     apaqbd()             
                                                   
                                                   
kkqcbydrvPreUA()+29  call     kkqcbyGetCost()      
89                                                 
                                                   
qksqbApplyToQbcLoc(  call     kkqcbydrvPreUA()     
)+635                                              
                                                   
qksqbApplyToQbcLoc(  call     qksqbApplyToQbcLoc(  
)+987                         )                    
...

A function kkoordi() is that which failed. Going through the stack, some human-readable names can be found. And that is what can help to get in the right way. Just two names gave a couple of idea of what to do next.
kkqcbyGetCost() - is certainly about Cost-Based Optimizer. And it means that the error must be reproducible by EXPLAIN PLAN statement, which was easily confirmed.
qksqbApplyToQbcLoc() - seems to apply something to a query block, that block which caused the error! So, turning on 10053 trace event and executing EXPLAIN PLAN one more time provides the second major piece of information. A tail of 10053 trace-file showed the query block (subquery) which caused the error while costing its different join orders.

So simple in this case! And it provided all necessary information for developers to rewrite just one subquery and eliminate the issue.

APPEND hint placement

It is pretty surprising,  that APPEND hint can be placed not only after INSERT keyword but also after SELECT keyword of a subquery.

Check these execution plans:

SQL> create table tab1 as select * from dba_objects;

Table TAB1 created.

SQL> create table tab2 as select * from tab1 where 1=2;

Table TAB2 created.

SQL> explain plan for
  2* insert /*+ append */ into tab2 select * from tab1;
Explained.

SQL> @geteplan

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------
Plan hash value: 888952673

----------------------------------------------------------------------
| Id  | Operation                        | Name | Rows  | Cost (%CPU)|
----------------------------------------------------------------------
|   0 | INSERT STATEMENT                 |      | 98784 |   350   (1)|
|   1 |  LOAD AS SELECT                  | TAB2 |       |            |
|   2 |   OPTIMIZER STATISTICS GATHERING |      | 98784 |   350   (1)|
|   3 |    TABLE ACCESS FULL             | TAB1 | 98784 |   350   (1)|
----------------------------------------------------------------------

SQL> explain plan for
  2* insert into tab2 select * from tab1;
Explained.

SQL> @geteplan

PLAN_TABLE_OUTPUT
--------------------------------------------------------------
Plan hash value: 2211052296

--------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Cost (%CPU)|
--------------------------------------------------------------
|   0 | INSERT STATEMENT         |      | 98784 |   350   (1)|
|   1 |  LOAD TABLE CONVENTIONAL | TAB2 |       |            |
|   2 |   TABLE ACCESS FULL      | TAB1 | 98784 |   350   (1)|
--------------------------------------------------------------

SQL> explain plan for
  2* insert into tab2 select /*+ append */ * from tab1;
Explained.

SQL> @geteplan

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------
Plan hash value: 888952673

----------------------------------------------------------------------
| Id  | Operation                        | Name | Rows  | Cost (%CPU)|
----------------------------------------------------------------------
|   0 | INSERT STATEMENT                 |      | 98784 |   350   (1)|
|   1 |  LOAD AS SELECT                  | TAB2 |       |            |
|   2 |   OPTIMIZER STATISTICS GATHERING |      | 98784 |   350   (1)|
|   3 |    TABLE ACCESS FULL             | TAB1 | 98784 |   350   (1)|
----------------------------------------------------------------------

And this fact is documented in a quite unusual place. See 20.4.2.3.1 Serial Mode Inserts with SQL Statements of Admin Guide for more details.

Bind variable peeking. Part 2.

As Jonathan Lewis wrote: "bind variable peeking is always done, even if histograms are not generated", so be prepared, your plan can be changed without any warning.

Let me show the following example:

create table tab1 as select * from all_objects;
create unique index tab1_obj_id on tab1(object_id);
create index tab1_date_obj_id on tab1(DATA_OBJECT_ID,object_name,object_id);
exec dbms_stats.gather_table_stats(user, 'TAB1', cascade=> true, method_opt => 'FOR ALL COLUMNS SIZE 1');

It is obvious that if a query has filter object_id=:bind, the first index tab1_obj_id has to be chosen by CBO.

explain plan for
select count(1) from tab1 where object_id=:a1 and DATA_OBJECT_ID=:a2;

Plan hash value: 17355806
--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     1 |     7 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |             |     1 |     7 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| TAB1        |     1 |     7 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | TAB1_OBJ_ID |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   2 - filter("DATA_OBJECT_ID"=TO_NUMBER(:A2))
   3 - access("OBJECT_ID"=TO_NUMBER(:A1))

But bind peeking can lead to some unexpected result. An unlucky bind value, which is NULL creates lots of confusion and also sometimes very inefficient plan.

variable a1 number

variable a2 number

begin

  :a1:=29;

  :a2:=null;

end;

/

select /*2*/ count(1) from tab1 where object_id=:a1 and DATA_OBJECT_ID=:a2;

select * from table(dbms_xplan.display_cursor(null, null, 'LAST ALLSTATS +peeked_binds'));

SQL_ID  25bxjz9jgv53y, child number 0

-------------------------------------

select /*2*/ count(1) from tab1 where object_id=:a1 and

DATA_OBJECT_ID=:a2

Plan hash value: 683818241

--------------------------------------------------------------------------------------

| Id  | Operation         | Name             | Starts | E-Rows | A-Rows |   A-Time   |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |                  |      1 |        |      1 |00:00:00.01 |

|   1 |  SORT AGGREGATE   |                  |      1 |      1 |      1 |00:00:00.01 |

|*  2 |   INDEX RANGE SCAN| TAB1_DATA_OBJ_ID |      1 |      1 |      0 |00:00:00.01 |

--------------------------------------------------------------------------------------

Peeked Binds (identified by position):

--------------------------------------

   1 - (NUMBER): 29

   2 - (NUMBER): (null)

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("DATA_OBJECT_ID"=:A2 AND "OBJECT_ID"=:A1)

       filter("OBJECT_ID"=:A1)

One more execution with all non-null binds just reuses already parsed plan. With real-world data, it might get stuck forever.

begin

  :a1:=29;

  :a2:=29;

end;

/

select /*2*/ count(1) from tab1 where object_id=:a1 and DATA_OBJECT_ID=:a2;

select * from table(dbms_xplan.display_cursor(null, null, 'LAST ALLSTATS +peeked_binds'));

SQL_ID  25bxjz9jgv53y, child number 0

-------------------------------------

select /*2*/ count(1) from tab1 where object_id=:a1 and

DATA_OBJECT_ID=:a2

Plan hash value: 683818241

----------------------------------------------------------------------------------------------

| Id | Operation         | Name             | Starts | E-Rows | A-Rows |   A-Time  | Buffers |

----------------------------------------------------------------------------------------------

|  0 | SELECT STATEMENT  |                  |      1 |        |      1 |00:00:00.01|       3 |

|  1 |  SORT AGGREGATE   |                  |      1 |      1 |      1 |00:00:00.01|       3 |

|* 2 |   INDEX RANGE SCAN| TAB1_DATA_OBJ_ID |      1 |      1 |      1 |00:00:00.01|       3 |

----------------------------------------------------------------------------------------------

Peeked Binds (identified by position):

--------------------------------------

   1 - (NUMBER): 29

   2 - (NUMBER): (null)

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("DATA_OBJECT_ID"=:A2 AND "OBJECT_ID"=:A1)

       filter("OBJECT_ID"=:A1)

A little variation of this case, OBJECT_TYPE VARCHAR2 is used instead of DATA_OBJECT_ID NUMBER:

create table tab1 as select * from all_objects;

create unique index tab1_obj_id on tab1(object_id);

create index tab1_type_obj_id on tab1(object_type,object_name,object_id);

exec dbms_stats.gather_table_stats(user, 'TAB1', cascade=> true, method_opt => 'FOR ALL COLUMNS SIZE 1'

Unlucky bind value:

begin

  :a1:=29;

  :a2:=null;

end;

/

select /*2*/ count(1) from tab1 where object_id=:a1 and object_type=:a2;

select * from table(dbms_xplan.display_cursor(null, null, 'LAST ALLSTATS +peeked_binds'));

SQL_ID  6buxk2515azrt, child number 0

-------------------------------------

select /*2*/ count(1) from tab1 where object_id=:a1 and object_type=:a2

Plan hash value: 3906737377

--------------------------------------------------------------------------------------

| Id  | Operation         | Name             | Starts | E-Rows | A-Rows |   A-Time   |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |                  |      1 |        |      1 |00:00:00.01 |

|   1 |  SORT AGGREGATE   |                  |      1 |      1 |      1 |00:00:00.01 |

|*  2 |   INDEX RANGE SCAN| TAB1_TYPE_OBJ_ID |      1 |      1 |      0 |00:00:00.01 |

--------------------------------------------------------------------------------------

Peeked Binds (identified by position):

--------------------------------------

   1 - (NUMBER): 29

   2 - (VARCHAR2(30), CSID=873): (null)

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("OBJECT_TYPE"=:A2 AND "OBJECT_ID"=:A1)

       filter("OBJECT_ID"=:A1)

But when it is time to lucky one, the picture is much better:

begin

  :a1:=29;

  :a2:='CLUSTER';

end;

/

select /*2*/ count(1) from tab1 where object_id=:a1 and object_type=:a2;

select * from table(dbms_xplan.display_cursor(null, null, 'LAST ALLSTATS +peeked_binds'));

SQL_ID  6buxk2515azrt, child number 1

-------------------------------------

select /*2*/ count(1) from tab1 where object_id=:a1 and object_type=:a2

Plan hash value: 17355806

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Starts | E-Rows | A-Rows | Buffers |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |      1 |        |      1 |       3 |
|   1 |  SORT AGGREGATE              |             |      1 |      1 |      1 |       3 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| TAB1        |      1 |      1 |      1 |       3 |
|*  3 |    INDEX UNIQUE SCAN         | TAB1_OBJ_ID |      1 |      1 |      1 |       2 |
-----------------------------------------------------------------------------------------

Peeked Binds (identified by position):

--------------------------------------

   1 - (NUMBER): 29

   2 - (VARCHAR2(30), CSID=873): 'CLUSTER'

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter("OBJECT_TYPE"=:A2)

   3 - access("OBJECT_ID"=:A1)

Please notice, the second execution resulted in creating new child cursor number 1.

Look at this comment, which is about the following claim "part of the identifying characteristic for a child cursor is the memory allocation for the input bind variables and, at some layer in the library code, character columns are allowed to have 4 different allocation sizes, namely 32, 128, 2000, or 4000 bytes". So, zero-size bind seems adds one more allocation size and identifies the first child cursor number 0 as that not suitable for execution with non-zero length bind.

Upd.: one more piece of information is here. 

SQLPATH environment variable length limit

I heavily use lots of sql scripts spread across several folders. Recently I started some reorganization of that directories to meet new needs of SQLcl (I'm testing it now in order to put it in my tool set). However some my scripts did not work properly with SQLcl, so I had to return to SQL*Plus for a while. But when I started SQL*Plus, I found that it could find neither login.sql nor any other scripts from my big library anymore.

It appeared almost accidentally that SQL*Plus can not handle SQLPATH variable which is longer than 260 characters. In contrast, SQLcl did not show any problem with the even longer value of SQLPATH.

Wrong result when hierarchical query uses rownum filter and subquery filter over dblink

Here is a test case to reproduce the issue. It uses well-known EMP and DEPT tables. If you need a script to create and populate it, see sql-script at the end of this article.
Additionally, loopback database link is needed:
create database link dbloop connect to USERNAME identified by PASSWORD using 'localhost:1521/SERVICENAME';

And here is a result:

SQL> select
  2    LPAD(' ',2*(LEVEL-1)) || ename ename, job
  3  from emp
  4  where
  5      deptno not in (select deptno from dept where loc='NEW YORK')
  6  and rownum=1
  7  start with mgr is null
  8  connect by prior empno=mgr;

ENAME           JOB
--------------- ---------
  JONES         MANAGER

SQL> select
  2    LPAD(' ',2*(LEVEL-1)) || ename ename, job
  3  from emp
  4  where
  5      deptno not in (select /*+ driving_site(dept) */ deptno from dept@dbloop where loc='NEW YORK')
  6  and rownum=1
  7  start with mgr is null
  8  connect by prior empno=mgr;

no rows selected

So, the only difference is that in the second query NOT IN subquery uses a table DEPT  behind dblink and DRIVING_SITE hint makes Oracle execute the whole statement remotely.

The execution plan for the second query gives some clue about the root cause of the wrong result:

Plan hash value: 1623810261

--------------------------------------------------------------------
| Id  | Operation              | Name | Cost (%CPU)| Inst   |IN-OUT|
--------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE|      |     3  (34)|        |      |
|   1 |  COUNT                 |      |            |        |      |
|*  2 |   FILTER               |      |            |        |      |
|   3 |    REMOTE              | EMP  |            |      ! | R->S |
|*  4 |    TABLE ACCESS FULL   | DEPT |     3   (0)| DB12C~ |      |
--------------------------------------------------------------------

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

   2 - filter( NOT EXISTS (SELECT 0 FROM "DEPT" "A2" WHERE 
              "A2"."LOC"='NEW YORK' AND LNNVL("A2"."DEPTNO"<>:B1)))
   4 - filter("A2"."LOC"='NEW YORK' AND LNNVL("A2"."DEPTNO"<>:B1))

Remote SQL Information (identified by operation id):
----------------------------------------------------

   3 - SELECT "MGR","EMPNO","DEPTNO","JOB","ENAME",PRIOR "EMPNO",LEVEL 
       FROM "EMP" "A7" WHERE ROWNUM=1 START WITH ("MGR" IS NULL) 
  CONNECT BY ("MGR"=PRIOR "EMPNO") (accessing '!' )


Note
-----
   - fully remote statement
   - dynamic statistics used: dynamic sampling (level=2)

It shows that ROWNUM=1 predicate is executed on step 3 and before NOT IN predicate on step 2, which is wrong.

Checked on 11.2.0.3, 12.1.0.2, 12.2.0.1.

P.S. I have not managed to find anything matching on MOS.

=============================================================

create table dept(  
  deptno     number(2,0),  
  dname      varchar2(14),  
  loc        varchar2(13),  
  constraint pk_dept primary key (deptno)  
);
create table emp(  
  empno    number(4,0),  
  ename    varchar2(10),  
  job      varchar2(9),  
  mgr      number(4,0),  
  hiredate date,  
  sal      number(7,2),  
  comm     number(7,2),  
  deptno   number(2,0),  
  constraint pk_emp primary key (empno),  
  constraint fk_deptno foreign key (deptno) references dept (deptno)  
);

insert into DEPT (DEPTNO, DNAME, LOC) values(10, 'ACCOUNTING', 'NEW YORK');
insert into dept  values(20, 'RESEARCH', 'DALLAS');
insert into dept  values(30, 'SALES', 'CHICAGO');
insert into dept  values(40, 'OPERATIONS', 'BOSTON');
insert into emp   values(7839, 'KING', 'PRESIDENT',  null,   to_date('17-11-1981','dd-mm-yyyy'),5000, null, 10  );
insert into emp   values(7698, 'BLAKE', 'MANAGER',   7839,   to_date('1-5-1981','dd-mm-yyyy'),  2850, null, 30  );
insert into emp   values(7782, 'CLARK', 'MANAGER',   7839,   to_date('9-6-1981','dd-mm-yyyy'),  2450, null, 10  );
insert into emp   values(7566, 'JONES', 'MANAGER',   7839,   to_date('2-4-1981','dd-mm-yyyy'),  2975, null, 20  );
insert into emp   values(7788, 'SCOTT', 'ANALYST',   7566,   to_date('30-MAR-87','dd-mm-rr'),   3000, null, 20  );
insert into emp   values(7902, 'FORD', 'ANALYST',    7566,   to_date('3-12-1981','dd-mm-yyyy'), 3000, null, 20  );
insert into emp   values(7369, 'SMITH', 'CLERK',     7902,   to_date('17-12-1980','dd-mm-yyyy'),800,  null, 20  );
insert into emp   values(7499, 'ALLEN', 'SALESMAN',  7698,   to_date('20-2-1981','dd-mm-yyyy'), 1600, 300, 30  );
insert into emp   values(7521, 'WARD', 'SALESMAN',   7698,   to_date('22-2-1981','dd-mm-yyyy'), 1250, 500, 30  );
insert into emp   values(7654, 'MARTIN', 'SALESMAN', 7698,   to_date('28-9-1981','dd-mm-yyyy'), 1250, 1400, 30  );
insert into emp   values(7844, 'TURNER', 'SALESMAN', 7698,   to_date('8-9-1981','dd-mm-yyyy'),  1500, 0, 30  );
insert into emp   values(7876, 'ADAMS', 'CLERK',     7788,   to_date('21-APR-87', 'dd-mm-rr'),  1100, null, 20  );
insert into emp   values(7900, 'JAMES', 'CLERK',     7698,   to_date('3-12-1981','dd-mm-yyyy'), 950,  null, 30  );
insert into emp   values(7934, 'MILLER', 'CLERK',    7782,   to_date('23-1-1982','dd-mm-yyyy'), 1300, null, 10  );
commit;

select
  LPAD(' ',2*(LEVEL-1)) || ename ename, job
from emp
where
    deptno not in (select deptno from dept where loc='NEW YORK')
and rownum=1
start with mgr is null
connect by prior empno=mgr;

select 
  LPAD(' ',2*(LEVEL-1)) || ename ename, job
from emp
where
    deptno not in (select /*+ driving_site(dept) */ deptno from dept@dbloop where loc='NEW YORK')
and rownum=1
start with mgr is null
connect by prior empno=mgr;

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.

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. 

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.

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>