Search This Blog

Total Pageviews

Saturday 3 September 2011

encrypted data in Oracle


How to store data DES encrypted in Oracle ?

This tip is from the Oracle Magazine, it shows the usage of the DBMS_OBFUSCATION_TOOLKIT.
The DBMS_OBFUSCATION_TOOLKIT is the DES encryption package. This package shipped with Oracle8i Release 2 and later. It provides first-time field-level encryption in the database. The trick to using this package is to make sure everything is a multiple of eight. Both the key and the input data must have a length divisible by eight (the key must be exactly 8 bytes long).

Example

CREATE OR REPLACE PROCEDURE obfuscation_demo AS
l_data varchar2(255);
l_string VARCHAR2(25) := 'hello world';
BEGIN
--
-- Both the key and the input data must have a length
-- divisible by eight (the key must be exactly 8 bytes long).
--
l_data := RPAD(l_string,(TRUNC(LENGTH(l_string)/8)+1)*8,CHR(0));
--
DBMS_OUTPUT.PUT_LINE('l_string before encrypt: ' || l_string);
--
-- Encrypt the input string
--
DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT
(input_string => l_data,
key_string => 'magickey',
encrypted_string => l_string);
--
DBMS_OUTPUT.PUT_LINE('l_string ENCRYPTED: ' || l_string);
--
--
-- Decrypt the input string
--
DBMS_OBFUSCATION_TOOLKIT.DESDECRYPT
(input_string => l_string,
key_string => 'magickey',
decrypted_string => l_data);
--
DBMS_OUTPUT.PUT_LINE('l_string DECRYPT: ' || L_DATA);
--
END;
/

SQL> exec obfuscation_demo

l_string before encrypt: hello world
l_string ENCRYPTED: ¿¿¿H?¿¿¿
l_string DECRYPT: hello world

PL/SQL procedure successfully completed.

You must protect and preserve your "magickey"—8 bytes of data that is used to encrypt/decrypt the data. If it becomes compromised, your data is vulnerable.


Posted by at No comments:

Oracle foreign key constraints procedure



CREATE OR REPLACE
PROCEDURE show_fkeys(
p_table_name IN user_constraints.table_name%TYPE)
IS
-- constants
-- identify fkeys on pkey for given table
CURSOR id_fkeys (
c_table_name user_constraints.table_name%TYPE)
IS
SELECT table_name, constraint_name fkey, r_constraint_name pkey,
status
FROM user_constraints
WHERE constraint_type='R'
AND r_constraint_name IN (
SELECT constraint_name
FROM user_constraints
WHERE table_name=c_table_name
AND constraint_type='P')
ORDER BY table_name, constraint_name;
-- record variables
rec_id_fkeys id_fkeys%ROWTYPE;
-- variables
l_table_name user_constraints.table_name%TYPE;
l_pkey_name user_constraints.constraint_name%TYPE;
l_status NUMERIC;
BEGIN
l_table_name := UPPER(p_table_name);
-- a primary key for the given table must exist
SELECT constraint_name
INTO l_pkey_name
FROM user_constraints
WHERE table_name=l_table_name
AND constraint_type='P';
DBMS_OUTPUT.put_line(
'show_fkeys: foreign key constraints on table [' || l_table_name || ']');
DBMS_OUTPUT.put_line(
' whose primary key is [' || l_pkey_name || ']');
OPEN id_fkeys(l_table_name);
LOOP -- display foreign keys
FETCH id_fkeys INTO rec_id_fkeys;
EXIT WHEN id_fkeys%NOTFOUND;
DBMS_OUTPUT.put_line(RPAD('Table: [' || rec_id_fkeys.table_name || ']',40) ||
RPAD('FK Name: [' || rec_id_fkeys.fkey || ']',42) ||
'Status: [' || rec_id_fkeys.status || ']');
END LOOP; -- display foreign keys
IF (id_fkeys%ROWCOUNT = 0) THEN -- no fkeys found
DBMS_OUTPUT.put_line(
'show_fkeys: No foreign keys found against table ' || l_table_name);

END IF; -- no rows found
CLOSE id_fkeys;
EXCEPTION
WHEN NO_DATA_FOUND THEN -- primary key lookup failed
DBMS_OUTPUT.put_line(
'show_fkeys: no primary key exists for table ' || l_table_name);
WHEN OTHERS THEN
l_status := SQLCODE;
DBMS_OUTPUT.put_line('show_fkeys: ' || SQLERRM(l_status));
IF (id_fkeys%ISOPEN) THEN
CLOSE id_fkeys;
END IF;
END show_fkeys;
/


SQL>set serveroutput on

SQL> exec show_fkeys('DEPT');
show_fkeys: foreign key constraints on table [DEPT]
whose primary key is [PK_DEPT]
Table: [EMP] FK Name: [FK_DEPTNO]
Status: [ENABLED]

PL/SQL procedure successfully completed.
Posted by at No comments:

Oracle Box ip address on SQLPLUS


Find a ip address of Oracle Box

select sys_context('userenv','ip_address') from dual;
Posted by at No comments:

Oracle missing index on foreign key



Oracle : Foreign Keys without Index








Oracle : Foreign Keys without Index
 
 

-- from wrox book chepter 3
-- run this script from schema.............


column columns format a30 word_wrapped
column tablename format a15 word_wrapped
column constraint_name format a15 word_wrapped
select table_name,constraint_name,
cname1 ||nvl2(cname2,','||cname2,null)||
nvl2(cname3,','||cname3,null)||nvl2(cname4,',' ||cname4,null)||
nvl2(cname5,','||cname5,null)||nvl2(cname6,',' ||cname6,null)||
nvl2(cname7,','||cname7,null)||nvl2(cname8,',' ||cname8,null) columns
from(select b.table_name,b.constraint_name,
max(decode(position,1,column_name,null)) cname1,
max(decode(position,2,column_name,null)) cname2,
max(decode(position,3,column_name,null)) cname3,
max(decode(position,4,column_name,null)) cname4,
max(decode(position,5,column_name,null)) cname5,
max(decode(position,6,column_name,null)) cname6,
max(decode(position,7,column_name,null)) cname7,
max(decode(position,8,column_name,null)) cname8,
count(*) col_cnt
from ( select substr(table_name,1,30) table_name,
substr(constraint_name,1,30) constraint_name,
substr(column_name,1,30) column_name,
position from user_cons_columns) a,
user_constraints b
where a.constraint_name=b.constraint_name
and b.constraint_type='R'
group by b.table_name,b.constraint_name) cons
where col_cnt>all (select count(*) from user_ind_columns i
where i.table_name=cons.table_name
and i.column_name in (cname1,cname2,cname3,cname4,cname5,cname6,cname7,cname8)
and i.column_position<=cons.col_cnt
group by i.index_name)
/

========



column columns format a20 word_wrapped
column table_name format a30 word_wrapped

select decode( b.table_name, NULL, '****', 'ok' ) Status,
a.table_name, a.columns, b.columns
from
( select a.table_name, a.constraint_name,
max(decode(position, 1, column_name,NULL)) ||
max(decode(position, 2,', '||column_name,NULL)) ||
max(decode(position, 3,', '||column_name,NULL)) ||
max(decode(position, 4,', '||column_name,NULL)) ||
max(decode(position, 5,', '||column_name,NULL)) ||
max(decode(position, 6,', '||column_name,NULL)) ||
max(decode(position, 7,', '||column_name,NULL)) ||
max(decode(position, 8,', '||column_name,NULL)) ||
max(decode(position, 9,', '||column_name,NULL)) ||
max(decode(position,10,', '||column_name,NULL)) ||
max(decode(position,11,', '||column_name,NULL)) ||
max(decode(position,12,', '||column_name,NULL)) ||
max(decode(position,13,', '||column_name,NULL)) ||
max(decode(position,14,', '||column_name,NULL)) ||
max(decode(position,15,', '||column_name,NULL)) ||
max(decode(position,16,', '||column_name,NULL)) columns
from user_cons_columns a, user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by a.table_name, a.constraint_name ) a,
( select table_name, index_name,
max(decode(column_position, 1, column_name,NULL)) ||
max(decode(column_position, 2,', '||column_name,NULL)) ||
max(decode(column_position, 3,', '||column_name,NULL)) ||
max(decode(column_position, 4,', '||column_name,NULL)) ||
max(decode(column_position, 5,', '||column_name,NULL)) ||
max(decode(column_position, 6,', '||column_name,NULL)) ||
max(decode(column_position, 7,', '||column_name,NULL)) ||
max(decode(column_position, 8,', '||column_name,NULL)) ||
max(decode(column_position, 9,', '||column_name,NULL)) ||
max(decode(column_position,10,', '||column_name,NULL)) ||
max(decode(column_position,11,', '||column_name,NULL)) ||
max(decode(column_position,12,', '||column_name,NULL)) ||
max(decode(column_position,13,', '||column_name,NULL)) ||
max(decode(column_position,14,', '||column_name,NULL)) ||
max(decode(column_position,15,', '||column_name,NULL)) ||
max(decode(column_position,16,', '||column_name,NULL)) columns
from user_ind_columns
group by table_name, index_name ) b
where a.table_name = b.table_name (+)
and b.columns (+) like a.columns || '%'


 
 
select 'CREATE INDEX ' || owner || '.' || replace(CONSTRAINT_NAME,'FK_','IX_') ||
 ' ON ' || owner || '.' || table_name || ' (' || col_list ||') TABLESPACE test ;' Indx from
 (select cc.owner, cc.TABLE_NAME, cc.CONSTRAINT_NAME,
 max(decode(position, 1, '"' ||
 substr(column_name,1,30) ||'"',NULL)) ||
 max(decode(position, 2,', '||'"'||
 substr(column_name,1,30) ||'"',NULL)) ||
 max(decode(position, 3,', '||'"'||
 substr(column_name,1,30) ||'"',NULL)) ||
 max(decode(position, 4,', '||'"'||
 substr(column_name,1,30) ||'"',NULL)) ||
 max(decode(position, 5,', '||'"'||
 substr(column_name,1,30) ||'"',NULL)) ||
 max(decode(position, 6,', '||'"'||
 substr(column_name,1,30) ||'"',NULL)) ||
 max(decode(position, 7,', '||'"'||
 substr(column_name,1,30) ||'"',NULL)) ||
 max(decode(position, 8,', '||'"'||
 substr(column_name,1,30) ||'"',NULL)) ||
 max(decode(position,9,', '||'"'||
 substr(column_name,1,30) ||'"',NULL)) ||
 max(decode(position, 10,', '||'"'||
 substr(column_name,1,30) ||'"',NULL)) col_list
 from dba_constraints dc, dba_cons_columns cc
 where dc.owner = cc.owner
 and dc.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
 and dc.CONSTRAINT_type = 'R'
 and upper(dc.owner) = upper('CAR')
 group by cc.owner, cc.TABLE_NAME, cc.CONSTRAINT_NAME
 ) con
 where not exists (
 select 1 from
 ( select table_owner, table_name, 
 max(decode(column_position, 1, '"'||
 substr(column_name,1,30) ||'"',NULL)) ||
 max(decode(column_position, 2,', '||'"'||
 substr(column_name,1,30) ||'"',NULL)) ||
 max(decode(column_position, 3,', '||'"'||
 substr(column_name,1,30) ||'"',NULL)) ||
 max(decode(column_position, 4,', '||'"'||
 substr(column_name,1,30) ||'"',NULL)) ||
 max(decode(column_position, 5,', '||'"'||
 substr(column_name,1,30) ||'"',NULL)) ||
 max(decode(column_position, 6,', '||'"'||
 substr(column_name,1,30) ||'"',NULL)) ||
 max(decode(column_position, 7,', '||'"'||
 substr(column_name,1,30) ||'"',NULL)) ||
 max(decode(column_position, 8,', '||'"'||
 substr(column_name,1,30) ||'"',NULL)) ||
 max(decode(column_position, 9,', '||'"'||
 substr(column_name,1,30) ||'"',NULL)) ||
 max(decode(column_position, 10,', '||'"'||
 substr(column_name,1,30) ||'"',NULL)) col_list
 from dba_ind_columns
 where upper(table_owner) = upper('CAR')
 group by table_owner, table_name, index_name ) col
 where con.owner = col.table_owner
 and con.table_name = col.table_name 
 and con.col_list = substr(col.col_list, 1, length(con.col_list) ) ) ; 
 
set verify on
set pages 60
 
=============


Example !!!!!!!!!


CREATE TABLE scott.test11 (col1 NUMBER NOT NULL, col2 NUMBER);

ALTER TABLE scott.test11 ADD CONSTRAINT test11_UNIQUE UNIQUE (col1, col2);



session1> INSERT INTO scott.test11 VALUES (11, NULL);
session2> INSERT INTO scott.test11 VALUES (22, NULL);

session2> INSERT INTO scott.test11 VALUES (11, NULL); -- <= this session will hang  !!!!!!!


col INDX for a80
select 'CREATE INDEX ' || owner || '.' || replace(CONSTRAINT_NAME,'FK_','IX_') || ' ON ' || owner || '.' || table_name || ' (' || col_list ||') TABLESPACE test ;' Indx from
 (select cc.owner, cc.TABLE_NAME, cc.CONSTRAINT_NAME,
 max(decode(position, 1, '"' ||
 substr(column_name,1,30) ||'"',NULL)) ||
 max(decode(position, 2,', '||'"'||
 substr(column_name,1,30) ||'"',NULL)) ||
 max(decode(position, 3,', '||'"'||
 substr(column_name,1,30) ||'"',NULL)) ||
 max(decode(position, 4,', '||'"'||
 substr(column_name,1,30) ||'"',NULL)) ||
 max(decode(position, 5,', '||'"'||
 substr(column_name,1,30) ||'"',NULL)) ||
 max(decode(position, 6,', '||'"'||
 substr(column_name,1,30) ||'"',NULL)) ||
 max(decode(position, 7,', '||'"'||
 substr(column_name,1,30) ||'"',NULL)) ||
 max(decode(position, 8,', '||'"'||
 substr(column_name,1,30) ||'"',NULL)) ||
 max(decode(position,9,', '||'"'||
 substr(column_name,1,30) ||'"',NULL)) ||
 max(decode(position, 10,', '||'"'||
 substr(column_name,1,30) ||'"',NULL)) col_list
 from dba_constraints dc, dba_cons_columns cc
 where dc.owner = cc.owner
 and dc.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
 and dc.CONSTRAINT_type = 'R'
 and upper(dc.owner) = upper('SCOTT')
 group by cc.owner, cc.TABLE_NAME, cc.CONSTRAINT_NAME
 ) con
 where not exists (
 select 1 from
 ( select table_owner, table_name, 
 max(decode(column_position, 1, '"'||
 substr(column_name,1,30) ||'"',NULL)) ||
 max(decode(column_position, 2,', '||'"'||
 substr(column_name,1,30) ||'"',NULL)) ||
 max(decode(column_position, 3,', '||'"'||
 substr(column_name,1,30) ||'"',NULL)) ||
 max(decode(column_position, 4,', '||'"'||
 substr(column_name,1,30) ||'"',NULL)) ||
 max(decode(column_position, 5,', '||'"'||
 substr(column_name,1,30) ||'"',NULL)) ||
 max(decode(column_position, 6,', '||'"'||
 substr(column_name,1,30) ||'"',NULL)) ||
 max(decode(column_position, 7,', '||'"'||
 substr(column_name,1,30) ||'"',NULL)) ||
 max(decode(column_position, 8,', '||'"'||
 substr(column_name,1,30) ||'"',NULL)) ||
 max(decode(column_position, 9,', '||'"'||
 substr(column_name,1,30) ||'"',NULL)) ||
 max(decode(column_position, 10,', '||'"'||
 substr(column_name,1,30) ||'"',NULL)) col_list
 from dba_ind_columns
 where 1=1 
 and upper(table_owner) = upper('SCOTT')
 group by table_owner, table_name, index_name ) col
 where con.owner = col.table_owner
 and con.table_name = col.table_name 
 and con.col_list = substr(col.col_list, 1, length(con.col_list) ) ) ; 
 set verify on pages 60
 



after creating index you will create following message !!


SQL> CREATE INDEX SCOTT.IX_DEPTNO ON SCOTT.EMP ("DEPTNO");

Index created.



SQL> INSERT INTO scott.test11 VALUES (11, NULL);
INSERT INTO scott.test11 VALUES (11, NULL)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.TEST11_UNIQUE) violated   <<<<<< 













==============================================================



set linesize 300 pagesize 300

col INDX for a200

select 'CREATE INDEX ' || owner || '.' || replace(CONSTRAINT_NAME,'FK_','IX_') || ' ON ' || owner || '.' || table_name || ' (' || col_list ||') TABLESPACE XXXX ;' Indx from

 (select cc.owner, cc.TABLE_NAME, cc.CONSTRAINT_NAME,

 max(decode(position, 1, '"' ||

 substr(column_name,1,30) ||'"',NULL)) ||

 max(decode(position, 2,', '||'"'||

 substr(column_name,1,30) ||'"',NULL)) ||

 max(decode(position, 3,', '||'"'||

 substr(column_name,1,30) ||'"',NULL)) ||

 max(decode(position, 4,', '||'"'||

 substr(column_name,1,30) ||'"',NULL)) ||

 max(decode(position, 5,', '||'"'||

 substr(column_name,1,30) ||'"',NULL)) ||

 max(decode(position, 6,', '||'"'||

 substr(column_name,1,30) ||'"',NULL)) ||

 max(decode(position, 7,', '||'"'||

 substr(column_name,1,30) ||'"',NULL)) ||

 max(decode(position, 8,', '||'"'||

 substr(column_name,1,30) ||'"',NULL)) ||

 max(decode(position,9,', '||'"'||

 substr(column_name,1,30) ||'"',NULL)) ||

 max(decode(position, 10,', '||'"'||

 substr(column_name,1,30) ||'"',NULL)) col_list  from dba_constraints dc, dba_cons_columns cc

 where dc.owner = cc.owner

 and dc.CONSTRAINT_NAME = cc.CONSTRAINT_NAME

 and dc.CONSTRAINT_type = 'R'

 and upper(dc.owner) not in  ( 'SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM','PERFSTAT','ORDDATA','BDA_METADATA')

 group by cc.owner, cc.TABLE_NAME, cc.CONSTRAINT_NAME

 ) con

 where not exists (

 select 1 from

 ( select table_owner, table_name,

 max(decode(column_position, 1, '"'||

 substr(column_name,1,30) ||'"',NULL)) ||

 max(decode(column_position, 2,', '||'"'||

 substr(column_name,1,30) ||'"',NULL)) ||

 max(decode(column_position, 3,', '||'"'||

 substr(column_name,1,30) ||'"',NULL)) ||

 max(decode(column_position, 4,', '||'"'||

 substr(column_name,1,30) ||'"',NULL)) ||

 max(decode(column_position, 5,', '||'"'||

 substr(column_name,1,30) ||'"',NULL)) ||

 max(decode(column_position, 6,', '||'"'||

 substr(column_name,1,30) ||'"',NULL)) ||

 max(decode(column_position, 7,', '||'"'||

 substr(column_name,1,30) ||'"',NULL)) ||

 max(decode(column_position, 8,', '||'"'||

 substr(column_name,1,30) ||'"',NULL)) ||

 max(decode(column_position, 9,', '||'"'||

 substr(column_name,1,30) ||'"',NULL)) ||

 max(decode(column_position, 10,', '||'"'||

 substr(column_name,1,30) ||'"',NULL)) col_list  from dba_ind_columns

 where 1=1

 and upper(table_owner) not in  ( 'SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM','PERFSTAT','ORDDATA','BDA_METADATA')

 group by table_owner, table_name, index_name ) col

 where con.owner = col.table_owner

 and con.table_name = col.table_name

 and con.col_list = substr(col.col_list, 1, length(con.col_list) ) ) ;



===============================

Script to Check for Foreign Key Locking Issues for a Specific User ( Doc ID 1019527.6 )



run as sys/system 

drop table ck_log;

create table ck_log (LineNum number,LineMsg varchar2(2000));  <<<< Create this table first 

save this script into fkidx.sql

declare
t_CONSTRAINT_TYPE 	user_constraints.CONSTRAINT_TYPE%type;
t_CONSTRAINT_NAME 	USER_CONSTRAINTS.CONSTRAINT_NAME%type;
t_TABLE_NAME 		USER_CONSTRAINTS.TABLE_NAME%type;
t_R_CONSTRAINT_NAME 	USER_CONSTRAINTS.R_CONSTRAINT_NAME%type;
tt_CONSTRAINT_NAME 	USER_CONS_COLUMNS.CONSTRAINT_NAME%type;
tt_TABLE_NAME 		USER_CONS_COLUMNS.TABLE_NAME%type;
tt_COLUMN_NAME 		USER_CONS_COLUMNS.COLUMN_NAME%type;
tt_POSITION 		USER_CONS_COLUMNS.POSITION%type;
tt_Dummy 		number;
tt_dummyChar 		varchar2(2000);
l_Cons_Found_Flag 	VarChar2(1);
Err_TABLE_NAME 		USER_CONSTRAINTS.TABLE_NAME%type;
Err_COLUMN_NAME 	USER_CONS_COLUMNS.COLUMN_NAME%type;
Err_POSITION 		USER_CONS_COLUMNS.POSITION%type;
l_OWNER                 dba_tables.OWNER%type := 'ANUJ';  ---- change username 

tLineNum number;

cursor UserTabs is select table_name from dba_tables
where 1=1
and OWNER=l_OWNER 
order by table_name;

cursor TableCons is
select CONSTRAINT_TYPE,CONSTRAINT_NAME,R_CONSTRAINT_NAME from dba_constraints
where OWNER = l_OWNER 
and table_name = t_Table_Name
and CONSTRAINT_TYPE = 'R'
order by TABLE_NAME, CONSTRAINT_NAME;

cursor ConColumns is select CONSTRAINT_NAME,TABLE_NAME,COLUMN_NAME,POSITION from dba_cons_columns
where OWNER = l_OWNER 
and CONSTRAINT_NAME = t_CONSTRAINT_NAME
order by POSITION;

cursor IndexColumns is
select TABLE_NAME,COLUMN_NAME,POSITION from dba_cons_columns
where OWNER = l_OWNER 
and CONSTRAINT_NAME = t_CONSTRAINT_NAME
order by POSITION;

DebugLevel number := 99; -- >>> 99 = dump all info`
DebugFlag varchar(1) := 'N'; -- Turn Debugging on
t_Error_Found varchar(1);

begin

tLineNum := 1000;
open UserTabs;
LOOP
Fetch UserTabs into t_TABLE_NAME;
t_Error_Found := 'N';
exit when UserTabs%NOTFOUND;

-- Log current table
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values ( tLineNum, NULL );

tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values ( tLineNum, 'Checking Table '||t_Table_Name);

l_Cons_Found_Flag := 'N';
open TableCons;
LOOP
FETCH TableCons INTO t_CONSTRAINT_TYPE,t_CONSTRAINT_NAME,t_R_CONSTRAINT_NAME;
exit when TableCons%NOTFOUND;

if ( DebugFlag = 'Y' and DebugLevel >= 99 )
then
begin
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values ( tLineNum, 'Found CONSTRAINT_NAME = '|| t_CONSTRAINT_NAME);

tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values ( tLineNum, 'Found CONSTRAINT_TYPE = '|| t_CONSTRAINT_TYPE);

tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values ( tLineNum, 'Found R_CONSTRAINT_NAME = '|| t_R_CONSTRAINT_NAME);
commit;
end;
end if;

open ConColumns;
LOOP
FETCH ConColumns INTO tt_CONSTRAINT_NAME,tt_TABLE_NAME,tt_COLUMN_NAME,tt_POSITION;
exit when ConColumns%NOTFOUND;
if ( DebugFlag = 'Y' and DebugLevel >= 99 )
then

begin
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values ( tLineNum, NULL );

tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values ( tLineNum, 'Found CONSTRAINT_NAME = '|| tt_CONSTRAINT_NAME);

tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values ( tLineNum, 'Found TABLE_NAME = '|| tt_TABLE_NAME);

tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values ( tLineNum, 'Found COLUMN_NAME = '|| tt_COLUMN_NAME);

tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values ( tLineNum, 'Found POSITION = '|| tt_POSITION);
commit;
end;
end if;

begin
select 1 into tt_Dummy from dba_ind_columns
where TABLE_NAME = tt_TABLE_NAME
and COLUMN_NAME = tt_COLUMN_NAME
and COLUMN_POSITION = tt_POSITION;

if ( DebugFlag = 'Y' and DebugLevel >= 99 )
then
begin
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values ( tLineNum, 'Row Has matching Index' );
end;
end if;
exception
when Too_Many_Rows then
if ( DebugFlag = 'Y' and DebugLevel >= 99 )
then
begin
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values ( tLineNum, 'Row Has matching Index' );
end;
end if;

when no_data_found then
if ( DebugFlag = 'Y' and DebugLevel >= 99 )
then
begin
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values ( tLineNum, 'NO MATCH FOUND' );
commit;
end;
end if;

t_Error_Found := 'Y';

select distinct TABLE_NAME into tt_dummyChar from dba_cons_columns
where OWNER =l_OWNER 
and CONSTRAINT_NAME = t_R_CONSTRAINT_NAME;

tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values ( tLineNum, 'Changing data in table '||tt_dummyChar||' will lock table ' ||tt_TABLE_NAME);

commit;
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values ( tLineNum,'Create an index on table '||tt_TABLE_NAME ||' with the following columns to remove lock problem');

open IndexColumns ;
loop
Fetch IndexColumns into Err_TABLE_NAME,Err_COLUMN_NAME,Err_POSITION;
exit when IndexColumns%NotFound;
tLineNum := tLineNum + 1;

insert into ck_log ( LineNum, LineMsg ) values ( tLineNum,'Column = '||Err_COLUMN_NAME||' ('||Err_POSITION||')');
end loop;
close IndexColumns;
end;
end loop;
commit;
close ConColumns;
end loop;
if ( t_Error_Found = 'N' )
then
begin
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values ( tLineNum,'No foreign key errors found');
end;
end if;
commit;
close TableCons;
end loop;
commit;
end;
/


sqlplus> @fkidx.sql

for report !!!

set pagesize 600 linesize 300
select LineMsg from ck_log
where LineMsg NOT LIKE 'Checking%' AND LineMsg NOT LIKE 'No foreign key%'
order by LineNum
/


====

https://github.com/fatdba/Oracle-Database-Scripts/blob/main/fklocking.sql

set serveroutput on
declare
 procedure print_all(s varchar2) is begin null;
  dbms_output.put_line(s);
 end;
 procedure print_ddl(s varchar2) is begin null;
  dbms_output.put_line(s);
 end;
begin
 dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES',false);
 for a in (
        select count(*) samples,
        event,p1,p2,o.owner c_owner,o.object_name c_object_name,p.object_owner p_owner,p.object_name p_object_name,id,operation,min(p1-1414332420+4) lock_mode,min(sample_time) min_time,max(sample_time) max_time,ceil(10*count(distinct sample_id)/60) minutes
        from dba_hist_active_sess_history left outer join dba_hist_sql_plan p using(dbid,sql_id) left outer join dba_objects o on object_id=p2 left outer join dba_objects po on po.object_id=current_obj#
        where event like 'enq: TM%' and p1>=1414332420 and sample_time>sysdate-15 and p.id=1 and operation in('DELETE','UPDATE','MERGE')
        group by
        event,p1,p2,o.owner,o.object_name,p.object_owner,p.object_name,po.owner,po.object_name,id,operation
        order by count(*) desc
 ) loop
  print_ddl('--  '||a.operation||' on '||a.p_owner||'.'||a.p_object_name||' has locked '||a.c_owner||'.'||a.c_object_name||' in mode '||a.lock_mode||' for '||a.minutes||' minutes between '||to_char(a.min_time,'dd-mon hh24:mi')||' and '||to_char(a.max_time,'dd-mon hh24:mi'));
  for s in (
   select distinct regexp_replace(cast(substr(sql_text,1,2000) as varchar2(60)),'[^a-zA-Z ]',' ') sql_text
   from dba_hist_active_sess_history join dba_hist_sqltext t using(dbid,sql_id)
   where event like 'enq: TM%' and p2=a.p2 and sample_time>sysdate-90
  ) loop
   print_all('--      '||'blocked statement: '||s.sql_text);
  end loop;
  for c in (
    with
      c as (
      select p.owner p_owner,p.table_name p_table_name,c.owner c_owner,c.table_name c_table_name,c.delete_rule,c.constraint_name
      from dba_constraints p
      join dba_constraints c on (c.r_owner=p.owner and c.r_constraint_name=p.constraint_name)
      where p.constraint_type in ('P','U') and c.constraint_type='R'
    )
    select c_owner owner,constraint_name,c_table_name,connect_by_root(p_owner||'.'||p_table_name)||sys_connect_by_path(decode(delete_rule,'CASCADE','(cascade delete)','SET NULL','(cascade set null)',' ')||' '||c_owner||'"."'||c_table_name,' referenced by') foreign_keys
    from c
    where level<=10 and c_owner=a.c_owner and c_table_name=a.c_object_name
    connect by nocycle p_owner=prior c_owner and p_table_name=prior c_table_name and ( level=1 or prior delete_rule in ('CASCADE','SET NULL') )
    start with p_owner=a.p_owner and p_table_name=a.p_object_name
  ) loop
   print_all('--      '||'FK chain: '||c.foreign_keys||' ('||c.owner||'.'||c.constraint_name||')'||' unindexed');
   for l in (select * from dba_cons_columns where owner=c.owner and constraint_name=c.constraint_name) loop
    print_all('--         FK column '||l.column_name);
   end loop;
   print_ddl('--      Suggested index: '||regexp_replace(translate(dbms_metadata.get_ddl('REF_CONSTRAINT',c.constraint_name,c.owner),chr(10)||chr(13),'  '),'ALTER TABLE ("[^"]+"[.]"[^"]+") ADD CONSTRAINT ("[^"]+") FOREIGN KEY ([(].*[)]).* REFERENCES ".*','CREATE INDEX ON \1 \3;'));
   for x in (
     select rtrim(translate(dbms_metadata.get_ddl('INDEX',index_name,index_owner),chr(10)||chr(13),'  ')) ddl
     from dba_ind_columns where (index_owner,index_name) in (select owner,index_name from dba_indexes where owner=c.owner and table_name=c.c_table_name)
     and column_name in (select column_name from dba_cons_columns where owner=c.owner and constraint_name=c.constraint_name)
     )
   loop
    print_ddl('--      Existing candidate indexes '||x.ddl);
   end loop;
   for x in (
     select rtrim(translate(dbms_metadata.get_ddl('INDEX',index_name,index_owner),chr(10)||chr(13),'  ')) ddl
     from dba_ind_columns where (index_owner,index_name) in (select owner,index_name from dba_indexes where owner=c.owner and table_name=c.c_table_name)
     )
   loop
    print_all('--       Other existing Indexes: '||x.ddl);
   end loop;
  end loop;
 end loop;
end;
/













Posted by



at





No comments:
  

































        

      









Subscribe to:
Posts (Atom)







Oracle DBA



My photo








Essex, United Kingdom











anuj blog Archive