Saturday, 3 September 2011

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&gt;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&lt;=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

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

SET HEADING ON

SET ECHO ON

SET FEEDBACK 6

SET LINESIZE 80

SET PAGESIZE 50

SELECT

-- Generate the CREATE INDEX command

'CREATE INDEX ' ||

-- Create a descriptive index name (e.g., IX_EMP_FK_DEPT_ID)

'IXF_' || SUBSTR(acc.table_name, 1, 15) || '_' || SUBSTR(acc.constraint_name, 1, 10) ||

' ON ' ||

acc.owner || '.' || acc.table_name ||

' (' || LISTAGG(acc.column_name, ', ') WITHIN GROUP (ORDER BY acc.position) || ');' AS create_index_command

FROM

all_cons_columns acc

JOIN

all_constraints ac ON acc.owner = ac.owner AND acc.constraint_name = ac.constraint_name

LEFT JOIN

all_ind_columns aic ON acc.owner = aic.table_owner

AND acc.table_name = aic.table_name

AND acc.column_name = aic.column_name

AND acc.position = aic.column_position

WHERE

ac.owner = 'XXX' -- <-- *** REPLACE THIS WITH YOUR SCHEMA NAME (IN CAPITALS) ***

AND ac.constraint_type = 'R' -- Only include Foreign Keys

-- Exclude Foreign Keys that already have a supporting index

AND NOT EXISTS (

SELECT 1

FROM all_ind_columns aic_check

WHERE aic_check.table_owner = acc.owner

AND aic_check.table_name = acc.table_name

-- Check if the index covers at least the first column of the FK

AND aic_check.column_name = acc.column_name

AND acc.position = 1 -- Only need to check for the first column match

)

GROUP BY

acc.owner, acc.table_name, acc.constraint_name

ORDER BY

acc.table_name, acc.constraint_name;

col CREATE_INDEX_COMMAND for a120

SELECT

-- Generate the CREATE INDEX command

'CREATE INDEX ' ||

-- Create a descriptive index name (e.g., IXF_EMPLOYEES_FK_DEPT)

'IXF_' || SUBSTR(acc.table_name, 1, 15) || '_' || SUBSTR(acc.constraint_name, 1, 10) ||

' ON ' ||

acc.owner || '.' || acc.table_name ||

' (' || LISTAGG(acc.column_name, ', ') WITHIN GROUP (ORDER BY acc.position) || ')' ||

-- *** ADD TABLESPACE CLAUSE ***

' XXXX_INDX ;' AS create_index_command

FROM

all_cons_columns acc

JOIN

all_constraints ac ON acc.owner = ac.owner AND acc.constraint_name = ac.constraint_name

LEFT JOIN

all_ind_columns aic ON acc.owner = aic.table_owner

AND acc.table_name = aic.table_name

AND acc.column_name = aic.column_name

AND acc.position = aic.column_position

WHERE

ac.owner = 'XXXXX' --- <-- *** REPLACE THIS WITH YOUR SCHEMA NAME (IN CAPITALS) ***

AND ac.constraint_type = 'R' -- Only include Foreign Keys

-- Exclude Foreign Keys that already have a supporting index

AND NOT EXISTS (

SELECT 1

FROM all_ind_columns aic_check

WHERE aic_check.table_owner = acc.owner

AND aic_check.table_name = acc.table_name

-- Check if the index covers at least the first column of the FK

AND aic_check.column_name = acc.column_name

AND acc.position = 1

)

GROUP BY

acc.owner, acc.table_name, acc.constraint_name

ORDER BY

acc.table_name, acc.constraint_name;

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

DECLARE

-- === 1. USER CONFIGURATION SECTION ===

v_schema_name CONSTANT VARCHAR2(128) := 'SCHEMA_NAME_HERE'; -- <-- SET YOUR SCHEMA NAME (UPPERCASE)

v_tablespace_name CONSTANT VARCHAR2(128) := 'INDEX_TABLESPACE_NAME'; -- <-- SET THE TARGET TABLESPACE

-- =====================================

v_create_index_sql VARCHAR2(4000);

v_index_name VARCHAR2(128);

v_column_list VARCHAR2(4000);

v_missing_index BOOLEAN;

-- Cursor to find all Foreign Key constraints (R) in the target schema

CURSOR c_fk_constraints IS

SELECT

ac.table_name,

ac.constraint_name

FROM

all_constraints ac

WHERE

ac.owner = v_schema_name

AND ac.constraint_type = 'R';

BEGIN

DBMS_OUTPUT.PUT_LINE('--- Starting FK Index Check and Creation Script for Schema: ' || v_schema_name || ' ---');

DBMS_OUTPUT.PUT_LINE('New indexes will be placed in TABLESPACE: ' || v_tablespace_name);

FOR fk_rec IN c_fk_constraints LOOP

-- 1. Get the comma-separated list of columns for the current FK

SELECT

LISTAGG(acc.column_name, ', ') WITHIN GROUP (ORDER BY acc.position),

-- Check if a matching index exists for the first column

CASE WHEN EXISTS (

SELECT 1

FROM all_ind_columns aic_check

WHERE aic_check.table_owner = v_schema_name

AND aic_check.table_name = fk_rec.table_name

AND aic_check.column_name = acc.column_name

AND acc.position = 1 -- Only need to match the first column

) THEN FALSE ELSE TRUE END

INTO

v_column_list,

v_missing_index

FROM

all_cons_columns acc

WHERE

acc.owner = v_schema_name

AND acc.constraint_name = fk_rec.constraint_name

GROUP BY acc.owner, acc.table_name, acc.constraint_name;

-- 2. Check if the index is missing

IF v_missing_index THEN

-- Generate a unique, descriptive index name (max 30 chars for older versions)

v_index_name := 'IXF_' || SUBSTR(fk_rec.table_name, 1, 15) || '_' || SUBSTR(fk_rec.constraint_name, 1, 10);

-- Construct the CREATE INDEX statement

v_create_index_sql :=

'CREATE INDEX ' || v_schema_name || '.' || v_index_name ||

' ON ' || v_schema_name || '.' || fk_rec.table_name ||

' (' || v_column_list || ')' ||

' TABLESPACE ' || v_tablespace_name;

-- 3. Execute the DDL

DBMS_OUTPUT.PUT_LINE('Creating index ' || v_index_name || ' on ' || fk_rec.table_name);

DBMS_OUTPUT.PUT_LINE('SQL: ' || v_create_index_sql || ';');

EXECUTE IMMEDIATE v_create_index_sql;

ELSE

DBMS_OUTPUT.PUT_LINE('Index exists for FK ' || fk_rec.constraint_name || ' on ' || fk_rec.table_name || '. Skipping.');

END IF;

END LOOP;

DBMS_OUTPUT.PUT_LINE('--- Script Finished. ---');

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('!!! ERROR occurred while processing: ' || fk_rec.table_name || ' / ' || fk_rec.constraint_name || '!!!');

DBMS_OUTPUT.PUT_LINE('SQL Error: ' || SQLERRM);

-- Re-raise the exception to halt the script and signal failure

RAISE;

END;

====

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;
	/

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

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

set linesize 500 pagesize 300

col CONSTRAINT_NAME for a20

col R_CONSTRAINT_NAME for a20

col OWNER for a14

col PARENT_OWNER for a14

col PARENT_CONSTRAINT_NAME for a14

col TABLE_NAME for a26

col PARENT_TABLE_NAME for a15

col R_OWNER for a14

col col_01 for a25

col col_02 for a15

col col_03 for a15

col col_04 for a15

col col_05 for a15

col col_06 for a15

col col_07 for a15

col col_08 for a15

col col_09 for a15

col col_10 for a15

col col_11 for a15

col col_12 for a15

col col_13 for a15

col col_14 for a15

col col_15 for a15

col col_16 for a15

WITH

ref_int_constraints AS (

SELECT

col.owner,

col.table_name,

col.constraint_name,

con.status,

con.r_owner,

con.r_constraint_name,

COUNT(*) col_cnt,

MAX(CASE col.position WHEN 01 THEN col.column_name END) col_01,

MAX(CASE col.position WHEN 02 THEN col.column_name END) col_02,

MAX(CASE col.position WHEN 03 THEN col.column_name END) col_03,

MAX(CASE col.position WHEN 04 THEN col.column_name END) col_04,

MAX(CASE col.position WHEN 05 THEN col.column_name END) col_05,

MAX(CASE col.position WHEN 06 THEN col.column_name END) col_06,

MAX(CASE col.position WHEN 07 THEN col.column_name END) col_07,

MAX(CASE col.position WHEN 08 THEN col.column_name END) col_08,

MAX(CASE col.position WHEN 09 THEN col.column_name END) col_09,

MAX(CASE col.position WHEN 10 THEN col.column_name END) col_10,

MAX(CASE col.position WHEN 11 THEN col.column_name END) col_11,

MAX(CASE col.position WHEN 12 THEN col.column_name END) col_12,

MAX(CASE col.position WHEN 13 THEN col.column_name END) col_13,

MAX(CASE col.position WHEN 14 THEN col.column_name END) col_14,

MAX(CASE col.position WHEN 15 THEN col.column_name END) col_15,

MAX(CASE col.position WHEN 16 THEN col.column_name END) col_16,

par.owner parent_owner,

par.table_name parent_table_name,

par.constraint_name parent_constraint_name

FROM dba_constraints con,

dba_cons_columns col,

dba_constraints par

WHERE 1=1

and con.constraint_type = 'R'

-- AND con.owner NOT IN ('ANONYMOUS','APEX_030200','APEX_040000','APEX_SSO','APPQOSSYS','CTXSYS','DBSNMP','DIP','EXFSYS','FLOWS_FILES','MDSYS','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OUTLN','OWBSYS')

--AND con.owner NOT IN ('SI_INFORMTN_SCHEMA','SQLTXADMIN','SQLTXPLAIN','SYS','SYSMAN','SYSTEM','TRCANLZR','WMSYS','XDB','XS$NULL','PERFSTAT','STDBYPERF','MGDSYS','OJVMSYS')

and con.owner in ('ANUJ') ---- <<<<

AND col.owner = con.owner

AND col.constraint_name = con.constraint_name

AND col.table_name = con.table_name

AND par.owner(+) = con.r_owner

AND par.constraint_name(+) = con.r_constraint_name

GROUP BY

col.owner,

col.constraint_name,

col.table_name,

con.status,

con.r_owner,

con.r_constraint_name,

par.owner,

par.constraint_name,

par.table_name

ref_int_indexes AS (

SELECT /*+ MATERIALIZE NO_MERGE */ /* 2a.87 */

r.owner,

r.constraint_name,

c.table_owner,

c.table_name,

c.index_owner,

c.index_name,

r.col_cnt

FROM ref_int_constraints r,

dba_ind_columns c,

dba_indexes i

WHERE c.table_owner = r.owner

AND c.table_name = r.table_name

AND c.column_position <= r.col_cnt

AND c.column_name IN (r.col_01, r.col_02, r.col_03, r.col_04, r.col_05, r.col_06, r.col_07, r.col_08,

r.col_09, r.col_10, r.col_11, r.col_12, r.col_13, r.col_14, r.col_15, r.col_16)

AND i.owner = c.index_owner

AND i.index_name = c.index_name

AND i.table_owner = c.table_owner

AND i.table_name = c.table_name

AND i.index_type != 'BITMAP'

GROUP BY

r.owner,

r.constraint_name,

c.table_owner,

c.table_name,

c.index_owner,

c.index_name,

r.col_cnt

HAVING COUNT(*) = r.col_cnt

)

SELECT /*+ NO_MERGE */ /* 2a.87 */

FROM ref_int_constraints c

WHERE NOT EXISTS (

SELECT NULL

FROM ref_int_indexes i

WHERE i.owner = c.owner

AND i.constraint_name = c.constraint_name

)

ORDER BY

1, 2, 3;

Friday, 2 September 2011

Size of Oracle database

This summary is not available. Please click here to view the post.

Oracle 15 bigest / Largest Objects

Oracle 15 Largest Objects
Oracle 15 bigest Objects
Oracle bigest Objects

col owner format a15
col segment_name format a30
col segment_type format a15
col mb format 999,999,999
select owner, segment_name , segment_type,Mb from ( select owner
, segment_name
, segment_type
, bytes/1024/1024 "MB"
from dba_segments
order by bytes desc
)
where rownum < 16

OWNER SEGMENT_NAME SEGMENT_TYPE MB
--------------- ------------------------------ --------------- ------------
SYS IDL_UB1$ TABLE 264
GGATE GGS_DDL_HIST TABLE 232
SYS SOURCE$ TABLE 96
GGATE GGS_MARKER TABLE 96
XDB SYS_LOB0000056506C00025$$ LOBSEGMENT 57
SYS I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX 46
SYS IDL_UB2$ TABLE 35
SYS WRI$_OPTSTAT_HISTGRM_HISTORY TABLE 29
MDSYS SYS_LOB0000062569C00006$$ LOBSEGMENT 26
SYS C_TOID_VERSION# CLUSTER 23
SYS I_WRI$_OPTSTAT_H_ST INDEX 21
SYS SYS_LOB0000064046C00004$$ LOBSEGMENT 20
SYS C_OBJ#_INTCOL# CLUSTER 20
SYS WRH$_SYSMETRIC_HISTORY TABLE 15
SYS ARGUMENT$ TABLE 14

15 rows selected.

Oracle Oracle OS watcher (OSWatcher) tips from metalink 301137.1

Oracle OS watcher (OSWatcher) tips from metalink 301137.1

oswhacher

OS Watcher by Carl Davis Center of Expertise May 31, 2011

tar xvf osw302.tar

-bash-3.2$ pwd
/aptus/oracle/home/osw

./startOSW.sh
./startOSW.sh 60 10 &

nohup ./startOSW.sh &

nohup /aptus/oracle/home/osw/startOSW.sh & > /tmp/oswatcher.log

/aptus/oracle/home/osw
-bash-3.2$ nohup ./startOSW.sh & ---- for default Value

-bash-3.2$ ps -ef|grep OS

oracle 7232 1 0 09:20 pts/1 00:00:00 /usr/bin/ksh ./OSWatcher.sh
oracle 7272 7232 0 09:20 pts/1 00:00:00 /usr/bin/ksh ./OSWatcherFM.sh 48

-bash-3.2$ cat nohup.out

Info...You did not enter a value for snapshotInterval.
Info...Using default value = 30
Info...You did not enter a value for archiveInterval.
Info...Using default value = 48

Testing for discovery of OS Utilities...

VMSTAT found on your system.
IOSTAT found on your system.
MPSTAT found on your system.
NETSTAT found on your system.
TOP found on your system.

Discovery completed.

Starting OSWatcher v3.0.2 on Fri Sep 2 09:20:20 BST 2011
With SnapshotInterval = 30
With ArchiveInterval = 48

OSWatcher - Written by Carl Davis, Center of Expertise, Oracle Corporation

Starting Data Collection...

osw heartbeat:Fri Sep 2 09:20:20 BST 2011

====

to stop

./stopOSW.sh

-bash-3.2$ ls -ld */
drwxr-xr-x 11 oracle dba 4096 Sep 2 09:20 archive/
drwxr-xr-x 2 oracle dba 4096 Sep 3 18:29 locks/

-bash-3.2$ pwd
/apt/oracle/home/osw/archive

-bash-3.2$ ls -ltr
total 72
drwxr-xr-x 2 oracle dba 4096 Sep 2 09:20 oswprvtnet
drwxr-xr-x 2 oracle dba 4096 Sep 3 18:00 oswvmstat
drwxr-xr-x 2 oracle dba 4096 Sep 3 18:00 oswtop
drwxr-xr-x 2 oracle dba 4096 Sep 3 18:00 oswslabinfo
drwxr-xr-x 2 oracle dba 4096 Sep 3 18:00 oswps
drwxr-xr-x 2 oracle dba 4096 Sep 3 18:00 oswnetstat
drwxr-xr-x 2 oracle dba 4096 Sep 3 18:00 oswmpstat
drwxr-xr-x 2 oracle dba 4096 Sep 3 18:00 oswmeminfo
drwxr-xr-x 2 oracle dba 4096 Sep 3 18:00 oswiostat

Anuj Singh Oracle DBA

Search This Blog

Total Pageviews

Saturday, 3 September 2011

Oracle missing index on foreign key

Oracle : Foreign Keys without Index

Friday, 2 September 2011

Size of Oracle database

Oracle 15 bigest / Largest Objects

Oracle Oracle OS watcher (OSWatcher) tips from metalink 301137.1

Oracle DBA

anuj blog Archive