1z0-182 Oracle Database 23ai Administration Associate Questions and Answers

C.True. Hot cloning requires the source PDB (PDB1) to be read-only to ensure consistency during the clone. Others are not prerequisites.

A.False. Session limits don’t tie to password changes.

B.False. CPU limits don’t lock accounts.

C.False. No direct “logged-in days” parameter.

D.True. PASSWORD_REUSE_MAX=UNLIMITED prevents reuse.

E.True. PASSWORD_LOCK_TIME sets inactivity lockout.

A.True. PDBs inherit default schemas from the seed.

B.True. Metadata tablespaces (e.g., SYSTEM, SYSAUX) are created.

C.False. ADMIN1 (not ADMIN) isn’t granted SYSDBA.

D.False. No read-only requirement post-creation.

E.False. PDBs start in MOUNTED state, not open.

F.True. ADMIN1 is a local user with CONNECT role only.

D.True. The Shared Pool caches data dictionary metadata (e.g., table definitions) in the Library Cache and Dictionary Cache, accessible to all processes. Others serve different purposes (e.g., Large Pool for backups).

A.False. Not all trace files indicate errors; some log normal activity.

B.True. Server processes write traces (e.g., ora_s000_123.trc) to the file system.

C.True. Background processes (e.g., ora_lgwr_123.trc) also write traces.

D.False. Names include process type and PID, not just DB name and sequence.

E.False. Traces go to DIAGNOSTIC_DEST, not necessarily FRA unless configured there.

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.True. sqlplus @script.sql works from shell scripts.

B.False. RMAN uses its own client, not SQL*Plus.

C.False. Bundled with Oracle DB software.

D.False. Command-line only; no GUI in 23ai.

E.True. @script runs scripts interactively.

F.True. Commands like SHOW PARAMETER are unique.

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.

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.

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%.

A.True. ROW MOVEMENT must be enabled for SHRINK SPACE.

B.False. Indexes and IOTs can be shrunk with CASCADE.

C.False. No such constraint requirement.

D.True. ASSM is required for online shrinking.

E.False. No primary key requirement.

A.True. Without DB_CREATE_ONLINE_LOG_DEST_n, redo logs and control files default to DB_RECOVERY_FILE_DEST.

B.False. If only DB_CREATE_FILE_DEST is set, redo logs and control files also use it unless overridden.

C.True. DB_CREATE_ONLINE_LOG_DEST_1 becomes the default for data files and temp files if DB_CREATE_FILE_DEST is unset.

D.True. DB_CREATE_FILE_DEST serves as the default for all file types if no log-specific parameter is set.

E.False. Data files and temp files would also use DB_CREATE_ONLINE_LOG_DEST_1 if no other parameter is specified.

F.False. No such requirement exists; DB_RECOVERY_FILE_DEST operates independently.

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.

A.True. Captures unused self-owned object privileges.

B.False. SYS is excluded from capture.

C.False. No exclusion option for admin users.

D.False. Direct role grants aren’t separately analyzed.

E.True. Tracks indirect role privileges used.

F.True. Identifies used self-owned privileges.

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.

This command moves/renames a data file online. Let’s evaluate:

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

Explanation:True. Introduced in 12c and refined in 23ai, MOVE DATAFILE is an online operation, allowing DML (e.g., INSERT, UPDATE) on tables within the file. Oracle ensures consistency via redo and undo.

Mechanics:The file is copied to the new location/name while tracking changes, then switched atomically.

Practical Use:Minimizes downtime in production systems.

B. If Oracle Managed Files (OMF) is used, then the file is renamed but moved to DB_CREATE_FILE_DEST.

Explanation:False. With OMF, omitting the TO clause would place the file in DB_CREATE_FILE_DEST, but here, TO '\u01/sales01.dbf' explicitly specifies the target, overriding OMF defaults.

Mechanics:OMF only applies if the destination is unspecified (e.g., MOVE DATAFILE ... without TO).

Why Incorrect:Explicit path trumps OMF behavior.

C. The tablespace containing SALES1.DBF must be altered READ ONLY before executing the command.

Explanation:False. READ ONLY isn’t required; the operation is online and supports active use.

Why Incorrect:Conflicts with Oracle’s online move feature.

D. The file is renamed and stored in the same location.

Explanation:True. The command renames /u01/sales1.dbf to /u01/sales01.dbf (correcting \u01 to /u01 as a Windows/Unix typo), keeping it in /u01/. REUSE allows overwriting if sales01.dbf exists.

Mechanics:File is copied and renamed in-place within the same filesystem directory.

E. The tablespace containing SALES1.DBF must be altered OFFLINE before executing the command.

Explanation:False. Offline mode isn’t needed; the online move handles activetablespaces.

A.True. Oracle 23ai uses DEFAULT_MAINTENANCE_PLAN with ORA$AUTOTASK subplan for maintenance tasks (e.g., stats gathering). Others are incorrect or speculative.

A.False. Not limited to CDB; PDBs can use common policies.

B.True. Common policies apply at CDB level.

C.False. Application Root uses app-specific policies.

D.False. “COMMON option” isn’t a feature; policies are common by creation context.

E.True. A common user can create policies for PDBs.

A.True. Auditing tracks suspicious actions.

B.False. That’s monitoring, not auditing.

C.False. Auditing detects, doesn’t prevent.

D.False. Corruption is a reliability issue.

E.True. Ensures privileged user compliance.

A.True. After ALTER PLUGGABLE DATABASE ... UNPLUG, CDB_PDBS.STATUS shows UNPLUGGED.

B.False. UNAVAILABLE isn’t a valid status here.

C.False. REMOVED isn’t used; UNPLUGGED persists until dropped.

D.False. DISABLED isn’t a PDB state in this context.

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

A. Roles must be password protected.

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

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

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

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

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

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

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.True. Integrates with MOS for patches and support.

B.True. Manages Exadata, etc.

C.True. Covers WebLogic and other middleware.

D.False. Web-based, but not built into the DB.

E.False. Can manage stopped instances via agents.

A.True. Migration updates index ROWIDs.

B.True. Updates can migrate rows if space is insufficient.

C.True. Large updates can chain rows across blocks.

D.False. Chained rows may span blocks but aren’t required to.

E.False. Inserts don’t migrate; they chain if too large initially.

The Automatic Workload Repository (AWR) is a cornerstone of Oracle’s self-tuning capabilities, collecting and storing performance statistics for analysis. Let’s dissect each option:

A. Automatic capture of statistical information from the SGA and storing it in the AWR using Automatic Database Diagnostic.

Explanation:True. AWR automatically captures statistics (e.g., wait events, SQL stats) from the System Global Area (SGA) via the MMON (Manageability Monitor) process, storing them in the AWR repository (in SYSAUX). This is part of the Automatic Database Diagnostic Monitor (ADDM) framework, though “Automatic Database Diagnostic” likely refers to this broader mechanism.

Mechanics:Snapshots are taken hourly by default (configurable via DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS), persisting data like DBA_HIST_SYSSTAT for self-tuning analysis.

Practical Use:Enables historical performance tracking, feeding tools like ADDM and SQL Tuning Advisor.

Edge Case:If STATISTICS_LEVEL=BASIC, AWR collection is disabled, haltingself-tuning.

Historical Note:Introduced in 10g, enhanced in 23ai for finer granularity.

B. ADDM, a server-based expert that reviews database performance statistics captured by Snapshots to identify potential problems before system performance degrades noticeably.

Explanation:True. The Automatic Database Diagnostic Monitor (ADDM) analyzes AWR snapshots to proactively detect issues (e.g., high CPU usage, I/O bottlenecks) and recommend fixes. It runs automatically after each snapshot in maintenance windows.

Mechanics:ADDM uses DBA_ADVISOR_FINDINGS to log issues, leveraging AWR data like DBA_HIST_SQLSTAT. Example: It might suggest adding an index for a slow query.

Practical Use:Prevents performance degradation in production systems by catching trends early.

Edge Case:Limited by snapshot frequency; real-time issues may need manual intervention.

C. Automatic Diagnostic Collector used to capture and store database errors and hung analysis.

Explanation:False. No such tool exists as an “Automatic Diagnostic Collector” in AWR context. This likely confuses the Incident Packaging Service (IPS) or ADR (Automatic Diagnostic Repository), which handles errors but isn’t AWR-based or self-tuning.

Why Incorrect:ADR collects trace files and logs, not AWR statistics, and isn’t part of self-tuning.

D. Automatic population of performance views (V$ views) from statistical data stored in AWR repository and using Automatic Database Diagnostic.

Explanation:False. V$ views (e.g., V$SESSION) are real-time memory structures in the SGA, not populated from AWR, which is historical (e.g., DBA_HIST_*). AWR doesn’t back-feed V$ views; the reverse occurs via snapshots.

Why Incorrect:Misunderstands the data flow; AWR is a sink, not a source for V$ views.

E. Automatic Application Tracing used to collect High-Load SQL statements and statistics.

Explanation:False. No “Automatic Application Tracing” exists as an AWR tool. SQL tracing (e.g., DBMS_MONITOR) is manual, and high-load SQL is captured by AWR indirectly via V$SQL snapshots, not a distinct tracing tool.

Why Incorrect:Conflates manual tracing with AWR’s passive collection.