FGAC and new partition features

It is better to start with minor note then to wait for long without any. Thus my first post in this blog would be about very specific problem.

I have spent some time investigating it before get to the bottom of this miscommunication between SQL statement improving and Fine-Grain Access Control (FGAC). Thus I believe it can be interesting for audience too. Below is the test case that allows to easily reproduce the issue.

Create partitioned table with non-standard partition names.

CREATE TABLE TESTONFGAC
( ID NUMBER(5,0) NOT NULL ENABLE,
PartitionKey NUMBER(5,0) DEFAULT 0 NOT NULL ENABLE,
COMENT VARCHAR2(255)
) TABLESPACE "USERS"
PARTITION BY RANGE (PartitionKey)
(PARTITION "1" VALUES LESS THAN (1) SEGMENT CREATION IMMEDIATE TABLESPACE "USERS" ,
PARTITION "2" VALUES LESS THAN (2) SEGMENT CREATION IMMEDIATE TABLESPACE "USERS" ,
PARTITION "3" VALUES LESS THAN (3) SEGMENT CREATION IMMEDIATE TABLESPACE "USERS" )
/

Create FGAC policy function. For our case it does not matter how complicate would it be, thus create just dummy one.

CREATE OR REPLACE FUNCTION TEST_FGA (object_schema IN VARCHAR2, object_name VARCHAR2) RETURN VARCHAR2
IS
BEGIN
RETURN '1=1';
END;
/

Create the policy on the newly created table using the function

exec DBMS_RLS.ADD_POLICY ( 'ETERPANI', 'TESTONFGAC', 'test_privacy', 'ETERPANI', 'TEST_FGA');

Everything before was just preparations. Now we are running the relatively new (since Oracle 10g) syntaxes.

DELETE FROM ETERPANI.TESTONFGAC PARTITION ("3") WHERE ID=3;

This reasonable simple statement fails with error “ORA-28113: policy predicate has error”. The further diagnostic can be found on oracle trace file.

*** 2011-06-25 01:23:27.188
-------------------------------------------------------------
Error information for ORA-28113:
Logon user : ETERPANI
Table/View : ETERPANI.TESTONFGAC
Policy name : TEST_PRIVACY
Policy function: ETERPANI.TEST_FGA
RLS view :
SELECT "ID","PARTITIONKEY","COMENT" FROM "ETERPANI"."TESTONFGAC" PARTITION (3) "TESTONFGAC" WHERE (1=1)
ORA-14108: illegal partition-extended table name syntax
-------------------------------------------------------------

As we see double quotes around partition name magically disappears.
In case of STANDARD partition naming (e.g. FIRST , SECOND e.t.c) it does not matter but if you choose lowercase naming or names starting from numbers you can get the ORA-28113.

Fancy automatic database schema generation tools frequently create objects in lower-case in this case it can be fatal.

The expected conclusion “little wit in the head makes much work for the feet” – keep your solution standard and simple (if you can).

PARTITION HASH SUBQUERY Oddity

Having spent the last few months working in an environment where CURSOR_SHARING = FORCE I have seen a number of interesting issues, but the one I’ll focus on below has particularly grabbed my attention.

CURSOR_SHARING = FORCE is set at a database level for the example below and as you’ll see the use of a hint changes the output of the query… Clearly a bug.

SQL> select --+ query_01
  2         id, status, state
  3    from a
  4   where id in (select a.childid
  5  	             from b
  6  	            where b.parentid = '0123456789ABCDEF'
  7  	              and b.relation = 'TEST01');

no rows selected

But…

SQL> select --+ cursor_sharing_exact query_02
  2  	    id, status, state
  3    from a
  4   where id in (select a.childid
  5  	             from b
  6  	            where b.parentid = '0123456789ABCDEF'
  7  	              and b.relation = 'TEST01');

ID               STATUS                         S
---------------- ------------------------------ -
999030550440700D COMPLETE                       U
9990306350106Z07 COMPLETE                       U
9991304333406Q0J COMPLETE                       U
9991304334706P0T COMPLETE                       U
9992604330506Q0M COMPLETE                       U

* For reasons of confidentiality the table names, column names and data have been modified.

OK, so this isn’t anything too radical. We all know that wrong results can happen. They make us scared, but if we catch them and can work around the error then we can return to our happy lives.

I’ve skipped over a lot of the digging around I did get to this point, but for those that are interested the other workarounds that produced the correct results are:

SQL> select --+ query_03
  2         id, status, state
  3    from a
  4   where id in (select to_char(a.childid)
  5  	             from b
  6  	            where b.parentid = '0123456789ABCDEF'
  7  	              and b.relation = 'TEST01');

ID               STATUS                         S
---------------- ------------------------------ -
999030550440700D COMPLETE                       U
9990306350106Z07 COMPLETE                       U
9991304333406Q0J COMPLETE                       U
9991304334706P0T COMPLETE                       U
9992604330506Q0M COMPLETE                       U

And…

SQL> select --+ opt_param ('_subquery_pruning_enabled','false') query_04
  2         id, status, state
  3    from a
  4   where id in (select a.childid
  5  	             from b
  6  	            where b.parentid = '0123456789ABCDEF'
  7  	              and b.relation = 'TEST01');

ID               STATUS                         S
---------------- ------------------------------ -
999030550440700D COMPLETE                       U
9990306350106Z07 COMPLETE                       U
9991304333406Q0J COMPLETE                       U
9991304334706P0T COMPLETE                       U
9992604330506Q0M COMPLETE                       U

I’d found the workarounds used in query_03 and query_04 first (in that order) and looking at the execution plans for all of all the queries there are 2 distinct types of plan:

Query 01 and 02 have the following plan:

----------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |             |       |       |  1708 (100)|          |       |       |
|*  1 |  HASH JOIN RIGHT SEMI     |             |     1 |   104 |  1708   (2)| 00:00:21 |       |       |
|*  2 |   INDEX RANGE SCAN        | IX_PARENTID |     1 |    75 |     2   (0)| 00:00:01 |       |       |
|   3 |   PARTITION RANGE ALL     |             | 14478 |   410K|  1705   (1)| 00:00:21 |     1 |    54 |
|   4 |    PARTITION HASH SUBQUERY|             | 14478 |   410K|  1705   (1)| 00:00:21 | KEY(S | KEY(S |
|   5 |     TABLE ACCESS FULL     | A           | 14478 |   410K|  1705   (1)| 00:00:21 |     1 |   108 |
----------------------------------------------------------------------------------------------------------

And Query 02 and 04 have the following plan:

-----------------------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |  1708 (100)|          |       |       |
|*  1 |  HASH JOIN RIGHT SEMI|             |     1 |   104 |  1708   (2)| 00:00:21 |       |       |
|*  2 |   INDEX RANGE SCAN   | IX_PARENTID |     1 |    75 |     2   (0)| 00:00:01 |       |       |
|   3 |   PARTITION RANGE ALL|             | 14478 |   410K|  1705   (1)| 00:00:21 |     1 |    54 |
|   4 |    PARTITION HASH ALL|             | 14478 |   410K|  1705   (1)| 00:00:21 |     1 |     2 |
|   5 |     TABLE ACCESS FULL| A           | 14478 |   410K|  1705   (1)| 00:00:21 |     1 |   108 |
-----------------------------------------------------------------------------------------------------

The key difference being the PARTITION HASH SUBQUERY vs PARTITION HASH ALL.

My view is that of the above workarounds the most appropriate depends on the number of subpartitions per partition, if you the query filters on the subpartition key and the number of distinct queries that will be hitting the database. However, this isn’t a post to discuss cursor reuse…

The above information is really just for completeness and context, but from this point on I’ll only be focusing on query_01 and query_02, and specifically on the recursive queries that run as part of the PARTITION HASH SUBQUERY operation.

The test case that follows does not give wrong results, but it does highlight the behaviour I want to draw attention to. I’m interested in the behaviour simply because it does not match with what I would expect, and having discussed it with others and tested other recursive queries, I’m drawing the conclusion that it must be a bug…

Test Case


set pages 300 lines 150 echo on
col string for a30

create user test identified by pass_word01
/

grant create session
    , create table
    , unlimited tablespace
    , alter session
   to test
/

conn test/pass_word01

create table part_tab (
       rec_date date
     , id number 
     , pad varchar2(300)
     , status number(1) default 0
     )
  partition by range (rec_date) 
    subpartition by hash (id) 
    subpartition template (
       subpartition a
     , subpartition b) (
       partition part_20100101 values less than (to_date('02-01-2010','DD-MM-YYYY'))
     , partition part_20100102 values less than (to_date('03-01-2010','DD-MM-YYYY'))
     , partition part_20100103 values less than (to_date('04-01-2010','DD-MM-YYYY'))
     , partition part_20100104 values less than (to_date('05-01-2010','DD-MM-YYYY'))
     , partition part_20100105 values less than (to_date('06-01-2010','DD-MM-YYYY'))
     , partition part_20100106 values less than (to_date('07-01-2010','DD-MM-YYYY'))
     , partition part_20100107 values less than (to_date('08-01-2010','DD-MM-YYYY'))
     , partition part_20100108 values less than (to_date('09-01-2010','DD-MM-YYYY'))
     , partition part_20100109 values less than (to_date('10-01-2010','DD-MM-YYYY'))
     , partition part_20100110 values less than (to_date('11-01-2010','DD-MM-YYYY'))
     , partition part_20100111 values less than (to_date('12-01-2010','DD-MM-YYYY'))
     , partition part_20100112 values less than (to_date('13-01-2010','DD-MM-YYYY'))
     , partition part_20100113 values less than (to_date('14-01-2010','DD-MM-YYYY'))
     , partition part_20100114 values less than (to_date('15-01-2010','DD-MM-YYYY'))
     , partition part_20100115 values less than (to_date('16-01-2010','DD-MM-YYYY'))
     , partition part_20100116 values less than (to_date('17-01-2010','DD-MM-YYYY'))
     , partition part_20100117 values less than (to_date('18-01-2010','DD-MM-YYYY'))
     , partition part_20100118 values less than (to_date('19-01-2010','DD-MM-YYYY'))
     , partition part_20100119 values less than (to_date('20-01-2010','DD-MM-YYYY'))
     , partition part_20100120 values less than (to_date('21-01-2010','DD-MM-YYYY'))
     )
/

set timing on
insert into part_tab (rec_date, id, pad)
select to_date(mod(rownum,20)+1 || '-01-2010','DD-MM-YYYY') rec_date
     , rownum id
     , rpad(rownum,300,'X')
  from dual connect by rownum <= 100000
/

exec dbms_stats.gather_table_stats(user,'PART_TAB')

create table other_tab (
       id number
     , name varchar2(30)
     , sex varchar2(1)
     )
/

insert into other_tab (id, name, sex)
select rownum id
     , username
     , decode(mod(rownum,2),1,'M','F')
  from all_users
/

exec dbms_stats.gather_table_stats(user,'OTHER_TAB')

alter session set cursor_sharing = force;

alter session set tracefile_identifier = PHS;

alter session set events '10046 trace name context level 4, forever';

set echo on lines 120 pages 100

col string for a30
select --+ query_01
       rec_date, id, substr(pad,1,20) string
  from part_tab
 where id in (select id
		from other_tab
	       where name = 'DBSNMP');

select --+ cursor_sharing_exact query_02
       rec_date, id, substr(pad,1,20) string
  from part_tab
 where id in (select id
		from other_tab
	       where name = 'DBSNMP');

alter session set events '10046 trace name context off';

Inspecting the generated trace file shows that when the recursive query for the PARTITION HASH SUBQUERY operation runs it uses “SELECT distinct TBL$OR$IDX$PART$NUM …” for each partition in order, I assume, to establish whether or not it needs to visit every subpartition in order to satisfy the query. And here’s the bit that just strikes me as wrong…

cursor_sharing = force results in recursive query:

SELECT distinct TBL$OR$IDX$PART$NUM("PART_TAB", 0, 2, 0, "ID") FROM (SELECT "OTHER_TAB"."ID" "ID" FROM "OTHER_TAB" "OTHER_TAB" WHERE "OTHER_TAB"."NAME"=:B1) ORDER BY 1

cursor_sharing = exact results in recursive query:

SELECT distinct TBL$OR$IDX$PART$NUM("PART_TAB", :"SYS_B_0", :"SYS_B_1", :"SYS_B_2", "ID") FROM (SELECT "OTHER_TAB"."ID" "ID" FROM "OTHER_TAB" "OTHER_TAB" WHERE "OTHER_TAB"."NAME"=:"SYS_B_3") ORDER BY :"SYS_B_4"

Using TKPROF to format the trace file gives:

When CURSOR_SHARING = FORCE

select --+ query_01
       rec_date, id, substr(pad,:"SYS_B_0",:"SYS_B_1") string
  from part_tab
 where id in (select id
		from other_tab
	       where name = :"SYS_B_2")

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.04       0.04          0       2448          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.04       0.04          0       2448          0           1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 76  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  HASH JOIN RIGHT SEMI (cr=2588 pr=0 pw=0 time=19501 us)
      1   TABLE ACCESS FULL OTHER_TAB (cr=7 pr=0 pw=0 time=153 us)
  49846   PARTITION RANGE ALL PARTITION: 1 20 (cr=2581 pr=0 pw=0 time=549987 us)
  49846    PARTITION HASH SUBQUERY PARTITION: KEY(SUBQUERY) KEY(SUBQUERY) (cr=2581 pr=0 pw=0 time=381745 us)
  49846     TABLE ACCESS FULL PART_TAB PARTITION: 1 40 (cr=2441 pr=0 pw=0 time=149931 us)

********************************************************************************

SELECT distinct TBL$OR$IDX$PART$NUM("PART_TAB", 0, 2, 0, "ID") 
FROM
 (SELECT "OTHER_TAB"."ID" "ID" FROM "OTHER_TAB" "OTHER_TAB" WHERE 
  "OTHER_TAB"."NAME"=:B1) ORDER BY 1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          7          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          7          0           1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 76     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT UNIQUE (cr=7 pr=0 pw=0 time=113 us)
      1   TABLE ACCESS FULL OTHER_TAB (cr=7 pr=0 pw=0 time=65 us)

********************************************************************************

SELECT distinct TBL$OR$IDX$PART$NUM("PART_TAB", 0, 2, 1, "ID") 
FROM
 (SELECT "OTHER_TAB"."ID" "ID" FROM "OTHER_TAB" "OTHER_TAB" WHERE 
  "OTHER_TAB"."NAME"=:B1) ORDER BY 1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          7          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          7          0           1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 76     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT UNIQUE (cr=7 pr=0 pw=0 time=89 us)
      1   TABLE ACCESS FULL OTHER_TAB (cr=7 pr=0 pw=0 time=55 us)

********************************************************************************

SELECT distinct TBL$OR$IDX$PART$NUM("PART_TAB", 0, 2, 2, "ID") 
FROM
 (SELECT "OTHER_TAB"."ID" "ID" FROM "OTHER_TAB" "OTHER_TAB" WHERE 
  "OTHER_TAB"."NAME"=:B1) ORDER BY 1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          7          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          7          0           1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 76     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT UNIQUE (cr=7 pr=0 pw=0 time=97 us)
      1   TABLE ACCESS FULL OTHER_TAB (cr=7 pr=0 pw=0 time=61 us)

********************************************************************************

<-- SNIP - Note how the 4th parameter to TBL$OR$IDX$PART$NUM is incremented -->

SELECT distinct TBL$OR$IDX$PART$NUM("PART_TAB", 0, 2, 19, "ID") 
FROM
 (SELECT "OTHER_TAB"."ID" "ID" FROM "OTHER_TAB" "OTHER_TAB" WHERE 
  "OTHER_TAB"."NAME"=:B1) ORDER BY 1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          7          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          7          0           1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 76     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT UNIQUE (cr=7 pr=0 pw=0 time=108 us)
      1   TABLE ACCESS FULL OTHER_TAB (cr=7 pr=0 pw=0 time=70 us)

********************************************************************************

When CURSOR_SHARING = EXACT (via hint)

select --+ cursor_sharing_exact query_02
       rec_date, id, substr(pad,1,20) string
  from part_tab
 where id in (select id
		from other_tab
	       where name = 'DBSNMP')

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.04       0.04          0       2448          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.04       0.04          0       2448          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 76  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  HASH JOIN RIGHT SEMI (cr=2588 pr=0 pw=0 time=20539 us)
      1   TABLE ACCESS FULL OTHER_TAB (cr=7 pr=0 pw=0 time=72 us)
  49846   PARTITION RANGE ALL PARTITION: 1 20 (cr=2581 pr=0 pw=0 time=550191 us)
  49846    PARTITION HASH SUBQUERY PARTITION: KEY(SUBQUERY) KEY(SUBQUERY) (cr=2581 pr=0 pw=0 time=386274 us)
  49846     TABLE ACCESS FULL PART_TAB PARTITION: 1 40 (cr=2441 pr=0 pw=0 time=199771 us)

********************************************************************************

SELECT distinct TBL$OR$IDX$PART$NUM("PART_TAB", :"SYS_B_0", :"SYS_B_1", 
  :"SYS_B_2", "ID") 
FROM
 (SELECT "OTHER_TAB"."ID" "ID" FROM "OTHER_TAB" "OTHER_TAB" WHERE 
  "OTHER_TAB"."NAME"=:"SYS_B_3") ORDER BY :"SYS_B_4"


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       20      0.00       0.00          0          0          0           0
Execute     20      0.02       0.02          0          0          0           0
Fetch       40      0.00       0.00          0        140          0          20
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       80      0.02       0.03          0        140          0          20

Misses in library cache during parse: 20
Misses in library cache during execute: 20
Optimizer mode: ALL_ROWS
Parsing user id: 76     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT UNIQUE (cr=7 pr=0 pw=0 time=86 us)
      1   TABLE ACCESS FULL OTHER_TAB (cr=7 pr=0 pw=0 time=52 us)

********************************************************************************

I did a double take when I spotted this in the trace file, so let me point it out if it’s not clear:

CURSOR_SHARING = FORCE => recursive query uses literals!

CURSOR_SHARING = EXACT => recursive query uses system generated binds.

I’m not too surprised by the behaviour when CURSOR_SHARING = EXACT, but I didn’t expect to find that CURSOR_SHARING = FORCE causes the recursive query to use literals.

I found myself discussing this with Jeremy Schneider during OpenWorld and he seemed equally surprised by the evidence. Since getting back to the UK I have done some further testing and playing around. I’m now confident that that CURSOR_SHARING = FORCE does not cause all recursive queries to run as if CURSOR_SHARING = EXACT. In fact all the recursive queries I looked into ran as if CURSOR_SHARING = FORCE regardless of what CURSOR_SHARING was set to.

I have checked the OPTIMIZER_ENV_HASH_VALUE for both sets of recursive queries and it matches… I’m stuck for where to go next in order to explain this behaviour, and understand if it is by design. If anyone has any ideas then I’d love to know about them.

… And after all this playing around I’m still no closer to understanding what is actually causing the wrong results :-)

Minor Bug in Supplied Password Function?

I get the feeling that most people will see this as ridiculously trivial, but it’s the sort of thing that my attention gets drawn to :-)

When I first spotted the double “backtick” in the punctarray within the VERIFY_FUNCTION created using the supplied (10.2) password function script (utlpwdmg.sql) I thought it was a bug introduced by me during my improving/modifying the script months earlier.

I’d already identified that there were punctuation characters missing from the array that needed to be added as their absence had caused a bit of confusion for our end users. Having been told that they needed to include at least one punctuation character they were discovering that some punctuation characters were not being seen as valid punctuation. As a result I was already motivated to “fix” the function. The punctarray I ended up with is provided later in this post.

After coming up with the new punctarry I then gave the double backticks no more thought, apart from kicking myself for making such a simple error.

… Then I recently saw the double backticks again in a VERIFY_FUNCTION at a new site… This couldn’t have been my bug, I’d been no where near this system until that day! This prompted me to dig a bit deeper.

First up, here is a demonstration of why I think this is a (very minor) bug:

“punctarray” in supplied utlpwdmg.sql

punctarray:='!"#$%&()``*+,-/:;<=>?_';

Creating a user with ` in the password and demonstrating that the password must be in double quotation marks (or set using the “password” command):

SQL> create user a identified by abcde`12345;
create user a identified by abcde`12345
*
ERROR at line 1:
ORA-00911: invalid character
SQL> create user a identified by "abcde`12345";

User created.

SQL> grant create session to a;

Grant succeeded.</div>

SQL> conn a/abcde`12345
Connected.
SQL>

So that demonstrates the creation of a user with a password containing ` using the supplied password verification function, but what happens if we remove one of the backticks…

The punctarray was modified in utlpwdmg.sql to be:

punctarray:='!"#$%&()`*+,-/:;<=>?_';

The function was created and a new user with a password containing a backtick was created:

SQL> create user b identified by "abcde`12345";

User created.

SQL> grant create session to b;

Grant succeeded.

SQL> conn b/abcde`12345
Connected.
SQL>

As demonstrated, the use of double backticks appears to be completely unnecessary, hence the suggestion that it is a bug (admittedly a harmless one, but a bug nonetheless). I can only assume that this is the result of a simple typing error.

Interestingly for 11g the new function, VERIFY_FUNCTION_11G, does not check for punctuation characters without modification, but VERIFY_FUNCTION is included in the same script for use or reference.

The point of this post is not just to point out the “bug”, but rather to share what I had previously arrived at for punctarray, which includes a wider range of characters and no duplicates.:

punctarray := '!#$%^()`.|*+,-:;<=>?_{}[]~\';