Declare p_owner_mask Varchar2(30 char):='^'||USER||'$'; --regular expression owner mask p_table_mask Varchar2(30 char):='.'; --regular expression table mask -- v_stmt Varchar2(4000); v_cols Varchar2(4000); v_loc Varchar2(20); BEGIN for r1 in ( select OWNER,CONSTRAINT_NAME,TABLE_NAME from ALL_CONSTRAINTS where regexp_like(OWNER,p_owner_mask) and regexp_like(TABLE_NAME,p_table_mask) and CONSTRAINT_TYPE in ('P','U') and STATUS='DISABLED' )loop Select listagg(COLUMN_NAME,',')within group(order by POSITION) into v_cols from ALL_CONS_COLUMNS where OWNER=r1.OWNER and CONSTRAINT_NAME=r1.CONSTRAINT_NAME and TABLE_NAME=r1.TABLE_NAME; -- Select Case when T.PARTITIONED='YES' and count(C.COLUMN_NAME)=0 then ' local' end into v_loc from ALL_TABLES T left join ALL_PART_KEY_COLUMNS C on C.OWNER=T.OWNER and C.NAME=T.TABLE_NAME and not regexp_like(C.COLUMN_NAME,'^(('||replace(v_cols,',',')|(')||'))$') where T.PARTITIONED='YES' group by T.PARTITIONED; -- v_stmt := 'Alter table '||r1.OWNER||'.'||r1.TABLE_NAME||' enable constraint '||r1.CONSTRAINT_NAME||' using index(create unique index '||r1.OWNER||'.'||r1.CONSTRAINT_NAME||' on '||r1.OWNER||'.'||r1.TABLE_NAME||'('||v_cols||')'||v_loc||')'; execute immediate(v_stmt); end loop; END; /