Comment calculer la taille des tables dans Oracle

vous pourriez être intéressé par cette requête. Il vous indique combien d'espace est alloué pour chaque table en tenant compte des index et des LOBs sur la table. Souvent, vous êtes intéressé à savoir "combien d'espaces la table de commande prendre, y compris les index" plutôt que juste la table elle-même. Vous pouvez toujours plonger dans les détails. Notez que cela nécessite l'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,
   ROUND( ratio_to_report( sum(bytes) ) over () * 100) Percent
FROM
(SELECT segment_name table_name, owner, bytes
 FROM dba_segments
 WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
 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 IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
 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 IN ('LOBSEGMENT', 'LOB PARTITION')
 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
;

-- 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 par les statistiques recueillies:

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

d'Abord, je voudrais généralement attention que la collecte de statistiques de la table afin de faire analyse de l'espace est potentiellement dangereux. Rassembler des statistiques peut changer les plans de requête, en particulier si le DBA a configuré une tâche de collecte de statistiques qui utilise des paramètres non-par défaut que votre appel n'utilise pas, et va amener Oracle à ré-analyser les requêtes qui utilisent la table en question qui peut être un succès de performance. Si le DBA a intentionnellement laissé certaines tables sans statistiques (commun si votre OPTIMIZER_MODE est choisir), la collecte de statistiques peut amener Oracle à arrêter d'utiliser l'optimiseur basé sur les règles et de commencer à utiliser l'optimiseur basé sur les coûts pour un ensemble de requêtes qui peuvent être un casse-tête de performance majeure si elle est faite de manière inattendue dans la production. Si vos statistiques sont exactes, Vous pouvez interroger 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 Je ne veux pas perturber le statu quo.

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

tout d'abord, rassembler des statistiques d'optimisation sur la table (si vous ne l'avez pas déjà fait):

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

avertissement: comme Justin le dit dans sa réponse, la collecte des statistiques d'optimisation affecte l'optimisation de la requête et ne devrait pas être faite sans le soin et la considération nécessaires !

puis trouver 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 les blocs alloués à la table sont les blocs + empty_blocks + num_freelist_blocks.

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

multiplier le nombre de blocs par la taille des blocs utilisés (habituellement 8KO) pour obtenir l'espace consommé - par exemple 17 blocs x 8KO = 136KO.

pour faire ceci pour toutes les tables dans un schéma à la fois:

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 AskTom

pour les tables et Index sous-partitionnés nous pouvons utiliser la requête suivante

    SELECT owner, table_name, ROUND(sum(bytes)/1024/1024/1024, 2) GB
    FROM
    (SELECT segment_name table_name, owner, bytes
     FROM dba_segments
     WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
     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 IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
     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
    ;

j'ai modifié la requête de WW pour fournir des informations plus détaillées:

SELECT * FROM (
  SELECT
    owner, object_name, object_type, table_name, ROUND(bytes)/1024/1024 AS meg,
    tablespace_name, extents, initial_extent,
    ROUND(Sum(bytes/1024/1024) OVER (PARTITION BY table_name)) AS total_table_meg
  FROM (
    -- Tables
    SELECT owner, segment_name AS object_name, 'TABLE' AS object_type,
          segment_name AS table_name, bytes,
          tablespace_name, extents, initial_extent
    FROM   dba_segments
    WHERE  segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
    UNION ALL
    -- Indexes
    SELECT i.owner, i.index_name AS object_name, 'INDEX' AS object_type,
          i.table_name, s.bytes,
          s.tablespace_name, s.extents, s.initial_extent
    FROM   dba_indexes i, dba_segments s
    WHERE  s.segment_name = i.index_name
    AND    s.owner = i.owner
    AND    s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
    -- LOB Segments
    UNION ALL
    SELECT l.owner, l.column_name AS object_name, 'LOB_COLUMN' AS object_type,
          l.table_name, s.bytes,
          s.tablespace_name, s.extents, s.initial_extent
    FROM   dba_lobs l, dba_segments s
    WHERE  s.segment_name = l.segment_name
    AND    s.owner = l.owner
    AND    s.segment_type = 'LOBSEGMENT'
    -- LOB Indexes
    UNION ALL
    SELECT l.owner, l.column_name AS object_name, 'LOB_INDEX' AS object_type,
          l.table_name, s.bytes,
          s.tablespace_name, s.extents, s.initial_extent
    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 = UPPER('&owner')
)
WHERE total_table_meg > 10
ORDER BY total_table_meg DESC, meg DESC
/

IIRC les tables dont vous avez besoin sont DBA_TABLES, DBA_EXTENS ou DBA_SEGMENTS et DBA_DATA_FILES. Il y a aussi USER_ et ALL_ versions de ceux-ci pour les tables que vous pouvez voir si vous n'avez pas les permissions d'administration sur la machine.

select segment_name,segment_type,bytes/1024/1024 MB
from dba_segments
where segment_name='TABLENAME' and owner ='OWNERNAME' order by mb desc;

Heres une variante de la WWs réponse, il comprend des partitions et des sous-partitions comme les autres ci-dessus ont suggéré, en plus d'une colonne qui affiche le TYPE: de Table/Index MÉTIER/etc

SELECT
   owner, "Type", table_name "Name", TRUNC(sum(bytes)/1024/1024) Meg
FROM
(  SELECT segment_name table_name, owner, bytes, 'Table' as "Type"
   FROM dba_segments
   WHERE segment_type in  ('TABLE','TABLE PARTITION','TABLE SUBPARTITION')
 UNION ALL
   SELECT i.table_name, i.owner, s.bytes, 'Index' as "Type"
   FROM dba_indexes i, dba_segments s
   WHERE s.segment_name = i.index_name
   AND   s.owner = i.owner
   AND   s.segment_type in ('INDEX','INDEX PARTITION','INDEX SUBPARTITION')
 UNION ALL
   SELECT l.table_name, l.owner, s.bytes, 'LOB' as "Type"
   FROM dba_lobs l, dba_segments s
   WHERE s.segment_name = l.segment_name
   AND   s.owner = l.owner
   AND   s.segment_type IN ('LOBSEGMENT','LOB PARTITION','LOB SUBPARTITION')
 UNION ALL
   SELECT l.table_name, l.owner, s.bytes, 'LOB Index' as "Type"
   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, "Type"
HAVING SUM(bytes)/1024/1024 > 10  /* Ignore really small tables */
ORDER BY SUM(bytes) desc;

dépend de ce que vous entendez par"taille de la table". Un tableau ne se rapporte pas à un fichier spécifique du système de fichiers. Une table résidera sur une tablespace (éventuellement plusieurs tablespaces si elle est partitionnée, et éventuellement plusieurs tablespaces si vous voulez également prendre en compte les index sur la table). Un tablespace contient souvent plusieurs tables et peut être réparti sur plusieurs fichiers.

si vous estimez combien d'espace vous aurez besoin pour l'avenir de la table la croissance, alors avg_row_len multiplié par le nombre de lignes dans la table (ou le nombre de lignes dans la table) sera un bon guide. Mais Oracle laissera un peu d'espace libre sur chaque bloc, en partie pour permettre aux lignes de "croître" si elles sont mises à jour, en partie parce qu'il ne peut pas être possible d'adapter une autre ligne entière sur ce bloc (par exemple un bloc de 8K ne conviendrait que pour 2 lignes de 3K, bien que ce serait un exemple extrême car 3K est beaucoup plus grand que la plupart des tailles de ligne). Donc les blocs (dans USER_TABLES) pourraient être un meilleur guide.

mais si vous aviez 200.000 lignes dans une table, supprimé la moitié d'entre eux, alors la table serait encore "posséder" le même nombre de blocs. Il ne les libère pas pour d'autres tables à utiliser. En outre, les blocs ne sont pas ajoutés à une table individuellement, mais dans les groupes appelés une "étendue". Donc il y aura généralement des EMPTY_BLOCKS (aussi dans USER_TABLES) dans une table.

Correction pour tableaux partitionnés:

SELECT owner, table_name, ROUND(sum(bytes)/1024/1024/1024, 2) GB
FROM
(SELECT segment_name table_name, owner, bytes
 FROM dba_segments
 WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
 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 IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
 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 in ('LOBSEGMENT', 'LOB PARTITION', 'LOB SUBPARTITION')
 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
;

simple select qui renvoie les tailles brutes des tables, basé sur la taille de bloc, comprend également la taille avec index

sélectionnez table_name, (nvl (( sélectionnez sum (blocs)) de dba_indexes a,b dba_segments où A. index_name=B. nom du segment et un.table_name=dba_tables.table_name ),0)+m)*8192/1024 TotalSize,blocs*8 taille_table de dba_tables commande au 3

j'ai trouvé cela un peu plus précis:

SELECT
   owner, table_name, TRUNC(sum(bytes)/1024/1024/1024) GB
FROM
(SELECT segment_name table_name, owner, bytes
FROM dba_segments
WHERE segment_type in  ('TABLE','TABLE PARTITION')
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 in ('INDEX','INDEX PARTITION')
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 IN ('LOBSEGMENT','LOB PARTITION')
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

select segment_name as tablename, sum(bytes/ (1024 * 1024 * 1024)) as tablesize_in_GB
From dba_segments /* if looking at tables not owned by you else use user_segments */
where segment_name = 'TABLE_WHOSE_SIZE_I_WANT_TO_KNOW'
and   OWNER = 'WHO OWNS THAT TABLE' /* if user_segments is used delete this line */ 
group by segment_name ;

j'ai modifié la requête pour obtenir la taille du schéma par tablespace ..

SELECT owner,
     tablespace_name,
     TRUNC (SUM (bytes) / 1024 / 1024)   Meg,
     ROUND (ratio_to_report (SUM (bytes)) OVER () * 100) Percent
FROM (SELECT tablespace_name, owner, bytes
        FROM dba_segments
       WHERE segment_type IN
                 ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
      UNION ALL
      SELECT i.tablespace_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 IN
                     ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
      UNION ALL
      SELECT l.tablespace_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 IN ('LOBSEGMENT', 'LOB PARTITION')
      UNION ALL
      SELECT l.tablespace_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 owner, tablespace_name
--HAVING SUM(bytes)/1024/1024 > 10  /* Ignore really small tables */
ORDER BY tablespace_name -- desc
;

Il ya une option de plus qui permet d'obtenir la taille "select" avec joins, et la taille de la table comme option trop

-- 1
EXPLAIN PLAN
   FOR
      SELECT
            Scheme.Table_name.table_column1 AS "column1",
            Scheme.Table_name.table_column2 AS "column2",
            Scheme.Table_name.table_column3 AS "column3",
            FROM Scheme.Table_name
       WHERE ;

SELECT * FROM TABLE (DBMS_XPLAN.display);

j'ai la même variante que les derniers qui calcule segments de données de table, index de table et blob-champs:

CREATE OR REPLACE FUNCTION
  SYS.RAZMER_TABLICY_RAW(pNazvanie in varchar, pOwner in varchar2)
return number
is
  val number(16);
  sz number(16);
begin
  sz := 0;

  --Calculate size of table data segments
  select
    sum(t.bytes) into val
  from
    sys.dba_segments t
  where
    t.segment_name = upper(pNazvanie)
  and
    t.owner = upper(pOwner);
  sz := sz + nvl(val,0);

  --Calculate size of table indexes segments
  select
    sum(s.bytes) into val
  from
    all_indexes t
  inner join
    dba_segments s
  on
    t.index_name = s.segment_name
  where
    t.table_name = upper(pNazvanie)
  and
    t.owner = upper(pOwner);
  sz := sz + nvl(val,0);

  --Calculate size of table blob segments
  select
    sum(s.bytes) into val
  from
    all_lobs t
  inner join
    dba_segments s on t.segment_name = s.segment_name
  where
    t.table_name = upper(pNazvanie)
  and
    t.owner = upper(pOwner);
  sz := sz + nvl(val,0);

  return sz;

end razmer_tablicy_raw;

Source .