Oracle 1z0-071 Exam Dumps & Practice Test Questions
Question No 1:
Which two of the following statements are true regarding the granting of object privileges on tables, views, and sequences in a database? (Choose two.)
A. The INSERT privilege can only be granted on tables and sequences.
B. The DELETE privilege can be granted on tables, views, and sequences.
C. The SELECT privilege can be granted on tables, views, and sequences.
D. The ALTER privilege can only be granted on tables and sequences.
E. The REFERENCES privilege can only be granted on tables.
Correct Answer:
C. SELECT can be granted on tables, views, and sequences.
E. REFERENCES can be granted only on tables.
Explanation:
In a database, object privileges define the access that a user or role has over different types of objects like tables, views, and sequences. Object privileges allow the database administrator or a user with appropriate permissions to control who can perform various actions on these objects.
A. INSERT can be granted only on tables and sequences:
This statement is incorrect. The INSERT privilege can be granted on tables and sequences. However, it is not granted on views. In contrast to tables and sequences, views are virtual and typically do not directly support the INSERT operation, as they do not hold data themselves.
B. DELETE can be granted on tables, views, and sequences:
This statement is incorrect. The DELETE privilege can only be granted on tables and views. Sequences do not support the DELETE operation because sequences are used to generate unique numeric values, not to store or delete data rows.
C. SELECT can be granted on tables, views, and sequences:
This statement is true. The SELECT privilege can be granted on tables, views, and sequences. For tables and views, it allows users to query the data. For sequences, the SELECT privilege allows users to retrieve the next value from the sequence.
D. ALTER can be granted only on tables and sequences:
This statement is incorrect. The ALTER privilege can be granted on tables, sequences, and views. For tables and sequences, it allows modifications to their structure or definition. In the case of views, ALTER allows modifying the view's definition.
E. REFERENCES can be granted only on tables:
This statement is true. The REFERENCES privilege allows a user to create foreign key constraints that refer to the primary key or unique constraints of a table. This privilege can only be granted on tables, not views or sequences.
Understanding object privileges is essential for managing access control in a relational database. By granting specific privileges to users or roles, database administrators can ensure that users can perform only the necessary actions on objects, thus maintaining data security and integrity.
Question No 2:
Which of the following statements about Oracle databases are correct? (Choose two)
A. A table can have multiple primary keys.
B. A column definition can specify multiple data types.
C. A table can have multiple foreign keys.
D. A VARCHAR2 column with no data will store a NULL value. E. A NUMBER column with no data will store a value of zero.
Correct Answer:
C. A table can have multiple foreign keys.
D. A VARCHAR2 column with no data will store a NULL value.
Explanation:
When working with Oracle databases, it's important to understand the behavior of tables, columns, and keys. Here's a detailed breakdown of the statements and why only two are correct.
A. A table can have multiple primary keys – This statement is incorrect. An Oracle table can only have one primary key. The primary key is a unique identifier for each row in the table, and it enforces the uniqueness constraint on one or more columns. While a table can have multiple unique constraints or indexes, it can only have a single primary key.
B. A column definition can specify multiple data types – This statement is incorrect. In Oracle, each column is defined with a single data type. For example, a column can be defined as VARCHAR2, NUMBER, or DATE, but it cannot simultaneously have multiple data types. The column definition specifies one data type to enforce the structure and validation of the data stored in that column.
C. A table can have multiple foreign keys – This statement is correct. A table can indeed have multiple foreign keys, each referencing different columns or other tables. Foreign keys are used to enforce referential integrity by ensuring that values in a column (or set of columns) match values in the primary key of another table. Each foreign key constraint establishes a relationship between two tables.
D. A VARCHAR2 column with no data will store a NULL value – This statement is correct. In Oracle, if a VARCHAR2 column has no data provided for a particular row, the column will store a NULL value. NULL is used to represent missing or unknown data, distinct from an empty string or a zero-length string.
E. A NUMBER column with no data will store a value of zero – This statement is incorrect. A NUMBER column in Oracle, when no data is provided, will store a NULL value by default. It will not store a zero unless explicitly set to zero by the user.
Understanding these database rules is crucial for correctly designing and working with Oracle databases, as they help ensure data integrity and optimal schema design.
Question No 3:
Which two actions can be performed using object privileges in a database? (Choose two.)
A. Create roles.
B. Create FOREIGN KEY constraints that reference tables in other schemas.
C. Delete rows from tables in any schema except SYS.
D. Set default and temporary tablespaces for a user.
E. Execute a procedure or function in another schema.
Correct Answer:
C. Delete rows from tables in any schema except SYS.
E. Execute a procedure or function in another schema.
Explanation:
Object privileges in a database management system (DBMS) grant specific rights to users on individual database objects such as tables, views, procedures, or functions. These privileges allow the user to perform various operations on those objects within the database.
Delete Rows from Tables in Any Schema Except SYS (Option C): One of the fundamental object privileges is the ability to modify the contents of tables, including deleting rows. When a user has the necessary DELETE privilege on a table, they can delete rows from it. Object privileges can be granted on tables belonging to any schema, with the exception of the SYS schema, which is reserved for internal use by the database. The DELETE privilege is granted specifically on a per-table basis, and it allows the user to remove data from the table as needed.
Execute a Procedure or Function in Another Schema (Option E): Object privileges also extend to the execution of stored procedures or functions. If a user has the EXECUTE privilege on a procedure or function in another schema, they can invoke that procedure or function, regardless of the schema to which it belongs. This is crucial for scenarios where one user needs to run logic that is encapsulated in a procedure or function located in another user’s schema. This privilege is granted specifically on the procedure or function object.
The other options provided are not related to object privileges:
Create Roles (Option A): Creating roles is a system-level privilege and not an object privilege.
Create FOREIGN KEY Constraints (Option B): Creating foreign key constraints involves data integrity rules, and while this might involve object privileges on tables, it typically requires the ability to modify schema objects and define relationships between them.
Set Default and Temporary Tablespaces (Option D): Setting tablespaces is an administrative action, not an object-level privilege. It involves altering user-level configurations, not operations on individual objects like tables or procedures.
In conclusion, object privileges allow users to directly interact with database objects such as tables and functions, enabling operations like data manipulation and execution of stored procedures across schemas.
Question No 4:
You are working with an Oracle database that does not use user-defined locks. Considering how Transaction Control Language (TCL) operates in this context, which three of the following statements are accurate?
A. Executing ROLLBACK without specifying a savepoint undoes all changes made in the transaction, clears all savepoints, and releases any held locks.
B. ROLLBACK without the TO SAVEPOINT clause reverses all changes in the transaction but keeps any locks in place.
C. ROLLBACK without the TO SAVEPOINT clause undoes all changes made so far but preserves the savepoints.
D. ROLLBACK TO SAVEPOINT reverses changes made after the specified savepoint and concludes the transaction.
E. COMMIT finalizes the transaction and permanently saves all changes.
F. COMMIT removes all savepoints and releases any locks held by the transaction.
Correct Answers: A, E, F
Explanation:
Transaction Control Language (TCL) in Oracle SQL is used to manage transactions and maintain the integrity of the database during and after operations. The two primary TCL commands are COMMIT and ROLLBACK.
When a ROLLBACK command is issued without referencing a savepoint (e.g., ROLLBACK;), Oracle interprets this as an instruction to undo all the changes made during the current transaction. It not only cancels those changes but also clears any savepoints that were set and releases any locks that the transaction may have held. This aligns with option A, making it correct.
In contrast, using ROLLBACK TO SAVEPOINT allows a developer to roll back part of the transaction, but it does not end the transaction. Therefore, option D is incorrect because it incorrectly claims that this command terminates the transaction.
The COMMIT command has two key roles: first, it makes all changes permanent, meaning data manipulated during the transaction is stored in the database for good. This confirms option E is correct. Second, COMMIT erases all savepoints associated with the transaction and releases any held locks—this behavior ensures a clean state after a transaction concludes. So option F is also valid.
Options B and C misrepresent the behavior of ROLLBACK by either suggesting that locks or savepoints are retained—both of which are incorrect. When ROLLBACK is issued without the TO SAVEPOINT clause, everything in the transaction is undone, all savepoints are lost, and all locks are released.
Understanding TCL behavior is vital for maintaining consistent data states and handling errors safely in transactional systems.
Question No 5:
You are working with an Oracle database and need to remove one or more columns from an existing table. Based on Oracle’s rules for modifying table structures, which three statements about dropping columns are true?
A. A column must first be marked as UNUSED before it can be dropped from a table.
B. Columns that are part of a primary key constraint cannot be dropped directly.
C. You can drop multiple columns at once using a single ALTER TABLE command.
D. A column can only be dropped if it does not contain any data.
E. A column referenced by a foreign key in another table cannot be dropped unless the foreign key constraint is removed first.
F. Dropping a column is an implicitly committed action that cannot be rolled back.
Correct Answers: B, C, F
Explanation:
Dropping a column from a table in Oracle Database is a Data Definition Language (DDL) operation and is governed by specific rules to maintain data integrity and consistency.
Let’s start with option B. Oracle does not allow you to directly drop a column that is part of a primary key constraint. To remove such a column, you must first drop or modify the primary key constraint. This ensures the uniqueness and integrity rules of the database are not unintentionally broken, making this option correct.
Option C is also valid. Oracle allows dropping multiple columns at once using a single ALTER TABLE command with this syntax:
This makes large structural changes more efficient.
Option F is correct because dropping a column is an implicitly committed operation. This means that as soon as the column is dropped, Oracle automatically commits the change to the database. It cannot be undone using ROLLBACK. This behavior is typical of DDL commands in Oracle.
Now, option A is incorrect. Although marking a column as UNUSED before dropping it can help improve performance (especially in large tables), it is not mandatory. You can directly drop a column without first marking it UNUSED.
Option D is false because a column can be dropped regardless of whether it holds data. Dropping it will remove both the structure and its contents.
Lastly, option E is partially true but misleading. While you cannot drop a column referenced by a foreign key unless the constraint is removed, the statement doesn’t mention this nuance clearly enough.
Overall, understanding these rules helps avoid errors and ensures database changes are done safely.
Question No 6:
In Oracle Database, views are used to simplify complex queries and enhance data security. Based on their characteristics and behavior, which three of the following statements about views are true?
A. If the defining SELECT query of a view has a WHERE clause, you cannot use another WHERE clause when querying that view.
B. Views do not consume physical storage space since they lack segments.
C. Views are not assigned an object number in the Oracle data dictionary.
D. Views can only join tables that exist in the same schema.
E. You can create a view even if the referenced base table does not yet exist, provided the FORCE option is used.
F. Data inserted into a base table via a view remains intact even if the view is dropped later.
Correct Answers: B, E, F
Explanation:
In Oracle Database, a view is a virtual table that represents the result of a SQL query. Views are powerful tools for simplifying complex SQL logic, enforcing security, and providing a layer of abstraction between users and base tables.
Let’s begin with option B. This is correct because views do not have physical segments, meaning they do not store data independently. Instead, they derive their data dynamically from the base tables every time they are queried. This is why views do not consume additional storage space—unless they are materialized views, which are an exception and store data physically.
Option E is also valid. Oracle allows the creation of a view even if the referenced base tables do not yet exist by using the FORCE keyword. This is particularly useful in environments where views are pre-defined before actual tables are deployed.
Option F is true. When you insert data into a base table through a view (assuming the view is updatable and the proper constraints are met), the data is stored in the base table. Even if the view is dropped later, the data remains unaffected in the base table because the view is just a logical layer.
Option A is incorrect. You can definitely include an additional WHERE clause when querying a view, even if the view’s defining query also includes one. The new WHERE clause simply adds more filtering on top of the existing logic.
Option C is false because every schema object in Oracle, including views, is assigned an object number in the data dictionary for identification.
Option D is incorrect. Views can join tables from different schemas as long as the user has the necessary privileges on those objects.
Understanding these behaviors helps developers design flexible and secure applications using Oracle views effectively.
Question No 7:
In Oracle SQL*Plus, the SET VERIFY ON command helps users understand how substitution variables are resolved during script execution.
Which two of the following statements correctly describe the functionality and scope of the SET VERIFY ON command?
A. It only displays substituted variable values used in the WHERE clause of SQL statements.
B. It shows the current values of variables defined using the DEFINE command.
C. This command is unique to the SQLPlus environment and does not apply in other tools.
D. It causes SQLPlus to show both the original and the substituted SQL statement when substitution variables are used.
E. It functions the same way in both SQL Developer and SQL*Plus environments.
Correct Answers: C, D
Explanation:
The SET VERIFY ON command in Oracle’s SQL*Plus environment is a diagnostic and debugging tool that allows users to clearly see how substitution variables (e.g., those prefixed with & or &&) are being interpreted and applied in SQL statements. When enabled, this command displays both the original SQL command entered by the user and the modified version with actual values substituted in place of variables.
This visibility is particularly useful in script execution where dynamic input is provided at runtime. By showing both versions of the command, it ensures the user can verify that variable substitution occurred correctly, helping to catch errors before execution.
Option D is correct because this is the core purpose of SET VERIFY ON—to present both the original and substituted statements, making variable substitution transparent during script execution.
Option C is also correct. The command is specific to the SQL*Plus environment and is not guaranteed to behave the same in other Oracle tools such as SQL Developer. While SQL Developer supports some SQL*Plus commands in its worksheet, behavior may vary or be limited.
Option A is incorrect because substitution variables can appear anywhere in the SQL statement—not just in the WHERE clause—and SET VERIFY ON applies globally.
Option B is wrong as well; while DEFINE creates user-defined variables, SET VERIFY ON does not display these values directly unless they are used in a SQL statement that triggers substitution.
Option E is false because the behavior of SET VERIFY ON is not consistent across SQL Developer and SQL*Plus—SQL Developer may not display variable substitutions as clearly.
In summary, SET VERIFY ON is a helpful debugging feature exclusive to SQL*Plus that enhances script transparency by showing both the pre- and post-substitution versions of SQL statements.
Question No 8:
Which SQL clause is used to restrict the rows returned by a query before any groupings are made?
A. HAVING
B. WHERE
C. GROUP BY
D. ORDER BY
Correct Answer: B
Explanation:
In SQL, the WHERE clause is used to filter rows before any groupings or aggregations are applied in a query. This means that when you write a query involving GROUP BY, any filters you want to apply to individual rows before the grouping takes place must go in the WHERE clause.
Let’s understand the flow of SQL query execution:
FROM – Tables and joins are processed first.
WHERE – This filters rows before grouping.
GROUP BY – Rows are grouped based on specified columns.
HAVING – Applies filters to groups (not individual rows).
SELECT – Columns are selected, possibly with aggregates.
ORDER BY – Final sorting of the result set.
So, when the question asks for restricting rows before grouping, only one correct answer fits: WHERE.
Here’s why the other options are incorrect:
A (HAVING) is used after grouping to filter grouped rows. You use HAVING when you want to apply a condition on an aggregate like COUNT, SUM, etc.
C (GROUP BY) is not a filtering clause; it’s used to create groups of rows for aggregation.
D (ORDER BY) is used for sorting the result after all operations are complete. It doesn’t filter any rows.
For example:
This query first filters all employees who earn more than 5000 (WHERE) and then groups them by department.
So, Option B is correct.
Question 9:
You want to retrieve all employees who do not have a manager assigned. Which SQL clause should you use to correctly filter out rows with NULL in the manager_id column?
A.WHERE manager_id = NULL
B.WHERE manager_id != NULL
C.WHERE manager_id IS NULL
D.WHERE manager_id <> NULL
Correct Answer: C
Explanation:
In SQL, when you are checking whether a column contains NULL values, you must use IS NULL. This is because NULL represents an unknown value, and comparisons like = NULL or <> NULL do not work as expected.
Let’s break it down:
NULL is not a value like 0 or 'ABC'; it signifies unknown or missing data.
Because of this, you can't use standard comparison operators (=, !=, <, >, <>) with NULL.
Instead, you must use IS NULL to check if a column contains NULL.
Likewise, use IS NOT NULL to find rows where the column has a known (non-null) value.
Now evaluating the options:
A (manager_id = NULL) is invalid—it will not return any rows because you can't use = with NULL.
B (manager_id != NULL) is also incorrect for the same reason.
C (manager_id IS NULL) is correct—it’s the proper syntax to check for NULL values.
D (manager_id <> NULL) is invalid and won’t work either.
Example:
This query correctly retrieves all employees who don’t report to anyone, i.e., have no manager assigned.
So, the correct answer is C.
Question No 10:
Which three of the following statements correctly reflect how Oracle Database handles GLOBAL TEMPORARY TABLES (GTTs)?
A. Space for a GLOBAL TEMPORARY TABLE is pre-allocated at the beginning of a user session.
B. Data stored in a GLOBAL TEMPORARY TABLE by one session is accessible to other sessions if they have the necessary privileges.
C. When a session issues a TRUNCATE command, only that session’s rows in the GLOBAL TEMPORARY TABLE are removed.
D. Rows held in a GLOBAL TEMPORARY TABLE are automatically deleted once the session ends.
E. DELETE commands on GLOBAL TEMPORARY TABLES are permanent and cannot be rolled back.
F. The structure (definition) of a GLOBAL TEMPORARY TABLE is available to all sessions across the database.
Correct Answers: C, D, F
Explanation:
Oracle’s GLOBAL TEMPORARY TABLES (GTTs) are designed to store data on a temporary, session-specific basis, allowing for efficient data manipulation during operations such as reporting, PL/SQL processing, or temporary staging of results. While their structure remains consistent and shared across the database, the data within them is private to each session.
One key characteristic is that Option F is correct: the table’s structure (definition) is stored in the data dictionary and is accessible to all sessions. This means any session with the appropriate privileges can use the GTT, but the data they insert remains isolated to that session.
Option C is also valid: when a session performs a TRUNCATE operation on a GTT, only the rows within that session are affected. This preserves data isolation and ensures no cross-session interference.
Option D is equally accurate: once the session ends—or the transaction concludes, depending on how the GTT was defined—the data is automatically deleted. Oracle allows two modes of GTT behavior: ON COMMIT DELETE ROWS clears rows after each transaction, while ON COMMIT PRESERVE ROWS retains rows until the session ends.
The incorrect options can be understood as follows:
Option A is incorrect because space is allocated dynamically, not pre-allocated when the session begins.
Option B is wrong; no session can view another session’s GTT data, regardless of privileges—data is always session-private.
Option E is false; DELETE operations are transactional and can be rolled back just like in regular tables.
In summary, GTTs provide a flexible and efficient way to handle temporary data in Oracle, ensuring session-level privacy while maintaining a shared structure.
