select
'create index '||s.owner||'.'||
case when length(s.new_idx_name) <= 30 then s.new_idx_name
else case when length(s.constraint_name) <= 28 then s.constraint_name||'_i'
else s.constraint_name end end ||
' on '||s.owner||'.'||s.table_name||'('||s.columnsa||') tablespace &IND_TBS;' cmd
, s.*
from
(
select decode( b.table_name, null, '****', 'ok' ) Status, a.owner
, a.table_name, a.columns columnsa, b.columns columnsb, a.constraint_name
, a.table_name||'_'||replace(a.columns,', ','_')||'_i ' new_idx_name
from
( select a.owner,
substr(a.table_name,1,30) table_name,
substr(a.constraint_name,1,30) 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)) ||
max(decode(position,11,', '||substr(column_name,1,30),null)) ||
max(decode(position,12,', '||substr(column_name,1,30),null)) ||
max(decode(position,13,', '||substr(column_name,1,30),null)) ||
max(decode(position,14,', '||substr(column_name,1,30),null)) ||
max(decode(position,15,', '||substr(column_name,1,30),null)) ||
max(decode(position,16,', '||substr(column_name,1,30),null)) columns
from dba_cons_columns a, dba_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by a.owner, substr(a.table_name,1,30), substr(a.constraint_name,1,30) ) a,
( select table_owner owner, substr(table_name,1,30) table_name, substr(index_name,1,30) index_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)) ||
max(decode(column_position,11,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,12,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,13,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,14,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,15,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,16,', '||substr(column_name,1,30),NULL)) columns
from dba_ind_columns
group by table_owner, substr(table_name,1,30), substr(index_name,1,30) ) b
where a.owner = b.owner (+)
and a.table_name = b.table_name (+)
and b.columns (+) like a.columns || '%'
) s
where status <> 'ok' and owner = '&OWNER'