LLI Wiki — My Digital Knowledge Base

Oracle: database size, size per schema and row counts

DBA_DATA_FILES, DBA_SEGMENTS and ALL_TABLES queries to measure an Oracle database, its schemas and the rows per table.

These queries report the size of an Oracle database, the space used per schema, and the number of rows per table. They read the DBA_ views, so run them with a DBA account.

Size of the datafiles

The space allocated to datafiles, in GB:

SELECT SUM(bytes) / 1024 / 1024 / 1024 AS GB FROM dba_data_files;

Space used by data

The space occupied by segments (tables, indexes, LOBs and so on), in GB:

SELECT SUM(bytes) / 1024 / 1024 / 1024 AS GB FROM dba_segments;

Total database size

The total size is the sum of the datafiles, tempfiles, redo logs and control files:

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;

The following query gives the database size, used space and free space in one result:

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;

Shell wrapper for every instance on the host

This script finds the running instances from the pmon processes, sets the environment with oraenv, and runs the query above for each one:

#!/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

Size per schema

Total size of each schema (owner), in GB:

SELECT owner, SUM(bytes) / 1024 / 1024 / 1024 AS "Size in GB"
FROM dba_segments
GROUP BY owner
ORDER BY 2 DESC;

Size of one schema by segment type, in MB:

SELECT SUM(bytes) / 1024 / 1024 AS size_in_mega, segment_type
FROM dba_segments
WHERE owner = UPPER('&schema_name')
GROUP BY segment_type;

Number of rows per table

The row counts stored in the data dictionary for the tables of a schema:

SELECT table_name, num_rows
FROM all_tables
WHERE owner = 'DWH_SCHEMA'
ORDER BY num_rows ASC;

Notes

Mastodon LLIMastodon SocialFosstodon