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

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

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

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 2: Which Oracle tool is primarily used to manage and monitor the database?

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

Correct answer: Oracle Enterprise Manager (OEM)

Oracle Enterprise Manager (OEM) is a comprehensive suite of tools designed for managing and monitoring Oracle Databases, applications, and cloud environments. It provides a graphical interface for tasks such as performance tuning, security management, backup and recovery, and deployment. OEM is a powerful solution for database administrators to maintain the health and efficiency of their Oracle systems.

Question 3: 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_RETENTION parameter to a very high value.
Create the undo tablespace with the `AUTOEXTEND ON MAXSIZE UNLIMITED` clause.
Enable `RETENTION GUARANTEE` on the active undo tablespace. (Correct answer)
Set the `UNDO_MANAGEMENT` parameter to `GUARANTEE`.

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 4: A long-running report fails with an ORA-01555 "snapshot too old" error. What is the most likely cause?

The database instance was restarted while the query was executing.
The temporary tablespace ran out of space during a sort operation.
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.

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 5: 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?

ROWS=N
CONTENT=METADATA_ONLY (Correct answer)
INCLUDE=TABLE,INDEX
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 6: 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 automatically repairs all detected failures without user intervention.
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.

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 7: What naming convention is required for common users created in a CDB?

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

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 8: 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 fail to start and report an ORA-01092 error.
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 start without an undo tablespace and use the SYSTEM tablespace for undo records. (Correct answer)

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 9: 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?

CREATE ROLE app_developer WITH ADMIN OPTION; GRANT CREATE TABLE, CREATE VIEW TO app_developer;
CREATE ROLE app_developer; GRANT CREATE TABLE, CREATE VIEW ON app_developer;
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)

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 10: Which Oracle feature is primarily used for monitoring database performance in real-time?

Oracle Text
Oracle Flashback
Data Pump
Automatic Workload Repository (AWR) (Correct answer)

Correct answer: Automatic Workload Repository (AWR)

The Automatic Workload Repository (AWR) is an Oracle feature that automatically collects, processes, and maintains performance statistics for problem detection and self-tuning. It captures snapshots of database performance metrics at regular intervals, providing a historical record that can be analyzed to identify performance bottlenecks and trends. AWR is crucial for proactive performance management and diagnostics.

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

An SPFILE is only used in Real Application Clusters (RAC) environments, while a PFILE is for single-instance databases.
Changes to a PFILE can be made dynamically, while SPFILE changes require a restart.
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.

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 12: 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?

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

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 13: Which SQL statement is used to create a new table in an Oracle schema?

MAKE TABLE
BUILD TABLE
CREATE TABLE (Correct answer)
NEW TABLE

Correct answer: CREATE TABLE

The CREATE TABLE statement is the standard DDL command used to define and create a new table in an Oracle schema.

Question 14: 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' has an expired password.
The database listener is not running.
The user 'john' was not granted the necessary privilege to connect to the database. (Correct answer)
The user 'john' does not have a quota on any tablespace.

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 15: Which component of the System Global Area (SGA) is used to store the most recently used SQL and PL/SQL statements, as well as data dictionary information?

Large Pool
Java Pool
Shared Pool (Correct answer)
Database Buffer Cache

Correct answer: Shared Pool

The Shared Pool is a key component of the SGA that caches various types of program data. It includes the Library Cache, which stores parsed SQL and PL/SQL code, and the Data Dictionary Cache, which holds information about database objects. Caching this information improves performance by reducing the need to re-parse statements and access the disk for dictionary data.

Question 16: 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
EXTENT MANAGEMENT DICTIONARY
SEGMENT SPACE MANAGEMENT MANUAL
SEGMENT SPACE MANAGEMENT AUTO (Correct answer)

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 17: Which constraint type enforces uniqueness of values in one or more columns but allows NULL values?

PRIMARY KEY
CHECK
UNIQUE (Correct answer)
NOT NULL

Correct answer: UNIQUE

A UNIQUE constraint ensures no two rows have the same non-null value(s) in the constrained column(s), but unlike PRIMARY KEY, it permits NULL values.

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

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

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 19: You are troubleshooting a connection issue where a client receives an `ORA-12154: TNS:could not resolve the connect identifier specified` error. The client is configured to use the Local Naming method. Which of the following is the MOST likely cause of this error?

The listener on the database server is not running.
The client has provided an incorrect username or password.
The database service has not registered with the listener.
The net service name used in the connect string does not have a corresponding entry in the `tnsnames.ora` file. (Correct answer)

Correct answer: The net service name used in the connect string does not have a corresponding entry in the `tnsnames.ora` file.

The `ORA-12154` error specifically indicates that the client was unable to resolve the provided connect identifier (net service name) into a connect descriptor. When using the Local Naming method, this resolution is done via the `tnsnames.ora` file. The error means the alias is either missing from the file, the file cannot be found, or there is a syntax error in the file.

Question 20: A database administrator needs to ensure that clients resolve connect identifiers by first checking a local `tnsnames.ora` file, and if not found, then attempting to resolve using the Easy Connect naming method. Which parameter in the `sqlnet.ora` file should be configured to define this specific resolution order?

NAMES.DEFAULT_DOMAIN
NAMES.DIRECTORY_PATH (Correct answer)
TNS_ADMIN
SQLNET.AUTHENTICATION_SERVICES

Correct answer: NAMES.DIRECTORY_PATH

The `NAMES.DIRECTORY_PATH` parameter in the `sqlnet.ora` file specifies the order of naming methods the client will use to resolve a connect identifier. To prioritize local naming followed by Easy Connect, this parameter should be set to `(TNSNAMES, EZCONNECT)`.

Question 21: 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 NEW USER app_user WITH PASSWORD a_password TABLESPACE app_data QUOTA 100M;
CREATE USER app_user IDENTIFIED BY a_password DEFAULT TABLESPACE app_data QUOTA 100M ON app_data; (Correct answer)
ADD USER app_user IDENTIFIED BY a_password DEFAULT TABLESPACE app_data;
CREATE USER app_user WITH a_password ON 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 22: 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 manage tablespace allocation
To create indexes automatically
To schedule jobs

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

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 24: An Oracle database server consists of a database and at least one instance. What is the fundamental difference between an Oracle database instance and an Oracle database?

The instance and the database are two terms for the same set of files and memory structures.
The instance consists of physical files on disk, while the database is a set of memory structures and background processes.
The instance is a set of memory structures (SGA and PGA) and background processes, while the database consists of the physical data files, control files, and redo log files on disk. (Correct answer)
The instance is only the background processes, and the database is only the System Global Area (SGA).

Correct answer: The instance is a set of memory structures (SGA and PGA) and background processes, while the database consists of the physical data files, control files, and redo log files on disk.

An Oracle Database instance is comprised of the System Global Area (SGA) memory structures and the background processes that manage the database. The database itself consists of the physical files stored on disk, which include data files, control files, and online redo log files. The instance exists in memory to manage and provide access to the data stored in the database files.

Question 25: What is the primary purpose of PDB$SEED in a CDB?

It hosts shared application data for all PDBs
It serves as a read-only template for creating new PDBs (Correct answer)
It provides a backup of the CDB$ROOT container
It stores the CDB administrator credentials

Correct answer: It serves as a read-only template for creating new PDBs

PDB$SEED is a read-only seed PDB that Oracle uses as a template when creating new pluggable databases.

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

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

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 27: Which Oracle feature provides automatic detection and correction of performance issues?

Oracle Text
Automatic Database Diagnostic Monitor (ADDM) (Correct answer)
Data Guard
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 28: What is the purpose of a database link (DBLINK) in Oracle?

To establish a backup channel to standby
To synchronize indexes automatically
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)

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 29: Where does Oracle Database write critical error messages, startup and shutdown information, and background process messages?

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

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 30: 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 and target databases must have compatible character sets and endian formats, or RMAN must be used for conversion. (Correct answer)
The source tablespace must be taken offline during the entire transport process.
The source and target databases must have the same DB_BLOCK_SIZE.

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 31: An extent is a logical unit of database storage allocation. Which of the following statements is true regarding extents?

An extent is allocated to a segment, and all extents for a segment must be in the same tablespace. (Correct answer)
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.

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 32: 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=BOTH (Correct answer)
SCOPE=PFILE
SCOPE=SPFILE
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 33: Which data dictionary view lists all indexes owned by the current user?

SESSION_INDEXES
ALL_INDEXES
USER_INDEXES (Correct answer)
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 34: What is the primary purpose of the DBMS_STATS package in Oracle?

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

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 35: Which of the following represents the correct hierarchy of logical storage structures in an Oracle database, from smallest to largest unit?

Data Block, Extent, Segment, Tablespace (Correct answer)
Data Block, Segment, Extent, Tablespace
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 36: A DBA issues the `SHUTDOWN IMMEDIATE` command. Which of the following best describes the actions the Oracle instance will take?

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

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 37: Which of the following are the three primary purposes of undo data in an Oracle database?

Archiving redo logs, managing password policies, and storing PL/SQL code.
Auditing user activity, enforcing resource limits, and caching data dictionary information.
Performing checkpoints, writing dirty buffers to disk, and managing the shared pool.
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 38: 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?

Data Dictionary
Parameter File (PFILE/SPFILE)
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 39: 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?

Database Triggers
Profiles (Correct answer)
Object Privileges
System Privileges

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 40: 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 41: What is the primary function of an Oracle Database instance?

Backing up the database
Running the Oracle software and handling data operations in memory (Correct answer)
Storing data files
Managing user permissions

Correct answer: Running the Oracle software and handling data operations in memory

An Oracle Database instance consists of the System Global Area (SGA) and background processes, which together run the Oracle software. Its primary function is to manage the database files on disk and handle all data operations, such as queries and transactions, by processing them efficiently in memory. This allows for fast access and manipulation of data.

Question 42: What is the purpose of the ANALYZE command in Oracle Database maintenance?

To update statistics for the optimizer (Correct answer)
To back up the database
To grant user privileges
To create a new tablespace

Correct answer: To update statistics for the optimizer

The `ANALYZE` command (or more commonly, `DBMS_STATS` procedures) is used to collect statistics about database objects like tables, indexes, and columns. These statistics are vital for the Oracle optimizer to determine the most efficient execution plan for SQL queries. Accurate statistics help the optimizer choose the best access paths, leading to improved query performance.

Question 43: What is a synonym in Oracle Database?

An alias for another schema object (Correct answer)
A type of index
A backup copy of a table
A stored procedure template

Correct answer: An alias for another schema object

A synonym is an alias that points to another database object such as a table, view, or procedure, simplifying object references across schemas.

Question 44: 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?

Temporary tablespace
Bigfile tablespace (Correct answer)
Smallfile tablespace
Undo 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 45: What is an 'application container' in Oracle 19c Multitenant?

A named container that serves as a sub-root for a group of related application PDBs (Correct answer)
A container that stores Oracle Forms and Reports applications
A read-only PDB dedicated to application reporting workloads
A PDB that exclusively hosts Java EE application data

Correct answer: A named container that serves as a sub-root for a group of related application PDBs

An application container is an optional layer between CDB$ROOT and regular PDBs, acting as a root for application PDBs that share common application metadata.

Question 46: 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 Database.
Flashback Table.

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 47: What is a 'common user' in Oracle Multitenant Architecture?

A default Oracle user such as SYS or SYSTEM
A user with read-only privileges across the CDB
A user shared between exactly two PDBs
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 48: 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_MAX_SIZE and PGA_AGGREGATE_LIMIT
DB_CACHE_SIZE and SHARED_POOL_SIZE
SGA_TARGET and PGA_AGGREGATE_TARGET

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 49: 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?

MOUNT (Correct answer)
OPEN
NOMOUNT
QUIESCE

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 50: What is a materialized view in Oracle Database?

A view that cannot be updated
A view stored in temporary tablespace
A view that spans multiple databases
A physical copy of query results stored as a table and refreshable on demand or schedule (Correct answer)

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 51: Which Oracle utility is used to reorganize tables and indexes to reclaim unused space?

DBMS_REDEFINITION (Correct answer)
SQL*Plus
Export/Import
Data Pump

Correct answer: DBMS_REDEFINITION

The `DBMS_REDEFINITION` package allows for online redefinition of tables, which can be used to reorganize tables and their associated indexes. This process helps reclaim unused space, improve storage efficiency, and enhance performance without requiring significant downtime. Other utilities like SQL*Plus are general interfaces, and Data Pump/Export/Import are for data movement, not online reorganization.

Question 52: Which dynamic performance view shows ALL containers in a CDB, including CDB$ROOT and PDB$SEED?

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

Correct answer: V$CONTAINERS

V$CONTAINERS includes every container (CDB$ROOT, PDB$SEED, and all user-created PDBs), while V$PDBS shows only PDBs.

Question 53: 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?

OWNER=HR,OE
TABLES=HR.*,OE.*
SCHEMAS=HR,OE (Correct answer)
FULL=N SCHEMAS=(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 54: Which initialization parameter must be set to 'AUTO' to enable Automatic Undo Management (AUM)?

TRANSACTION_CONTROL
UNDO_TABLESPACE
ROLLBACK_SEGMENTS
UNDO_MANAGEMENT (Correct answer)

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 55: 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?

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

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 56: Which dynamic performance view shows all currently connected sessions in an Oracle database?

V$PROCESS
V$SESSION (Correct answer)
V$TRANSACTION
V$SQL

Correct answer: V$SESSION

V$SESSION contains one row for each current session connected to the Oracle database, including background and user sessions with their status and resource usage.

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

It is converted to an external table
It is moved to the Recycle Bin (Correct answer)
It is archived to a backup tablespace
It is immediately and permanently deleted

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 58: Which system privilege allows a user to create tables within their own schema?

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

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 59: Which two Oracle Net Services configuration files are primarily involved in a standard client-server connection using the Local Naming method?

`cman.ora` and `sqlnet.ora`
`tnsnames.ora` and `ldap.ora`
`listener.ora` and `tnsnames.ora` (Correct answer)
`sqlnet.ora` and `protocol.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 60: What does CDB stand for in Oracle Multitenant Architecture?

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

Correct answer: Container Database

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

Question 61: A data analyst needs to query data residing in a large comma-separated values (CSV) file located on the database server's file system without permanently loading it into the database. Which Oracle feature is best suited for this task?

External Tables (Correct answer)
SQL*Loader
Database Links
Transportable Tablespaces

Correct answer: External Tables

External Tables allow Oracle to treat a flat file on the server's file system as if it were a read-only database table. This enables users to query the data using standard SQL, including joins with other tables, without the need to load the data into the database first, which avoids data duplication and the overhead of insert operations.

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

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.
Roles automatically audit all activities performed by users who are granted the role.

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 63: Which Oracle background process is responsible for writing the contents of the Redo Log Buffer to the redo log files?

LGWR (Log Writer) (Correct answer)
DBWn (Database Writer)
CKPT (Checkpoint)
SMON (System Monitor)

Correct answer: LGWR (Log Writer)

The LGWR (Log Writer) background process is specifically responsible for writing the contents of the Redo Log Buffer to the online redo log files on disk. This process is critical for ensuring data durability and recovery, as it records all changes made to the database before they are permanently written to datafiles.

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

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

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 65: Which of the following is an example of a system privilege?

CREATE TABLE (Correct answer)
EXECUTE on the CALC_BONUS procedure
UPDATE on the ORDERS table
SELECT on the EMPLOYEES 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 66: 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
Resumable Space Allocation
ALTER TABLE ... DEALLOCATE UNUSED
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 67: A database administrator is reviewing the mandatory background processes of a healthy Oracle 19c database instance. Which background process is responsible for writing modified data blocks from the database buffer cache to the data files on disk?

SMON (System Monitor)
CKPT (Checkpoint)
DBWn (Database Writer) (Correct answer)
LGWR (Log Writer)

Correct answer: DBWn (Database Writer)

The Database Writer (DBWn) process is responsible for writing dirty (modified) buffers from the database buffer cache in the SGA to the physical data files on disk. LGWR writes redo information, CKPT updates file headers and signals DBWn, and SMON performs instance recovery and other system monitoring tasks.

Question 68: 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?

RESTORE DATAFILE 5; RECOVER DATAFILE 5; ALTER DATABASE DATAFILE 5 ONLINE;
SHUTDOWN IMMEDIATE; STARTUP MOUNT; RESTORE TABLESPACE users; RECOVER TABLESPACE users; ALTER DATABASE OPEN;
ALTER TABLESPACE users OFFLINE; RESTORE TABLESPACE users; RECOVER TABLESPACE users; ALTER TABLESPACE users ONLINE; (Correct answer)
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 69: What prerequisite must be met to use FLASHBACK TABLE to restore a dropped table in Oracle?

The table must have no indexes
The Recycle Bin must be enabled and the table must still exist in it (Correct answer)
The table must have been exported first
A full backup must be available

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.

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

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

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 71: What does ADDM stand for in Oracle Database?

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

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 72: Which DDL command is used to modify an existing column's data type or add a new column to a table?

CHANGE TABLE
MODIFY TABLE
ALTER TABLE (Correct answer)
UPDATE 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 73: Which of the following is an Oracle Database structure that can be used to organize and store user data?

Trigger
Synonym
View
Table (Correct answer)

Correct answer: Table

A table is the fundamental Oracle Database structure used to organize and store user data in a relational format. It consists of rows and columns, where each row represents a record and each column represents an attribute of that record. Tables are the primary means by which data is structured and accessed within the database.

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