Oracle : taille de la base, par schéma et nombre de lignes
Requêtes DBA_DATA_FILES, DBA_SEGMENTS et ALL_TABLES pour mesurer la taille d'une base Oracle, de ses schémas et compter les lignes des tables.
Ces requêtes donnent la taille d'une base Oracle, l'espace utilisé par schéma et le nombre de lignes par table. Elles lisent les vues DBA_ : exécutez-les avec un compte DBA.
Taille des datafiles
L'espace alloué aux datafiles, en Go :
SELECT SUM(bytes) / 1024 / 1024 / 1024 AS GB FROM dba_data_files;
Espace utilisé par les données
L'espace occupé par les segments (tables, index, LOB, etc.), en Go :
SELECT SUM(bytes) / 1024 / 1024 / 1024 AS GB FROM dba_segments;
Taille totale de la base
La taille totale est la somme des datafiles, des tempfiles, des redo logs et des fichiers de contrôle :
SELECT
(SELECT SUM(bytes) / 1024 / 1024 / 1024 FROM dba_data_files) +
(SELECT NVL(SUM(bytes), 0) / 1024 / 1024 / 1024 FROM dba_temp_files) +
(SELECT SUM(bytes) / 1024 / 1024 / 1024 FROM sys.v_$log) +
(SELECT SUM(block_size * file_size_blks) / 1024 / 1024 / 1024 FROM v$controlfile) AS "Size in GB"
FROM dual;
La requête suivante donne en un seul résultat la taille de la base, l'espace utilisé et l'espace libre :
COL "Database Size" FORMAT a20
COL "Free space" FORMAT a20
COL "Used space" FORMAT a20
SELECT
ROUND(SUM(used.bytes) / 1024 / 1024 / 1024) || ' GB' "Database Size",
ROUND(SUM(used.bytes) / 1024 / 1024 / 1024) - ROUND(free.p / 1024 / 1024 / 1024) || ' GB' "Used space",
ROUND(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
FROM (SELECT bytes FROM v$datafile
UNION ALL
SELECT bytes FROM v$tempfile
UNION ALL
SELECT bytes FROM v$log) used,
(SELECT SUM(bytes) AS p FROM dba_free_space) free
GROUP BY free.p;
Script shell pour toutes les instances du serveur
Ce script détecte les instances en cours à partir des processus pmon, positionne l'environnement avec oraenv et exécute la requête ci-dessus pour chacune :
#!/bin/bash
for I in $(ps -ef | egrep "(asm|ora)_pmon_" | awk '{print $NF}' | sed 's/.*pmon_//')
do
DB=$(echo ${I} | sed 's/[1-9]$//')
. oraenv <<< ${I} > /dev/null 2>&1
export PATH=$PATH:/usr/local/bin:$ORACLE_HOME/bin
DPATH=$(sqlplus -s "/ as sysdba" <<'EOF'
COL "Database Size" FORMAT a20
COL "Free space" FORMAT a20
COL "Used space" FORMAT a20
SELECT ROUND(SUM(used.bytes) / 1024 / 1024 / 1024) || ' GB' "Database Size",
ROUND(SUM(used.bytes) / 1024 / 1024 / 1024) - ROUND(free.p / 1024 / 1024 / 1024) || ' GB' "Used space",
ROUND(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
FROM (SELECT bytes FROM v$datafile
UNION ALL
SELECT bytes FROM v$tempfile
UNION ALL
SELECT bytes FROM v$log) used,
(SELECT SUM(bytes) AS p FROM dba_free_space) free
GROUP BY free.p;
EOF
)
echo "$DPATH"
done
Taille par schéma
Taille totale de chaque schéma (propriétaire), en Go :
SELECT owner, SUM(bytes) / 1024 / 1024 / 1024 AS "Size in GB"
FROM dba_segments
GROUP BY owner
ORDER BY 2 DESC;
Taille d'un schéma par type de segment, en Mo :
SELECT SUM(bytes) / 1024 / 1024 AS size_in_mega, segment_type
FROM dba_segments
WHERE owner = UPPER('&schema_name')
GROUP BY segment_type;
Nombre de lignes par table
Les nombres de lignes stockés dans le dictionnaire de données pour les tables d'un schéma :
SELECT table_name, num_rows
FROM all_tables
WHERE owner = 'DWH_SCHEMA'
ORDER BY num_rows ASC;
Remarques
NUM_ROWSprovient des statistiques de l'optimiseur. C'est une estimation, qui peut être obsolète ou vide tant que les statistiques de la table ne sont pas collectées. Pour un comptage exact, exécutezSELECT COUNT(*)sur la table.- Les valeurs de
dba_segmentsincluent tous les segments du schéma, y compris les index et les segments LOB ; elles sont donc supérieures aux seules données des tables. - Pour aller plus loin : Oracle : get record counts for all tables (en anglais).