In an Oracle style database, how do you add a new data file to an existing tablespace to increase its storage capacity?

Introduction / Context:
As database objects grow, tablespaces may need additional storage capacity. In Oracle style databases, tablespaces are made up of one or more data files at the operating system level. To increase the size of a tablespace, a database administrator can either resize existing data files or add new data files. The standard method for adding a data file uses an ALTER TABLESPACE statement with an ADD DATAFILE clause. Understanding this command is fundamental for DBAs managing storage.

Given Data / Assumptions:

• The database is open or at least mounted and the DBA has appropriate privileges.
• The target tablespace already exists and currently contains one or more data files.
• The underlying file system has enough free space to store another data file.
• The administrator wants to increase tablespace capacity without recreating the database.

Concept / Approach:
To add a data file, the DBA issues an ALTER TABLESPACE command that names the target tablespace and includes an ADD DATAFILE clause specifying the full path and initial size of the new file. The database then creates and registers this file as part of the tablespace. From that point on, segments in the tablespace can allocate extents from the new data file. This approach is safer and more controlled than manipulating files directly at the operating system level without using SQL commands.

Step-by-Step Solution:
Step 1: Verify the name of the tablespace that requires additional space, for example USERS or DATA.Step 2: Choose a suitable location on disk for the new data file and decide its initial size and optional autoextend settings.Step 3: Connect to the database as a user with DBA privileges using a tool such as SQL Plus or another client.Step 4: Execute a command such as ALTER TABLESPACE users ADD DATAFILE '/path/to/users02.dbf' SIZE 500M; adjusting the path and size to your environment.Step 5: Confirm that the data file has been added by querying data dictionary views that list data files and tablespace contents.

Verification / Alternative check:
After running the ALTER TABLESPACE command, you can query views such as DBA_DATA_FILES or V$DATAFILE to confirm that the new file appears with the expected size and status. You can also monitor segment growth in the tablespace over time and verify that extents are being allocated from the new file. Operating system tools will show that the file was created on disk, matching the path specified in the SQL statement. These checks verify that the tablespace now has increased capacity and that the data file is correctly integrated.

Why Other Options Are Wrong:
Option B suggests copying an existing data file at the operating system level without informing the database, which can corrupt the database because the control file and data dictionary would not know about the new file. Option C describes recreating the entire database, which is unnecessary and risky just to add space to one tablespace. Option D proposes modifying only the init parameter file and restarting, which does not automatically create new data files and will not increase tablespace storage by itself. These methods do not follow recommended practices for managing tablespace capacity.

Common Pitfalls:
A common pitfall is adding data files without planning naming conventions or storage layout, leading to confusing file structures and potential performance problems. Another mistake is forgetting to set appropriate autoextend limits, which can cause disks to fill unexpectedly. DBAs should plan tablespace growth, monitor free space, and document all ALTER TABLESPACE operations. Using the correct SQL commands ensures that the control file and data dictionary remain consistent with the physical files, supporting reliable operation and recovery.

Final Answer:
Correct answer: By issuing an ALTER TABLESPACE statement with an ADD DATAFILE clause specifying the file name and size