01 | --------------------------------------------------------------------------- |
02 | -- title : find pk without index |
03 | -- author : proud |
04 | -- discription : find pk without (unique) index - drop and readd |
05 | -- usage : @find_pk_without_idx |
06 | -- created : 2010.02.02 |
07 | -- update date: |
08 | --------------------------------------------------------------------------- |
09 |
10 | set serveroutput on |
11 |
12 | drop table result_pk_without_idx; |
13 |
14 | create table result_pk_without_idx |
15 | ( |
16 | script_text varchar2(4000) |
17 | ) |
18 | nologging |
19 | compress |
20 | ; |
21 |
22 | declare |
23 | v_drop_sql varchar2(4000); |
24 | v_add_sql varchar2(4000); |
25 | v_own varchar2(4000); |
26 | v_tab varchar2(4000); |
27 | v_con varchar2(4000); |
28 | v_idx varchar2(4000); |
29 | v_col varchar2(4000); |
30 | v_cnt pls_integer; |
31 |
32 | begin |
33 |
34 | dbms_output.enable(9999999); |
35 |
36 | for i in ( |
37 | select a.owner |
38 | ,a.table_name |
39 | ,a.constraint_name |
40 | ,a.index_name |
41 | from dba_constraints a |
42 | ,dba_indexes b |
43 | where a.index_name = b.index_name(+) |
44 | and a.constraint_type = 'P' |
45 | and b.index_name is null |
46 | ) loop |
47 | v_own := i.owner; |
48 | v_tab := i.table_name; |
49 | v_con := i.constraint_name; |
50 | v_idx := i.index_name; |
51 |
52 | v_drop_sql := 'alter table '||v_own||'.'||v_tab||' drop constraint '||v_con; |
53 | v_add_sql := 'alter table '||v_own||'.'||v_tab||' add constraint '||v_con||' primary key ('; |
54 |
55 | v_cnt := 0; |
56 |
57 | begin |
58 | for c in ( |
59 | select column_name |
60 | from dba_cons_columns |
61 | where owner = ''||v_own||'' |
62 | and table_name = ''||v_tab||'' |
63 | order by position |
64 | ) loop |
65 |
66 | v_col := c.column_name; |
67 |
68 | if v_cnt > 0 then |
69 | v_add_sql := v_add_sql||','||v_col; |
70 | else |
71 | v_add_sql := v_add_sql||v_col; |
72 | end if; |
73 |
74 | v_cnt := v_cnt + 1; |
75 |
76 | end loop; |
77 | end; |
78 |
79 | v_add_sql := v_add_sql||')'; |
80 |
81 | execute immediate 'insert into result_pk_without_idx values ('''||v_drop_sql||''')'; |
82 | execute immediate 'insert into result_pk_without_idx values ('''||v_add_sql||''')'; |
83 | execute immediate 'commit'; |
84 | end loop; |
85 |
86 | dbms_output.put_line('select * from result_pk_without_idx;'); |
87 | end; |
88 | / |
