오라클 테이블 별 용량 확인 SQL 쿼리

테이블 별 용량을 확인하는 SQL 쿼리는 사용하는 데이터베이스에 따라 약간씩 다릅니다. 아래는 주요 데이터베이스에 대한 쿼리 예시입니다.

1. Oracle

SELECT 
    segment_name AS table_name,
    segment_type,
    bytes / 1024 / 1024 AS size_in_mb
FROM 
    user_segments
WHERE 
    segment_type = 'TABLE'
ORDER BY 
    size_in_mb DESC;

 

2. MySQL
 • MySQL에서는 information_schema.tables를 이용합니다

SELECT 
    table_schema AS database_name,
    table_name,
    ROUND(data_length / 1024 / 1024, 2) AS data_size_in_mb,
    ROUND(index_length / 1024 / 1024, 2) AS index_size_in_mb,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_size_in_mb
FROM 
    information_schema.tables
WHERE 
    table_schema = 'your_database_name'
ORDER BY 
    total_size_in_mb DESC;

 

3. PostgreSQL

SELECT 
    schemaname AS schema_name,
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    pg_size_pretty(pg_relation_size(relid)) AS table_size,
    pg_size_pretty(pg_indexes_size(relid)) AS index_size
FROM 
    pg_catalog.pg_statio_user_tables
ORDER BY 
    pg_total_relation_size(relid) DESC;

 

4. SQL Server

SELECT 
    t.name AS table_name,
    s.name AS schema_name,
    p.rows AS row_count,
    (a.total_pages * 8) / 1024 AS total_size_mb,
    (a.used_pages * 8) / 1024 AS used_size_mb,
    (a.data_pages * 8) / 1024 AS data_size_mb
FROM 
    sys.tables t
INNER JOIN 
    sys.indexes i ON t.object_id = i.object_id AND i.index_id <= 1
INNER JOIN 
    sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
INNER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
ORDER BY 
    total_size_mb DESC;

5. MariaDB

(MySQL과 동일)

SELECT 
    table_schema AS database_name,
    table_name,
    ROUND(data_length / 1024 / 1024, 2) AS data_size_in_mb,
    ROUND(index_length / 1024 / 1024, 2) AS index_size_in_mb,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_size_in_mb
FROM 
    information_schema.tables
WHERE 
    table_schema = 'your_database_name'
ORDER BY 
    total_size_in_mb DESC;