Oracle Database Administration I (1Z0-082) — Questions and Answers

Question 1: Which SQL statement opens all PDBs in a CDB at once?

OPEN ALL PLUGGABLE DATABASE
STARTUP ALL PDBS
ALTER PLUGGABLE DATABASE ALL OPEN (Correct answer)
ALTER DATABASE OPEN ALL CONTAINERS

Correct answer: ALTER PLUGGABLE DATABASE ALL OPEN

ALTER PLUGGABLE DATABASE ALL OPEN opens every PDB in the current CDB instance in a single command.

Question 2: A backup strategy involves an RMAN incremental level 0 backup on Sunday. On Monday and Tuesday, RMAN incremental level 1 differential backups are performed. If a media failure occurs on Wednesday morning, which backups are essential to restore and recover the database to its most recent state?

The level 0 backup from Sunday and the level 1 backup from Tuesday.
Only the level 1 backup from Tuesday.
The level 0 backup from Sunday, the level 1 backup from Monday, and the level 1 backup from Tuesday. (Correct answer)
The level 0 backup from Sunday and the level 1 backup from Monday.

Correct answer: The level 0 backup from Sunday, the level 1 backup from Monday, and the level 1 backup from Tuesday.

A differential level 1 backup includes all blocks changed since the most recent incremental backup at either level 1 or level 0. To recover using this strategy, you must apply the base level 0 backup, followed by every subsequent level 1 differential backup in sequence. Therefore, the Sunday level 0, Monday level 1, and Tuesday level 1 backups are all required.

Question 3: Which of the following are the three primary purposes of undo data in an Oracle database?

Performing checkpoints, writing dirty buffers to disk, and managing the shared pool.
Archiving redo logs, managing password policies, and storing PL/SQL code.
Auditing user activity, enforcing resource limits, and caching data dictionary information.
Transaction rollback, read consistency, and instance recovery. (Correct answer)

Correct answer: Transaction rollback, read consistency, and instance recovery.

Undo data is essential for three core database functions: 1) Rolling back uncommitted transactions (e.g., via a `ROLLBACK` statement), 2) Providing read consistency for queries so they see a consistent version of the data as it existed when the query began, and 3) Rolling back uncommitted changes during instance recovery after a crash. [14, 15, 22]

Question 4: Which component of the Oracle Database architecture is responsible for managing memory structures and background processes?

Oracle Instance (Correct answer)
Control Files
Tablespaces
Datafiles

Correct answer: Oracle Instance

The Oracle Instance is the combination of memory structures (like the SGA and PGA) and background processes that manage the database. It is the active component that runs when the database is started, handling all operations related to memory allocation, process management, and interaction with the database files. Datafiles, Control Files, and Tablespaces are physical storage components, not the managing entity.

Question 5: Which SQL statement enables auditing of SELECT operations on the HR.EMPLOYEES table for all users?

AUDIT SELECT ON hr.employees BY ACCESS (Correct answer)
SET AUDIT SELECT hr.employees ON
ENABLE AUDIT SELECT ON hr.employees
CREATE AUDIT POLICY ON hr.employees FOR SELECT

Correct answer: AUDIT SELECT ON hr.employees BY ACCESS

The AUDIT statement with the SELECT action and object name enables auditing of SELECT operations on a specific table, optionally specifying BY ACCESS or BY SESSION.

Question 6: Which Oracle feature provides automatic detection and correction of performance issues?

Automatic Database Diagnostic Monitor (ADDM) (Correct answer)
Data Guard
Oracle Text
Oracle Streams

Correct answer: Automatic Database Diagnostic Monitor (ADDM)

The Automatic Database Diagnostic Monitor (ADDM) is an Oracle feature that automatically analyzes AWR data to identify the root causes of performance problems and recommend solutions. It runs after each AWR snapshot, providing proactive advice on how to improve database performance. ADDM helps database administrators quickly diagnose and resolve performance bottlenecks without manual intervention.

Question 7: During the startup sequence of an Oracle database, in which stage are the control files read to identify the location of data files and online redo log files?

OPEN
QUIESCE
MOUNT (Correct answer)
NOMOUNT

Correct answer: MOUNT

The Oracle startup sequence proceeds through three main stages: NOMOUNT, MOUNT, and OPEN. During the MOUNT stage, the instance reads the control files to get the names and locations of the data files and online redo log files, thereby associating the database with the instance.

Question 8: Which view should be queried to check the real-time OPEN_MODE of all PDBs in a CDB?

V$DATABASE
CDB_PDBS
DBA_PDBS
V$PDBS (Correct answer)

Correct answer: V$PDBS

V$PDBS is a dynamic performance view that reflects the current OPEN_MODE (READ WRITE, READ ONLY, or MOUNTED) of each PDB.

Question 9: Which initialization parameter must be set to 'AUTO' to enable Automatic Undo Management (AUM)?

TRANSACTION_CONTROL
ROLLBACK_SEGMENTS
UNDO_MANAGEMENT (Correct answer)
UNDO_TABLESPACE

Correct answer: UNDO_MANAGEMENT

The `UNDO_MANAGEMENT` parameter controls the undo mode for the instance. Setting it to `AUTO` enables Automatic Undo Management, where the database transparently manages undo segments within an undo tablespace. The legacy `MANUAL` mode requires manual DBA management of rollback segments. [4, 5, 6]

Question 10: How is an AWR report typically generated from the Oracle command line using SQL*Plus?

By executing SELECT * FROM AWR_REPORT
By calling DBMS_STATS.GENERATE_AWR
By running the awrrpt.sql script from $ORACLE_HOME/rdbms/admin/ (Correct answer)
By running EXEC DBMS_AWR.GENERATE_REPORT

Correct answer: By running the awrrpt.sql script from $ORACLE_HOME/rdbms/admin/

The awrrpt.sql script located in $ORACLE_HOME/rdbms/admin/ is run from SQL*Plus to interactively generate a text or HTML AWR report for a specified snapshot range.

Question 11: What is the primary purpose of a sequence object in Oracle Database?

To define referential integrity
To generate unique numeric values automatically (Correct answer)
To cache query results
To store binary data

Correct answer: To generate unique numeric values automatically

A sequence is a schema object that automatically generates unique sequential integers, commonly used to populate primary key columns.

Question 12: A database administrator needs to enable Automatic Memory Management (AMM) for an Oracle instance. Which two initialization parameters must be set to achieve this?

MEMORY_TARGET and MEMORY_MAX_TARGET (Correct answer)
SGA_TARGET and PGA_AGGREGATE_TARGET
DB_CACHE_SIZE and SHARED_POOL_SIZE
SGA_MAX_SIZE and PGA_AGGREGATE_LIMIT

Correct answer: MEMORY_TARGET and MEMORY_MAX_TARGET

Automatic Memory Management (AMM) allows Oracle to automatically manage and tune the total memory allocated to the System Global Area (SGA) and the Program Global Area (PGA). To enable AMM, you must set MEMORY_TARGET to define the total memory for the instance, and it is best practice to also set MEMORY_MAX_TARGET, which specifies the maximum value to which MEMORY_TARGET can be dynamically increased.

Question 13: You are managing an Oracle database that uses a Server Parameter File (SPFILE). You need to change a dynamic initialization parameter and ensure the change persists across instance restarts. Which `SCOPE` option should you use with the `ALTER SYSTEM` command?

SCOPE=SPFILE
SCOPE=PFILE
SCOPE=BOTH (Correct answer)
SCOPE=MEMORY

Correct answer: SCOPE=BOTH

When using an SPFILE, the `SCOPE=BOTH` option applies the change to the currently running instance (memory) and also writes the change to the SPFILE. This ensures the new parameter value is used immediately and also persists after the next shutdown and startup.

Question 14: What is the primary purpose of a database trigger in Oracle?

To automatically execute PL/SQL code in response to a DML or DDL event (Correct answer)
To schedule jobs
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 15: An organization wants to centralize the management of its database connection information to avoid distributing and updating `tnsnames.ora` files on hundreds of client machines. Which Oracle Net naming method is best suited for this requirement?

External Naming
Host Naming
Directory Naming (Correct answer)
Local Naming

Correct answer: Directory Naming

Directory Naming is designed for centralized management of connect identifiers. It stores the connect descriptors in an LDAP-compliant directory server, such as Oracle Internet Directory (OID) or Microsoft Active Directory. Clients are configured to look up connection information from this central directory, simplifying administration and eliminating the need to manage individual `tnsnames.ora` files.

Question 16: What is the primary purpose of the DBMS_STATS package in Oracle?

To monitor active sessions
To manage redo log files
To back up the database
To collect optimizer statistics on tables, indexes, and schemas (Correct answer)

Correct answer: To collect optimizer statistics on tables, indexes, and schemas

DBMS_STATS gathers, manages, and exports optimizer statistics that the Cost-Based Optimizer uses to generate efficient execution plans for SQL statements.

Question 17: A database administrator wants to create a role named `APP_DEVELOPER` and grant it the ability to create tables and views. Additionally, any user granted this role should be able to grant the role to other users. Which set of SQL statements accomplishes this?

NEW ROLE app_developer; GRANT CREATE TABLE, CREATE VIEW TO app_developer WITH GRANT;
CREATE ROLE app_developer; GRANT CREATE TABLE, CREATE VIEW TO app_developer WITH ADMIN OPTION; (Correct answer)
CREATE ROLE app_developer; GRANT CREATE TABLE, CREATE VIEW ON app_developer;
CREATE ROLE app_developer WITH ADMIN OPTION; GRANT CREATE TABLE, CREATE VIEW TO app_developer;

Correct answer: CREATE ROLE app_developer; GRANT CREATE TABLE, CREATE VIEW TO app_developer WITH ADMIN OPTION;

First, the `CREATE ROLE` statement is used to create the role. Then, the `GRANT` statement is used to assign the `CREATE TABLE` and `CREATE VIEW` system privileges to the new role. The `WITH ADMIN OPTION` clause is specified to allow any grantee of this role to further grant the role to other users.

Question 18: A DBA needs to identify the names and locations of all data files and redo log files associated with the database. Which physical component of the Oracle Database architecture contains this critical structural information?

Parameter File (PFILE/SPFILE)
Data Dictionary
Control File (Correct answer)
Password File

Correct answer: Control File

The control file is a small binary file that records the physical structure of the database. It contains essential metadata, including the database name, the names and locations of data files and online redo log files, the timestamp of database creation, and checkpoint information.

Question 19: Which Oracle tool is primarily used for performing database maintenance tasks such as backup and recovery?

SQL*Loader
Oracle Enterprise Manager (OEM) (Correct answer)
Oracle Net Manager
Oracle Text

Correct answer: Oracle Enterprise Manager (OEM)

Oracle Enterprise Manager (OEM) is a comprehensive suite of tools designed for managing and monitoring Oracle environments. It provides a graphical interface and robust capabilities for performing various database maintenance tasks, including backup and recovery, performance tuning, and security management. The other options are specialized tools for specific functions, not general maintenance.

Question 20: What does the STATSPACK utility do in Oracle Database?

Controls memory allocation
Manages user sessions
Automates backup and recovery
Gathers performance data at regular intervals for trend analysis (Correct answer)

Correct answer: Gathers performance data at regular intervals for trend analysis

STATSPACK is an Oracle utility that gathers performance data at regular intervals and stores it in the database. It collects a wide range of statistics, including wait events, SQL statistics, and resource usage, allowing administrators to analyze performance trends over time. While largely superseded by AWR in newer versions, it was a key tool for performance diagnostics and capacity planning.

Question 21: Which Oracle object type stores a precompiled SQL SELECT statement and can be queried like a table?

View (Correct answer)
Trigger
Sequence
Index

Correct answer: View

A view stores a named SQL SELECT statement in the data dictionary and presents it as a virtual table that users can query.

Question 22: A DBA is using the Oracle Net Manager (netmgr) graphical tool. Which of the following tasks can be accomplished using this utility?

Configuring listeners, naming methods, and network profiles. (Correct answer)
Creating and managing database users and roles.
Monitoring real-time database performance and executing SQL queries.
Starting and stopping the database instance.

Correct answer: Configuring listeners, naming methods, and network profiles.

Oracle Net Manager is a graphical utility specifically designed for configuring Oracle Net Services. Its primary functions include configuring listeners (`listener.ora`), naming methods like Local Naming (`tnsnames.ora`) and Directory Naming, and managing profile settings (`sqlnet.ora`).

Question 23: What does CDB stand for in Oracle Multitenant Architecture?

Container Database (Correct answer)
Consolidated Database
Central Database
Clustered Database

Correct answer: Container Database

CDB stands for Container Database, which is the top-level database that can hold multiple Pluggable Databases (PDBs).

Question 24: Which data dictionary view lists all indexes owned by the current user?

USER_INDEXES (Correct answer)
SESSION_INDEXES
ALL_INDEXES
DBA_INDEXES

Correct answer: USER_INDEXES

USER_INDEXES shows all indexes owned by the currently connected user, providing details such as index type, uniqueness, and associated table.

Question 25: A DBA is creating a new locally managed tablespace and wants to simplify space management within segments by letting Oracle manage free and used space automatically using bitmaps. Which clause should be included in the `CREATE TABLESPACE` statement?

EXTENT MANAGEMENT AUTO
SEGMENT SPACE MANAGEMENT AUTO (Correct answer)
SEGMENT SPACE MANAGEMENT MANUAL
EXTENT MANAGEMENT DICTIONARY

Correct answer: SEGMENT SPACE MANAGEMENT AUTO

Automatic Segment Space Management (ASSM) is enabled for a locally managed tablespace by specifying `SEGMENT SPACE MANAGEMENT AUTO`. This method uses bitmaps to track the status of blocks within a segment, which is more efficient and simplifies administration compared to the manual method that uses freelists. `EXTENT MANAGEMENT DICTIONARY` is an older method for managing extents at the data dictionary level, and `MANUAL` specifies the use of freelists, not automatic bitmap-based management.

Question 26: What is the primary purpose of the Oracle Optimizer?

To generate the most efficient execution plan for a SQL query (Correct answer)
To configure the database network
To manage user permissions
To back up the database

Correct answer: To generate the most efficient execution plan for a SQL query

The Oracle Optimizer is a critical component that determines the most efficient way to execute a SQL statement. It analyzes various factors, such as table statistics, indexes, and available resources, to choose the optimal execution plan. Its primary purpose is to minimize resource consumption and maximize query performance, ensuring fast data retrieval.

Question 27: Which of the following is a key benefit of using roles to manage database security?

Roles automatically audit all activities performed by users who are granted the role.
Roles simplify privilege management by grouping multiple privileges that can be granted to users or other roles collectively. (Correct answer)
Roles can be assigned their own storage quotas on tablespaces.
Roles allow for password-protected access to specific schemas.

Correct answer: Roles simplify privilege management by grouping multiple privileges that can be granted to users or other roles collectively.

Roles are designed to simplify security administration. Instead of granting a large number of individual privileges to each user, you can group related privileges into a role and then grant that single role to users. This makes granting, revoking, and managing privileges much more efficient.

Question 28: A DBA needs to perform a Data Pump Import (`impdp`) operation to create all tables and indexes from a dump file but without loading any of the row data. Which parameter and value should be specified?

CONTENT=METADATA_ONLY (Correct answer)
INCLUDE=TABLE,INDEX
ROWS=N
CONTENT=STRUCTURE_ONLY

Correct answer: CONTENT=METADATA_ONLY

The `CONTENT=METADATA_ONLY` parameter tells the Data Pump utility to process only the object definitions (DDL) from the dump file. This creates the structures like tables, indexes, and views but skips the actual data load.

Question 29: A DBA needs to analyze historical undo generation rates and identify the longest-running queries over the past few days to properly size the undo tablespace. Which dynamic performance view is best suited for this task?

DBA_UNDO_EXTENTS
V$SESSION_LONGOPS
V$TRANSACTION
V$UNDOSTAT (Correct answer)

Correct answer: V$UNDOSTAT

`V$UNDOSTAT` collects statistics on undo space usage over 10-minute intervals. It is the primary tool for monitoring undo generation (`UNDOBLKS`), transaction counts (`TXNCOUNT`), and identifying the duration of the longest query (`MAXQUERYLEN`) within each interval, making it ideal for tuning `UNDO_RETENTION` and sizing the undo tablespace. [2, 9, 20]

Question 30: A DBA needs to perform a critical administrative task that requires no active non-DBA transactions to be running. However, they want to avoid shutting down the database and disconnecting all users. Which command can be used to achieve this state?

STARTUP RESTRICT
ALTER SYSTEM QUIESCE RESTRICTED (Correct answer)
ALTER SYSTEM ENABLE RESTRICTED SESSION
SHUTDOWN TRANSACTIONAL

Correct answer: ALTER SYSTEM QUIESCE RESTRICTED

The `ALTER SYSTEM QUIESCE RESTRICTED` command puts the database into a quiesced state. In this state, all active non-DBA sessions are allowed to complete their current transaction or query, but no new non-DBA activity is permitted. This allows DBAs to perform tasks that require a stable state without shutting down the instance.

Question 31: You are tasked with reclaiming fragmented free space within a table's segment to improve the performance of full table scans. The space is currently below the high water mark (HWM). Which Oracle feature must be used to accomplish this online?

Data Pump Export/Import
ALTER TABLE ... DEALLOCATE UNUSED
Resumable Space Allocation
Online Segment Shrink (Correct answer)

Correct answer: Online Segment Shrink

Online Segment Shrink is the feature designed to reclaim fragmented space both above and below the high water mark (HWM) by compacting data and moving the HWM. This is an online operation. `ALTER TABLE ... DEALLOCATE UNUSED` only reclaims space above the HWM. Resumable Space Allocation is for suspending and resuming large operations, and while Data Pump can be used to rebuild objects, it is a much more involved process than the dedicated shrink operation.

Question 32: A database running in ARCHIVELOG mode experiences a media failure, losing a single data file belonging to a non-system, non-undo tablespace. The DBA needs to perform a complete recovery of the affected tablespace while the rest of the database remains online. Which sequence of RMAN commands is the correct way to accomplish this?

ALTER TABLESPACE users OFFLINE; RESTORE TABLESPACE users; RECOVER TABLESPACE users; ALTER TABLESPACE users ONLINE; (Correct answer)
RESTORE DATAFILE 5; RECOVER DATAFILE 5; ALTER DATABASE DATAFILE 5 ONLINE;
SHUTDOWN IMMEDIATE; STARTUP MOUNT; RESTORE TABLESPACE users; RECOVER TABLESPACE users; ALTER DATABASE OPEN;
RESTORE DATABASE; RECOVER DATABASE;

Correct answer: ALTER TABLESPACE users OFFLINE; RESTORE TABLESPACE users; RECOVER TABLESPACE users; ALTER TABLESPACE users ONLINE;

To recover a non-system tablespace while the database is open, the correct procedure is to first take the tablespace offline. Then, restore the affected data files for that tablespace and recover them using archived redo logs. Finally, bring the tablespace back online. Shutting down the entire database is unnecessary for a non-system tablespace recovery.

Question 33: A DBA is configuring a database and wants to centralize the storage of backup and recovery-related files, allowing Oracle to manage their lifecycle automatically. Which of the following file types would be stored in the Fast Recovery Area (FRA) by default?

Alert logs and trace files.
Archived redo logs, RMAN backupsets, and flashback logs. (Correct answer)
Password files and server parameter files (SPFILE).
Data files and temporary files.

Correct answer: Archived redo logs, RMAN backupsets, and flashback logs.

The Fast Recovery Area (FRA) is a unified storage location for recovery-related files. Oracle automatically manages the files within it. Common files stored in the FRA include RMAN backups (backupsets and image copies), archived redo logs, control file autobackups, and flashback logs.

Question 34: Which of the following represents the correct hierarchy of logical storage structures in an Oracle database, from smallest to largest unit?

Data Block, Segment, Extent, Tablespace
Data Block, Extent, Segment, Tablespace (Correct answer)
Segment, Extent, Data Block, Tablespace
Extent, Data Block, Segment, Tablespace

Correct answer: Data Block, Extent, Segment, Tablespace

The logical storage hierarchy in an Oracle database is as follows: The smallest unit is the Data Block. A set of contiguous data blocks forms an Extent. A set of extents allocated for a specific object (like a table or index) is called a Segment. Finally, a Tablespace is a logical container for segments.

Question 35: What is the primary difference between an Oracle SPFILE and a PFILE?

An SPFILE is a binary file whose parameters can be changed dynamically, while a PFILE is a text file that requires an instance restart for changes to take effect. (Correct answer)
A PFILE is a binary file, while an SPFILE is a text file.
Changes to a PFILE can be made dynamically, while SPFILE changes require a restart.
An SPFILE is only used in Real Application Clusters (RAC) environments, while a PFILE is for single-instance databases.

Correct answer: An SPFILE is a binary file whose parameters can be changed dynamically, while a PFILE is a text file that requires an instance restart for changes to take effect.

The fundamental difference is that an SPFILE (Server Parameter File) is a binary file maintained by the Oracle server, allowing for dynamic parameter changes using `ALTER SYSTEM` that can persist across restarts. A PFILE (Parameter File) is a client-side, static text file that can be manually edited, but any changes require the instance to be restarted to become effective.

Question 36: What is the primary content of the Row Directory section within an Oracle data block header?

A bitmap indicating the free space within the block.
Address information for each row piece stored in that block. (Correct answer)
Information about the tables that have rows stored in the block.
The actual row data, including all column values.

Correct answer: Address information for each row piece stored in that block.

The Row Directory, located in the data block header, contains entries for each row piece in the block. These entries store the address of the corresponding row piece in the row data area of the block. The actual row data is stored in the 'Row Data' section. The 'Table Directory' contains information about the tables owning the rows, and free space is managed separately.

Question 37: A DBA issues the `SHUTDOWN IMMEDIATE` command. Which of the following best describes the actions the Oracle instance will take?

Immediately stops all database processes without rolling back transactions, requiring instance recovery on the next startup.
Terminates all active sessions, rolls back uncommitted transactions, and then shuts down. (Correct answer)
Waits for all active user sessions to disconnect before shutting down.
Waits for all active transactions to complete, prevents new transactions, and then shuts down.

Correct answer: Terminates all active sessions, rolls back uncommitted transactions, and then shuts down.

The `SHUTDOWN IMMEDIATE` command does not wait for current user sessions to disconnect. It proceeds to terminate all active sessions, roll back any uncommitted transactions, and then performs a clean shutdown. No instance recovery is needed upon the next startup.

Question 38: What is the main purpose of running the DBMS_STATS.GATHER_SCHEMA_STATS procedure in Oracle?

To optimize query performance by updating statistics for all objects in a schema (Correct answer)
To manage database connections
To recover lost data
To create a new user

Correct answer: To optimize query performance by updating statistics for all objects in a schema

The `DBMS_STATS.GATHER_SCHEMA_STATS` procedure is used to collect and update optimizer statistics for all tables, indexes, and columns within a specified schema. Accurate statistics are crucial for the Oracle optimizer to generate efficient execution plans for SQL queries. By keeping statistics up-to-date, this procedure significantly improves overall query performance.

Question 39: What is a 'common user' in Oracle Multitenant Architecture?

A user with read-only privileges across the CDB
A user shared between exactly two PDBs
A default Oracle user such as SYS or SYSTEM
A user created in CDB$ROOT that exists across all containers (Correct answer)

Correct answer: A user created in CDB$ROOT that exists across all containers

A common user is defined in CDB$ROOT and automatically has a presence in every container, including PDB$SEED and all PDBs.

Question 40: When using Automatic Undo Management (AUM), what happens if the instance starts and the undo tablespace specified in the `UNDO_TABLESPACE` parameter is not available?

The instance will start without an undo tablespace and use the SYSTEM tablespace for undo records. (Correct answer)
The instance will automatically create a new undo tablespace with a default name.
The instance will start in restricted mode, allowing only DBA connections.
The instance will fail to start and report an ORA-01092 error.

Correct answer: The instance will start without an undo tablespace and use the SYSTEM tablespace for undo records.

If the specified undo tablespace is unavailable, or if none is specified and no undo tablespace is available, the instance will still start. However, it will store undo records in the `SYSTEM` tablespace. This is a non-recommended configuration, and an alert will be written to the alert log to warn the DBA. [1, 5]

Question 41: A junior developer accidentally executes a `DROP TABLE` command on a critical application table. The DBA needs to recover the table as quickly as possible with minimal impact on other database operations. Which Oracle feature is specifically designed for this scenario?

Flashback Drop. (Correct answer)
RMAN Point-in-Time Recovery (PITR) of the tablespace.
Flashback Table.
Flashback Database.

Correct answer: Flashback Drop.

Flashback Drop is the feature designed to reverse the effects of a `DROP TABLE` statement. It retrieves the table and its dependent objects from the Recycle Bin. Flashback Table is used to revert a table's data to a previous point in time but cannot be used if the table has been dropped. RMAN PITR and Flashback Database are much heavier operations that affect more than just the single dropped table.

Question 42: A DBA needs to export only the `HR` and `OE` schemas from a production database to a dump file using Oracle Data Pump. Which `expdp` parameter should be used to specify these schemas?

SCHEMAS=HR,OE (Correct answer)
FULL=N SCHEMAS=(HR,OE)
OWNER=HR,OE
TABLES=HR.*,OE.*

Correct answer: SCHEMAS=HR,OE

The `SCHEMAS` parameter in `expdp` is used to specify a list of one or more schemas to be exported in schema-mode. The `OWNER` parameter was used in the original `exp` utility and has been replaced by `SCHEMAS` in Data Pump. `TABLES` is used for exporting specific tables, not entire schemas.

Question 43: An extent is a logical unit of database storage allocation. Which of the following statements is true regarding extents?

A segment can only have one extent.
An extent is made up of a number of non-contiguous data blocks.
An extent can contain data from multiple data files.
An extent is allocated to a segment, and all extents for a segment must be in the same tablespace. (Correct answer)

Correct answer: An extent is allocated to a segment, and all extents for a segment must be in the same tablespace.

A segment is a collection of extents, and all of these extents must reside within the same tablespace. However, a segment can span multiple data files within that tablespace. An extent itself is a set of *contiguous* data blocks and cannot span data files; all blocks for a given extent must come from a single data file. Segments typically have many extents, allocated as the object grows.

Question 44: How does TRUNCATE TABLE differ from DELETE without a WHERE clause in Oracle?

TRUNCATE requires a WHERE clause
TRUNCATE removes all rows quickly without generating undo and cannot be rolled back (Correct answer)
TRUNCATE is DML; DELETE is DDL
TRUNCATE fires row-level triggers; DELETE does not

Correct answer: TRUNCATE removes all rows quickly without generating undo and cannot be rolled back

TRUNCATE is a DDL operation that removes all rows instantly, generates minimal undo, and commits immediately, making it irreversible without Flashback.

Question 45: What does 'unplugging' a PDB accomplish?

Converts the PDB to a standalone non-CDB Oracle database
Permanently deletes the PDB and its associated data files
Immediately moves the PDB to a different CDB
Closes the PDB and generates an XML manifest file describing its structure for future use (Correct answer)

Correct answer: Closes the PDB and generates an XML manifest file describing its structure for future use

Unplugging closes the PDB and creates an XML metadata file so the PDB can later be plugged into another CDB.

Question 46: Which of the following is a proactive database maintenance task to prevent tablespace overflow?

Rebuilding indexes
Increasing the size of datafiles or adding more datafiles to a tablespace (Correct answer)
Auditing user activities
Creating user roles

Correct answer: Increasing the size of datafiles or adding more datafiles to a tablespace

Tablespace overflow occurs when a tablespace runs out of free space to store new data, leading to errors. Proactively increasing the size of existing datafiles (if they are autoextensible) or adding new datafiles to the tablespace ensures sufficient storage capacity. This prevents data insertion failures and maintains database availability, making it a key proactive maintenance task.

Question 47: What is the purpose of the Oracle Data Dictionary?

To perform database backups
To maintain the physical storage of data
To hold metadata that describes the database structure (Correct answer)
To store user passwords

Correct answer: To hold metadata that describes the database structure

The Oracle Data Dictionary is a collection of tables and views that store metadata, which is 'data about the data.' It describes the logical and physical structure of the database, including information about tables, indexes, users, privileges, and storage parameters. This metadata is essential for the database to function correctly and for users and administrators to understand its organization.

Question 48: What is the purpose of a database link (DBLINK) in Oracle?

To link two tables within the same schema
To create a connection that allows queries to access objects in a remote Oracle database (Correct answer)
To establish a backup channel to standby
To synchronize indexes automatically

Correct answer: To create a connection that allows queries to access objects in a remote Oracle database

A database link defines a named connection path from a local Oracle database to a remote Oracle database, enabling distributed queries and DML across databases.

Question 49: A database administrator is tasked with enforcing a stricter password policy. They need to ensure that user accounts are locked after 3 failed login attempts. Which database security mechanism should be used to configure this setting?

System Privileges
Profiles (Correct answer)
Object Privileges
Database Triggers

Correct answer: Profiles

Profiles are used to manage password policies and resource limits for users. The `FAILED_LOGIN_ATTEMPTS` parameter within a profile can be set to lock an account after a specified number of consecutive unsuccessful login attempts.

Question 50: Which statement accurately describes a key advantage of using a direct path load with SQL*Loader compared to a conventional path load?

It generates significantly more redo and undo data for better recoverability.
It bypasses the database buffer cache and writes formatted data blocks directly to the datafiles, improving performance. (Correct answer)
It automatically builds all indexes on the table before the data load begins.
It allows triggers and referential integrity constraints to be active on the target table during the load.

Correct answer: It bypasses the database buffer cache and writes formatted data blocks directly to the datafiles, improving performance.

A direct path load significantly improves performance for large data loads by creating formatted data blocks and writing them directly to the datafiles. This bypasses much of the SQL command processing and the database buffer cache. A conventional path load uses standard `INSERT` statements, which is a slower process.

Question 51: A client is attempting to connect to an Oracle database using the following connect string: `sqlplus scott/tiger@//dbhost:1527/orclpdb`. This connection attempt is failing. The administrator has verified the database is running and the listener on `dbhost` is configured to listen on port 1527 for the service `orclpdb`. Which Oracle Net naming method is being used, and what is the key benefit of this method?

External Naming, which relies on a third-party naming service like NIS.
Directory Naming, which centralizes administration of network service names in an LDAP-compliant directory server.
Local Naming, which uses a local `tnsnames.ora` file to map service names to connect descriptors.
Easy Connect (or Host Naming), which allows for simple TCP/IP connections without requiring a `tnsnames.ora` file. (Correct answer)

Correct answer: Easy Connect (or Host Naming), which allows for simple TCP/IP connections without requiring a `tnsnames.ora` file.

The connect string syntax `//host[:port][/service_name]` is characteristic of the Easy Connect naming method. The primary advantage of this method is its simplicity; it eliminates the need for client-side configuration files like `tnsnames.ora` or directory server lookups for basic TCP/IP connections.

Question 52: When AUDIT_TRAIL is set to DB, where are standard audit records stored?

In the operating system audit file
In the SYS.AUD$ table in the database (Correct answer)
In the redo log files
In a separate audit database

Correct answer: In the SYS.AUD$ table in the database

When AUDIT_TRAIL=DB, Oracle writes audit records to the SYS.AUD$ table, which is accessible through the DBA_AUDIT_TRAIL data dictionary view.

Question 53: A long-running report fails with an ORA-01555 "snapshot too old" error. What is the most likely cause?

The UNDO_RETENTION period is shorter than the query's execution time, causing necessary undo data to be overwritten. (Correct answer)
The user running the report lacks the necessary SELECT privileges on the underlying tables.
The database instance was restarted while the query was executing.
The temporary tablespace ran out of space during a sort operation.

Correct answer: The UNDO_RETENTION period is shorter than the query's execution time, causing necessary undo data to be overwritten.

The ORA-01555 error occurs when a query requires a version of a data block to maintain read consistency, but that version is no longer available in the undo tablespace. This typically happens when the undo information has been overwritten by newer transactions because the query's duration exceeded the configured UNDO_RETENTION period. [8, 10, 21]

Question 54: Which initialization parameter must be set to enable traditional (non-unified) database auditing in Oracle?

DB_AUDIT
AUDIT_TRAIL (Correct answer)
ENABLE_AUDIT
AUDIT_LEVEL

Correct answer: AUDIT_TRAIL

The AUDIT_TRAIL parameter controls whether auditing is enabled and where audit records are stored, with values such as DB, OS, XML, or NONE.

Question 55: Where does Oracle Database write critical error messages, startup and shutdown information, and background process messages?

The control file
The Alert Log (Correct answer)
V$DIAG_INFO
The redo log files

Correct answer: The Alert Log

The Alert Log is a chronological file that records database events including startup/shutdown, errors (ORA- messages), and administrative commands.

Question 56: When a table is dropped using the DROP TABLE command in Oracle, where does it go by default?

It is moved to the Recycle Bin (Correct answer)
It is archived to a backup tablespace
It is immediately and permanently deleted
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 57: A database administrator needs to ensure that unexpired undo data is never overwritten, even if it means subsequent DML operations that require undo space will fail. Which action accomplishes this?

Set the `UNDO_MANAGEMENT` parameter to `GUARANTEE`.
Enable `RETENTION GUARANTEE` on the active undo tablespace. (Correct answer)
Set the UNDO_RETENTION parameter to a very high value.
Create the undo tablespace with the `AUTOEXTEND ON MAXSIZE UNLIMITED` clause.

Correct answer: Enable `RETENTION GUARANTEE` on the active undo tablespace.

By default, `UNDO_RETENTION` is a target, not a strict guarantee. To prevent unexpired undo from being overwritten, you must enable `RETENTION GUARANTEE` on the undo tablespace using the `ALTER TABLESPACE ... RETENTION GUARANTEE` command. This forces the database to honor the retention period, causing new DML to fail if space runs out, rather than overwriting needed undo data. [1, 2, 16]

Question 58: When using SQL*Loader to load data from a flat file into a database table, what is the primary purpose of the control file?

To authenticate the user running the load process.
To store the data that is being loaded before it is committed.
To describe the format of the input data file and map its fields to the target table columns. (Correct answer)
To log all errors that occur during the data load operation.

Correct answer: To describe the format of the input data file and map its fields to the target table columns.

The SQL*Loader control file is a text file containing DDL instructions that tell SQL*Loader where to find the data, how to parse it, which table and columns to load it into, and how to handle potential errors. It essentially provides the metadata map for the load operation.

Question 59: A database administrator is creating a new tablespace for a large-scale data warehousing application that will store terabytes of data. To simplify file management and support extremely large data volumes within a single file, which type of tablespace should be created?

Undo tablespace
Smallfile tablespace
Bigfile tablespace (Correct answer)
Temporary tablespace

Correct answer: Bigfile tablespace

A Bigfile tablespace is designed to contain a single, very large data file (or temp file), which can be up to 128 terabytes for a 32K block size. This simplifies the management of data files for very large databases (VLDBs) by reducing the number of files a DBA has to manage. Smallfile tablespaces are the traditional type and can contain many data files, but each file has a smaller size limit. Undo and temporary tablespaces serve specific purposes (transaction rollback and sorting operations, respectively) and do not inherently address the need for managing massive, single-file data storage.

Question 60: Which two Oracle Net Services configuration files are primarily involved in a standard client-server connection using the Local Naming method?

`listener.ora` and `tnsnames.ora` (Correct answer)
`sqlnet.ora` and `protocol.ora`
`tnsnames.ora` and `ldap.ora`
`cman.ora` and `sqlnet.ora`

Correct answer: `listener.ora` and `tnsnames.ora`

In a typical Local Naming scenario, the `tnsnames.ora` file on the client side is used to resolve the net service name into a connect descriptor (host, port, service name). The listener process on the server side reads its configuration from the `listener.ora` file to know which protocol addresses to listen on for incoming connection requests.

Question 61: After a database failure, a DBA uses the Data Recovery Advisor (DRA) through RMAN. What is the primary function of the `ADVISE FAILURE` command?

It lists all failures currently stored in the Automatic Diagnostic Repository (ADR).
It analyzes detected failures and generates one or more recommended repair scripts. (Correct answer)
It generates a human-readable report of all backups available in the RMAN catalog.
It automatically repairs all detected failures without user intervention.

Correct answer: It analyzes detected failures and generates one or more recommended repair scripts.

After using `LIST FAILURE` to see detected problems, the `ADVISE FAILURE` command is used to analyze those failures. The Data Recovery Advisor then determines the optimal repair strategy and presents it as a script of RMAN commands that the DBA can review and then execute using the `REPAIR FAILURE` command.

Question 62: A new developer, 'John', has been created with the `CREATE USER john IDENTIFIED BY ...` command. When John tries to connect to the database, he receives an `ORA-01045: user JOHN lacks CREATE SESSION privilege; logon denied` error. What is the most likely cause of this error?

The user 'john' does not have a quota on any tablespace.
The user 'john' was not granted the necessary privilege to connect to the database. (Correct answer)
The user 'john' has an expired password.
The database listener is not running.

Correct answer: The user 'john' was not granted the necessary privilege to connect to the database.

When a user is created, their privilege domain is empty. To be able to log on to the database, a user must be granted the `CREATE SESSION` system privilege. The error message explicitly states that this privilege is lacking.

Question 63: What naming convention is required for common users created in a CDB?

Names must begin with SYS or SYSTEM
Names must begin with the prefix C## (Correct answer)
Names must be in all uppercase letters
Names must end with the suffix _CDB

Correct answer: Names must begin with the prefix C##

Common user names must start with C## (e.g., C##ADMIN) by default to distinguish them from local users scoped to a single PDB.

Question 64: Which of the following is an example of a system privilege?

EXECUTE on the CALC_BONUS procedure
CREATE TABLE (Correct answer)
SELECT on the EMPLOYEES table
UPDATE on the ORDERS table

Correct answer: CREATE TABLE

System privileges grant the ability to perform actions on a type of object or to perform a system-level action, such as `CREATE TABLE`, `CREATE VIEW`, or `CREATE SESSION`. In contrast, object privileges grant permission to perform a specific action (like SELECT, INSERT, UPDATE, DELETE) on a specific, existing object (like a particular table or view).

Question 65: What is the purpose of Oracle's Control File?

To maintain the metadata of the database's physical structure (Correct answer)
To hold redo logs
To store user data and indexes
To cache SQL statements

Correct answer: To maintain the metadata of the database's physical structure

The control file is a small, binary file essential for the operation of an Oracle database. It contains critical metadata such as the database name, the names and locations of datafiles and redo log files, and checkpoint information. Oracle uses this metadata to open and maintain the database's physical structure, making it vital for database consistency and recovery.

Question 66: A database administrator needs to create a new user named 'APP_USER' who will own application objects. Which SQL statement correctly creates the user, assigns a default tablespace for their objects, and allows them to use 100M of space in that tablespace?

CREATE USER app_user WITH a_password ON app_data QUOTA 100M;
ADD USER app_user IDENTIFIED BY a_password DEFAULT TABLESPACE app_data;
CREATE USER app_user IDENTIFIED BY a_password DEFAULT TABLESPACE app_data QUOTA 100M ON app_data; (Correct answer)
CREATE NEW USER app_user WITH PASSWORD a_password TABLESPACE app_data QUOTA 100M;

Correct answer: CREATE USER app_user IDENTIFIED BY a_password DEFAULT TABLESPACE app_data QUOTA 100M ON app_data;

The correct syntax for creating a user in Oracle includes the `CREATE USER` keywords, followed by the username, the `IDENTIFIED BY` clause for the password, the `DEFAULT TABLESPACE` clause to specify where the user's objects will be stored, and the `QUOTA ... ON` clause to set a space limit within that tablespace.

Question 67: Which DDL command is used to modify an existing column's data type or add a new column to a table?

UPDATE TABLE
ALTER TABLE (Correct answer)
CHANGE TABLE
MODIFY 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 68: What does ADDM stand for in Oracle Database?

Automatic Data Distribution Manager
Active Database Diagnostic Module
Adaptive DDL Management Module
Automatic Database Diagnostic Monitor (Correct answer)

Correct answer: Automatic Database Diagnostic Monitor

ADDM stands for Automatic Database Diagnostic Monitor, the self-diagnosing engine that analyzes AWR data to identify and report database performance issues.

Question 69: You are planning to move a large tablespace from an Oracle database on an AIX server to another database on a Linux server using the transportable tablespaces method. What is a critical prerequisite for this operation?

The source and target databases must have the same database version and patch level.
The source tablespace must be taken offline during the entire transport process.
The source and target databases must have the same DB_BLOCK_SIZE.
The source and target databases must have compatible character sets and endian formats, or RMAN must be used for conversion. (Correct answer)

Correct answer: The source and target databases must have compatible character sets and endian formats, or RMAN must be used for conversion.

Transportable tablespaces involve physically copying datafiles. When moving between platforms with different endian formats (byte ordering), such as AIX (big-endian) and Linux (little-endian), you must use the RMAN `CONVERT` command to reformat the datafiles. Additionally, the source and target databases must have compatible character sets.

Question 70: Which clause must be included in the CREATE DATABASE statement to create a Container Database (CDB)?

MULTITENANT ON
ENABLE PLUGGABLE DATABASE (Correct answer)
CREATE PLUGGABLE DATABASE
CONTAINER DATABASE ON

Correct answer: ENABLE PLUGGABLE DATABASE

The ENABLE PLUGGABLE DATABASE clause in CREATE DATABASE instructs Oracle to create a CDB rather than a traditional non-CDB.

Question 71: What is Unified Auditing introduced in Oracle 12c?

An auditing feature limited to DML operations only
A single consolidated audit framework that replaces all previous auditing methods with one policy-based system (Correct answer)
An external OS-level logging solution
A feature that audits only privileged users

Correct answer: A single consolidated audit framework that replaces all previous auditing methods with one policy-based system

Unified Auditing consolidates standard auditing, FGA, SYS auditing, and RMAN auditing into a single framework stored in AUDSYS.AUD$UNIFIED and managed via audit policies.

Question 72: Which system privilege allows a user to create tables within their own schema?

CREATE ANY TABLE
INSERT ANY TABLE
ALTER TABLE
CREATE TABLE (Correct answer)

Correct answer: CREATE TABLE

The CREATE TABLE system privilege grants a user the ability to create tables in their own schema only, unlike CREATE ANY TABLE which spans all schemas.

Question 73: What is the purpose of the Oracle Statspack tool?

To manage ASM disk groups
To monitor network throughput between RAC nodes
To export and import schema statistics
To collect and store point-in-time database performance snapshots for trend analysis before AWR was introduced (Correct answer)

Correct answer: To collect and store point-in-time database performance snapshots for trend analysis before AWR was introduced

Statspack is a predecessor to AWR that collects performance snapshots into the PERFSTAT schema; it is still available in Oracle Database as a free alternative to the licensed AWR feature.

Oracle Database Administration I (1Z0-082)

The 1Z0-082 exam validates expertise in Oracle Database administration including installation, configuration, user security, storage management, backup and recovery, and performance monitoring.

Questions

120 min

Time Limit

60%

To Pass

Checking for saved progress...

Exam Rules

You can skip questions and return to them later
Flag questions for review before submitting
No feedback shown until you submit the entire exam
Unanswered questions count as wrong — answer everything
10 pretest questions are mixed in and don't affect your score
Timer auto-submits when time runs out
Your progress is auto-saved every 30 seconds