Datafiles :
select sum(bytes)/1048576 “DATAFILES_SIZE_MB” from dba_data_files;
Tempfiles:
select sum(bytes)/1048576 “TEMPFILES_SIZE_MB” from dba_temp_files;
Your redo logs can also use up a large amount of disk space – especially if your database has more than the minimum number of 2 redo log groups. (You may also have several members within each group).
Redologs:
select sum(bytes)/1048576 “REDOLOGS_SIZE_MB” from v$log;
The database obviously needs controlfiles to record information such as which datafiles belong to the database. If your CONTROL_FILE_RECORD_KEEP_TIME is set to a large value, then your controlfiles can become quite large.
Controlfiles:
select round(sum(block_size*file_size_blks)/1048576,2) “CONTROLFILESIZE_MB” from v$controlfile;
From 10g onwards, flashback database is not enabled by default, but if it is, then this area can grow rapidly over time.
Flash Recovery Area:
select * from v$recovery_file_dest;
select * from v$flash_recovery_area_usage;
These views will show sizing details and free space available.
Note: If your backups are held outside of the flash recovery area, then you’ll also need to allow space for these. This will depend on your backup strategy and backup retention policy. (Export/datapump export dumpfiles also need to be planned for).
If you are using RMAN incremental backups and have block change tracking enabled, then include this file:
Block change tracking file
select filename, nvl(bytes/1048576,0) “BLOCK_CT_SIZE_MB” from v$block_change_tracking;
Files referenced by database directories or the utl_file_dir parameter:
Your application may read from, or write to external files via database directories or the utl_file_dir parameter.
Other examples of using external directories are for
(a) External tables –
select a.owner||’.’||a.table_name||’ stored in directory ‘||b.directory_path “EXTERNAL_TABLES”
from dba_external_locations a, dba_directories b
where a.directory_owner=b.owner
and a.directory_name=b.directory_name;
(b) If you are storing multiple versions of the same tablespace within a file group repository. (i.e. tablespace versioning).
select a.tablespace_name, a.version, a.file_group_owner, a.file_group_name,
b.file_name, b.file_directory
from dba_file_group_tablespaces a, dba_file_group_files b
where a.file_group_owner=b.file_group_owner
and a.file_group_name=b.file_group_name;
Thank you
osama Mustafa
Technology Geek 