1Z0-082 Managing Schemas and Database Objects 2 — Questions and Answers
Question 1: Which data dictionary view shows all tables accessible to the current user, including those in other schemas?
- USER_TABLES
- DBA_TABLES
- ALL_TABLES (Correct answer)
- SESSION_TABLES
Correct answer: ALL_TABLES
ALL_TABLES shows all tables that the current user has privileges to access, including their own and those granted by other schemas.
Question 2: What is the primary purpose of a database trigger in Oracle?
- To schedule jobs
- To automatically execute PL/SQL code in response to a DML or DDL event (Correct answer)
- To create indexes automatically
- To manage tablespace allocation
Correct answer: To automatically execute PL/SQL code in response to a DML or DDL event
A trigger is a stored PL/SQL block that fires automatically when a specified DML (INSERT, UPDATE, DELETE) or DDL event occurs on a table or schema.
Question 3: Which DDL command is used to modify an existing column's data type or add a new column to a table?
- MODIFY TABLE
- UPDATE TABLE
- ALTER TABLE (Correct answer)
- CHANGE TABLE
Correct answer: ALTER TABLE
The ALTER TABLE statement is used to modify existing table structure, including adding, modifying, or dropping columns and constraints.
Question 4: What is a materialized view in Oracle Database?
- A view that cannot be updated
- A physical copy of query results stored as a table and refreshable on demand or schedule (Correct answer)
- A view stored in temporary tablespace
- A view that spans multiple databases
Correct answer: A physical copy of query results stored as a table and refreshable on demand or schedule
A materialized view physically stores the results of a query and can be refreshed either on demand or automatically on a schedule to reflect source data changes.
Question 5: When a table is dropped using the DROP TABLE command in Oracle, where does it go by default?
- It is immediately and permanently deleted
- It is moved to the Recycle Bin (Correct answer)
- It is archived to a backup tablespace
- It is converted to an external table
Correct answer: It is moved to the Recycle Bin
By default in Oracle 10g and later, dropped tables are moved to the Recycle Bin and can be recovered using the FLASHBACK TABLE ... TO BEFORE DROP statement.
Question 6: What prerequisite must be met to use FLASHBACK TABLE to restore a dropped table in Oracle?
- The table must have been exported first
- The Recycle Bin must be enabled and the table must still exist in it (Correct answer)
- A full backup must be available
- The table must have no indexes
Correct answer: The Recycle Bin must be enabled and the table must still exist in it
FLASHBACK TABLE ... TO BEFORE DROP works by restoring the table from the Recycle Bin, which requires the Recycle Bin feature to be enabled and the object not yet purged.
1Z0-082 Managing Schemas and Database Objects 2
Which data dictionary view shows all tables accessible to the current user, including those in other schemas?