1z0-182 Questions and Answers

A.False. ORCL is a TNS alias, not necessarily the DB name.

B.True. Must map to a connect descriptor (e.g., HOST=... PORT=1531) in tnsnames.ora.

C.False. It’s not the server name but a network alias.

D.False. Client-side tnsnames.ora is used, not server-side.

E.False. SERVICE_NAMES is server-side, not client-side.

A.False. LREG handles registration in 23ai, not PMON.

B.False. CKPT (Checkpoint) process updates the control file.

C.True. PMON cleans up after failed processes (e.g., releases locks).

D.True. PMON rolls back uncommitted transactions of failed processes.

E.False. SMON (System Monitor) manages temporary segment cleanup.

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.

Explanation: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.

Explanation: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.

Explanation: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.

Explanation: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.

Explanation: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.

A.False. LMT is default in 23ai.

B.True. Bigfile tablespaces have one data file only.

C.True. AUTOEXTEND works with bigfile data files.

D.False. Block size defaults to DB_BLOCK_SIZE (e.g., 8K).

E.False. No size comparison requirement.

A.False. “Logging out to the abc database” is nonsensical; abc is a script, not a database.

B.False. SQL*Plus finds abc.sql in the current directory by default; a full path isn’t required unless it’s elsewhere.

C.False. The first @ specifies the TNS alias (orcl), the second runs the script (@abc); this is valid syntax.

D & E.True (identical options). The command connects to orcl via TNSNAMES.ORA, authenticates HR, and executes abc.sql.

Mechanics:sqlplus hr/hr@orcl resolves orcl to a listener address, connects, and@abc runs the script post-login.

A.False. Dispatchers are for shared servers, not dedicated.

B.False. Dedicated processes die with the session.

C.True. Direct client-server communication occurs.

D.False. No dispatchers in dedicated mode.

E.True. Listener spawns for local/remote clients.

F.True. Local logins (e.g., sqlplus /) bypass the listener.

A.False. External tables are read-only; no DELETE.

B.False. Require OPEN state, unlike V$ views.

C.False. No UPDATE support; read-only.

D.True. Queryable like regular tables without loading.

E.True. Data Pump can unload query results to files.

A.False. Revoking from USR1 doesn’t cascade due to WITH ADMIN OPTION.

B.True. Revoking from USR2 cascades to USR3 because USR2 granted it.

C.False. DBA can revoke the full privilege, not just ADMIN OPTION.

D.False. USR1 can’t revoke from USR3 directly; only the grantor (USR2) can.

E.True. WITH ADMIN OPTION breaks the revoke chain from USR1 onward.

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.

Explanation: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.

Explanation: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.

Explanation: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.

Explanation: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.

Explanation: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.

A.True. CREATE SPFILE FROM PFILE works when idle.

B.False. Includes dynamic parameters too.

C.True. CREATE PFILE FROM SPFILE generates a PFILE.

D.False. A PFILE can start the instance if no SPFILE exists.

E.True. Dynamic parameters (e.g., DB_CACHE_SIZE) are modifiable.

D.True. The .pdb file (archive) contains the manifest and data files; all must be copied to the target CDB for plugging.

A-C.False. The archive alone isn’t enough; all components are needed.

A.False. Connect Time Failover doesn’t need TAF; TAF is for runtime failover.

B.False. Source Routing doesn’t mandate encryption.

C.True. Needs multiple addresses (e.g., (ADDRESS_LIST=...)) for failover.

D.False. Load balancing works with tnsnames.ora, no name server required.

E.True. Balances connections across shared server dispatchers.

A.False. STOP_JOB=IMMEDIATE halts the job, not backgrounds it.

B.False. You can monitor before stopping or after restarting.

C.True. Before stopping, you can reattach with expdp attach=exp_db_full.

D.True. STOP_JOB=IMMEDIATE terminates the job instantly.

A.False. Undo is stored in a dedicated undo tablespace, not SYSTEM.

B.True. Large transactions can span multiple undo segments.

C.False. Undo isn’t stored in SYSAUX.

D.True. Undo segments extend as needed, space permitting.

E.False. Undo doesn’t “wrap”; new extents are allocated.

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.

A.False. Diagnostic collection is ADR, not AMT.

B.True. Resource Manager allocates resources to AMTs.

C.True. Scheduler defines maintenance windows.

D.False. Packaging is IPS/ADR, not AMT.

E.False. Alert log is separate from AMTs.

F.True. Tasks like stats collection are AMTs.

A.False. External tables are read-only.

B.True. Defines how external data (e.g., CSV) is mapped to SQL.

C.False. Data is external, not in the DB.

D.False. Storage is external; Oracle doesn’t define it.

Background processes manage core database operations. Let’s break it down:

A. Creating dedicated server connections.

Explanation:False. Dedicated server connections are created by the listener, not background processes. The listener hands off the connection to a server process (e.g., ora_s000), which isn’t a background process.

B. Registering services with Oracle Net listeners.

Explanation:True. The LREG process (Listener Registration, formerly PMON’s role) registers services with listeners, using parameters like LOCAL_LISTENER.

Mechanics:LREG sends service details (e.g., orcl) to the listener every 60 seconds or on startup.

C. Writing redo to log files.

Explanation:True. The LGWR (Log Writer) process writes redo entries from the redo log buffer to online redo log files, ensuring transaction durability.

Mechanics:Triggered by commits, log buffer fills, or checkpoints.

D. Writing dirty database block images from the buffer cache.

Explanation:True. The DBWn (Database Writer) processes write modified (dirty) blocks from the buffer cache to data files, maintaining consistency.

Mechanics:Occurs at checkpoints or when free buffers are needed.

E. Reading database blocks into the buffer cache.

Explanation:False. Server processes (foreground) read blocks into the buffer cache during user queries, not background processes.

A.False. DBCA can’t change character sets post-creation.

B.False. Listeners are managed via NetCA or lsnrctl.

C.True. DBCA can switch server modes for existing DBs.

D.False. Tablespaces are created via SQL, not DBCA for existing DBs.

E.True. DBCA can generate clone templates from existing DBs.

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.

A.False. No checkpoint occurs with ABORT.

B.False. Instance recovery, not media recovery, is needed.

C.True. Crash recovery rolls back uncommitted changes on startup.

D.False. Files aren’t closed cleanly with ABORT.

E.True. Shutdown doesn’t roll back; recovery does.

A.False. One instance can’t mount multiple DBs without multitenant.

B.True. RAC allows multiple instances to share one DB.

C.False. Same as A; not possible without CDB.

D.True. An instance can start in NOMOUNT with no DB.

E.True. Standard single-instance configuration.

A.False. Requires MOUNT to access data files.

B.False. Requires MOUNT for redo logs.

C.True. CREATE DATABASE runs in NOMOUNT.

D.True. CREATE CONTROLFILE is possible.

E.False. Recovery needs MOUNT or OPEN.