Free 1Z0-182 Practice Test Questions and Answers (2026)

A .False. V$TABLESPACE doesn’t show free space directly.

B .False. It’s from the control file, not the data dictionary.

C .True. Renaming in MOUNT state updates DBA_DATA_FILES post-open.

D .False. DBA_TABLESPACES shows attributes, not free space directly (use DBA_FREE_SPACE).

E .True. V$TABLESPACE reflects control file data.

Reference:Oracle Database Reference 23ai, "V$TABLESPACE, DBA_DATA_FILES."

The ALTER DATABASE MOVE DATAFILE command relocates a data file to a new location while the

database remains online, introduced in Oracle 12c and enhanced in subsequent releases like 23ai.

Let’s evaluate each option:

A . DML may be performed on tables with one or more extents in this data file during the execution

of this command.True. The move operation is online by default in Oracle 23ai, allowing DML (INSERT,

UPDATE, DELETE) operations on tables within the data file being moved. The database ensures

consistency using redo and undo mechanisms.

B . It overwrites any existing file with the name sales02.dbf in /u02 by default.False. By default, the

command does not overwrite an existing file unless the REUSE clause is specified (e.g., ALTER

DATABASE MOVE DATAFILE ... REUSE). Without it, the command fails if the target file exists.

C . The "TO" clause containing the new file name must be specified even if Oracle Managed Files

(OMF) is used.False. When OMF is enabled (via DB_CREATE_FILE_DEST), the TO clause is optional. If

omitted, Oracle automatically generates a file name and places it in the OMF destination.

D . Compressed objects in sales01.dbf will be uncompressed in sales02.dbf after the move.False. The

move operation is a physical relocation of the data file; it does not alter the logical structure or

compression state of objects within it. Compressed data remains compressed.

E . Tables with one or more extents in this data file may be queried during the execution of this

command.True. The online nature of the move allows queries (SELECT statements) to proceed

without interruption, leveraging Oracle’s multi-version consistency model.

Reference:Oracle Database Administrator’s Guide 23ai, Chapter "Managing Data Files and Temp

Files," Section "Moving Data Files Online."

Roles in Oracle manage privileges efficiently. Let’s dive into each option:

A . Roles must be password protected.

False. Roles can be password-protected (e.g., CREATE ROLE mgr IDENTIFIED BY secret), but it’s

optional. Non-protected roles (default) are enabled automatically if granted, requiring no password.

Mechanics:Password-protected roles need SET ROLE mgr IDENTIFIED BY secret, enhancing security

for sensitive privileges.

B . Roles may be granted to other roles.

True. Roles can form hierarchies (e.g., GRANT clerk TO mgr), allowing nested privilege management.

Mechanics:A user with mgr inherits clerk privileges indirectly. Revoking clerk from mgr cascades

appropriately.

Practical Use:Simplifies complex privilege structures in large organizations.

C . The SET ROLE statement can enable one or more roles for a session.

True. SET ROLE role1, role2; activates specified roles for the session, assuming they’re granted and

not password-protected (or password is provided).

Mechanics:Enabled roles grant their privileges immediately within the session scope.

D . Object privileges may not be granted to roles.

False. Object privileges (e.g., GRANT SELECT ON emp TO clerk) are a primary use of roles, making this

statement incorrect.

Why Incorrect:Roles are designed for this purpose, contradicting the option.

E . All roles granted to a user are set on default when the user logs in.

False. Only roles marked as DEFAULT ROLE (via ALTER USER ... DEFAULT ROLE role1) are enabled at

login. Non-default roles require SET ROLE.

Mechanics:Check via SELECT * FROM DBA_ROLE_PRIVS WHERE DEFAULT_ROLE='YES'.

F . The SET ROLE statement can disable one or more roles for a session.

True. SET ROLE NONE disables all roles, or SET ROLE role1 implicitly disables others not listed,

providing granular control.

Practical Use:Useful for testing or restricting privileges temporarily.

Reference:Oracle Database Security Guide 23ai, "Managing Roles"; Oracle Database SQL Language

Reference 23ai, "SET ROLE."

A .True. ASSM categorizes blocks (e.g., 0-25%, 25-50%) for efficient space use.

B .False. Updates may cause chaining/migration, not always relocation.

C .True. ASSM optimizes inserts into suitable blocks.

D .False. ASSM uses a bitmap, not necessarily the first block.

E .False. PCTFREE is segment-specific, not universally 10%.

Reference:Oracle Database Concepts 23ai, "Automatic Segment Space Management."

A .False. Recovery happens on startup, not shutdown.

B .False. TRANSACTIONAL waits for commits or rollbacks, then disconnects.

C .True. New connections are blocked during shutdown.

D .True. Inactive sessions persist until shutdown completes.

E .True. Active transactions can commit before disconnection.

Reference:Oracle Database Administrator’s Guide 23ai, "Shutting Down a Database."

A .False. Backups are configured via RMAN, not DBCA.

B .True. DBCA allows nonstandard block sizes during DB creation.

C .True. DBCA can register new DBs with EM.

D .False. Block size is fixed post-creation.

E .False. Flashback is enabled via SQL, not DBCA for existing DBs.

Reference:Oracle Database Administrator’s Guide 23ai, "DBCA Features."

Temp UNDO, introduced in Oracle 12c and refined in 23ai, stores undo for global temporary tables

(GTTs) in temporary tablespaces:

A . It permits DML on GTTs even if the database is opened read-only.False. In read-only mode, DML

on GTTs is allowed regardless of temp UNDO, as GTT data is session-private, but temp UNDO doesn’t

specifically enable this.

B . It reduces the amount of UNDO stored in the UNDO tablespace.True. Temp UNDO stores undo in

the temporary tablespace, reducing usage of the permanent UNDO tablespace.

C . It reduces I/Os to the SYSTEM tablespace.True. By avoiding permanent undo, it reduces metadata

updates in the SYSTEM tablespace related to undo management.

D . It reduces the amount of redo generated.True. Temp UNDO changes are not redo-logged to the

same extent as permanent undo, minimizing redo generation.

E . It reduces I/Os to the SYSAUX tablespace.False. SYSAUX is unrelated to undo management; temp

UNDO affects temporary and SYSTEM tablespaces.

Reference:Oracle Database Concepts 23ai, "Temporary Undo."

B .True. Without SCOPE, ALTER SYSTEM defaults to BOTH (memory and SPFILE); PFILE isn’t updated

unless manually recreated.

Reference:Oracle Database Administrator’s Guide 23ai, "Managing Initialization Parameters."

User authentication in Oracle 23ai determines how users (especially administrative ones) connect to

the database. Let’s analyze each option with extensive detail:

A . Operating System authentication may be used for system-privileged administrative users.

True. OS authentication allows users mapped to OS accounts (e.g., ops$oracle) to connect without a

password, often used for administrative users like SYS or SYSTEM. This is configured by creating an

externally authenticated user (e.g., CREATE USER "OPS$ORACLE" IDENTIFIED EXTERNALLY) and relies

on the OS to verify identity.

Mechanics:When a user logs in via sqlplus / as sysdba, Oracle checks the OS user against the dba

group (Unix) or ORA_DBA (Windows). If matched, no password is needed, leveraging OS security.

Practical Use:Common for DBAs managing local instances, reducing password management

overhead.

Edge Case:Requires REMOTE_LOGIN_PASSWORDFILE=NONE for exclusive OS auth, but this isn’t

mandatory if a password file exists alongside.

Historical Note:Introduced in early Oracle versions, this remains a robust option in 23ai for local

admin access.

B . Password authentication must be used for system-privileged administrative users.

False. “Must” is incorrect; password authentication (e.g., sqlplus sys/password) is an option, not a

requirement. OS authentication or password file authentication can also be used for users like SYS.

This option overstates the necessity of password-based login.

Why Incorrect:Oracle’s flexibility allows multiple methods, contradicting the absolute phrasing here.

C . Password File authentication is supported for any type of database user.

False. Password file authentication is restricted to users with SYSDBA, SYSOPER, or similar system

privileges (e.g., SYSBACKUP). Regular users (e.g., HR) can’t use the password file (orapw); they

rely on database authentication (passwords stored in the DB) or external methods.

Mechanics:The password file stores hashed credentials for privileged users, checked during remote

AS SYSDBA logins.

Why Incorrect:Extending this to “any user” ignores Oracle’s security model limiting password file

usage.

D . REMOTE_LOGIN_PASSWORDFILE must be set to EXCLUSIVE to permit password changes for

system-privileged administrative users.

False. REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE allows a dedicated password file for one

instance, enabling password changes via ALTER USER SYS IDENTIFIED BY newpass. However, SHARED

mode also permits changes for SYS, though not for other users added to the file. The “must”

overstates the requirement; it’s sufficient, not necessary.

Mechanics:EXCLUSIVE locks the file to one DB, while SHARED allows multiple DBs to use it, with

restrictions on non-SYS users.

E . Password File authentication must be used for system-privileged administrative users.

True. For remote administrative access (e.g., sqlplus sys/password@orcl as sysdba), a password file is

mandatory when REMOTE_LOGIN_PASSWORDFILE is EXCLUSIVE or SHARED. Local OS authentication

is an alternative, but for network-based admin tasks, the password file is required, making this

statement true in that context.

Mechanics:Set via orapwd (e.g., orapwd file=orapworcl password=oracle entries=10), enabling

remote SYSDBA logins.

Edge Case:If REMOTE_LOGIN_PASSWORDFILE=NONE, only OS auth works locally, but this isn’t the

default or typical setup.

Reference:Oracle Database Security Guide 23ai, "Authentication Methods"; Oracle Database

Administrator’s Guide 23ai, "Managing the Password File."

Dynamic service registration allows a database to automatically register its services with a listener

without manual configuration in LISTENER.ORA. Let’s analyze each option:

A . The definition for LISTENER_1 requires a CONNECT_DATA section to enable dynamic service

registration.

False. The CONNECT_DATA section is part of a client-side TNSNAMES.ORA entry for connecting to a

service, not for listener registration. Dynamic registration is handled by the database’s LREG (Listener

Registration) process, which uses the LOCAL_LISTENER parameter to locate the listener’s address

(e.g., host1.abc.com:1521). No CONNECT_DATA is needed in the listener address definition itself. This

option confuses client connection syntax with listener configuration.

Mechanics:The listener address in TNSNAMES.ORA (LISTENER_1) is sufficient for LREG to find and

register with it, as long as the listener is running at that address.

B . LISTENER_1 must also be defined in the LISTENER.ORA file to enable dynamic service registration.

False. Dynamic registration doesn’t require the listener to be explicitly defined in LISTENER.ORA. The

LOCAL_LISTENER parameter pointing to LISTENER_1 (resolved via TNSNAMES.ORA) tells LREG where

to register services. If the listener is running on host1.abc.com:1521, LREG will find it without a

LISTENER.ORA entry. However, LISTENER.ORA is needed tostartthe listener process, but that’s

separate from dynamic registration.

Practical Note:If LISTENER.ORA isn’t configured, a default listener might run on port 1521, but the

question implies LISTENER_1 is operational.

C . The LREG process registers services dynamically with the LISTENER_1 listener.

True. In Oracle 23ai, the LREG background process (replacing PMON’s registration role in earlier

versions) dynamically registers database services with listeners specified by LOCAL_LISTENER. Here,

LOCAL_LISTENER=LISTENER_1 resolves to host1.abc.com:1521 via TNSNAMES.ORA. LREG

periodically sends service information (e.g., service names, instance details) to the listener, enabling

clients to connect without static configuration.

Mechanics:LREG uses the TNS alias (LISTENER_1) to locate the listener’s IP and port, registers

services like orcl or orclpdb, and updates the listener’s service table. This happens automatically

every 60 seconds or on significant events (e.g., instance startup).

D . Dynamic service registration cannot be used for this database instance.

False. The setup (LOCAL_LISTENER set and a valid TNSNAMES.ORA entry) explicitly supports dynamic

registration. No blockers (e.g., REGISTRATION_EXCLUDED_LISTENERS) are mentioned, so LREG can

function normally.

E . There are two listeners named LISTENER and LISTENER_1 running simultaneously using port 1521

on the same host as the database in LISTENERS.

False. The question mentions only LISTENER_1 in the SPFILE and TNSNAMES.ORA. There’s no

evidence of a second listener (LISTENER) or a LISTENERS configuration (possibly a typo). Two listeners

can’t share the same port (1521) on the same host due to port conflicts unless explicitly configured

with different IPs, which isn’t indicated here.

Reference:Oracle Database Net Services Administrator’s Guide 23ai, "Understanding Dynamic Service

Registration"; Oracle Database Reference 23ai, "LOCAL_LISTENER Parameter."

Optimizer Statistics drive the cost-based optimizer’s query plans. Let’s dissect each option:

A . Optimizer Statistics are collected automatically by an automatic maintenance job that runs during

predefined maintenance windows.

True. Oracle 23ai uses the AutoTask framework to gather stats automatically during maintenance

windows (e.g., nightly 10 PM–2 AM). The GATHER_STATS_PROG job, managed by

DBMS_AUTO_TASK_ADMIN, collects stats for stale or missing objects.

Mechanics:Controlled by STATISTICS_LEVEL=TYPICAL (default) and the

DEFAULT_MAINTENANCE_PLAN. It prioritizes objects with >10% changes (stale stats) or no stats.

Practical Use:Ensures stats are current without manual intervention, critical for dynamic workloads.

Edge Case:Disabled if STATISTICS_LEVEL=BASIC or the job is manually disabled via

DBMS_AUTO_TASK_ADMIN.DISABLE.

B . Optimizer Statistics are collected in real-time as data is inserted, deleted, or updated.

False. Stats aren’t updated in real-time; this would be too resource-intensive. Instead, Oracle tracks

changes (e.g., via DBA_TAB_MODIFICATIONS) and updates stats periodically via AutoTask or

manually. Real-time stats exist in 23ai for specific cases (e.g., GATHER_TABLE_STATS with

REAL_TIME_STATS), but it’s not the default.

Why Incorrect:Real-time collection would degrade performance for OLTP systems, contradicting

Oracle’s batch approach.

C . Optimizer Statistics can be manually collected at multiple levels using

DBMS_STATS.GATHER_*_STATS PL/SQL procedures.

True. The DBMS_STATS package offers granular control: GATHER_TABLE_STATS,

GATHER_SCHEMA_STATS, GATHER_DATABASE_STATS, etc., allowing stats collection for tables,

schemas, or the entire database.

Mechanics:Example: BEGIN DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES'); END;. Options

like ESTIMATE_PERCENT and DEGREE fine-tune the process.

Practical Use:Used for immediate stats updates post-DML or for custom schedules outside

maintenance windows.

Edge Case:Overuse can lock stats (e.g., FORCE=TRUE), requiring careful management.

D . Optimizer Statistics are collected by the Statistics Advisor.

False. The Statistics Advisor (new in 23ai) analyzes and recommends stats improvements but doesn’t

collect them. Collection is still via DBMS_STATS or AutoTask.

Why Incorrect:It’s a diagnostic tool, not an executor.

E . Optimizer Statistics are collected automatically by Automatic Workload Repository (AWR)

Snapshot.

False. AWR snapshots capture performance metrics (e.g., wait times), not optimizer stats. Stats

collection is a separate process via AutoTask or manual commands.

Why Incorrect:AWR and stats collection serve distinct purposes—monitoring vs. optimization.

Reference:Oracle Database SQL Tuning Guide 23ai, "Managing Optimizer Statistics"; Oracle Database

PL/SQL Packages and Types Reference 23ai, "DBMS_STATS Package."

A .Incorrect syntax and view (pdb_spfile$ is not a valid view; PDB_SPFILE$ exists but lacks value$).

B .From CDB root, V$PARAMETER shows all parameters, not PDB-specific ones.

C .pdb_spfiles is not a valid view; PDB_SPFILE$ exists but requires scoping to a PDB.

D .True. From the PDB, V$PARAMETER shows parameters specific to that PDB, including inherited

and PDB-modified values, with ISPDB_MODIFIABLE indicating alterability.

Reference:Oracle Multitenant Administrator’s Guide 23ai, "Managing PDB Initialization Parameters."

To change a PDB_MODIFIABLE parameter, the PDB must be in READ WRITE mode, allowing ALTER

SYSTEM commands to modify parameters stored in the PDB’s SPFILE or memory. READONLY modes

prevent modifications, and no RESTRICTED WRITE ONLY mode exists.

Reference:Oracle Multitenant Administrator’s Guide 23ai, "Modifying PDB Parameters."

A . Dependent indexes become UNUSABLE.False. SHRINK SPACE COMPACT does not affect index

usability; only SHRINK SPACE without COMPACT may require index maintenance if CASCADE is

omitted.

B . ... starting toward the end of the ORDERS segment.False. Rows are compacted toward the

beginning of the segment, not the end.

C . Only queries are allowed ...False. Both queries and DML are allowed during SHRINK SPACE

COMPACT as it’s an online operation.

D . The high-water mark (HWM) of ORDERS is adjusted.False. COMPACT moves rows but doesn’t

adjust the HWM; the full SHRINK SPACE command is needed for HWM adjustment.

E . Queries and DML statements are allowed ...True. This is an online operation in 23ai, allowing

concurrent access.

F . ... starting from the beginning of the ORDERS segment.True. Rows are moved to fill free space

from the segment’s start.

Reference:Oracle Database SQL Language Reference 23ai, "ALTER TABLE ... SHRINK SPACE."

Logical structures (e.g., segments, extents) map to physical structures (e.g., data files, blocks). Let’s

dissect each option:

A . A segment can span multiple data files in some tablespaces.

True. A segment (e.g., a table or index) is a logical entity stored in a tablespace. In a smallfile

tablespace (default in Oracle), a segment’s extents can span multiple data files if the tablespace has

multiple files and space allocation requires it. This is common in large tables or when autoextend

adds new files.

Mechanics:Oracle allocates extents across available data files in a round-robin fashion (with ASSM) or

as needed, ensuring the segment’s data is distributed. This doesn’t apply to bigfile tablespaces,

which use a single data file.

Example:A 10GB table in a tablespace with two 5GB data files will span both.

B . Segments can span multiple tablespaces.

False. A segment is confined to a single tablespace. Oracle enforces this to maintain logical

separation (e.g., a table’s data stays in its assigned tablespace). Partitioned tables can have partitions

in different tablespaces, but each partition is a separate segment.

Why Not:The segment header and extent map reside in one tablespace, preventing cross-tablespace

spanning for a single segment.

C . A segment might have only one extent.

True. A segment starts with one extent upon creation (e.g., a small table or index). If no further

growth occurs, it remains a single-extent segment. This is common with small objects or when

INITIAL extent size suffices.

Mechanics:In locally managed tablespaces (default), the initial extent is allocated based on INITIAL or

tablespace defaults (e.g., 64KB), and additional extents are added only as needed.

D . All tablespaces may have one or more data files.

False. Bigfile tablespaces are restricted to one data file (up to 128TB). Smallfile tablespaces

(traditional) can have multiple data files (up to 1022), but the “all” phrasing makes this false due to

bigfile exceptions.

Clarification:The question’s intent may assume smallfile tablespaces, but Oracle 23ai supports both

types.

E. A segment’s blocks can be of different sizes.

False. All blocks in a segment use the tablespace’s block size (e.g., 8KB). While a database can have

tablespaces with different block sizes (e.g., 8KB, 32KB), a single segment’s blocks are uniform, as it

resides in one tablespace.

Reference:Oracle Database Concepts 23ai, "Logical Storage Structures"; Oracle Database

Administrator’s Guide 23ai, "Managing Tablespaces."