In Oracle administration using SQL*Plus, which statement shows the correct syntax to create a new database user with a password and default tablespace?

Introduction / Context:
SQL*Plus is a common command line tool used to administer Oracle databases. One of the first tasks for a database administrator is creating new database user accounts with appropriate credentials and storage settings. This question checks whether you understand the correct Oracle syntax to create a user, including the IDENTIFIED BY clause and the DEFAULT TABLESPACE clause, which are frequently used in real world administration tasks and interview questions.

Given Data / Assumptions:

• We are working in an Oracle database environment using SQL*Plus.
• We need to create a new user named john.
• The user must have a password, for example welcome1.
• The user must have a default tablespace, for example users.
• We focus on the basic create user syntax, without additional profile or quota options.

Concept / Approach:
In Oracle, a database user is created with the CREATE USER statement. The IDENTIFIED BY clause sets the user password in a simple scenario. The DEFAULT TABLESPACE clause defines where user objects such as tables will be stored if no tablespace is specified at creation time. Other clauses like TEMPORARY TABLESPACE, PROFILE, and QUOTA can also be added, but the core pattern remains CREATE USER username IDENTIFIED BY password DEFAULT TABLESPACE tablespace_name. Any syntax that uses non standard verbs such as ADD USER or GRANT USER to create a user is incorrect for Oracle.

Step-by-Step Solution:
Step 1: Recall the standard Oracle syntax for user creation: CREATE USER username IDENTIFIED BY password DEFAULT TABLESPACE tablespace_name; Step 2: Compare each option with this template to see which one matches exactly. Step 3: Observe that Option A follows the pattern CREATE USER john IDENTIFIED BY welcome1 DEFAULT TABLESPACE users; which is valid Oracle syntax. Step 4: Notice that options B, C, D, and E use non Oracle syntax such as ADD USER, NEW USER, CREATE LOGIN, or GRANT USER, which are not correct in standard Oracle SQL*Plus. Step 5: Conclude that Option A is the only one that both creates a user and uses valid Oracle syntax.

Verification / Alternative check:
To verify, you can imagine executing Option A in an Oracle SQL*Plus session. The database would successfully create the user, assuming you have the CREATE USER system privilege. Oracle documentation for CREATE USER also shows similar examples, confirming the use of IDENTIFIED BY and DEFAULT TABLESPACE. If you tried to execute Option B or C, Oracle would return a syntax error, because those verbs and structures are not recognized. CREATE LOGIN is more characteristic of SQL Server, so Option D also indicates a different database product rather than Oracle.

Why Other Options Are Wrong:

• Option B is wrong because ADD USER is not valid Oracle syntax and the clause WITH PASSWORD is also not standard here.
• Option C is wrong because NEW USER and the format PASSWORD welcome1 TABLESPACE users do not match Oracle syntax.
• Option D is wrong because CREATE LOGIN is a SQL Server concept and the equals signs are not part of the Oracle CREATE USER syntax.
• Option E is wrong because GRANT is used to assign privileges, not to create users, so GRANT USER is invalid.

Common Pitfalls:
New administrators sometimes confuse user creation syntax between different relational database systems such as Oracle, SQL Server, and MySQL. Another common pitfall is omitting DEFAULT TABLESPACE and then struggling when user segments go into the system tablespace. It is also important not to hard code simple passwords in real environments and to use stronger password policies. Understanding the full CREATE USER syntax, including options for password lifetime, profiles, and quotas, is useful, but this question focuses on the essential structure.

Final Answer:
The correct statement is Option A: CREATE USER john IDENTIFIED BY welcome1 DEFAULT TABLESPACE users;