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.

gather_plan_statistics

Its often recommended when you are tuning a sql statement the optimizer cardinality estimates are accurate, but how to verify that? by using an undocumented hint  gather_plan_statistics 

rajesh@ORA10GR2> create table t1 as select * from all_objects;

Table created.

rajesh@ORA10GR2> create table t2 as select * from all_objects;

Table created.

rajesh@ORA10GR2>
rajesh@ORA10GR2> alter table t1 add constraint t1_pk primary key(object_id);

Table altered.

rajesh@ORA10GR2> alter table t2 add constraint t2_pk primary key(object_id);

Table altered.

rajesh@ORA10GR2>
rajesh@ORA10GR2> begin
  2     dbms_stats.gather_table_stats(user,'T1');
  3     dbms_stats.gather_table_stats(user,'T2');
  4  end;
  5  /

PL/SQL procedure successfully completed.

rajesh@ORA10GR2>


rajesh@ORA10GR2> @d:\script.sql
rajesh@ORA10GR2> variable x varchar2(20);
rajesh@ORA10GR2> exec :x :='SYS';

PL/SQL procedure successfully completed.

rajesh@ORA10GR2>
rajesh@ORA10GR2> set termout off;
rajesh@ORA10GR2>
rajesh@ORA10GR2>
rajesh@ORA10GR2>
rajesh@ORA10GR2> select * from table( dbms_xplan.display_cursor(null,0,'ALLSTATS +peeked_binds'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------
SQL_ID  f6k8ugj5gkwz0, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from t1, t2 where t1.object_id = t2.objecT_id and t1.owner = :x
Plan hash value: 1838229974
-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem |  O/1/M   |
-------------------------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN         |      |      1 |   1812 |  23083 |00:00:06.32 |    1781 |   1614 |  3333K|   953K|     1/0/0|
|*  2 |   TABLE ACCESS FULL| T1   |      1 |   1766 |  23083 |00:00:00.01 |     814 |    807 |       |       |          |
|   3 |   TABLE ACCESS FULL| T2   |      1 |  58267 |  58267 |00:00:31.69 |     967 |    807 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------
   1 - (VARCHAR2(30), CSID=178): 'SYS'
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   2 - filter("T1"."OWNER"=:X)

25 rows selected.
rajesh@ORA10GR2>

Starts:  This is the number of times the particular step is executed. Most of the times it is 1, but in case of a nested loop you will likely see a higher number. Note that it shows the actual number of times the operation has been performed. So it only shows up when plan statistics have been gathered by using the /*+ gather_plan_statistics */ hint or by setting the statistics_level parameter to all.

Incase of Nested Loops the "starts" looks like this

rajesh@ORA10GR2>
rajesh@ORA10GR2> select * from table( dbms_xplan.display_cursor(null,0,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
SQL_ID  cnr0m6hbgaq4c, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ t1.owner, t2.object_id from t1, t2 where t1.objecT_id =
t2.objecT_id and t1.object_id between 50 and 60
Plan hash value: 3696715680
---------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS                |       |      1 |      4 |     11 |00:00:00.03 |      18 |      4 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |      1 |      5 |     11 |00:00:00.01 |       5 |      2 |
|*  3 |    INDEX RANGE SCAN          | T1_PK |      1 |      5 |     11 |00:00:00.01 |       3 |      2 |
|*  4 |   INDEX UNIQUE SCAN          | T2_PK |     11 |      1 |     11 |00:00:00.02 |      13 |      2 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T1"."OBJECT_ID">=50 AND "T1"."OBJECT_ID"<=60)
   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
       filter(("T2"."OBJECT_ID"<=60 AND "T2"."OBJECT_ID">=50))

23 rows selected.
rajesh@ORA10GR2>

We scanned index T1_PK once with filter T1.OBJECT_ID>=50 AND T1.OBJECT_ID<=60 and found 11 rows, for each of these rows we hit the index T2_PK to find the object_id identified by step#3 exists in step#4.  So 11 rows followed by 11 hits and hence Starts=11 at step#4.

E-Rows:
This is the estimated number of rows. This is the number you would previously see in the Rows column after a "explain plan for " and "select * from table(dbms_xplan.display)". It is the cardinality of this step, which the cost based optimizer calculated during its parse phase. It may be totally off, because it is an expectation based on the statistics. For several reasons an explain plan can "lie", so you'll want to be careful with this statistic.
A-Rows:
This is the actual number of rows. This number is taken from the plan_statistics, so it only shows up when plan statistics have been gathered. In a tkprof file, this number corresponds to the Rows column in the "Row source operation" section. It is the real number of rows processed by this step. You can get valuable information when this A-rows number differs a lot from E-rows

Reads: This is the number of physical reads performed

Buffers: This represents number of logic IO's performed.

MEMSTATS: This displays statistics regarding the estimated sizes of the required PGA workareas to do a SORT, HASH JOIN, BITMAP MERGE or BITMAP CREATE actions. The three columns are "OMem", "1Mem" and "Used-Mem" will only be displayed if there is an operation involved in the query that used PGA workarea memory.  the memory stats matchup with entries in v$sql_workarea

rajesh@ORA10GR2> select estimated_optimal_size/1024 as estimated_kb,
  2         estimated_onepass_size/1024 as estimated_01pass_kb,
  3         last_memory_used/1024 as last_used_kb
  4  from v$sql_workarea
  5  where sql_id ='f6k8ugj5gkwz0'
  6  and child_number = 0 ;

ESTIMATED_KB ESTIMATED_01PASS_KB LAST_USED_KB
------------ ------------------- ------------
        3333                 953         3471

1 row selected.

rajesh@ORA10GR2>

you can use 'PEEKED_BINDS' for the format parameter, giving a section with the values of bind variables that are used. This is a lot easier than digging through the trace file looking for a "#BIND" and a "value=" clause.

Configuring Clients to Use the External Password Store

Hardcoding passwords in shell scripts is a bad practice. The source code may be shared by many developers, may resides on unsecure servers (CVS), may be printed, etc... The passwords may change often too.

So without Hardcoding passwords how to connect to database server? Its all possible by means of storing password in a Client side wallet.  If you want a client to use the secure external password store feature, then perform the following configuration task:

Step#1 Create a wallet on the client by using the following syntax at the command

D:\app\179818\product\11.2.0>mkstore -wrl d:\app\179818\product\11.2.0 -create
Oracle Secret Store Tool : Version 12.1.0.1
Copyright (c) 2004, 2012, Oracle and/or its affiliates. All rights reserved.
Enter password:
Enter password again:
D:\app\179818\product\11.2.0>


d:\app\179818\product\11.2.0  - is the path to the directory where you want to create and store the wallet

Step#2 Create database connection credentials in the wallet by using the following syntax at the command line

D:\app\179818\product\11.2.0>mkstore -wrl D:\app\179818\product\11.2.0 -createCredential iradsnvl rajesh
Oracle Secret Store Tool : Version 12.1.0.1
Copyright (c) 2004, 2012, Oracle and/or its affiliates. All rights reserved.
Your secret/Password is missing in the command line
Enter your secret/Password:
Re-enter your secret/Password:
Enter wallet password:
Create credential oracle.security.client.connect_string1
D:\app\179818\product\11.2.0>

Step#3 In the client sqlnet.ora file, enter the WALLET_LOCATION parameter and set it to the directory location of the wallet you created in Step#1 and enter the SQLNET.WALLET_OVERRIDE parameter and set it to TRUE as follows

D:\app\client\179818\product\12.1.0\client_1\network\admin>type sqlnet.ora
# sqlnet.ora Network Configuration File: D:\app\client\179818\product\12.1.0\client_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.
# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
SQLNET.WALLET_OVERRIDE = TRUE
WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
  (DIRECTORY = D:\app\179818\product\11.2.0)
  )
 )

Once done with the above steps, you can connect to database without providing credentials. all those details will be available from Wallet.

C:\Users\179818>sqlplus /@iradsnvl
SQL*Plus: Release 12.1.0.1.0 Production on Sat Nov 9 13:23:41 2013
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
rajesh@ORA10GR2>

Load mutliple files using multiple load options in sql loader

Learnt something new in Oracle, its all about handling mutliple load options for loading into multiple table using sql-loader.

Here is a quick demo of what i have.

rajesh@ORA10GR2> select * from t1;

no rows selected

rajesh@ORA10GR2> select * from t2;

         X          Y
---------- ----------
         2          1

1 row selected.

rajesh@ORA10GR2>

D:\app\179818\product\11.2.0\dbhome_1\BIN>sqlldr rajesh/oracle@iradsnvl control=d:\ctl.txt log=d:\log.txt data=d:\data.txt

SQL*Loader: Release 11.2.0.3.0 - Production on Tue Oct 22 13:58:52 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 4

D:\app\179818\product\11.2.0\dbhome_1\BIN>exit

rajesh@ORA10GR2>
rajesh@ORA10GR2>
rajesh@ORA10GR2> select * from t1;

         X          Y
---------- ----------
         1          1
         1          2
         1          3

3 rows selected.

rajesh@ORA10GR2> select * from t2;

         X          Y
---------- ----------
         2          4

1 row selected.

rajesh@ORA10GR2>

now the data got loaded into T1 but got updated in T2.

Here is what i have in Control file ( Table T1 in TRUNCATE option and T2 in REPLACE option)

D:\>type ctl.txt
LOAD DATA
INTO TABLE T1
TRUNCATE
WHEN X='1'
FIELDS TERMINATED BY ','  TRAILING NULLCOLS
(
        X ,
        Y
)
INTO TABLE T2
REPLACE
WHEN X='2'
FIELDS TERMINATED BY ','  TRAILING NULLCOLS
(
        X position(1) ,
        Y
)

and data file contents are here.

D:\>type data.txt
1,1
1,2
1,3
2,4