Skip to content
By using AWS re:Post, you agree to the AWS re:Post Terms of Use
AWS re:Postre:Post
About re:Post

AWS RDS for Oracle 19c - Data Files Size is much larger than the sum of individual tables in a schema.

0

Hi! I ran this query to determine the spread of the space that's allocated to different components of the Oracle database:

SELECT  
'===========================================================' || CHR(10) ||  
'Total Database Physical Size = ' || ROUND(redolog_size_gib + dbfiles_size_gib + tempfiles_size_gib + ctlfiles_size_gib, 2) || ' GiB' || CHR(10) ||  
'===========================================================' || CHR(10) ||  
' Redo Logs Size : ' || ROUND(redolog_size_gib, 3) || ' GiB' || CHR(10) ||  
' Data Files Size : ' || ROUND(dbfiles_size_gib, 3) || ' GiB' || CHR(10) ||  
' Temp Files Size : ' || ROUND(tempfiles_size_gib, 3) || ' GiB' || CHR(10) ||  
-- ' Archive Log Size : ' || ROUND(archlog_size_gib, 3) || ' GiB' || CHR(10) ||  
' Control Files Size : ' || ROUND(ctlfiles_size_gib, 3) || ' GiB' || CHR(10) ||  
'===========================================================' || CHR(10) ||  
' Used Database Size : ' || used_db_size_gib || ' GiB' || CHR(10) ||  
' Free Database Size : ' || free_db_size_gib || ' GiB' || CHR(10) ||  
' Data Pump Directory Size : ' || dpump_db_size_gib || ' GiB' || CHR(10) ||  
' BDUMP Directory Size : ' || bdump_db_size_gib || ' GiB' || CHR(10) ||  
' ADUMP Directory Size : ' || adump_db_size_gib || ' GiB' || CHR(10) ||  
'===========================================================' || CHR(10) ||  
'Total Size (including Dump and Log Files) = ' || ROUND(ROUND(redolog_size_gib, 2) + ROUND(dbfiles_size_gib, 2) + ROUND(tempfiles_size_gib, 2) + ROUND(ctlfiles_size_gib, 2) + ROUND(adump_db_size_gib, 2) + ROUND(dpump_db_size_gib, 2) + ROUND(bdump_db_size_gib, 2), 2) || ' GiB' || CHR(10) ||  
'===========================================================' AS summary  
FROM (SELECT sys_context('USERENV', 'DB_NAME')  
db_name,  
(SELECT SUM(bytes) / 1024 / 1024 / 1024 redo_size  
FROM (SELECT bytes FROM v$log UNION ALL SELECT bytes FROM v$standby_log))  
redolog_size_gib,  
(SELECT SUM(bytes) / 1024 / 1024 / 1024 data_size  
FROM dba_data_files)  
dbfiles_size_gib,  
(SELECT NVL(SUM(bytes), 0) / 1024 / 1024 / 1024 temp_size  
FROM dba_temp_files)  
tempfiles_size_gib,  
(SELECT SUM(block_size * file_size_blks) / 1024 / 1024 / 1024  
controlfile_size  
FROM v$controlfile)  
ctlfiles_size_gib,  
ROUND(SUM(used.bytes) / 1024 / 1024 / 1024, 3)  
db_size_gib,  
ROUND(SUM(used.bytes) / 1024 / 1024 / 1024, 3) - ROUND(  
free.f / 1024 / 1024 / 1024)  
used_db_size_gib,  
ROUND(free.f / 1024 / 1024 / 1024, 3)  
free_db_size_gib,  
(SELECT ROUND(SUM(filesize) / 1024 / 1024 / 1024, 3)  
FROM TABLE(rdsadmin.rds_file_util.listdir('BDUMP')))  
bdump_db_size_gib,  
(SELECT ROUND(SUM(filesize) / 1024 / 1024 / 1024, 3)  
FROM TABLE(rdsadmin.rds_file_util.listdir('ADUMP')))  
adump_db_size_gib,  
(SELECT ROUND(SUM(filesize) / 1024 / 1024 / 1024, 3)  
FROM TABLE(rdsadmin.rds_file_util.listdir('DATA_PUMP_DIR')))  
dpump_db_size_gib  
FROM (SELECT bytes  
FROM v$datafile  
UNION ALL  
SELECT bytes  
FROM v$tempfile) used,  
(SELECT SUM(bytes) AS f  
FROM dba_free_space) free  
GROUP BY free.f);

which returns:

===========================================================
Total Database Physical Size = 669.62 GiB
===========================================================
 Redo Logs Size : 5.5 GiB
 Data Files Size : 663.422 GiB
 Temp Files Size : .684 GiB
 Control Files Size : .013 GiB
===========================================================
 Used Database Size : 632.106 GiB
 Free Database Size : 32.082 GiB
 Data Pump Directory Size : 0 GiB
 BDUMP Directory Size : .014 GiB
 ADUMP Directory Size : .032 GiB
===========================================================
Total Size (including Dump and Log Files) = 669.65 GiB
===========================================================

However, when I run this query to see the size of all tables in a schema:

select     sum( bytes)/10241/10241/1024 GB
 from     dba_tables t
Inner join dba_segments s
  On table_name =segment_name and t.owner =s.owner
Where segment_type='TABLE'
And s.owner='SCHEMA_OWNER'

I get this number: 0.00308 GB

So, the questions are: What's eating space and what can I do to fix it?

Infinite thanks in advance, Mike

Topics
Database
Tags
Amazon Relational Database ServiceOracleDatabase
Language
English

asked a year ago347 views

2 Answers
0

There are several reasons why your data files size (663.422 GiB) is much larger than the sum of individual tables in your schema (0.00308 GiB):

  1. Other schemas: Your second query only checks tables in one specific schema ('SCHEMA_OWNER'), but your database likely contains many other schemas with their own tables.

  2. Other segment types: Your query only counts segments of type 'TABLE', but databases also contain indexes, LOBs (Large Objects), partitions, and other segment types that consume space.

  3. Unused allocated space: Oracle tablespaces can have space allocated but not yet used by segments. By default, Oracle tablespaces are created with auto-extend turned on and no maximum size, which can lead to excessive growth.

  4. Temporary tablespaces: Your database has temp files (0.684 GiB) that are used for sorting operations and other temporary storage needs.

To address the space issue:

  1. Identify large segments: Run queries against DBA_SEGMENTS to identify the largest segments across all schemas and segment types.

  2. Resize tablespaces: You can resize permanent tablespaces using the resize_datafile or autoextend_datafile procedures, and temporary tablespaces using resize_temp_tablespace, resize_tempfile, or autoextend_tempfile procedures.

  3. Set appropriate maximum sizes: Specify an appropriate maximum size for permanent and temporary tablespaces to prevent excessive growth.

  4. Consider hybrid partitioning: For large tables with historical data, you might want to implement hybrid partitioning to move older data to external storage like Amazon EFS.

  5. Monitor space usage: Regularly monitor your tablespace usage to prevent unexpected growth.

Note that in RDS for Oracle, the maximum size of a single file is limited to 16 TiB due to the ext4 filesystem used by the instance.
Sources
Resizing tablespaces, data files, and temp files - Amazon Relational Database Service
Optimize your database storage for Oracle workloads on AWS, Part 2: Using hybrid partitioning and ILM data movement policies | AWS Database Blog
RDS for Oracle limitations - Amazon Relational Database Service

answered a year ago

0

Hi Mike, The query you are using to find the space utilization in your RDS ORACLE instance mentioned in this re:Post basically uses view dba_data_files , dba_temp_files , v$controlfile and size occupied in the ADUMP, BDUMP, DATA_PUMP_DIR directory to calculate their size and give output as sum. However when you are using the query below :

select     sum( bytes)/10241/10241/1024 GB
 from     dba_tables t
Inner join dba_segments s
  On table_name =segment_name and t.owner =s.owner
Where segment_type='TABLE'
And s.owner='SCHEMA_OWNER'

You are only calculating the size of the tables which are owned by the 'SCHEMA_OWNER' , you need to consider all schemas in your database , further you are only considering to calculate size of TABLES only what about indexes?

A better approach would be that you query your dba_data_files view and dba_free_space to see the actual physical space consumed in the database. Sample query below :

select
"Reserved_Space(GB)", "Reserved_Space(GB)" - "Free_Space(GB)" "Used_Space(GB)",
"Free_Space(GB)"
from(
select 
(select sum(bytes/(1014*1024*1024)) from dba_data_files) "Reserved_Space(GB)",
(select sum(bytes/(1024*1024*1024)) from dba_free_space) "Free_Space(GB)"
from dual
);

From above output if the Reserved_Space is quite more than the used_space that indicates you have lot of sufficient space in the datafiles , you can then try to resize the tablespace below their high water mark using query like below :

alter tablespace <tablespace_name> resize <size in GB>;

If you get error while resizing then you will have to remove the fragmentation to bring the high water mark down and then retry the resize operation.

Regarding how to remove fragmentation please review this article

If you just want to know the space occupied by each user in your RDS Oracle instance then run below query and review :

select owner, sum(bytes)/1024/1024 Size_MB from dba_segments group  by owner;
AWS
SUPPORT ENGINEER

answered a year ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Relevant content