Taille des Indexes et nom de la table

Version imprimable

Bonjour,

Je viens de faire une requete :

Code:

1
2
3
4
 
SELECT segment_name "INDEX" , SUM(BYTES) /1024/1024 || 'Mo' "Taille", blocks "Blocs" ,tablespace_name "TABLESPACE"
FROM   dba_segments WHERE  segment_type = 'INDEX' AND OWNER = 'toto' AND SEGMENT_NAME in (select INDEX_NAME from dba_indexes where TABLE_OWNER='toto' AND TABLE_NAME='tutu')
GROUP BY SEGMENT_NAME , tablespace_name, blocks;

je voudrai rajouter une colonne pour mettre le nom de la table correspondante, l'equivalent de :

Code:

1
2
 
select INDEX_NAME,TABLE_NAME from dba_indexes where TABLE_OWNER='toto' AND TABLE_NAME='tutu'

une idée ?

13/01/2010, 15h34
Waldar

Au lieu de faire un IN, faites une jointure !

Merci pour votre Réponse , j'ai trouvé :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
SELECT dba_indexes.table_name "TABLE", dba_segments.segment_name "INDEX",
SUM (BYTES) / 1024 / 1024 || 'Mo' "Taille",
dba_segments.blocks "Blocs",
dba_segments.tablespace_name "TABLESPACE"
FROM dba_segments, dba_indexes
WHERE segment_type = 'INDEX'
AND dba_segments.owner = :nom_owner
AND segment_name = dba_indexes.index_name
AND table_owner = dba_segments.owner
AND dba_indexes.table_name = :nom_table
GROUP BY dba_indexes.table_name,
dba_segments.segment_name,
dba_segments.tablespace_name,
dba_segments.blocks;