Thinking Out Loud

October 31, 2012

Knowledge vs Experience

Filed under: Uncategorized — mdinh @ 9:36 pm

OFF TOPIC!

I know how to change engine oil for automobiles having been doing so since I was 18+. Today was the first time I have had to change the engine oil for my wife’s 2010 Highlander. Spent several hours calling and driving to auto parts stores looking for the correct oil filter wrench. The general auto parts store people did not know the correct part and even one of Toyota Parts Person did not know as well.

This website was not a great help, providing inaccurate information – http://www.trdparts4u.com/scripts/prodView.asp?idproduct=-8482

Finally, I was able to find a dealership which had the correct information – 64mm wrench with 14 flutes, not 65mm

Having the experience, if anyone ever asks, I now know.

October 29, 2012

dbms_redefinition and NOVALIDATE

Filed under: oracle — mdinh @ 4:52 am

Here are some references:

Master Note: An overview of online redefinition of tables (DBMS_REDEFINITION) [ID 1357825.1]
SYS.DBMS_REDEFINITION Fails With ORA-600 [17183] On Larger Table [ID 1458923.1]
WHY ARE ‘NOT NULL’ CONSTRAINTS NOT COPIED BY DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS? [ID 1089860.1]
AN EXAMPLE OF A COMPLEX ONLINE TABLE REDEFINITION (DBMS_REDEFINITION) [ID 1358236.1]
DBMS_REDEFINITION ONLINE REORGANIZATION OF TABLES [ID 149564.1]

Bug 13528814 – ORA-600 [17182] on redefinition with many referential constraints – superceded [ID 13528814.8]
Bug 13572659 – DBMS_REDEFINITION disables Foreign Keys used for REFERENCE partitioning [ID 13572659.8]
Bug 8507266 DBMS_REDEFINITION needs ANY privileges – cannot be used by normal users

Privileges Required
====================
Following privileges are needed to run this package:
– Execute privilege to DBMS_REDEFINITION
– Create any table
– Alter any table
– Drop any table
– Lock any table
– Select any table

If COMPATIBLE => 10.2 the constraints will be copied … but will be in NOVALIDATE mode

Here is the test case using SQL*Plus: Release 11.2.0.2.0

UPDATED: link to a nicer text format – dbms_redefinition.out

October 24, 2012

Decrypting SHA1 Password

Filed under: Uncategorized — mdinh @ 1:32 pm

In my previous post, I have written about data privacy and how data destruction and encryption may be viable methods.

Then, I was in awe when a colleague was showed me how easily SHA1 encryption can be decrypted.

Chose your passwords carefully.

Click image to enlarge.

October 16, 2012

Using Oracle regexp_like to encrypt valid SSN

Filed under: 11g,oracle — mdinh @ 5:41 am

In my previous post on data destruction, I implement a quick solution to encrypt SSN filtering on  – REGEXP_LIKE(ssn,’^[[:digit:]]{9}$’).

As the result, even invalid SSN will be encrypted. It all depends on how accurate the process should be. However, keep in mind the above implementation will modify more rows than necessary.

Here is an improved version with test case.

Test Case:
create table t(ssn varchar2(40));
insert into t values(‘326217894’);
insert into t values(‘4522969912’);
insert into t values(‘000333333’);
insert into t values(‘111004444’);
insert into t values(‘004770000’);
insert into t values(‘023882391’);
insert into t values(‘3452371’);
insert into t values(‘123456’);
insert into t values(‘a123456’);
commit;

I found a solution from netmail but it would not work in Oracle:

(?!000\d{6})      Exclude any number that contains 000 followed by 6 digits
(?!\d{3}00\d{4})  Exclude any number that begins with 3 digits, followed by 00, followed by 4 digits
(?!\d{5}0000)     Exclude any number that begins with 5 digits followed by 0000
(?!666\d{6})      Exclude any number that contains 666 followed by 6 digits

The ! (exclamation) would return ZERO rows in Oracle with SQL*Plus. The alternative is to use not regexp_like.

INVALID SSN:

SQL> select ssn,lpad(ssn,9,0) fmt_ssn from t where regexp_like(lpad(ssn,9,0),'(?000\d{6})|(?\d{3}00\d{4})|(?\d{5}0000)|(?666\d{6})') 
and length(ssn) between 7 and 9;  2  

SSN          FMT_SSN
------------ ------------
000333333    000333333
111004444    111004444
004770000    004770000

9-DIGIT STRING:

SQL> select ssn,lpad(ssn,9,0) fmt_ssn from t where regexp_like(lpad(ssn,9,0),'^[0-8]{3}[0-9]{2}[0-9]{4}$') 
and length(ssn) between 7 and 9;  2  

SSN          FMT_SSN
------------ ------------
326217894    326217894
000333333    000333333
111004444    111004444
004770000    004770000
023882391    023882391
3452371      003452371

ENCRYPT VALID SSN:

SQL> select ssn,lpad(ssn,9,0) fmt_ssn, DBMS_CRYPTO.hash(UTL_RAW.cast_to_raw(ssn),3) encrypt_ssn
from t 
where not regexp_like(lpad(ssn,9,0),'(?000\d{6})|(?\d{3}00\d{4})|(?\d{5}0000)|(?666\d{6})')
and regexp_like(lpad(ssn,9,0),'^[0-8]{3}[0-9]{2}[0-9]{4}$')
and length(ssn) between 7 and 9;  2    3    4    5  

SSN          FMT_SSN      ENCRYPT_SSN
------------ ------------ ----------------------------------------
326217894    326217894    681549D4B1597FF63530A6E8B9735241BBDACE56
023882391    023882391    DE30969505E26F6EA1849A4EA600619686977744
3452371      003452371    95A8011153DC2AF120E5C60CEB167C97809495E1

Tested on SQL*Plus: Release 11.2.0.2.0

References:
http://help.netmail.com/pages/viewpage.action?pagessn=6684926
http://www.ssa.gov/employer/randomization.html
http://www.ssa.gov/history/ssn/geocard.html

2012/OCT/16 UPDATE:

Comparison between regexp and substr

select /*+ PARALLEL(a,8) FULL(a) */count(*)
from t a
WHERE regexp_like(ssn,'^[0-8]{1,3}[0-9]{2}[0-9]{4}$') AND
(
length(ssn)= 9 and substr(ssn,-4)<>'0000' and substr(ssn,-6,2)<>'00' and substr(ssn,-9,3)<>'000' and substr(ssn,-9,3)<>'666' OR
length(ssn)= 8 and substr(ssn,-4)<>'0000' and substr(ssn,-6,2)<>'00' and substr(ssn,-8,2)<>'00'  OR
length(ssn)= 7 and substr(ssn,-4)<>'0000' and substr(ssn,-6,2)<>'00' and substr(ssn,-7,1)<>'0'
);

  COUNT(*)
----------
   1634972

Elapsed: 00:00:04.60

select /*+ PARALLEL(a,8) FULL(a) */count(*)
from t a
where not regexp_like(lpad(ssn,9,0),'(?000\d{6})|(?\d{3}00\d{4})|(?\d{5}0000)|(?666\d{6})')
and regexp_like(ssn,'^[0-8]{1,3}[0-9]{2}[0-9]{4}$');

  COUNT(*)
----------
   1634972

Elapsed: 00:00:39.47

October 15, 2012

OOW2012 from Thomas Kyte

Filed under: oracle — mdinh @ 12:49 pm

Just a quick post

“12 Things About The Latest Generation of Database Technology Thomas Kyte” HT @yvelikanov

What do you like and what can you use?

October 13, 2012

Data Destruction

Filed under: 11g,oracle — mdinh @ 2:47 am

One method to ensure data privacy is to destroy it.

The quick approach is to TRUNCATE tables and remove columns.

Dropping columns from tables with million of rows can take a very long time while SET UNUSED is much faster.

In order not to break the application, the same columns are added back.

This approach will not work if existing DML in code/application does not specify column names and values pair.

Update:

The update to fk_parent3_id, and pk_parent3_id columns are necessary because supposedly natural keys are being used.

This is where artificial key can benefit since the update can be make to the alternate key on the parent table only and still maintain referential integrity.

The process is outlined below:

set serverout on lines 130 echo off pages 1000
— DO NOT EXECUTE IN PRODUCTION
WHENEVER SQLERROR EXIT 1
begin
if (substr(SYS_CONTEXT(‘USERENV’,’SERVER_HOST’),1,4) = ‘prod’) then
RAISE_APPLICATION_ERROR (-20222, ‘!!!!! NOT ALLOWED IN PRODUCTION !!!!!’);
end if;
end;
/
— DYNAMICALLY CREATE AND NAME LOGFILE
WHENEVER SQLERROR CONTINUE
define _logfile=””
col logfile new_value _logfile
col table_name for a20
col column_name for a30
col data_type for a10
col fmt_data_type for a20
col tn for a20
col cn for a80
set echo on
select lower(user)||’_dm.log’ logfile from dual;
spool &_logfile
timing start dm_DESTROY
— DOMAIN INDEX MUST BE DROPPED TO SET UNUSED COLUMNS
timing start dm_DROPINDEX
EXEC EXECUTE IMMEDIATE ‘drop index DOMAIN_IDX force’; EXCEPTION WHEN OTHERS THEN NULL;
timing stop dm_DROPINDEX
— TRUNCATE TABLES WHERE APPLICABLE
timing start dm_TRUNCATE
truncate table T1;
truncate table T2;
alter table PARENT1 DROP PRIMARY KEY CASCADE;
truncate table PARENT1_CHILD1;
truncate table PARENT1_CHILD2;
timing stop dm_TRUNCATE
— CREATE TMP TABLE TO STORE COLUNM ATTRIBUTES.
set echo off
timing start dm_TMP_MASK_COLS
EXEC EXECUTE IMMEDIATE ‘drop table TMP_MASK_COLS cascade constraint’; EXCEPTION WHEN OTHERS THEN NULL;
CREATE TABLE TMP_MASK_COLS
AS
SELECT table_name,column_name,data_type,data_length,data_precision,data_scale,
(CASE data_type
WHEN ‘VARCHAR2’ THEN data_type||'(‘||data_length||’)’
WHEN ‘DATE’ THEN data_type
WHEN ‘NUMBER’ THEN DECODE(data_precision,NULL,data_type,data_type||'(‘||data_precision||’,’||data_scale||’)’)
END) fmt_data_type
FROM user_tab_cols
WHERE table_name IN (‘PRIVATE1′,’PRIVAT2′,’PPRIVATE3′)
AND REGEXP_LIKE (column_name,’ADDR|CITY|EMAIL|FAX|NAME|PHONE|ZIP|DOB|SSN’)
ORDER BY table_name, column_id ASC;
— SET UNUSED COLUMNS
DECLARE
l_sql VARCHAR2 (4000);
BEGIN
FOR x IN (
SELECT table_name tn, LISTAGG(column_name,’,’) WITHIN GROUP(ORDER BY column_name) cn FROM TMP_MASK_COLS GROUP BY table_name
) LOOP
l_sql := ‘ALTER TABLE ‘||x.tn||’ SET UNUSED (‘||x.cn||’)’;
DBMS_OUTPUT.put_line(l_sql);
EXECUTE IMMEDIATE l_sql;
END LOOP;
END;
/
— RE-ADD COLUMNS TO REPLACE UNUSED
DECLARE
l_sql VARCHAR2 (4000);
BEGIN
FOR x IN (
SELECT table_name tn, column_name||’ ‘||fmt_data_type cn FROM TMP_MASK_COLS ORDER BY tn,cn
) LOOP
l_sql := ‘ALTER TABLE ‘||x.tn||’ ADD (‘||x.cn||’)’;
DBMS_OUTPUT.put_line(l_sql);
EXECUTE IMMEDIATE l_sql;
END LOOP;
END;
/
— UPDATE CHILD3 FK (only 9 digit SSN candidate)
— Step is necessary since column is part of composite primary key
set echo on
timing start dm_CHILD3
alter table CHILD3 disable constraint FK_CHILD3_PARENT3;
alter table CHILD3 disable all triggers;
UPDATE CHILD3
SET fk_parent3_id=DBMS_CRYPTO.hash(UTL_RAW.cast_to_raw(fk_parent3_id),3)
WHERE REGEXP_LIKE(fk_parent3_id,’^[[:digit:]]{9}$’);
commit;
timing stop dm_CHILD3
— UPDATE PARENT3 PK (only 9 digit SSN candidate)
timing start dm_PARENT3
alter table PARENT3 disable all triggers;
UPDATE PARENT3
SET pk_parent3_id=DBMS_CRYPTO.hash(UTL_RAW.cast_to_raw(pk_parent3_id),3)
WHERE REGEXP_LIKE(pk_parent3_id,’^[[:digit:]]{9}$’);
timing stop dm_PARENT3
— ENABLE CONSTRAINTS / TRIGGERS
timing start dm_ENABLE
alter table PARENT3 enable all triggers;
alter table CHILD3 enable all triggers;
alter table CHILD3 enable constraint FK_CHILD3_PARENT3;
timing stop dm_ENABLE
— LIST TABLES AND COLUMNS DESTROYED
timing stop dm_DESTROY
SELECT table_name, LISTAGG(column_name,’,’) WITHIN GROUP(ORDER BY column_name) mask_cols
FROM TMP_MASK_COLS GROUP BY table_name;
timing stop dm_TMP_MASK_COLS
spool off
exit

October 6, 2012

Identifying Trace File 11.2

Filed under: 11g,oracle — mdinh @ 2:21 pm

Just a quick example showing how to identify trace files for current session and other sessions.

Basically, there are 2 methods to identify current trace file for current session and only 1 way for other session (or what I know how to)

set lines 130
col sql_trace for a15
col sql_trace_waits for a15
col sql_trace_binds for a15
col traceid for a15
col tracefile for a100
col value for a100
set echo on
SELECT s.sql_trace, s.sql_trace_waits, s.sql_trace_binds, traceid, tracefile
FROM v$session s JOIN v$process p ON (p.addr = s.paddr)
WHERE audsid = USERENV ('SESSIONID');
select value from v$diag_info where name='Default Trace File';

Identifying Trace File for Current Session:

 Connected to:
 Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options 
 SQL> @tracefile
 SQL> show user
 USER is "SCOTT"
 SQL> SELECT s.sql_trace, s.sql_trace_waits, s.sql_trace_binds, traceid, tracefile
 2 FROM v$session s JOIN v$process p ON (p.addr = s.paddr)
 3 WHERE audsid = USERENV ('SESSIONID'); SQL_TRACE SQL_TRACE_WAITS SQL_TRACE_BINDS TRACEID
 --------------- --------------- --------------- ---------------
 TRACEFILE
 ----------------------------------------------------------------------------------------------------
 DISABLED FALSE FALSE
 /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4602.trc 

 SQL> select value from v$diag_info where name='Default Trace File'; VALUE
 ----------------------------------------------------------------------------------------------------
 /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4602.trc
 
 SQL> alter session set tracefile_identifier='traceme'; 
 Session altered. 
 SQL> @tracefile
 SQL> show user
 USER is "SCOTT"
 SQL> SELECT s.sql_trace, s.sql_trace_waits, s.sql_trace_binds, traceid, tracefile
 2 FROM v$session s JOIN v$process p ON (p.addr = s.paddr)
 3 WHERE audsid = USERENV ('SESSIONID'); SQL_TRACE SQL_TRACE_WAITS SQL_TRACE_BINDS TRACEID
 --------------- --------------- --------------- ---------------
 TRACEFILE
 ----------------------------------------------------------------------------------------------------
 DISABLED FALSE FALSE traceme
 /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4602_traceme.trc 

 SQL> select value from v$diag_info where name='Default Trace File'; VALUE
 ----------------------------------------------------------------------------------------------------
 /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4602_traceme.trc

Identifying Trace File for Other Session: 

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set lines 130
col sql_trace for a15
col sql_trace_waits for a15
col sql_trace_binds for a15
col traceid for a15
col tracefile for a100
col value for a100SQL> SQL> SQL> SQL> SQL> SQL> 
SQL> show user
USER is "SYSTEM"
SQL> SELECT s.sql_trace, s.sql_trace_waits, s.sql_trace_binds, p.traceid, p.tracefile
FROM v$session s JOIN v$process p ON (p.addr = s.paddr)
WHERE s.username='SCOTT'; 2 3
SQL_TRACE SQL_TRACE_WAITS SQL_TRACE_BINDS TRACEID
--------------- --------------- --------------- ---------------
TRACEFILE
----------------------------------------------------------------------------------------------------
DISABLED FALSE FALSE
/home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4602.trc

SQL> /
SQL_TRACE SQL_TRACE_WAITS SQL_TRACE_BINDS TRACEID
--------------- --------------- --------------- ---------------
TRACEFILE
----------------------------------------------------------------------------------------------------
DISABLED FALSE FALSE traceme
/home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4602_traceme.trc

Blog at WordPress.com.