1
2
3
4 """SQL queries ordered by theme in dictionary
5 @author: Sébastien Renard (sebastien.renard@digitalfox.org)
6 @author: Sébastien Delcros (sebastien.delcros@gmail.com)
7 @license: GNU GPL V3
8 """
9
10
11 searchObjectSql = {
12 "datafile" : (u"""select 'Datafiles', file_name from dba_data_files
13 where (%s) /*%s*/ order by %s""", "file_name"),
14 "directory" : (u"""select owner, directory_name from all_directories
15 where (%s) and owner like '%s' order by %s""", "directory_name"),
16 "index" : (u"""select owner, index_name from all_indexes
17 where (%s) and owner like '%s' order by %s""", "index_name"),
18 "function" : (u"""select distinct owner, name from all_source
19 where (%s) and owner like '%s' and type='FUNCTION' order by %s""", "name"),
20 "package" : (u"""select distinct owner, name from all_source
21 where (%s) and owner like '%s' and type='PACKAGE' order by %s""", "name"),
22 "procedure" : (u"""select distinct owner, name from all_source
23 where (%s) and owner like '%s' and type='PROCEDURE' order by %s""", "name"),
24 "role": (u"""select 'Roles', role from dba_roles
25 where (%s) /*%s*/ order by %s""", "role"),
26 "profile": (u"""select distinct 'Profiles', profile from dba_profiles
27 where (%s) /*%s*/ order by %s""", "profile"),
28 "sequence" : (u"""select sequence_owner, sequence_name from all_sequences
29 where (%s) and sequence_owner like '%s' order by %s""", "sequence_name"),
30 "synonym" : (u"""select owner, synonym_name from all_synonyms
31 where (%s) and owner like '%s' order by %s""", "synonym_name"),
32 "table" : (u"""select owner, table_name from all_tables
33 where (%s) and owner like '%s' order by %s""", "table_name"),
34 "tablespace": (u"""select 'Tablespaces', tablespace_name from dba_tablespaces
35 where (%s) /*%s*/ order by %s""", "tablespace_name"),
36 "trigger" : (u"""select owner, trigger_name from all_triggers
37 where (%s) and owner like '%s' order by %s""", "trigger_name"),
38 "user": (u"""select 'Users', username from all_users
39 where (%s) /*%s*/ order by %s""", "username"),
40 "view" : (u"""select owner, view_name from all_views
41 where (%s) and owner like '%s' order by %s""", "view_name")
42 }
43
44 guessInfoSql = {
45 "commentFromNameAndOwner" : u"""select comments from all_tab_comments
46 where table_name=:1
47 and owner=:2""",
48 "typeFromNameAndOwner" : u"""select object_type from all_objects
49 where object_name=:1
50 and owner=:2""",
51 "typeFromNameAndSYS" : u"""select object_type from dba_objects
52 where object_name=:1
53 and owner='SYS'""",
54 "otherTypeFromName" : u"""select 'USER' from all_users
55 where username=:1
56 union
57 select 'TABLESPACE' from dba_tablespaces
58 where tablespace_name=:1
59 union
60 select 'DATA FILE' from dba_data_files
61 where file_name=:1""",
62 "objectStatusFromName" : u"""select status from all_objects
63 where object_name=:1""",
64 "objectStatusFromNameAndOwner" : u"""select status from all_objects
65 where object_name=:1
66 and owner=:2""",
67 "dbfStatusFromName" : u"""select status from dba_data_files
68 where file_name=:1""",
69 "tbsStatusFromName" : u"""select status from dba_tablespaces
70 where tablespace_name=:1""",
71 "userStatusFromName" : u"""select account_status from dba_users
72 where username=:1"""
73 }
74
75 directorySql = {
76 "pathFromName" : u"""select directory_path
77 from all_directories
78 where directory_name=:1"""
79 }
80
81 datafileSql = {
82 "tablespaceFromName" : u"""select tablespace_name
83 from dba_data_files
84 where file_name=:1""",
85 "allocatedBytesFromName" : u"""select bytes
86 from dba_data_files
87 where file_name=:1""",
88 "freeBytesFromName" : u"""select nvl(sum(fr.bytes), 0)
89 from dba_data_files df, dba_free_space fr
90 where df.file_id=fr.file_id
91 and df.file_name=:1"""
92 }
93
94 dbLinkSql = {
95 "hostFromOwnerAndName" : u"""select owner, host
96 from all_db_links
97 where db_link=:1""",
98 "usernameFromOwnerAndName" : u"""select owner, username
99 from all_db_links
100 where db_link=:1"""
101 }
102
103 indexSql = {
104 "propertiesFromOwnerAndName" : u""" select TABLE_OWNER, TABLE_NAME, INDEX_TYPE, UNIQUENESS,
105 COMPRESSION, LEAF_BLOCKS, DISTINCT_KEYS,
106 AVG_LEAF_BLOCKS_PER_KEY
107 from all_indexes
108 where owner=:1
109 and index_name=:2""",
110 "indexedColumnsFromOwnerAndName" : u"""select COLUMN_NAME, COLUMN_POSITION
111 from ALL_IND_COLUMNS
112 where index_owner=:1
113 and index_name=:2
114 order by COLUMN_POSITION"""
115 }
116
117 metadataSql = {
118 "ddlFromTypeNameAndOwner" : u"""select
119 dbms_metadata.get_ddl(:1, :2, :3)
120 from dual"""
121 }
122
123 mviewSql = {
124 "queryFromOwnerAndName" : u"""select owner, query
125 from all_mviews
126 where owner=:1
127 and mview_name=:2"""
128 }
129
130 packageSql = {
131 "proceduresFromOwnerAndName" : u"""select procedure_name
132 from all_procedures
133 where owner=:1
134 and object_name=:2""",
135 "sourceFromOwnerAndName" : u""" select line, replace(replace(text, chr(10), ' '), chr(13), ' ')
136 from all_source
137 where owner=:1
138 and name=:2
139 order by line"""
140 }
141
142 sequenceSql = {
143 "lastFromOwnerAndName" : u"""select sequence_owner, last_number
144 from all_sequences
145 where sequence_owner=:1
146 and sequence_name=:2""",
147 "minFromOwnerAndName" : u"""select sequence_owner, min_value
148 from all_sequences
149 where sequence_owner=:1
150 and sequence_name=:2""",
151 "maxFromOwnerAndName" : u"""select sequence_owner, max_value
152 from all_sequences
153 where sequence_owner=:1
154 and sequence_name=:2""",
155 "stepFromOwnerAndName" : u"""select sequence_owner, increment_by
156 from all_sequences
157 where sequence_owner=:1
158 and sequence_name=:2"""
159 }
160
161 synonymSql = {
162 "targetFromOwnerAndName" : u"""select table_owner, table_name
163 from all_synonyms
164 where owner=:1
165 and synonym_name=:2"""
166 }
167
168 storedObjectSql = {
169 "sourceFromOwnerAndNameAndType" : u"""select text from all_source
170 where owner=:1
171 and name=:2
172 and type=:3
173 order by line"""
174 }
175
176 tableSql = {
177 "indexedColFromOwnerAndName" : u"""select column_name, index_name, column_position
178 from all_ind_columns
179 where table_owner=:1
180 and table_name=:2""",
181 "primaryKeyFromOwnerAndName" : u"""select col.column_name
182 from all_constraints cons, all_cons_columns col
183 where cons.constraint_type='P'
184 and cons.owner=:1
185 and cons.table_name=:2
186 and cons.owner=col.owner
187 and cons.table_name=col.table_name
188 and col.constraint_name=cons.constraint_name
189 order by col.position""",
190 "lastAnalyzedFromOwnerAndName" : u"""select last_analyzed
191 from all_tables
192 where owner=:1
193 and table_name=:2""",
194 "numRowsFromOwnerAndName" : u"""select num_rows
195 from all_tables
196 where owner=:1
197 and table_name=:2""",
198 "avgRowLengthFromOwnerAndName" : u"""select avg_row_len
199 from all_tables
200 where owner=:1
201 and table_name=:2""",
202 "usedBlocksFromOwnerAndName" : u"""select blocks
203 from dba_segments
204 where owner=:1
205 and segment_type='TABLE'
206 and segment_name=:2""",
207 "neededBlocksFromOwnerAndName" : u"""select
208 count(distinct dbms_rowid.rowid_block_number(rowid))
209 from %s.%s""",
210 "isPartitionedFromOwnerAndName" : u"""select partitioned
211 from all_tables
212 where owner=:1
213 and table_name=:2"""
214 }
215
216 tablespaceSql = {
217 "datafilesFromName" : u"""select file_name
218 from dba_data_files
219 where tablespace_name=:1"""
220 }
221
222 tabularSql = {
223 "commentFromOwnerAndName" : u"""select comments from all_tab_comments
224 where owner=:1
225 and table_name=:2""",
226 "commentFromDBAAndName" : u"""select comments from dba_tab_comments
227 where owner=:1
228 and table_name=:2""",
229 "createdFromOwnerAndName" : u"""select created from all_objects
230 where owner=:1
231 and object_name=:2""",
232 "createdFromDBAAndName" : u"""select created from dba_objects
233 where owner=:1
234 and object_name=:2""",
235 "lastDDLFromOwnerAndName" : u"""select last_ddl_time from all_objects
236 where owner=:1
237 and object_name=:2""",
238 "lastDDLFromDBAAndName" : u"""select last_ddl_time from dba_objects
239 where owner=:1
240 and object_name=:2""",
241 "columnsFromOwnerAndName" : u"""select a.column_name, a.data_type||'('||a.data_length||')', a.nullable, c.comments
242 from all_tab_columns a, all_col_comments c
243 where a.owner=:1
244 and a.owner=c.owner
245 and a.table_name=:2
246 and a.table_name=c.table_name
247 and a.column_name=c.column_name""",
248 "columnsFromDBAAndName" : u"""select a.column_name, a.data_type||'('||a.data_length||')', a.nullable, c.comments
249 from dba_tab_columns a, dba_col_comments c
250 where a.owner=:1
251 and a.owner=c.owner
252 and a.table_name=:2
253 and a.table_name=c.table_name
254 and a.column_name=c.column_name""",
255 "numberOfColumnsFromOwnerAndName" : u"""select count(*)
256 from all_tab_columns
257 where owner=:1
258 and table_name=:2"""
259 }
260
261 triggerSql = {
262 "typeFromOwnerAndName" : u"""select trigger_type
263 from all_triggers
264 where owner=:1
265 and trigger_name=:2""",
266 "eventFromOwnerAndName" : u"""select triggering_event
267 from all_triggers
268 where owner=:1
269 and trigger_name=:2""",
270 "bodyFromOwnerAndName" : u"""select trigger_body
271 from all_triggers
272 where owner=:1
273 and trigger_name=:2""",
274 "statusFromOwnerAndName" : u"""select status
275 from all_triggers
276 where owner=:1
277 and trigger_name=:2""",
278 "tableFromOwnerAndName" : u"""select table_owner, table_name
279 from all_triggers
280 where owner=:1
281 and trigger_name=:2"""
282 }
283
284 userSql = {
285 "tablespaceFromName" : u"""select tablespace_name
286 from dba_segments
287 where owner=:1
288 group by tablespace_name
289 union
290 select default_tablespace
291 from dba_users
292 where username=:1""",
293 "defaultTbsFromName" : u"""select default_tablespace
294 from dba_users
295 where username=:1""",
296 "tempTbsFromName" : u"""select temporary_tablespace
297 from dba_users
298 where username=:1""",
299 "nbTablesFromNameAndTbs" : u"""select count(*)
300 from all_tables
301 where owner=:1
302 and tablespace_name like :2""",
303 "nbIndexesFromNameAndTbs" : u"""select count(*)
304 from all_indexes
305 where owner=:1
306 and tablespace_name like :2"""
307 }
308
309 viewSql = {
310 "queryFromOwnerAndName" : u"""select owner, text
311 from all_views
312 where owner=:1
313 and view_name=:2""",
314 "replaceQueryFromName" : u"""create or replace view %s as %s""",
315 "replaceQueryFromFullName" : u"""create or replace view %s as %s"""
316 }
317
318
319 sessionStatSql = {
320 "all" : u"""Select a.Sid "Id", a.Serial# "Serial", a.SchemaName "Schema",
321 a.OsUser "Osuser", substr(a.Machine, 0, decode(instr(a.Machine, '.'), 0, length(a.Machine), instr(a.Machine, '.')-1)) "Machine", a.Program "Program",
322 To_Char(a.Logon_Time, 'MON-DD HH24:MI') "Logged Since",
323 b.Block_Gets "Blk Gets", b.Consistent_Gets "Cons Gets",
324 b.Physical_Reads "Phy Rds", b.Block_Changes "Blk Chg",
325 b.Consistent_Changes "Cons Chg", c.Value * 10 "CPU(ms)",
326 a.Process "C PID", e.SPid "S PID", d.sql_text "SQL"
327 from v$session a, v$sess_io b, v$sesstat c, v$sql d, v$process e
328 where a.sid = b.sid ( + )
329 and a.sid = c.sid ( + )
330 and ( c.statistic# = 12 OR c.statistic# IS NULL )
331 and a.sql_address = d.address ( + )
332 and a.sql_hash_value = d.hash_value ( + )
333 and ( d.child_number = 0 OR d.child_number IS NULL )
334 and a.paddr = e.addr ( + )
335 %s
336 and a.TYPE!= 'BACKGROUND'
337 order by a.Sid""",
338 "details" : u"""select b.Name,a.Value
339 from V$SesStat a, V$StatName b
340 where a.SID = :1 and a.statistic# = b.statistic#""",
341 "ios" : u"""select sum(block_gets) "Block gets", sum(consistent_gets) "Consistent gets",
342 sum(physical_reads) "Physical reads", sum(block_changes) "Block changes",
343 sum(consistent_changes) "Consistent changes"
344 from v$sess_io
345 where sid in
346 (select b.sid
347 from v$session a,v$session b
348 where a.sid = :1 and a.audsid = b.audsid)""",
349 "locks" : u"""select b.Object_Name "Object Name", b.Object_Type "Type",
350 decode(a.locked_mode,0,'None',1,'Null',2,'Row-S',
351 3,'Row-X',4,'Share',5,'S/Row-X',
352 6,'Exclusive',a.Locked_Mode) "Locked Mode"
353 from v$locked_object a,sys.all_objects b
354 where a.object_id = b.object_id
355 and a.session_id = :1""",
356 "waitEvents" : u"""select cpu*10 "CPU(ms)", parallel*10 "Parallel execution",
357 filewrite*10 "DB File Write", writecomplete*10 "Write Complete",
358 fileread*10 "DB File Read", singleread*10 "DB Single File Read",
359 control*10 "Control File I/O", direct*10 "Direct I/O",
360 log*10 "Log file", net*10 "SQL*Net",
361 (total-parallel-filewrite-writecomplete-fileread-singleread-control-direct-log-net)
362 *10 "Other"
363 from (select
364 sum(decode(substr(event,1,2),'PX',time_waited,0))
365 -sum(decode(event,'PX Idle Wait',time_waited,0)) parallel,
366 sum(decode(event,'db file parallel write',time_waited,
367 'db file single write',time_waited,0)) filewrite,
368 sum(decode(event,'write complete waits',time_waited,NULL))
369 writecomplete, sum(decode(event,
370 'db file parallel read',time_waited,
371 'db file sequential read',time_waited,0)) fileread,
372 sum(decode(event,'db file scattered read',time_waited,0)) singleread,
373 sum(decode(substr(event,1,12),'control file',time_waited,0)) control,
374 sum(decode(substr(event,1,11),'direct path',time_waited,0)) direct,
375 sum(decode(substr(event,1,3),'log',time_waited,0)) log,
376 sum(decode(substr(event,1,7),'SQL*Net',time_waited,0))
377 -sum(decode(event,
378 'SQL*Net message from client',time_waited,0)) net,
379 sum(decode(event,
380 'PX Idle Wait',0,'SQL*Net message from client',0,time_waited)) total
381 from v$session_event
382 where sid in (select b.sid
383 from v$session a,v$session b
384 where a.sid = :1 and a.audsid = b.audsid)),
385 (select value*10 cpu
386 from v$sesstat a where statistic# = 12
387 and a.sid in
388 (select b.sid from v$session a,v$session b
389 where a.sid = :1 and a.audsid = b.audsid))""",
390 "openCursors" : u"""select SQL_Text "SQL", Address||':'||Hash_Value " Address"
391 from v$open_cursor where sid = :1""",
392 "currentStatement": u"""Select b.sql_text "SQL" from v$session a, v$sqltext b
393 where a.sql_address = b.address ( + )
394 and a.sql_hash_value = b.hash_value ( + )
395 and a.Sid=:1
396 order by b.piece"""
397 }
398
399
400 datamodelSql = {
401 "tablesFromOwner" : u"""SELECT table_name
402 FROM all_tables tab
403 WHERE owner='%s'
404 AND (%s)
405 AND table_name NOT LIKE '%%PLAN_TABLE'
406 AND table_name NOT LIKE 'TOAD%%'
407 AND temporary='N'
408 AND NOT EXISTS (SELECT 1
409 FROM all_external_tables ext
410 WHERE ext.owner=tab.owner
411 AND ext.table_name=tab.table_name)""",
412 "columnsFromOwnerAndTable" : u"""SELECT column_name
413 , data_type
414 , (SELECT position
415 FROM all_cons_columns col, all_constraints cst
416 WHERE cst.owner=col.owner
417 AND tab.owner=col.owner
418 AND tab.table_name=col.table_name
419 AND tab.column_name=col.column_name
420 AND col.constraint_name=cst.constraint_name
421 AND cst.constraint_type='P') pk_position
422 FROM all_tab_columns tab
423 WHERE owner=:1
424 AND table_name=:2
425 ORDER BY pk_position, column_id""",
426 "constraintsFromOwner" : u"""SELECT fk.constraint_name, fk.table_name, pk.table_name
427 FROM all_constraints fk, all_constraints pk
428 WHERE fk.owner='%s'
429 AND pk.table_name in (%s)
430 AND fk.table_name in (%s)
431 AND fk.owner=pk.owner
432 AND fk.r_constraint_name = pk.constraint_name
433 AND fk.constraint_type = 'R'
434 AND pk.constraint_type = 'P'"""
435 }
436
437 dependenciesSql = {
438 "refByFromOwnerAndName" : u"""SELECT referenced_owner, referenced_name, referenced_type
439 FROM all_dependencies
440 WHERE owner=:1
441 AND name=:2""",
442 "refOnFromOwnerAndName" : u"""SELECT owner, name, 'None'
443 FROM all_dependencies
444 WHERE referenced_owner=:1
445 AND referenced_name=:2 """
446 }
447
448 diskusageSql = {
449 "Tablespaces" : u"""SELECT DISTINCT tablespace_name
450 FROM user_segments
451 WHERE segment_type in ('TABLE', 'TABLE PARTITION',
452 'INDEX', 'INDEX PARTITION')""",
453 "TablespacesFromOwner" : u"""SELECT DISTINCT tablespace_name
454 FROM dba_segments
455 WHERE owner=:1
456 AND segment_type in ('TABLE', 'TABLE PARTITION',
457 'INDEX', 'INDEX PARTITION')""",
458 "TablesFromTbs" : u"""SELECT t.table_name, t.num_rows, t.avg_row_len, s.bytes
459 FROM all_tables t, user_segments s
460 WHERE t.tablespace_name=:1
461 AND t.table_name=s.segment_name
462 AND t.temporary='N'
463 AND t.table_name NOT IN (select table_name from all_external_tables)
464 UNION
465 SELECT p.table_name||'/'||p.partition_name, p.num_rows, p.avg_row_len, s.bytes
466 FROM all_tab_partitions p, user_segments s
467 WHERE p.tablespace_name=:1
468 AND p.partition_name=s.partition_name
469 AND s.segment_type='TABLE PARTITION'""",
470 "IndexesFromTbs" : u"""SELECT i.index_name, i.num_rows, i.distinct_keys, s.bytes, i.table_name
471 FROM all_indexes i, user_segments s
472 WHERE i.tablespace_name=:1
473 AND i.index_name=s.segment_name
474 AND s.segment_type='INDEX'
475 UNION
476 SELECT p.index_name||'/'||p.partition_name, p.num_rows, p.distinct_keys, s.bytes, ''
477 FROM all_ind_partitions p, user_segments s
478 WHERE p.tablespace_name=:1
479 AND p.partition_name=s.partition_name
480 AND s.segment_type='INDEX PARTITION'""",
481 "TablesFromOwnerAndTbs" : u"""SELECT t.table_name, t.num_rows, t.avg_row_len, s.bytes
482 FROM all_tables t, dba_segments s
483 WHERE t.owner=:1
484 AND t.tablespace_name=:2
485 AND t.owner=s.owner
486 AND t.table_name=s.segment_name
487 AND t.temporary='N'
488 AND t.table_name NOT IN (select table_name from all_external_tables)
489 UNION
490 SELECT p.table_name||'/'||p.partition_name, p.num_rows, p.avg_row_len, s.bytes
491 FROM all_tab_partitions p, dba_segments s
492 WHERE p.table_owner=:1
493 AND p.tablespace_name=:2
494 AND p.table_owner=s.owner
495 AND p.partition_name=s.partition_name
496 AND s.segment_type='TABLE PARTITION'""",
497 "IndexesFromOwnerAndTbs" : u"""SELECT i.index_name, i.num_rows, i.distinct_keys, s.bytes, i.table_name
498 FROM all_indexes i, dba_segments s
499 WHERE i.owner=:1
500 AND i.tablespace_name=:2
501 AND i.owner=s.owner
502 AND i.index_name=s.segment_name
503 AND s.segment_type='INDEX'
504 UNION
505 SELECT p.index_name||'/'||p.partition_name, p.num_rows, p.distinct_keys, s.bytes, ''
506 FROM all_ind_partitions p, dba_segments s
507 WHERE s.owner=:1
508 AND p.index_owner=s.owner
509 AND p.partition_name=s.partition_name
510 AND s.segment_type='INDEX PARTITION'"""
511 }
512
513
514 perfSql = {
515 "db_id" : u"""select to_char(dbid) from v$database""",
516 "instance_num" : u"""select to_char(instance_number) from v$instance""",
517 "snapshots" : u"""select snap_id id, begin_interval_time time from dba_hist_snapshot where begin_interval_time > (sysdate - :1) order by snap_id desc""",
518 "addm_report_text" : u"""select dbms_advisor.get_task_report(:1, :2, :3) from sys.dual""",
519 "awr_report_html" : u"""select * from table(dbms_workload_repository.awr_report_html(:1, :2, :3, :4))""",
520 "awr_report_text" : u"""select * from table(dbms_workload_repository.awr_report_text(:1, :2, :3, :4))""",
521 "sqltune_text" : u"""select dbms_sqltune.report_tuning_task(:1, :2, :3) from sys.dual"""
522 }
523
524 durptSql = {
525 "nbTotalBlocks" : u"""SELECT SUM(a.blocks) FROM DBA_SEGMENTS a WHERE a.tablespace_name LIKE :1 AND a.owner LIKE :2""",
526 "tablesForTbsAndUser" : u"""SELECT a.owner "Owner", a.tablespace_name "Tablespace", a.segment_name "Table", DECODE(COUNT(a.partition_name), 0, '', '*') "Part?", COUNT(c.column_name) "#Cols", d.num_rows "#Rows", a.blocks "Size(blk)", ROUND(a.blocks*b.block_size/1024/1024, 1) "Size(Mo)", ROUND((100*a.blocks)/:1, 1) "Size(%)" FROM DBA_SEGMENTS a, DBA_TABLESPACES b, DBA_TAB_COLUMNS C, DBA_TABLES d WHERE a.tablespace_name LIKE :2 AND a.owner LIKE :3 AND a.tablespace_name=b.tablespace_name AND a.segment_name=c.table_name AND a.segment_name=d.table_name AND a.segment_name NOT LIKE '%PLAN_TABLE' AND a.segment_type LIKE 'TABLE%' AND NOT EXISTS (SELECT NULL FROM DBA_TABLES WHERE owner=a.owner AND temporary='Y' AND table_name=a.segment_name) GROUP BY a.owner, a.tablespace_name, a.segment_name, a.blocks, b.block_size, d.num_rows ORDER BY a.blocks DESC""",
527 "indexesForTbsAndUser" : u"""SELECT a.owner "Owner", a.tablespace_name "Tablespace", a.segment_name "Index", DECODE(COUNT(a.partition_name), 0, '', '*') "Part?", COUNT(c.blevel) "Level", c.distinct_keys "Keys", a.blocks "Size(blk)", ROUND(a.blocks*b.block_size/1024/1024, 1) "Size(Mo)", ROUND((100*a.blocks)/:1, 1) "Size(%)" FROM DBA_SEGMENTS a, DBA_TABLESPACES b, DBA_INDEXES c WHERE a.tablespace_name LIKE :2 AND a.owner LIKE :3 AND a.tablespace_name=b.tablespace_name AND a.segment_name=c.index_name AND a.segment_name NOT LIKE '%PLAN_TABLE' AND a.segment_type LIKE 'INDEX%' AND NOT EXISTS (SELECT NULL FROM DBA_TABLES WHERE owner=a.owner AND temporary='Y' AND table_name=a.segment_name) GROUP BY a.owner, a.tablespace_name, a.segment_name, a.blocks, b.block_size, c.blevel, c.distinct_keys ORDER BY a.blocks DESC"""
528 }
529