Data file and Log File

Data file: Datafiles are physical files of the operating system that store the data of all logical structures in the database. They must be explicitly created for each tablespace. Oracle assigns each datafile two associated file numbers, an absolute file number and a relative file number, that are used to uniquely identify it.

In Oracle, you can check the internal free space in a data file and log file by querying specific data dictionary views. Here are the steps to check the internal free space in Oracle:

Checking Internal Free Space in Data File:

Query the DBA_FREE_SPACE view to get information about free space in data files.

select file_name, bytes/1024/1024 from dba_data_files ;

This query retrieves information about free space in data files. It displays the
file name, tablespace name, and free space in megabytes. Adjust the query to
include additional columns or conditions as needed.


Log  File:  Redo log files are operating system files used by Oracle to maintain logs of
 all transactions performed against the database. Theprimary purpose of these log
 files is to allow Oracle.to recover changes made to the database in the case of a failure.
Checking Internal Free Space inLog File:
Query the V$LOGFILE view to get information about the log files.
SELECT
a.GROUP#,
a.THREAD#,
a.SEQUENCE#,
a.ARCHIVED,
a.STATUS,
b.MEMBER AS FILE_NAME,
(a.BYTES/1024/1024) AS SIZE_MB
FROM v$log a
JOIN v$logfile b ON a.Group#=b.Group#
ORDER BY a.GROUP#;

This query retrieves information about the log files. It displays the log file group
number, member (file name), and log file size in megabytes. The free space in a
log file is typically managed automatically by Oracle, and you might not see
explicit "free space" in the same way you would for a data file.
Instead, Oracle manages space within the log files to accommodate ongoing
transactions.
Note:It's important to have the necessary privileges to query the DBA views or V$
views. You might need to connect as a user with DBA privileges or request the
required permissions from your database administrator.
Shrink Data file and LogFile
In Oracle, the process of shrinking data files and log files is different compared
to SQL Server. Oracle does not have a direct equivalent to the
"shrink" operation as it exists in SQL Server. However, you can
reclaim unused space and resize data files in Oracle. Below are steps to
achieve this:
Shrink Data File:
Check for unused space within the data file by querying the dba_free_space view.
select file_name, bytes/1024/1024 from dba_data_files ;

Resize Data File:
If there is unused space and you want to shrink the
data file, you can resize it using the ALTER DATABASE statement.
ALTER DATABASE DATAFILE'/u02/oradata/CLOTADB/clota_data.dbf'
RESIZE 200M;


Replace 'full_path_to_data_file' with the actual
path to your data file, and new_size_in_megabytes with the desired new size. Be
cautious about setting the size too small, as it may impact the database's
ability to accommodate future data growth.
Shrink Log File:
Oracle automatically manages the size of the online
redo log files, and there is no direct equivalent to "shrinking" log
files. Oracle will reuse and overwrite space in the online redo log files as
needed.
However, if you need to resize the redo log files,
you can follow these steps:
Check Redo Log Information:
Query the v$log view to get information about the
redo log files.
SELECT group#, member, bytes / (1024 * 1024) AS log_file_size_mb
FROM v$log;
Switch Logs:
Perform a log switch to force the database to start
using a new redo log file.
ALTER SYSTEM SWITCH LOGFILE;
Resize Redo Log File:
After switching logs, you can resize the inactive
redo log file using the ALTER DATABASE statement.
ALTER DATABASE DROP LOGFILE 'full_path_to_log_file';
Then, add a new redo log file with the desired size.
ALTER DATABASE ADD LOGFILE 'full_path_to_new_log_file' SIZE 
new_size_in_megabytes;
Replace 'full_path_to_log_file' and
'full_path_to_new_log_file' with the actual paths to the old and new log files,
respectively. Also, set new_size_in_megabytes to the desired size.
Remember to carefully plan and test any changes to file sizes,
especially in a production environment, to avoid potential issues related to
database performance and space management. Always perform these operations
during periods of low database activity and with proper backups in place