Skip to Main Content

PeopleSoft Enterprise

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

what is the use of Oracle Data Masking?

Aravindan JSep 23 2013

Hi,

Iam using HCM 9.2, PT 8.53, DB.11.2.0.2.

What is this particular navigation does??

PeopleTools >Utilities> Administration >Oracle Data Masking

does this encrypts the value in the specified field??
Suggestions welcomed !!

Aravind.

Comments

Charles Hooper
Anthony,

If you are trying to see the consequences of missing indexes on the child table's declared foreign key columns, you should be looking for blocking TM type locks. For example:
SELECT
  S.SID,
  S.USERNAME,
  S.PROGRAM,
  S.SQL_ID,
  S.SQL_ADDRESS,
  S.SQL_HASH_VALUE,
  S.SQL_CHILD_NUMBER CN,
  S.ROW_WAIT_OBJ#,
  S.ROW_WAIT_FILE#,
  S.ROW_WAIT_BLOCK#,
  S.ROW_WAIT_ROW#,
  L.LMODE,
  L.REQUEST,
  L.ID1,
  L.ID2,
  L.TYPE,
  L.BLOCK
FROM
  V$LOCK L,
  V$SESSION S
WHERE
  (L.ID1, L.ID2, L.TYPE) IN
    (SELECT
      ID1,
      ID2,
      TYPE
    FROM
      V$LOCK
    WHERE
      REQUEST > 0)
  AND L.SID=S.SID;
An example output with 3 sessions involved might look like this:
SID USERNAME PROGRAM     SQL_ID        SQL_ADDR SQL_HASH_VALUE CN ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# LMODE REQUEST ID1   ID2 TY BLOCK
--- -------- ----------- ------------- -------- -------------- -- ------------- -------------- --------------- ------------- ----- ------- ----- --- -- -----
307 USER2    sqlplus.exe 4rtg0hv0atfkx 224E2B7C     3232545373  0            -1              0               0             0     3       5 82913   0 TM     1
314 USER3    sqlplus.exe cv338j6z2530g 224DAE38     3189935119  0            -1              0               0             0     0       3 82913   0 TM     0
320 TESTUSER sqlplus.exe 0vbusv12hnbk6 22480E10     1158295110  0         12517              1           29656             0     3       0 82913   0 TM     1
If you search on the Internet or read various Oracle performance tuning books, you are bound to find several variants of the above SQL statement - some of those variant SQL statement versions simply do not work. Investigating why one of these variants does not work can be helpful to understanding the information exposed by some of the Oracle performance views. Below is a link to one of my articles were various people discussed why a variant of the SQL statement might produce misleading information:
http://hoopercharles.wordpress.com/2010/06/03/lock-watching-what-is-wrong-with-this-sql-statement/

If you want to work through an example of what happens when indexes are missing on the foreign key columns in the child table, take a look at the following article. On Oracle Database 10.2.0.5 and below the example will trigger a TX enqueue once the TM lock is released, while on 11.1.0.6 and later the example will trigger a deadlock when the TM lock is released:
http://hoopercharles.wordpress.com/2010/01/07/deadlock-on-oracle-11g-but-not-on-10g/

Charles Hooper
http://hoopercharles.wordpress.com/
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Anthony.P
Thank you very much Charles, but I'm still doing something wrong since your request display no rows...
By the way, the one I've written is this one:
SELECT
  s.sid ||','||s.serial# AS "Sid/Serial",
  s.username AS "Name",
  l.type AS "lkType",
  decode(l.lmode,1,'none',2,'SS',3,'SX',4,'S',5,'SSX',6,'X',null)  "lkMode",
  decode(l.request,1,'none',2,'SS',3,'SX',4,'S',5,'SSX',6,'X',null)  "lkReq",
  l.ctime "Seconds",
  decode(l.block,1,'Yes',NULL) AS "Blocking?",
  l.id1, l.id2,
  o.object_name "Object"
FROM
  v$lock l,
  v$session s,
  dba_objects o,
  v$locked_object lko
WHERE
  s.sid=l.sid AND
  lko.session_id=s.sid AND
--  (l.request != 0 OR l.block=1) AND
  lko.object_id=o.object_id
ORDER BY l.ctime DESC, o.object_name;
I have to read more carefully your blog...
jgarry
Maybe you need two sessions to see one get blocked? See http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:292016138754#3986404442549
Jonathan Lewis
Anthony wrote:

I've seen many many posts here where guys have troubles with locks created on parent table when editing a child table.
Can you supply links to a couple of articles.
The "foreign key locking" problem is about locks on the CHILD table when you edit (specifically, modify or delete the key) on the PARENT.

You may, as a consequence, see people saying that they can't update the parent when someone else is editing a child, but that's because they want to lock the child (typically in mode 4 (S), possibly mode 5 (SSX)) when the other person is holding an incompatible lock (typically mode 3) on the child.


Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
Author: <b><em>Oracle Core</em></b>
Charles Hooper
Anthony,

Thank you for providing the SQL statement that you are using.

Jonathan and Joel have provided very good information to help you. The SQL statement that you posted has some of the same issues as the SQL statement I found in a book (referenced in the first of the blog articles I previously provided). I believe that it is a key point that an entry in the V$LOCK view is insufficient to state that the session will block another session. As mentioned by Jonathan, there are different lock modes (NULL (1), SS (2), SX (3), S (4), SSX (5), and X (6)) for the various lock types (TX, TM, etc.) - some of those lock modes (and type combinations) will potentially block other sessions from accomplishing a specific task. For example, if one session modifies a row in a table, the session will take a TM lock on the table, which will prevent another session from dropping that table but not necessarily block another session from inserting, updating, or deleting a row in the table. The lock mode compatibility (whether one session will block another) is found in the documentation here:
http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_lock.htm#sthref3243

You will need at least 2 sessions for your experiments to see the potential issues of missing foreign key indexes on the child tables. As Jonathan clearly stated, it is the child table that will be the determining factor in the blocking table lock if a suitable index is not present on that table when the parent table is modified (specifically when the primary and/or unique key columns in the parent table are modified or possibly when a row is deleted from the parent table).

Charles Hooper
http://hoopercharles.wordpress.com/
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Anthony.P
Thank you all,

Thanks to your help, I could reproduce this issue. I do need 2 sessions (what I did before but didn't mentioned here) but I was doing a wrong update to watch the issue.

Session #2:
insert into t2 values(3,1);

Session #1:
update t1 set id=4 where id=3;
-- (hanging)

Where t1(id) is PK. Before, I was performing updates like "update t1 set idt='Three' where id=3;" which can run without problem.

Thanks again, and I'm going to write a more accurate script to display locks.
1 - 6
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Oct 21 2013
Added on Sep 23 2013
0 comments
87 views