In Oracle database administration, how can you resize an existing data file in a tablespace?

Introduction / Context:
Resizing a data file is a very common administrative task in Oracle database administration. As application data grows, the space inside a tablespace might become full or under used, and the Oracle Database Administrator needs a safe way to increase or sometimes decrease the physical size of a data file on disk. This question checks whether you understand the correct Data Definition Language (DDL) command used to resize an existing data file, rather than creating or dropping objects in an unsafe or indirect way.

Given Data / Assumptions:

• We are working with an existing Oracle database installation.
• A tablespace already contains at least one physical data file on disk.
• The administrator wants to change the size of that specific data file.
• No requirement is mentioned to drop or recreate the tablespace.

Concept / Approach:
In Oracle, structural changes to data files and tablespaces are usually performed with ALTER DATABASE or ALTER TABLESPACE statements. To resize a data file, Oracle provides the RESIZE clause. The typical syntax is: ALTER DATABASE DATAFILE 'file_name' RESIZE new_size. This DDL command directly adjusts the physical size of the specified data file at the operating system level, provided that there is free or unused space at the end of the file for shrinking operations or sufficient free disk space for growth. The change does not require dropping the tablespace, and it does not create a new data file. It simply modifies the size of the existing one.

Step-by-Step Solution:
Step 1: Identify the data file that needs resizing by querying DBA_DATA_FILES or using data dictionary views. Step 2: Decide whether the file needs to grow or shrink and determine the target size, for example 500M or 2G. Step 3: Use the correct DDL command: ALTER DATABASE DATAFILE 'file_name' RESIZE new_size. Step 4: Execute the command as a user with appropriate administrative privileges, typically SYSDBA. Step 5: Verify the new size in DBA_DATA_FILES or other dictionary views to confirm that the resize operation completed successfully.

Verification / Alternative check:
After running the ALTER DATABASE DATAFILE command, the administrator can verify the effect by querying DBA_DATA_FILES, checking the BYTES column for that file. Operating system utilities can also confirm the new file size on disk. If the database returns an error such as ORA 03297, it usually means that the requested shrink size is too small because there are used extents near the end of the file. In that case, the administrator must choose a larger target size or relocate data segments first.

Why Other Options Are Wrong:
Option B is wrong because dropping the entire tablespace, even with INCLUDING CONTENTS, is a destructive action and does not represent a safe way to resize a data file. Option C is incorrect because CREATE DATAFILE creates a new data file instead of resizing an existing one. Option D is unrelated because switching the redo log file has nothing to do with data file size management. Option E is wrong because granting UNLIMITED TABLESPACE affects user quotas, not the physical size of any data file.

Common Pitfalls:
A common mistake is confusing resizing a data file with adding a completely new data file. Another frequent confusion is thinking that user quotas, roles, or privileges can change physical storage structures directly, which they cannot. Some administrators also attempt to shrink data files to very small sizes without understanding that extents near the file end prevent shrinking below certain limits. It is important to monitor tablespace usage, segment locations, and consider automatic extension settings, rather than issuing resize commands without analysis.

Final Answer:
The correct way is to use the DDL statement ALTER DATABASE DATAFILE file_name RESIZE new_size to change the size of the existing data file in the tablespace.