Comment calculer la taille des tables dans Oracle

Question

Étant habitué à (et potentiellement gâté par) MSSQL, je me demande comment je peux obtenir la taille des tables dans Oracle 10g. J'ai fait une recherche sur Google et je suis maintenant conscient que je n'ai peut-être pas une option aussi facile que sp_spaceused. Pourtant, les réponses potentielles que j'ai obtenues sont la plupart du temps dépassées ou ne fonctionnent pas. Probablement parce que je ne suis pas un DBA pour le schéma avec lequel je travaille.

Quelqu'un aurait-il des solutions ou des recommandations ?

Answer 1

Vous pourriez être intéressé par cette requête. Elle vous indique combien d'espace est alloué à chaque table en tenant compte des index et des LOBs de la table. Souvent, ce qui vous intéresse, c'est de savoir "Combien d'espace prend la table Bon de commande, y compris les index" plutôt que la table elle-même. Vous pouvez toujours vous plonger dans les détails. Notez que cela nécessite un accès aux vues DBA_*.

COLUMN TABLE_NAME FORMAT A32
COLUMN OBJECT_NAME FORMAT A32
COLUMN OWNER FORMAT A10

SELECT
   owner, table_name, TRUNC(sum(bytes)/1024/1024) Meg
FROM
(SELECT segment_name table_name, owner, bytes
 FROM dba_segments
 WHERE segment_type = 'TABLE'
 UNION ALL
 SELECT i.table_name, i.owner, s.bytes
 FROM dba_indexes i, dba_segments s
 WHERE s.segment_name = i.index_name
 AND   s.owner = i.owner
 AND   s.segment_type = 'INDEX'
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.segment_name
 AND   s.owner = l.owner
 AND   s.segment_type = 'LOBSEGMENT'
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.index_name
 AND   s.owner = l.owner
 AND   s.segment_type = 'LOBINDEX')
WHERE owner in UPPER('&owner')
GROUP BY table_name, owner
HAVING SUM(bytes)/1024/1024 > 10  /* Ignore really small tables */
ORDER BY SUM(bytes) desc
;

Answer 2

-- Tables + Size MB
select owner, table_name, round((num_rows*avg_row_len)/(1024*1024)) MB 
from all_tables 
where owner not like 'SYS%'  -- Exclude system tables.
and num_rows > 0  -- Ignore empty Tables.
order by MB desc -- Biggest first.
;

--Tables + Rows
select owner, table_name, num_rows
 from all_tables 
where owner not like 'SYS%'  -- Exclude system tables.
and num_rows > 0  -- Ignore empty Tables.
order by num_rows desc -- Biggest first.
;

Note : Il s'agit d'estimations, rendues plus précises grâce aux statistiques recueillies :

exec dbms_utility.analyze_schema(user,'COMPUTE');

Answer 3

Tout d'abord, je vous mets en garde contre le fait que la collecte de statistiques sur les tableaux dans le but d'effectuer une analyse spatiale est une chose potentiellement dangereuse. La collecte de statistiques peut modifier les plans de requête, en particulier si le DBA a configuré un travail de collecte de statistiques qui utilise des paramètres par défaut que votre appel n'utilise pas, et fera en sorte qu'Oracle reparse les requêtes qui utilisent la table en question, ce qui peut avoir un impact sur les performances. Si le DBA a intentionnellement laissé certaines tables sans statistiques (ce qui est courant si votre OPTIMIZER_MODE est CHOOSE), la collecte de statistiques peut amener Oracle à cesser d'utiliser l'optimiseur basé sur les règles et à commencer à utiliser l'optimiseur basé sur les coûts pour un ensemble de requêtes, ce qui peut constituer un problème de performance majeur si cela se produit de manière inattendue en production. Si vos statistiques sont exactes, vous pouvez effectuer les requêtes suivantes USER_TABLES (ou ALL_TABLES ou DBA_TABLES ) directement sans appeler GATHER_TABLE_STATS . Si vos statistiques ne sont pas exactes, il y a probablement une raison à cela et vous ne voulez pas perturber le statu quo.

Deuxièmement, l'équivalent le plus proche du serveur SQL sp_spaceused est probablement la procédure d'Oracle DBMS_SPACE paquet. Tom Kyte a une belle show_space procédure qui fournit une interface simple à ce paquet et imprime des informations similaires à celles de l'application sp_spaceused s'imprime.

Answer 4

Tout d'abord, rassemblez les statistiques de l'optimiseur sur le tableau (si ce n'est pas déjà fait) :

begin
   dbms_stats.gather_table_stats('MYSCHEMA','MYTABLE');
end;
/

AVERTISSEMENT : Comme le dit Justin dans sa réponse, la collecte des statistiques de l'optimiseur affecte l'optimisation des requêtes et ne doit pas être effectuée sans précaution et considération. !

Trouvez ensuite le nombre de blocs occupés par la table à partir des statistiques générées :

select blocks, empty_blocks, num_freelist_blocks
from   all_tables
where  owner = 'MYSCHEMA'
and    table_name = 'MYTABLE';

• Le nombre total de blocs alloués à la table est blocs + empty_blocks + num_freelist_blocks.

• blocks est le nombre de blocs qui contiennent réellement des données.

Multipliez le nombre de blocs par la taille du bloc utilisé (généralement 8KB) pour obtenir l'espace consommé - par exemple, 17 blocs x 8KB = 136KB.

Pour faire cela pour toutes les tables d'un schéma en même temps :

begin
    dbms_stats.gather_schema_stats ('MYSCHEMA');
end;
/

select table_name, blocks, empty_blocks, num_freelist_blocks
from   user_tables;

Note : modifications apportées à ce qui précède après lecture ce fil de discussion AskTom

Answer 5

Je crois que les tables dont vous avez besoin sont DBA_TABLES, DBA_EXTENTS ou DBA_SEGMENTS et DBA_DATA_FILES. Il existe également des versions USER_ et ALL_ de ces tables que vous pouvez consulter si vous n'avez pas les droits d'administration sur la machine.