-
Notifications
You must be signed in to change notification settings - Fork 849
/
Copy pathtext_hc_060710.sql
executable file
·221 lines (206 loc) · 8.27 KB
/
text_hc_060710.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
SET LINESIZE 400;
SET SERVEROUTPUT ON FORMAT WRAP;
DECLARE
db_name VARCHAR2(30);
db_version VARCHAR2(30);
v_count NUMBER := 0;
ctx_check NUMBER := 0;
v_ver_dict VARCHAR2(10);
v_ver_code VARCHAR2(10);
v_dri_ver VARCHAR2(10);
v_stmt VARCHAR2 (250);
CURSOR c_feat IS SELECT comp_name,status,version
FROM dba_registry ORDER BY comp_id;
CURSOR c_inval IS SELECT * FROM dba_objects
WHERE status !='VALID' AND OWNER = 'CTXSYS' ORDER BY object_type, object_name;
CURSOR c_other_objects IS SELECT owner, object_name, object_type, status FROM dba_objects
WHERE owner = 'SYS'
AND (object_name like 'CTX_%' or object_name like 'DRI%')
ORDER BY 2,3;
CURSOR c_count_obj IS SELECT object_type, count(*) count FROM dba_objects
WHERE owner='CTXSYS' GROUP BY object_type ORDER BY 1;
CURSOR c_text_indexes IS
SELECT c.*, i.status,i.domidx_status,i.domidx_opstatus
FROM ctxsys.ctx_indexes c, dba_indexes i
WHERE c.idx_owner = i.owner
AND c.idx_name = i.index_name
ORDER BY 2,3;
CURSOR c_errors IS SELECT * FROM ctxsys.ctx_index_errors
ORDER BY err_timestamp DESC, err_index_owner, err_index_name;
PROCEDURE display_banner
IS
BEGIN
DBMS_OUTPUT.PUT_LINE( '**********************************************************************');
END display_banner;
BEGIN
DBMS_OUTPUT.ENABLE(900000);
SELECT name INTO db_name FROM v$database;
SELECT version INTO db_version FROM v$instance;
DBMS_OUTPUT.PUT_LINE( 'Oracle Text Health Check Tool ' || TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
DBMS_OUTPUT.PUT_LINE('.');
display_banner;
DBMS_OUTPUT.PUT_LINE('Database:');
display_banner;
DBMS_OUTPUT.PUT_LINE ('--> name: ' || db_name );
DBMS_OUTPUT.PUT_LINE ('--> version: ' || db_version );
DBMS_OUTPUT.PUT_LINE ('.');
display_banner;
DBMS_OUTPUT.PUT_LINE ( 'Database Components:');
display_banner;
FOR v_feat IN c_feat LOOP
DBMS_OUTPUT.PUT_LINE( '--> ' || rpad(v_feat.comp_name, 35) || ' '
|| rpad(v_feat.version, 10) || ' ' || rpad(v_feat.status, 10));
END LOOP;
DBMS_OUTPUT.PUT_LINE ('.');
display_banner;
DBMS_OUTPUT.PUT_LINE ('Oracle Text Status and Version:');
display_banner;
SELECT * INTO v_ver_dict, v_ver_code FROM ctxsys.ctx_version;
DBMS_OUTPUT.PUT_LINE('.. CTXSYS data dictionary version (Ver Dict) : '||
v_ver_dict);
DBMS_OUTPUT.PUT_LINE('.. Linked-in code version (Ver Code) : '||
v_ver_code);
SELECT substr(ctxsys.dri_version,1,10) INTO v_dri_ver FROM dual;
DBMS_OUTPUT.PUT_LINE('.. DRI Version : '|| v_dri_ver);
DBMS_OUTPUT.PUT_LINE ('.');
display_banner;
DBMS_OUTPUT.PUT_LINE ( 'Invalid Objects in CTXSYS Schema:');
display_banner;
FOR v_inval IN c_inval LOOP
DBMS_OUTPUT.PUT_LINE( '.. CTXSYS.' || rpad(v_inval.object_name,30) ||
' - ' || v_inval.object_type );
v_count := c_inval%ROWCOUNT;
END LOOP;
IF v_count = 0 THEN
DBMS_OUTPUT.PUT_LINE('There are no invalid objects in the CTXSYS schema');
DBMS_OUTPUT.PUT_LINE ('.');
END IF;
display_banner;
DBMS_OUTPUT.PUT_LINE ( 'Possible Text-related Objects under the SYS schema:');
display_banner;
v_count := 0;
FOR v_other_objects IN c_other_objects LOOP
DBMS_OUTPUT.PUT_LINE( '.. ' || v_other_objects.owner || '.' ||
v_other_objects.object_name || ' - ' || v_other_objects.object_type ||
' - ' || v_other_objects.status );
v_count := c_other_objects%ROWCOUNT;
END LOOP;
IF v_count = 0 THEN
DBMS_OUTPUT.PUT_LINE('There are no Text-related Objects under the SYS schema');
ELSE
DBMS_OUTPUT.PUT_LINE (' ');
DBMS_OUTPUT.PUT_LINE('If objects listed above are INVALID, see:');
DBMS_OUTPUT.PUT_LINE(' Note 558894.1 - Invalid Oracle Text Object under user SYS');
DBMS_OUTPUT.PUT_LINE('If Oracle Text is invalid, open a Service Request.');
DBMS_OUTPUT.PUT_LINE(' Support, see INTERNAL Note.746970.1.');
END IF;
DBMS_OUTPUT.PUT_LINE ('.');
display_banner;
DBMS_OUTPUT.PUT_LINE ( 'Summary count of CTXSYS schema objects:');
display_banner;
FOR v_count_obj IN c_count_obj LOOP
DBMS_OUTPUT.PUT_LINE('.. ' || rpad(v_count_obj.object_type,14) ||
' ' || lpad(v_count_obj.count,3));
END LOOP;
DBMS_OUTPUT.PUT_LINE ('.');
display_banner;
DBMS_OUTPUT.PUT_LINE ('Text Indexes:');
display_banner;
v_count := 0;
FOR v_text_indexes IN c_text_indexes LOOP
DBMS_OUTPUT.PUT('.. ' || v_text_indexes.idx_owner ||
'.' || v_text_indexes.idx_name || ' is ');
IF (v_text_indexes.status != 'VALID' OR
v_text_indexes.domidx_status != 'VALID' OR
v_text_indexes.domidx_opstatus != 'VALID') THEN
DBMS_OUTPUT.PUT_LINE('INVALID');
DBMS_OUTPUT.PUT_LINE('.... INDEX STATUS => '||v_text_indexes.status);
DBMS_OUTPUT.PUT_LINE('.... DOMAIN INDEX STATUS => '||v_text_indexes.domidx_status);
DBMS_OUTPUT.PUT_LINE('.... DOMAIN INDEX OPERATION STATUS => '
||v_text_indexes.domidx_opstatus);
ELSE
DBMS_OUTPUT.PUT_LINE('VALID');
END IF;
DBMS_OUTPUT.PUT('.... Table: ' || v_text_indexes.idx_table_owner
|| '.' || v_text_indexes.idx_table);
DBMS_OUTPUT.PUT_LINE(', Indexed Column: ' || v_text_indexes.idx_text_name);
DBMS_OUTPUT.PUT_LINE('.... Index Type: ' || v_text_indexes.idx_type);
v_count := c_text_indexes%ROWCOUNT;
END LOOP;
IF v_count = 0 then
DBMS_OUTPUT.PUT_LINE('There are no Text indexes');
END IF;
DBMS_OUTPUT.PUT_LINE ('.');
display_banner;
DBMS_OUTPUT.PUT_LINE ('Ten (10) most recent text index errors (ctx_index_errors):');
display_banner;
v_count := 0;
FOR v_errors IN c_errors LOOP
EXIT WHEN (c_errors%NOTFOUND) OR (c_errors%ROWCOUNT > 9);
DBMS_OUTPUT.PUT_LINE(to_char(v_errors.ERR_TIMESTAMP,'Dy Mon DD HH24:MI:SS YYYY'));
DBMS_OUTPUT.PUT_LINE('.. Index name: ' || v_errors.err_index_owner
|| '.' || v_errors.err_index_name || ' Rowid: ' || v_errors.err_textkey);
DBMS_OUTPUT.PUT_LINE('.. Error: ');
DBMS_OUTPUT.PUT_LINE(' '||
rtrim(replace(v_errors.err_text,chr(10),chr(10)||' '),chr(10)||' '));
v_count := c_errors%ROWCOUNT;
END LOOP;
IF v_count = 0 THEN
DBMS_OUTPUT.PUT_LINE('There are no errors logged in CTX_INDEX_ERRORS');
END IF;
DBMS_OUTPUT.PUT_LINE ('.');
display_banner;
DBMS_OUTPUT.PUT_LINE ('Testing Text Index Creation:');
display_banner;
-- Create text_healthcheck user
SELECT COUNT (1) INTO v_count FROM dba_users
WHERE username = 'TEXT_HEALTHCHECK';
IF v_count != 0 THEN
DBMS_OUTPUT.PUT_LINE ('..Dropping user TEXT_HEALTHCHECK');
EXECUTE IMMEDIATE ('DROP USER text_healthcheck CASCADE');
DBMS_OUTPUT.PUT_LINE ('....User TEXT_HEALTHCHECK dropped successfully');
END IF;
DBMS_OUTPUT.PUT_LINE ('..Creating user TEXT_HEALTHCHECK');
v_stmt := 'GRANT connect,resource,ctxapp TO text_healthcheck IDENTIFIED BY text_healthcheck';
EXECUTE IMMEDIATE (v_stmt);
DBMS_OUTPUT.PUT_LINE ('....User TEXT_HEALTHCHECK created successfully');
-- Create context index
DBMS_OUTPUT.PUT_LINE ('..Testing creation of Text index type CONTEXT');
v_stmt :=
'CREATE TABLE text_healthcheck.text_hc_tab (quick_id NUMBER '
|| 'constraint text_hc_pk PRIMARY KEY, '
|| 'text VARCHAR2(80))';
DBMS_OUTPUT.PUT_LINE('....Creating table TEXT_HC_TAB');
EXECUTE IMMEDIATE(v_stmt);
DBMS_OUTPUT.PUT_LINE('....Inserting test data');
v_stmt :=
'INSERT INTO text_healthcheck.text_hc_tab VALUES (1,'
|| '''The cat sat on the mat'')';
EXECUTE IMMEDIATE(v_stmt);
v_stmt :=
'INSERT INTO text_healthcheck.text_hc_tab VALUES (2,'
|| '''The quick brown fox jumps over the lazy dog'')';
EXECUTE IMMEDIATE(v_stmt);
EXECUTE IMMEDIATE('COMMIT');
v_stmt :=
'CREATE INDEX text_healthcheck.text_hc_idx '
|| 'ON text_healthcheck.text_hc_tab(text) INDEXTYPE IS CTXSYS.CONTEXT';
DBMS_OUTPUT.PUT_LINE('....Creating text index TEXT_HC_IDX');
EXECUTE IMMEDIATE(v_stmt);
DBMS_OUTPUT.PUT_LINE ('....Text index TEXT_HC_IDX created successfully');
DBMS_OUTPUT.PUT_LINE (' ');
DBMS_OUTPUT.PUT_LINE ('..Dropping user TEXT_HEALTHCHECK');
EXECUTE IMMEDIATE ('DROP USER text_healthcheck CASCADE');
DBMS_OUTPUT.PUT_LINE ('....User TEXT_HEALTHCHECK dropped successfully');
DBMS_OUTPUT.PUT_LINE (' ');
DBMS_OUTPUT.PUT_LINE ('Text Index Creation Test complete');
DBMS_OUTPUT.PUT_LINE ('.');
display_banner;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT('....');
DBMS_OUTPUT.PUT_LINE (SQLERRM);
display_banner;
END;
/
SET SERVEROUTPUT OFF