Oracle Data Redaction 12c

Oracle Data Redaction provides functionality to mask (redact) data that is returned from user SELECT queries. The masking takes place in real time. The Data Redaction policy applies to the querying user, depending on this user's SYS_CONTEXT values. Oracle Database redacts only the data for the rows specified by the user's query, not the data for the entire column. The redaction takes place immediately before the data is returned to the querying user or application.

To set up a demonstration, uses the setup.sql script, then uses the code shown in below listing to set up a redaction policy on the EMP table.

Setup.sql
create table emp
(  empno number,
   ename varchar2(20),
   salary number,
   hire_date date,
   email_id varchar2(30) ) ;
 
insert into emp(empno,ename,salary,hire_date,email_id)
values(1,'John',1013,sysdate,'John@example.com');
insert into emp(empno,ename,salary,hire_date,email_id)
values(2,'Miller',1578,sysdate-2,'miller@example.com');
insert into emp(empno,ename,salary,hire_date,email_id)
values(3,'Kim',1398,sysdate-3,'Kim@example.com');
commit;

rajesh@PDB1> select * from emp;

     EMPNO ENAME                    SALARY HIRE_DATE               EMAIL_ID

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

         1 John                       1013 28-DEC-2013 04:10:34 PM John@example.com

         2 Miller                     1578 26-DEC-2013 04:10:34 PM miller@example.com

         3 Kim                        1398 25-DEC-2013 04:10:34 PM Kim@example.com

3 rows selected.

rajesh@PDB1>

When the owner of the table "EMP" selects data, the results displays the values intact - but when any other user selects data from the table, the data must be masked.

Column	Description of redaction
Salary	replace with some random numbers and don't show actual values
Hire_date	show only the day and month year should be replaced with 1990
Email_id	Keep the domain name and rest others should be masked

rajesh@PDB1> grant select on emp to scott;

Grant succeeded.

rajesh@PDB1> begin
  2     dbms_redact.add_policy
  3     ( object_schema=>user,
  4       object_name=>'EMP',
  5       policy_name=>'EMP_REDACT',
  6       column_name=>'SALARY',
  7       function_type=>dbms_redact.random,
  8       expression=> q'|sys_context('userenv','current_user')!= 'RAJESH'|'
  9      );
 10  end;
 11  /

PL/SQL procedure successfully completed.

rajesh@PDB1>
rajesh@PDB1> begin
  2     dbms_redact.alter_policy
  3     ( object_schema=> user,
  4       object_name=>'EMP',
  5       policy_name=>'EMP_REDACT',
  6       action=>dbms_redact.add_column,
  7       column_name=>'HIRE_DATE',
  8       function_type=>dbms_redact.partial,
  9       function_parameters=>'MDy1990' );
 10  end;
 11  /

PL/SQL procedure successfully completed.

rajesh@PDB1> begin
  2     dbms_redact.alter_policy
  3     ( object_schema=> user,
  4       object_name=>'EMP',
  5       policy_name=>'EMP_REDACT',
  6       action=>dbms_redact.add_column,
  7       column_name=>'EMAIL_ID',
  8       function_type=>dbms_redact.regexp,
  9       regexp_pattern=>dbms_redact.RE_PATTERN_EMAIL_ADDRESS,
 10       regexp_replace_string=>dbms_redact.RE_REDACT_EMAIL_NAME,
 11       regexp_match_parameter=>dbms_redact.RE_CASE_INSENSITIVE );
 12  end;
 13  /

PL/SQL procedure successfully completed.

rajesh@PDB1>
rajesh@PDB1> select * from emp ;

     EMPNO ENAME          SALARY HIRE_DATE               EMAIL_ID
---------- ---------- ---------- ----------------------- --------------------
         1 John             1013 28-DEC-2013 04:10:34 PM John@example.com
         2 Miller           1578 26-DEC-2013 04:10:34 PM miller@example.com
         3 Kim              1398 25-DEC-2013 04:10:34 PM Kim@example.com

3 rows selected.

rajesh@PDB1>

When connected from Scott schema the redacted data look like this.

scott@PDB1> select * from rajesh.emp ;

     EMPNO ENAME                    SALARY HIRE_DATE               EMAIL_ID

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

         1 John                        796 28-DEC-1990 04:10:34 PM xxxx@example.com

         2 Miller                      694 26-DEC-1990 04:10:34 PM xxxx@example.com

         3 Kim                          79 25-DEC-1990 04:10:34 PM xxxx@example.com

3 rows selected.

scott@PDB1> 

Information's on redaction policies and policy columns are maintained in REDACTION_POLICIES and REDACTION_COLUMNS data dictionary.

rajesh@PDB1> @printtbl ' select * from redaction_policies '

OBJECT_OWNER                  : "RAJESH"

OBJECT_NAME                   : "EMP"

POLICY_NAME                   : "EMP_REDACT"

EXPRESSION                    : "sys_context('userenv','current_user')!= 'RAJESH'"

ENABLE                        : "YES    "

POLICY_DESCRIPTION            : ""

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

PL/SQL procedure successfully completed.

rajesh@PDB1> 

rajesh@PDB1>

rajesh@PDB1>

rajesh@PDB1> @printtbl ' select * from redaction_columns '

OBJECT_OWNER                  : "RAJESH"

OBJECT_NAME                   : "EMP"

COLUMN_NAME                   : "SALARY"

FUNCTION_TYPE                 : "RANDOM REDACTION"

FUNCTION_PARAMETERS           : ""

REGEXP_PATTERN                : ""

REGEXP_REPLACE_STRING         : ""

REGEXP_POSITION               : "0"

REGEXP_OCCURRENCE             : "0"

REGEXP_MATCH_PARAMETER        : ""

COLUMN_DESCRIPTION            : ""

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

OBJECT_OWNER                  : "RAJESH"

OBJECT_NAME                   : "EMP"

COLUMN_NAME                   : "EMAIL_ID"

FUNCTION_TYPE                 : "REGEXP REDACTION"

FUNCTION_PARAMETERS           : ""

REGEXP_PATTERN                : "([A-Za-z0-9._%+-]+)@([A-Za-z0-9.-]+\.[A-Za-z]{2,4})"

REGEXP_REPLACE_STRING         : "xxxx@\2"

REGEXP_POSITION               : "1"

REGEXP_OCCURRENCE             : "0"

REGEXP_MATCH_PARAMETER        : "i"

COLUMN_DESCRIPTION            : ""

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

OBJECT_OWNER                  : "RAJESH"

OBJECT_NAME                   : "EMP"

COLUMN_NAME                   : "HIRE_DATE"

FUNCTION_TYPE                 : "PARTIAL REDACTION"

FUNCTION_PARAMETERS           : "MDy1990"

REGEXP_PATTERN                : ""

REGEXP_REPLACE_STRING         : ""

REGEXP_POSITION               : "0"

REGEXP_OCCURRENCE             : "0"

REGEXP_MATCH_PARAMETER        : ""

COLUMN_DESCRIPTION            : ""

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

PL/SQL procedure successfully completed.

rajesh@PDB1> 

However If the querying user has the EXEMPT REDACTION POLICY system privilege, redaction will not be performed.

rajesh@PDB1> connect sys/oracle@pdb1 as sysdba

Connected.

sys@PDB1> grant exempt redaction policy to scott;

Grant succeeded.

sys@PDB1> set timing off

sys@PDB1> 

sys@PDB1> 

sys@PDB1> grant exempt redaction policy to scott;

Grant succeeded.

sys@PDB1> connect scott/tiger@pdb1

Connected.

scott@PDB1> 

scott@PDB1> select * from session_privs ;

PRIVILEGE

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

CREATE SESSION

EXEMPT REDACTION POLICY

2 rows selected.

scott@PDB1> 

scott@PDB1> select * from rajesh.emp ;

     EMPNO ENAME                    SALARY HIRE_DATE               EMAIL_ID

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

         1 John                       1013 28-DEC-2013 04:10:34 PM John@example.com

         2 Miller                     1578 26-DEC-2013 04:10:34 PM miller@example.com

         3 Kim                        1398 25-DEC-2013 04:10:34 PM Kim@example.com

3 rows selected.

scott@PDB1> 

Asynchronous Global Index Maintenance 12c - Part III

There’s actually no such thing as a Non-Unique index entry as such as Oracle ensures all index entries are effectively unique by adding the rowid to the index key for all Non-Unique indexes. Fundamentally, this is essential because Oracle needs some way of efficiently finding the precise index entry associated with an update or delete operation. Without having the rowid as part of the index key, Oracle would be forced to navigate to the first occurrence of an index value and search through all occurrences of the index value until it finds the specific entry containing the rowid of interest. This could potentially result in visiting many leaf blocks if the index value spans multiple leaf blocks. By including the rowid as the last index key column, non-unique index values are further ordered based on the corresponding rowid within the same indexed values. Oracle can therefore always navigate directly to the leaf block containing the exact index entry of interest as the rowid can be included in the branch blocks to determine both the index entry and rowid ranges found in specific leaf blocks

Now if we were to delete and subsequently re-insert a row in the table with same index value within a single transaction, note the rowid of the new row by definition will differ from the deleted row. Therefore, we would need a different index entry for the new index row because if the rowids differ, then the associated index entries must differ as well, Therefore, if we were to delete and re-insert the same index value within a single transaction, Oracle is forced to create a new index entry and will not reuse the existing, deleted index entry

In  unique index. It’s simply not possible to have duplicate index entries within a Unique index structure. Therefore, it’s not necessary to have the rowid as a separate column of the index entry as the index values themselves are sufficient to uniquely identify each and every index entry. The rowid is basically just another piece of overhead associated with the index entry rather than a separate index column

If we were to now delete and re-insert the same index value within a single transaction, Oracle can now reuse the same, deleted index entry, because the index entry is effectively identical to the deleted one. The only possible difference is the rowid but the rowid is no longer a part of the index column list and so can just be updated as necessary.

The same happens with Global unique index Maintenance, when the same key values reaches the index structure delete orphan entries get reused.

rajesh@PDB1>
rajesh@PDB1> create table t
  2  partition by list(x)
  3  (
  4     partition p1 values (1),
  5     partition p2 values (2),
  6     partition p3 values (3),
  7     partition p4 values (4),
  8     partition p5 values (5)
  9  )
 10  nologging as
 11  select b.*, 1 as x,rownum as y
 12  from big_table b
 13  where rownum <= 1000 ;

Table created.

rajesh@PDB1> create unique index t_idx on t(owner,object_type,object_name,y) nologging;

Index created.

rajesh@PDB1>
rajesh@PDB1> alter table t drop partition p1 update global indexes ;

Table altered.

rajesh@PDB1> analyze index t_idx validate structure;

Index analyzed.
rajesh@PDB1>
rajesh@PDB1> select name,lf_rows,del_lf_rows from index_stats;

NAME                                                  LF_ROWS DEL_LF_ROWS
-------------------------------------------------- ---------- -----------
T_IDX                                                    1000        1000

1 row selected.
rajesh@PDB1> alter table t add partition p1 values (1);

Table altered.
rajesh@PDB1> insert into t
  2  select b.*, 1 as x,rownum as y
  3  from big_table b
  4  where rownum <= 1000;

1000 rows created.
rajesh@PDB1> commit;

Commit complete.
rajesh@PDB1>
rajesh@PDB1> analyze index t_idx validate structure;

Index analyzed.
rajesh@PDB1> select name,lf_rows,del_lf_rows from index_stats;

NAME                                                  LF_ROWS DEL_LF_ROWS
-------------------------------------------------- ---------- -----------
T_IDX                                                    1000           0

1 row selected.
rajesh@PDB1>
rajesh@PDB1>

Asynchronous Global Index Maintenance 12c - Part II

In part I we discussed how global indexes can now be asynchronously maintained in Oracle 12c when a table partition is dropped or truncated. Basically, when a table partition is dropped/truncated with the UPDATE GLOBAL INDEXES clause, Oracle simply keeps track of the object numbers of those table partitions and ignores any corresponding rowids within the index during subsequent index scans. As such, these table partition operations are very fast and efficient as the global indexes are not actually maintained during the partition operation, but importantly, continue to remain in a usable state

So prior to Oracle 12c, to update global indexes on the fly was a relatively expensive operation as it required all the associated index entries to be deleted from the global indexes.

With Oracle 12c, There are a number of possible ways to cleanup out the orphaned index entries.

#1 - One simple way is to simply rebuild the global index (or index partitions)

alter index t_idx rebuild;


#2 - another possible alternative is to use CLEANUP coaleasce clause.

alter index t_idx coalesce cleanup;

#3 - Yet another possible option is to simply wait for the PMO_DEFERRED_GIDX_MAINT_JOB job to run (scheduled by default during the 2am maintenance window) to clean out orphaned index entries from all currently impacted global indexes

rajesh@PDB1>
rajesh@PDB1> select job_name , start_date,enabled,state,comments
  2  from dba_scheduler_jobs
  3  where job_name ='PMO_DEFERRED_GIDX_MAINT_JOB';
JOB_NAME             START_DATE           ENABL STATE           COMMENTS
-------------------- -------------------- ----- --------------- --------------------
PMO_DEFERRED_GIDX_MA 29-JUN-13 02.00.00.6 TRUE  SCHEDULED       Oracle defined autom
INT_JOB              00000 AM US/CENTRAL                        atic index cleanup f
                                                                or partition mainten
                                                                ance operations with
                                                                 deferred global ind
                                                                ex maintenance

1 row selected.
rajesh@PDB1>

#4 - Yet another alternative is to manually run the dbms_part.cleanup_gidx procedure which is in turn called by this job.

exec dbms_part.cleanup_gidx ;

So with the new asynchronous global index maintenance capabilities of the Oracle 12c database, we can perform a much faster and more efficient drop/truncate table partition operation while keeping our global indexes in a usable state and leave the tidying up of the resultant orphaned index entries to another time and method of our convenience

Asynchronous Global Index Maintenance 12c - Part I

Starting with Oracle 12c Asynchronous Global Index Maintenance for drop and Truncate operation are opimtized by making the index maintance for metadata only.

What oracle does is maintain a list of invalid data object ids and ignore those entries in the index from then on.  Below is a comparision demo of 12c and 11g databases.

Table 'T' is loaded with 1,000,000 records and a has a global index 'T_IDX'

rajesh@PDB1>
rajesh@PDB1> create table t
  2  partition by list(x)
  3  (
  4     partition p1 values (1),
  5     partition p2 values (2),
  6     partition p3 values (3),
  7     partition p4 values (4),
  8     partition p5 values (5)
  9  )
 10  nologging as
 11  select b.*, mod(rownum,5)+1 as x,rownum as y
 12  from big_table b ;

Table created.

Elapsed: 00:00:08.71
rajesh@PDB1> create index t_idx on t(owner,object_type,object_name,y) nologging;

Index created.

Elapsed: 00:00:15.17
rajesh@PDB1> column redo_size new_value r
rajesh@PDB1> column logical_io new_value l
rajesh@PDB1>
rajesh@PDB1> select nvl(redo_size,0) as redo_size,
  2             nvl(logical_io,0) as logical_io
  3  from (
  4  select s2.value,s1.name
  5  from v$sysstat s1,
  6       v$mystat s2
  7  where s1.statistic# = s2.statistic#
  8  and s1.name in ('redo size','db block gets')
  9       )
 10  pivot
 11  ( sum(value)
 12    for name in ('redo size' as redo_size,'db block gets' as logical_io) ) ;

 REDO_SIZE LOGICAL_IO
---------- ----------
    739224      31356

1 row selected.

Elapsed: 00:00:00.16
rajesh@PDB1> alter table t drop partition p1 update global indexes ;

Table altered.

Elapsed: 00:00:00.21
rajesh@PDB1> select redo_size - &r as redo_diff,
  2        nvl(logical_io,0) - &l as logical_io_diff
  3  from (
  4  select s2.value,s1.name
  5  from v$sysstat s1,
  6       v$mystat s2
  7  where s1.statistic# = s2.statistic#
  8  and s1.name in ('redo size','db block gets')
  9       )
 10  pivot
 11  ( sum(value)
 12    for name in ('redo size' as redo_size,'db block gets' as logical_io) ) ;
old   1: select redo_size - &r as redo_diff,
new   1: select redo_size -     739224 as redo_diff,
old   2:           nvl(logical_io,0) - &l as logical_io_diff
new   2:           nvl(logical_io,0) -      31356 as logical_io_diff
 REDO_DIFF LOGICAL_IO_DIFF
---------- ---------------
     10924              55

1 row selected.

Elapsed: 00:00:00.11
rajesh@PDB1>

the drop partition statement is pretty fast in 12c database and generated only 10KB of redo and modified 55 blocks. while the same operation in 11g generated about 14MB or redo and produced 29K logical IO's


rajesh@ORA11GR2> column redo_size new_value r
rajesh@ORA11GR2> column logical_io new_value l
rajesh@ORA11GR2> select nvl(redo_size,0) as redo_size,
  2             nvl(logical_io,0) as logical_io
  3  from (
  4  select s2.value,s1.name
  5  from v$sysstat s1,
  6       v$mystat s2
  7  where s1.statistic# = s2.statistic#
  8  and s1.name in ('redo size','db block gets')
  9       )
 10  pivot
 11  ( sum(value)
 12    for name in ('redo size' as redo_size,'db block gets' as logical_io) ) ;

 REDO_SIZE LOGICAL_IO
---------- ----------
    640716      30701

1 row selected.

Elapsed: 00:00:00.00
rajesh@ORA11GR2> alter table t drop partition p1 update global indexes ;

Table altered.

Elapsed: 00:00:08.35
rajesh@ORA11GR2>
rajesh@ORA11GR2> select redo_size - &r as redo_diff,
  2        nvl(logical_io,0) - &l as logical_io_diff
  3  from (
  4  select s2.value,s1.name
  5  from v$sysstat s1,
  6       v$mystat s2
  7  where s1.statistic# = s2.statistic#
  8  and s1.name in ('redo size','db block gets')
  9       )
 10  pivot
 11  ( sum(value)
 12    for name in ('redo size' as redo_size,'db block gets' as logical_io) ) ;
old   1: select redo_size - &r as redo_diff,
new   1: select redo_size -     640716 as redo_diff,
old   2:           nvl(logical_io,0) - &l as logical_io_diff
new   2:           nvl(logical_io,0) -      30701 as logical_io_diff

 REDO_DIFF LOGICAL_IO_DIFF
---------- ---------------
  14214048           29004

1 row selected.

Elapsed: 00:00:00.02
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2>

Basically, when dropping (or truncating) a table partition, Oracle in 12c now “postpones” the actual removal of the global index entries associated with the dropped/truncated partition. This can now be performed asynchronously at a time of our choosing. So it’s therefore now very quick and very cheap to update these global indexes on the fly

However, most importantly, the indexes are still usable and can be guaranteed to return the correct results, ignoring any orphaned entires as required. These can be easily ignored as they all have an object number in the index entry rowids associated with the dropped table partition object and not the table partition(s) of interest as required by the queries

rajesh@PDB1> select index_name,status,ORPHANED_ENTRIES
  2  from user_indexes
  3  where index_name ='T_IDX' ;

INDEX_NAME           STATUS   ORP
-------------------- -------- ---
T_IDX                VALID    YES

1 row selected.

Elapsed: 00:00:00.01
rajesh@PDB1>

So if we now select values via the indexed columns that only spans data in the dropped table partition:

rajesh@PDB1> select *
  2  from t
  3  where owner = 'PUBLIC'
  4  and object_type ='SYNONYM'
  5  and object_name = 'V$MAP_LIBRARY'
  6  and y between 20 and 40
  7  /

no rows selected

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 1562714629
------------------------------------------------------------------------------------
| Id  | Operation                                  | Name  | Rows  | Pstart| Pstop |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |       |     1 |     |       |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| T     |     1 | ROWID | ROWID |
|*  2 |   INDEX RANGE SCAN                         | T_IDX |     1 |       |       |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OWNER"='PUBLIC' AND "OBJECT_TYPE"='SYNONYM' AND "OBJECT_NAME"='V$MAP_LIBRARY' AND "Y">=20
              AND "Y"<=40)
       filter(TBL$OR$IDX$PART$NUM("T",0,8,0,"T".ROWID)=1)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
       1437  bytes sent via SQL*Net to client
        355  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

We notice that quite correctly, no rows are now returned.

If we look at the INDEX_STATS of these indexes, we notice at one level that the orphaned index entries are counted as if they’re deleted entries:

rajesh@PDB1> analyze index t_idx validate structure;

Index analyzed.

Elapsed: 00:00:03.21
rajesh@PDB1> select name,lf_rows,del_lf_rows from index_stats;

NAME                                                  LF_ROWS DEL_LF_ROWS
-------------------------------------------------- ---------- -----------
T_IDX                                                 1000000      200000

1 row selected.

Elapsed: 00:00:00.10
rajesh@PDB1>

We see that the index statistics is indicating that there are 200K -called deleted index entries. The validation process is ensuring that the orphaned index entries only reference partitions that indeed no longer exist and counts such entries as deleted ones.

So it currently looks we’ve got the best of both worlds here. We effectively get the same performance during the drop table partition operation as if we don’t maintain the global indexes but get the same index availability and subsequent query performance as if we do.

Well, very importantly, unlike actual deleted index entries, they are not readily removed and their space reused by subsequent DML activities within the leaf blocks. In fact, these orphaned index entries can even “get in the way” as we see here when we attempt to reinsert the same data back into table:

rajesh@PDB1> alter table t add partition p1 values (1);

Table altered.

Elapsed: 00:00:00.29
rajesh@PDB1> insert into t
  2  select b.*, 1 as x,rownum as y
  3  from big_table b
  4  where rownum <= 1000;

1000 rows created.

Elapsed: 00:00:00.37
rajesh@PDB1> commit;

Commit complete.

Elapsed: 00:00:00.00
rajesh@PDB1>
rajesh@PDB1> analyze index t_idx validate structure;

Index analyzed.

Elapsed: 00:00:03.10
rajesh@PDB1> select name,lf_rows,del_lf_rows from index_stats;

NAME                                                  LF_ROWS DEL_LF_ROWS
-------------------------------------------------- ---------- -----------
T_IDX                                                 1001000      200000

1 row selected.

Elapsed: 00:00:00.02
rajesh@PDB1>

We notice that unlike actual deleted index entries in which all the deleted space would have simply have been reused, we see instead that none of the space occupied by the orphaned rows has been reused.

Improved defaults in 12c

Just learnt a new thing in 12c and this relates to improved defaults in 12c database and the below quote is from docs

<quote>
If you specify the DEFAULT clause for a column, then the default value is stored as metadata but the column itself is not populated with data.
</quote>

Oracle 11g has the improved way of adding DEFAULT value to NOT NULLABLE column, but 12c has improved way for NULLABLE columns too.

 rajesh@PDB1> exec show_space('BIG_TABLE');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               8
Full Blocks        .....................           1,713
Total Blocks............................           1,768
Total Bytes.............................      14,483,456
Total MBytes............................              13
Unused Blocks...........................               0
Unused Bytes............................               0
Last Used Ext FileId....................              11
Last Used Ext BlockId...................           3,328
Last Used Block.........................              40

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.19
rajesh@PDB1>
rajesh@PDB1>
rajesh@PDB1> alter table big_table add x char(2000) default 'x' ;

Table altered.

Elapsed: 00:00:00.04
rajesh@PDB1> exec show_space('BIG_TABLE');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               8
Full Blocks        .....................           1,713
Total Blocks............................           1,768
Total Bytes.............................      14,483,456
Total MBytes............................              13
Unused Blocks...........................               0
Unused Bytes............................               0
Last Used Ext FileId....................              11
Last Used Ext BlockId...................           3,328
Last Used Block.........................              40

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.16
rajesh@PDB1>

so the table size remains 13MB even after adding  a new column of 2000 bytes, but in 11g size increases from 13MB to 264MB.

rajesh@ORA11GR2>
rajesh@ORA11GR2> exec show_space('BIG_TABLE');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................           1,525
Total Blocks............................           1,664
Total Bytes.............................      13,631,488
Total MBytes............................              13
Unused Blocks...........................             105
Unused Bytes............................         860,160
Last Used Ext FileId....................               5
Last Used Ext BlockId...................          54,912
Last Used Block.........................              23

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table big_table add x char(2000) default 'x' ;

Table altered.

Elapsed: 00:01:41.17
rajesh@ORA11GR2> exec show_space('BIG_TABLE');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................          31,808
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               1
FS4 Blocks (75-100).....................             223
Full Blocks        .....................           1,525
Total Blocks............................          33,792
Total Bytes.............................     276,824,064
Total MBytes............................             264
Unused Blocks...........................               0
Unused Bytes............................               0
Last Used Ext FileId....................               5
Last Used Ext BlockId...................          86,656
Last Used Block.........................           1,024

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.38
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table big_table add y char(2000) default 'y' not null ;

Table altered.

Elapsed: 00:00:00.25
rajesh@ORA11GR2> exec show_space('BIG_TABLE');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................          31,808
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               1
FS4 Blocks (75-100).....................             223
Full Blocks        .....................           1,525
Total Blocks............................          33,792
Total Bytes.............................     276,824,064
Total MBytes............................             264
Unused Blocks...........................               0
Unused Bytes............................               0
Last Used Ext FileId....................               5
Last Used Ext BlockId...................          86,656
Last Used Block.........................           1,024

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.38
rajesh@ORA11GR2>

Faster plsql in sql from 12c

This week end i read about two kinds of plsql functions, that run faster in sql.

1) plsql functions declared and defined in WITH clause of sql statements ( and be aware of  HINT: with_plsql when your top level dml statment is  Inser, update, Delete or Merge commands )

2) plsql functions that are defined with UDF (user defined function) pragma.

First, we will start our discussion with WITH clause of sql statements

dba_user@PDB1> create table t
  2  as
  3  select rownum as x,
  4     object_name ,
  5     object_type
  6  from all_objects ;

Table created.

dba_user@PDB1>
dba_user@PDB1> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

dba_user@PDB1> create or replace function is_number(p_x in varchar2)
  2  return varchar2
  3  as
  4     num_exp exception;
  5     pragma exception_init(num_exp,-06502);
  6  begin
  7     if to_number(p_x) is not null then
  8             return 'Y';
  9     else
 10             return 'N';
 11     end if;
 12
 13     exception
 14             when num_exp then
 15                     return 'N';
 16  end;
 17  /

Function created.

dba_user@PDB1>

dba_user@PDB1> @d:\script2.sql
dba_user@PDB1> /*
dba_user@PDB1> select x,is_number(object_name),
dba_user@PDB1>         is_number(object_type)
dba_user@PDB1> from t;
dba_user@PDB1>
dba_user@PDB1> with function is_num(p_x varchar2)
dba_user@PDB1> return varchar2 is
dba_user@PDB1>         num_exp exception;
dba_user@PDB1>         pragma exception_init(num_exp,-06502);
dba_user@PDB1> begin
dba_user@PDB1>         if to_number(p_x) is not null then
dba_user@PDB1>                 return 'Y' ;
dba_user@PDB1>         else
dba_user@PDB1>                 return 'N';
dba_user@PDB1>         end if;
dba_user@PDB1>
dba_user@PDB1>         exception
dba_user@PDB1>                 when num_exp then
dba_user@PDB1>                         return 'N';
dba_user@PDB1> end;
dba_user@PDB1> select x,is_num(object_name) ,
dba_user@PDB1>         is_num(object_type)
dba_user@PDB1> from t;
dba_user@PDB1> /
dba_user@PDB1>
dba_user@PDB1> */
dba_user@PDB1> set echo off;

And Tkprof shows me this

********************************************************************************
select x,is_number(object_name),
 is_number(object_type)
from t
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      600      1.32       1.26          0       1138          0       89721
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      602      1.32       1.27          0       1138          0       89721
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 111 
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
     89721      89721      89721  TABLE ACCESS FULL T (cr=1138 pr=0 pw=0 time=36630 us cost=154 size=3499119 card=89721)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                     600        0.00          0.00
  SQL*Net message from client                   600        0.00          0.87
********************************************************************************
with function is_num(p_x varchar2)
return varchar2 is
 num_exp exception;
 pragma exception_init(num_exp,-06502);
begin
 if to_number(p_x) is not null then
  return 'Y' ;
 else
  return 'N';
 end if;
 exception
  when num_exp then
   return 'N';
end;
select x,is_num(object_name) ,
 is_num(object_type)
from t
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      600      0.84       0.74          0       1138          0       89721
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      602      0.84       0.75          0       1138          0       89721
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 111 
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
     89721      89721      89721  TABLE ACCESS FULL T (cr=1138 pr=0 pw=0 time=49829 us cost=154 size=3499119 card=89721)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                     600        0.00          0.00
  SQL*Net message from client                   600        0.00          0.85
********************************************************************************

WITH Clause sql statements can be used straight away with DDL's  but not in DML's

dba_user@PDB1> create table t2 as
  2  with function is_num(p_x in varchar2)
  3  return varchar2
  4  is
  5     num_exp exception;
  6     pragma exception_init(num_exp,-06502);
  7  begin
  8     if to_number(p_x) is not null then
  9             return 'Y';
 10     else
 11             return 'N';
 12     end if;
 13     exception
 14             when num_exp then
 15                     return 'N' ;
 16  end;
 17  select rownum as x, object_name, object_type,
 18     is_num(object_name) as flag1
 19  from t
 20  where rownum <=10;
 21  /

Table created.

dba_user@PDB1> truncate table t2;

Table truncated.

dba_user@PDB1> insert into t2
  2  with function is_num(p_x in varchar2)
  3  return varchar2
  4  is
  5     num_exp exception;
  6     pragma exception_init(num_exp,-06502);
  7  begin
  8     if to_number(p_x) is not null then
  9             return 'Y';
 10     else
 11             return 'N';
 12     end if;
 13     exception
 14             when num_exp then
 15                     return 'N' ;
 16  end;
 17  select rownum as x, object_name, object_type,
 18     is_num(object_name) as flag1
 19  from t
 20  where rownum <=10;
 21  /
with function is_num(p_x in varchar2)
*
ERROR at line 2:
ORA-32034: unsupported use of WITH clause

so do remember your with_plsql hint when you work with WITH clause plsql declarations in dml statements.


dba_user@PDB1> insert /*+ with_plsql */ into t2
  2  with function is_num(p_x in varchar2)
  3  return varchar2
  4  is
  5     num_exp exception;
  6     pragma exception_init(num_exp,-06502);
  7  begin
  8     if to_number(p_x) is not null then
  9             return 'Y';
 10     else
 11             return 'N';
 12     end if;
 13     exception
 14             when num_exp then
 15                     return 'N' ;
 16  end;
 17  select rownum as x, object_name, object_type,
 18     is_num(object_name) as flag1
 19  from t
 20  where rownum <=10;
 21  /

10 rows created.

dba_user@PDB1> commit;

Commit complete.

dba_user@PDB1>
dba_user@PDB1> delete /*+ with_plsql */ from t2
  2  where (x,flag1) in
  3  ( with function is_num(p_x in varchar2)
  4  return varchar2
  5  is
  6     num_exp exception;
  7     pragma exception_init(num_exp,-06502);
  8  begin
  9     if to_number(p_x) is not null then
 10             return 'Y';
 11     else
 12             return 'N';
 13     end if;
 14     exception
 15             when num_exp then
 16                     return 'N' ;
 17  end;
 18  select rownum as x, is_num(object_name) as flag1
 19  from t
 20  where rownum <=10) ;
 21  /

10 rows deleted.

dba_user@PDB1> commit;

Commit complete.
dba_user@PDB1> merge /*+ with_plsql */ into t2 using
  2     ( with function is_num(p_x in varchar2)
  3             return varchar2
  4             is
  5                     num_exp exception;
  6                     pragma exception_init(num_exp,-06502);
  7             begin
  8                     if to_number(p_x) is not null then
  9                             return 'Y';
 10                     else
 11                             return 'N';
 12                     end if;
 13                     exception
 14                             when num_exp then
 15                                     return 'N' ;
 16             end;
 17             select rownum as x, object_name, object_type,
 18                     is_num(object_name) as flag1
 19             from t
 20             where rownum <=10 ) t3
 21  on (t2.x = t3.x)
 22  when matched then
 23     update set t2.flag1 = t3.flag1
 24  when not matched then
 25     insert (x,object_name,object_type,flag1)
 26     values (t3.x, t3.object_name, t3.object_type, t3.flag1)
 27  /

10 rows merged.
dba_user@PDB1> commit;


When you write plsql function's that heaving invoked from sql handling huge datasets, PRAGMA UDF compiler directives can be used to execute the statements efficiently.

dba_user@PDB1> create or replace function normal_function(x in varchar2)
  2  return number
  3  as
  4  begin
  5     return (length(x));
  6  end;
  7  /

Function created.

dba_user@PDB1> declare
  2     type num_array is table of number;
  3     l_array num_array := num_array();
  4     l_sql varchar2(4000);
  5     l_cursor sys_refcursor;
  6     l_cputime number;
  7     l_elapstime number;
  8  begin
  9     l_sql :=' select normal_function(object_name) from t ';
 10
 11     l_cputime := dbms_utility.get_cpu_time ;
 12     l_elapstime := dbms_utility.get_time;
 13     open l_cursor for l_sql;
 14     fetch l_cursor bulk collect into l_array;
 15     close l_cursor;
 16
 17     dbms_output.put_line ('Normal Function Cpu Time ='||
 18             ( dbms_utility.get_cpu_time - l_cputime) ||
 19             ' Elaps Time ='|| ( dbms_utility.get_time - l_elapstime) ) ;
 20
 21     l_sql := ' with function f(x varchar2) '||
 22                      ' return number as '||
 23                      ' begin '||
 24                      ' return (length(x)); '||
 25                      ' end; '||
 26                      ' select f(object_name) from t ' ;
 27     l_cputime := dbms_utility.get_cpu_time ;
 28     l_elapstime := dbms_utility.get_time;
 29     open l_cursor for l_sql;
 30     fetch l_cursor bulk collect into l_array;
 31     close l_cursor;
 32
 33     dbms_output.put_line ('WITH Clause Cpu Time ='||
 34             ( dbms_utility.get_cpu_time - l_cputime) ||
 35             ' Elaps Time ='|| ( dbms_utility.get_time - l_elapstime) );
 36  end;
 37  /
Normal Function Cpu Time =30 Elaps Time =30
WITH Clause Cpu Time =6 Elaps Time =6

PL/SQL procedure successfully completed.

dba_user@PDB1>

As you can see there is a huge difference in CPU time, when the same function is redefined as PRAGMA UDF, the difference appears to be zero fold.


dba_user@PDB1>
dba_user@PDB1> create or replace function udf_function(x in varchar2)
  2  return number
  3  as
  4     pragma udf;
  5  begin
  6     return (length(x));
  7  end;
  8  /

Function created.

dba_user@PDB1> declare
  2     type num_array is table of number;
  3     l_array num_array := num_array();
  4     l_sql varchar2(4000);
  5     l_cursor sys_refcursor;
  6     l_cputime number;
  7     l_elapstime number;
  8  begin
  9     l_sql :=' select udf_function(object_name) from t ';
 10
 11     l_cputime := dbms_utility.get_cpu_time ;
 12     l_elapstime := dbms_utility.get_time;
 13     open l_cursor for l_sql;
 14     fetch l_cursor bulk collect into l_array;
 15     close l_cursor;
 16
 17     dbms_output.put_line ('UDF Function Cpu Time ='||
 18             ( dbms_utility.get_cpu_time - l_cputime) ||
 19             ' Elaps Time ='|| ( dbms_utility.get_time - l_elapstime) ) ;
 20
 21     l_sql := ' with function f(x varchar2) '||
 22                      ' return number as '||
 23                      ' begin '||
 24                      ' return (length(x)); '||
 25                      ' end; '||
 26                      ' select f(object_name) from t ' ;
 27     l_cputime := dbms_utility.get_cpu_time ;
 28     l_elapstime := dbms_utility.get_time;
 29     open l_cursor for l_sql;
 30     fetch l_cursor bulk collect into l_array;
 31     close l_cursor;
 32
 33     dbms_output.put_line ('WITH Clause Cpu Time ='||
 34             ( dbms_utility.get_cpu_time - l_cputime) ||
 35             ' Elaps Time ='|| ( dbms_utility.get_time - l_elapstime) );
 36  end;
 37  /
UDF Function Cpu Time =8 Elaps Time =7
WITH Clause Cpu Time =6 Elaps Time =5

PL/SQL procedure successfully completed.

dba_user@PDB1>

BEQUEATH Clause in Oracle 12c

When invoker rights (IR) routine invoked from a View, then IR routine is no more Invoker rights rather it appear as Define routine (DR).

rajesh@ORA11GR2> create or replace function f
  2  return number
  3  authid current_user
  4  as
  5     l_cnt number;
  6  begin
  7     select count(*) into l_cnt
  8     from dept ;
  9     return l_cnt;
 10  end;
 11  /

Function created.

Elapsed: 00:00:00.17
rajesh@ORA11GR2> create or replace view v
  2  as
  3  select f from dual ;

View created.

Elapsed: 00:00:00.90
rajesh@ORA11GR2> create user a identified by a;

User created.

Elapsed: 00:00:00.36
rajesh@ORA11GR2> grant create session to a;

Grant succeeded.

Elapsed: 00:00:01.16
rajesh@ORA11GR2> grant select on v to a;

Grant succeeded.

Elapsed: 00:00:00.05
rajesh@ORA11GR2> connect a/a@ora11g
Connected.
a@ORA11GR2> select * from rajesh.v ;

         F
----------
         4

1 row selected.

Elapsed: 00:00:00.11
a@ORA11GR2> desc dept;
ERROR:
ORA-04043: object dept does not exist

a@ORA11GR2> desc rajesh.dept
ERROR:
ORA-04043: object rajesh.dept does not exist

a@ORA11GR2>

Eventhough the user 'A' dont have access to Dept table is still able to access this view 'V' without any issue, but this got changed starting Oracle 12c, where the user got privilege of controlling DR and IR in view using BEQUEATH Clause


rajesh@PDB1> create or replace view v
  2  BEQUEATH CURRENT_USER
  3  as
  4  select f from dual ;

View created.
Elapsed: 00:00:00.16
rajesh@PDB1> connect a/a@pdb1
Connected.
a@PDB1>
a@PDB1> select * from rajesh.v ;
select * from rajesh.v
       *
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "RAJESH.F", line 7

Elapsed: 00:00:00.02
a@PDB1>
a@PDB1> desc dept
ERROR:
ORA-04043: object dept does not exist

a@PDB1> desc rajesh.dept
ERROR:
ORA-04043: object rajesh.dept does not exist

a@PDB1>

Now the user 'A' have access to view 'v'  but not to the "rajesh.dept" table, hence the access to view 'v' from schema 'A' got failed.