Backport of Data Redaction in 11.2.0.4

Data Redaction has been backported to Oracle 11.2.0.4 .  See the examples below . 
For the original post on Data Redaction , please see this link http://mfzahirdba.blogspot.com/2014/04/data-redaction-in-oracle.html. 

I would not upgrade to 11.2.0.4 , as the support for this release ends soon ( in few months from now) . :-( 

Examples below 


SQL> select * from v$version ;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for 64-bit Windows: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> create table employee as
  2      select lpad(object_id * object_id, 9, 5)  ssn , object_name ename  ,
  3          object_id * 2 as empid
  4          from all_objects
  5          where object_id > 70000  and rownum <= 10 ;

Table created.

SQL> col ename format a30
SQL> Select * from employee;

SSN       ENAME                               EMPID
--------- ------------------------------ ----------
490014000 USER_XML_SCHEMAS                   140002
490042000 DBA_XML_INDEXES                    140006
490056001 ALL_XML_INDEXES                    140008
490070002 ALL_XML_INDEXES                    140010
490084003 USER_XML_INDEXES                   140012
490098004 USER_XML_INDEXES                   140014
490112006 USER_XML_COLUMN_NAMES              140016
490126008 USER_XML_COLUMN_NAMES              140018
490154012 DBA_XML_SCHEMA_IMPORTS             140022
490182016 DBA_XML_SCHEMA_INCLUDES            140026

10 rows selected.

SQL>            BEGIN
  2            DBMS_REDACT.ADD_POLICY(
  3              object_schema        => 'scott',
  4              object_name          => 'employee',
  5              column_name          => 'ssn',
  6              policy_name          => 'hide_emp_ssn',
  7              function_type        => DBMS_REDACT.FULL,
  8               function_parameters => DBMS_REDACT.REDACT_US_SSN_F5,
  9              expression           => 'SYS_CONTEXT(''USERENV'',''CLIENT_INFO'') != ''PAYROLL''');
 10        END;
 11        /

PL/SQL procedure successfully completed.

SQL> exec dbms_application_info.set_client_info('PAYROLL') ;

PL/SQL procedure successfully completed.

SQL> Select SYS_CONTEXT('USERENV','CLIENT_INFO') from dual ;

SYS_CONTEXT('USERENV','CLIENT_INFO')
--------------------------------------------------------------------------------
PAYROLL

SQL> Select * from employee;

SSN       ENAME                               EMPID
--------- ------------------------------ ----------
490014000 USER_XML_SCHEMAS                   140002
490042000 DBA_XML_INDEXES                    140006
490056001 ALL_XML_INDEXES                    140008
490070002 ALL_XML_INDEXES                    140010
490084003 USER_XML_INDEXES                   140012
490098004 USER_XML_INDEXES                   140014
490112006 USER_XML_COLUMN_NAMES              140016
490126008 USER_XML_COLUMN_NAMES              140018
490154012 DBA_XML_SCHEMA_IMPORTS             140022
490182016 DBA_XML_SCHEMA_INCLUDES            140026

10 rows selected.

SQL>  exec dbms_application_info.set_client_info('MIS') ;

PL/SQL procedure successfully completed.

SQL> Select SYS_CONTEXT('USERENV','CLIENT_INFO') from dual ;

SYS_CONTEXT('USERENV','CLIENT_INFO')
--------------------------------------------------------------------------------
MIS

SQL> Select * from employee;

SSN       ENAME                               EMPID
--------- ------------------------------ ----------
          USER_XML_SCHEMAS                   140002
          DBA_XML_INDEXES                    140006
          ALL_XML_INDEXES                    140008
          ALL_XML_INDEXES                    140010
          USER_XML_INDEXES                   140012
          USER_XML_INDEXES                   140014
          USER_XML_COLUMN_NAMES              140016
          USER_XML_COLUMN_NAMES              140018
          DBA_XML_SCHEMA_IMPORTS             140022
          DBA_XML_SCHEMA_INCLUDES            140026

10 rows selected.

SQL>

Color / Colour the SQL

We use AWR and ASH to troubleshoot performance ( provided we licensed diagostic pack option )  .
Typically , only the top consumers ( SQLs ) is displayed in AWR. 

You can color / colour the SQL to  see the execution plans and other metrics of a SQL , 
even if the SQL does not qualify as top consumer ( if the SQL is in the cache at the snapshot time) . 
The utility is ADD_COLORED_SQL  in DBMS_WORKLOAD_REPOSITORY.  

As a side note , I found a documentation bug in oracle 11g documentation . It was wrongly documented as DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_HTML , instead of ADD_COLORED_SQL.  But this is corrected in Oracle 12c documentation. 

  
Here is an example. 

I am using "ZAHIR_COLOR_SQL" as comment just for clarity . You need not comment to get the SQL id. 


Session 1 :
---------

SQL> show user
USER is "SCOTT"
SQL> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SQL> Select /* ZAHIR_COLOR_SQL */ empno , ename from  emp;

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS

     EMPNO ENAME
---------- ----------
      7900 JAMES
      7902 FORD
      7934 MILLER

14 rows selected.


Go to another session and determine the SQL ID of this SQL and color it. 

SQL> select sql_id , sql_text  from v$sql where sql_text like '%ZAHIR_COLOR%';

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------
dfjudnqgk5c11
select sql_id , sql_text  from v$sql where sql_text like '%ZAHIR_COLOR%'

drfh6qd1ctrb7
Select /* ZAHIR_COLOR_SQL */ empno , ename from  emp


SQL> exec DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL(sql_id  => 'drfh6qd1ctrb7');

PL/SQL procedure successfully completed.


SQL> exec dbms_workload_repository.create_snapshot();

PL/SQL procedure successfully completed.


We can look at the execution plan using dbms_xplan or via AWR from Enterprise Manager or AWR  by executing awrrpt.sql . 

Here is an example of getting the execution plan from AWR as shown below. 


SQL> select * from table(dbms_xplan.display_awr('drfh6qd1ctrb7'))
  2  ;

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
SQL_ID drfh6qd1ctrb7
--------------------
Select /* ZAHIR_COLOR_SQL */ empno , ename from  emp

Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   140 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------


13 rows selected.



Comments Welcome. 

DESCRIBE ref cursor - How to display the metadata of the ref cursor.

In  most of the development houses , the database API ( aka  package ) are developed by the database development team and then the API's signature handed over the application / web development team. 

For the most part , it is easier to print the ref cursor contents ; but to get the column name and its datatypes , it is not that obvious in sqlplus ( Some of the tools has features ) .  In Oracle 11g , we can use dbms_sql package to display the column type , size , precision and so on. 

Let us see an example . 
First , let us create a package and package body .  This API will display 10 zipcodes for a given state code. 



SQL> desc zips
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ZIPCODE                                            VARCHAR2(5)
 PO_NAME                                            VARCHAR2(255)
 COUNTY                                             VARCHAR2(255)
 STATE                                              VARCHAR2(2)

SQL> create or replace PACKAGE listzipcode_pkg
  2  AS
  3    PROCEDURE list_by_statecode_p(
  4        statecode_p VARCHAR2 ,
  5        zipcode_list_rc OUT sys_refcursor) ;
  6  END listzipcode_pkg;
  7  /

Package created.

SQL> CREATE OR REPLACE PACKAGE body listzipcode_pkg
  2  AS
  3  PROCEDURE list_by_statecode_p(
  4      statecode_p VARCHAR2 ,
  5      zipcode_list_rc OUT sys_refcursor)
  6  AS
  7  BEGIN
  8    OPEN zipcode_list_rc FOR SELECT * FROM
  9    (
 10      SELECT
 11        state ,
 12        zipcode ,
 13        to_number(zipcode) AS ZipCode_Number
 14      FROM
 15        zips
 16      WHERE
 17        STATE = statecode_p
 18      ORDER BY
 19        zipcode
 20    )
 21    WHERE rownum < 11 ;
 22  END list_by_statecode_p;
 23  END listzipcode_pkg;
 24  /

Package body created.


We can use the ref cursor to display the contents as shown below. 

SQL> variable x refcursor;
SQL> exec listzipcode_pkg.list_by_statecode_p( 'NJ' , :x) ;

PL/SQL procedure successfully completed.

SQL> print :x ;

ST ZIPCO ZIPCODE_NUMBER
-- ----- --------------
NJ 07001           7001
NJ 07002           7002
NJ 07003           7003
NJ 07004           7004
NJ 07005           7005
NJ 07006           7006
NJ 07007           7007
NJ 07008           7008
NJ 07009           7009
NJ 07010           7010


10 rows selected.


To describe ( aka to list the column names , data types ) , let us create an anonymous PL/SQL block . 

Here we go.

SQL> SET serveroutput ON
SQL> DECLARE
  2    l_rcursor sys_refcursor;
  3    l_statecode   VARCHAR2(2);
  4    l_col_type    VARCHAR2(100) ;
  5    l_cursor_name VARCHAR2(100) ;
  6    l_colCnt      NUMBER;
  7    l_descTbl dbms_sql.desc_tab;
  8  BEGIN
  9    l_statecode := 'NJ';
 10    listzipcode_pkg.list_by_statecode_p( statecode_p => l_statecode ,
 11    zipcode_list_rc => l_rcursor) ;
 12    l_cursor_name := 'list_by_statecode_p';
 13    dbms_sql.describe_columns ( c => dbms_sql.to_cursor_number(l_rcursor) ,
 14    col_cnt => l_colCnt, desc_t => l_descTbl );
 15    dbms_output.put_line( RPAD( 'Name' , 25 , ' ' ) || 'Type' );
 16    dbms_output.put_line( RPAD( '----' , 25 , ' ' ) || '----' );
 17    FOR i IN 1 .. l_colCnt
 18    LOOP
 19      IF l_descTbl(i).col_type    = 2 THEN
 20        l_col_type               := 'NUMBER';
 21      ELSIF l_descTbl(i).col_type = 1 THEN
 22        l_col_type               := 'VARCHAR2';
 23      ELSE
 24        l_col_type := l_descTbl(i).col_type ;
 25      END IF;
 26      dbms_output.put_line( RPAD( l_descTbl(i).col_name, 25 , ' ' ) || l_col_type
 27      );
 28    END LOOP;
 29  END ;
 30
 31  /
Name                     Type
----                     ----
STATE                    VARCHAR2
ZIPCODE                  VARCHAR2
ZIPCODE_NUMBER           NUMBER


PL/SQL procedure successfully completed.



In the PL/SQL block , we use dbms_sql to get the metadata contents of the ref cursor . The above shows only the column name , column's datatype . We can extend the above block to show the maximum length , precision , scale and so forth. 

Another thing to note is that the return value of "col_type" is an integer . You need to look it up against the associated name . This information can be found in ALL_SOURCE view. 


SQL> select   text   from   all_source
  2  where   owner = 'SYS' and name = 'DBMS_TYPES' and type = 'PACKAGE';

TEXT
--------------------------------------------------------------------------------
PACKAGE dbms_types AS
  TYPECODE_DATE            PLS_INTEGER :=  12;
  TYPECODE_NUMBER          PLS_INTEGER :=   2;
  TYPECODE_RAW             PLS_INTEGER :=  95;
  TYPECODE_CHAR            PLS_INTEGER :=  96;
  TYPECODE_VARCHAR2        PLS_INTEGER :=   9;
  TYPECODE_VARCHAR         PLS_INTEGER :=   1;
  TYPECODE_MLSLABEL        PLS_INTEGER := 105;
  TYPECODE_BLOB            PLS_INTEGER := 113;
  TYPECODE_BFILE           PLS_INTEGER := 114;
  TYPECODE_CLOB            PLS_INTEGER := 112;
  TYPECODE_CFILE           PLS_INTEGER := 115;
  TYPECODE_TIMESTAMP       PLS_INTEGER := 187;
  TYPECODE_TIMESTAMP_TZ    PLS_INTEGER := 188;
  TYPECODE_TIMESTAMP_LTZ   PLS_INTEGER := 232;
  TYPECODE_INTERVAL_YM     PLS_INTEGER := 189;
  TYPECODE_INTERVAL_DS     PLS_INTEGER := 190;

  TYPECODE_REF             PLS_INTEGER := 110;
  TYPECODE_OBJECT          PLS_INTEGER := 108;
  TYPECODE_VARRAY          PLS_INTEGER := 247;            /* COLLECTION TYPE */
  TYPECODE_TABLE           PLS_INTEGER := 248;            /* COLLECTION TYPE */
  TYPECODE_NAMEDCOLLECTION PLS_INTEGER := 122;
  TYPECODE_OPAQUE          PLS_INTEGER := 58;                 /* OPAQUE TYPE */

  /* NOTE: These typecodes are for use in AnyData api only and are short forms
     for the corresponding char typecodes with a charset form of SQLCS_NCHAR.
  */
  TYPECODE_NCHAR           PLS_INTEGER := 286;
  TYPECODE_NVARCHAR2       PLS_INTEGER := 287;
  TYPECODE_NCLOB           PLS_INTEGER := 288;

  /* Typecodes for Binary Float, Binary Double and Urowid. */
  TYPECODE_BFLOAT          PLS_INTEGER := 100;
  TYPECODE_BDOUBLE         PLS_INTEGER := 101;
  TYPECODE_UROWID          PLS_INTEGER := 104;

  SUCCESS                  PLS_INTEGER := 0;
  NO_DATA                  PLS_INTEGER := 100;

  /* Exceptions */
  invalid_parameters EXCEPTION;
  PRAGMA EXCEPTION_INIT(invalid_parameters, -22369);

  incorrect_usage EXCEPTION;
  PRAGMA EXCEPTION_INIT(incorrect_usage, -22370);

  type_mismatch EXCEPTION;
  PRAGMA EXCEPTION_INIT(type_mismatch, -22626);

END dbms_types;


51 rows selected.


Happy describing . 

Comments Welcome !