1z0-182 Practice Exam Questions with Answers Oracle Database 23ai Administration Associate Certification

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. Background processes (e.g., PMON) start.

B.False. Consistency checks require MOUNT.

C.False. Control files open in MOUNT.

D.True. SGA is allocated at startup.

E.False. SYS access requires OPEN.

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.

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. V$ views reflect real-time memory data, not consistent snapshots.

B.True. V$FIXED_TABLE lists all V$ views.

C.True. Data comes from memory structures and data dictionary metadata.

D.False. Owned by SYS, not SYSTEM.

E.False. Some V$ views are accessible in MOUNT state.

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.

A.False. No such restriction exists; DB_32K_CACHE_SIZE is independent of DB_CACHE_SIZE.

B.True. A nonstandard block size (32K) requires a corresponding cache (DB_32K_CACHE_SIZE) set to a non-zero value within SGA limits.

C.False. OS block size is irrelevant; Oracle manages its own block sizes.

D.False. No requirement for it to exceed DB_CACHE_SIZE.

E.True. The file system must have 100G available for the datafile.

SQL*Loader (sqlldr) loads data from external files into Oracle tables. The command sqlldr u1/oracle TABLE=emp uses defaults since no control file is specified. Let’s evaluate:

A. It overwrites the data for Alan and adds data for Curl and Bob.

Explanation:False. SQLLoader’s default mode is APPEND, not REPLACE. It doesn’t “overwrite” existing rows unless REPLACE or TRUNCATE is specified in a control file. Here, row 1, Alan, 2 exists, and SQLLoader will either skip it (if a primary key rejects duplicates) or raise an error, but it won’t overwrite. 3, Curl, 4 and 4, Bob, 4 are appended.

Mechanics:Without a control file, SQL*Loader assumes APPEND and matches columns positionally (ENO, ENAME, DN).

B. It generates a log that contains control file entries, which can be used with normal SQL*Loader operations.

Explanation:True. SQL*Loader always generates a log file (e.g., emp.log) when invoked. With no control file specified, it auto-generates one internally and logs it, including entries like LOAD DATA INFILE 'emp.dat' APPEND INTO TABLE emp FIELDS TERMINATED BY ',' (ENO, ENAME, DN). This can be reused.

Practical Use:The log’s control section is editable for future runs (e.g., changing to REPLACE).

C. It appends data from EMP.DAT to EMP.

Explanation:True. Default behavior without a control file is APPEND, adding new rows (3, Curl, 4 and 4, Bob, 4) to EMP. Existing rows (1, Alan, 2, 2, Ben, 2) remain unless constrained (e.g., unique key violations).

Mechanics:SQL*Loader processes each line of emp.dat, skipping duplicates if constrained, appending otherwise.

D. It generates a SQL script that it uses to load data from EMP.DAT to EMP.

Explanation:False. SQL*Loader doesn’t generate SQL scripts; it uses direct path or conventional path loading, not SQL scripts. The log contains control file syntax, not a script.

E. It overwrites all data in EMP with data from EMP.DAT.

Explanation:False. REPLACE or TRUNCATE would overwrite, but these require a control file with those options. Default APPEND preserves existing data.

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

Automatic Maintenance Tasks (AMTs) in 23ai optimize database performance. Let’s explore:

A.False. SQL Access Advisor exists but isn’t an AMT; it’s manual or invoked separately.

B.False. SQL Performance Analyzer assesses change impacts but isn’t part of AMTs.

C.False. No “Automatic Index Advisor” exists as an AMT; Auto Index is a separate feature.

D.True. The SPM Evolve Advisor task (part of ORA$AUTOTASK) automatically evolves SQL plan baselines, testing and accepting new plans.

Mechanics:Runs in maintenance windows, managed by DBMS_SPM.

E.True. SQL Tuning Advisor (STA) runs automatically via AMTs, tuning high-load SQL.

Mechanics:Identifies candidates from AWR and suggests indexes, profiles, etc.

A.True. ADR is a file system directory structure (e.g., /u01/app/oracle/diag), external to the database.

B.False. ADR also stores diagnostics for non-database components (e.g., ASM, listener).

C.False. It’s file-based, not schema-based.

D.True. ADR logs (e.g., alert logs, trace files) are accessible even if the instance is down.

E.True. DIAGNOSTIC_DEST sets the ADR base directory.

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.

A.True. DUAL is public; any user with SELECT can query it.

B.True. One row, one VARCHAR2 column (DUMMY).

C.False. Fixed at one row.

D.False. Not restricted to SYS.

E.False. One row, one column only.

F.False. Can return expressions, not just constants.

A.False. Schema owners need EXECUTE on DBMS_FGA.

B.False. Not limited to SYSDBA.

C.True. AUDIT_ADMIN includes FGA creation rights.

D.False. No such role exists.

E.True. DBMS_FGA.ADD_POLICY requires this privilege.

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.

With OMF enabled, Oracle automatically manages file creation. The command ALTER TABLESPACE sales_q1 ADD DATAFILE without a file specification relies on initialization parameters:

A. Specify a path in the DATAFILE clause ... with at least 100 MB of available space.False. With OMF, explicitly specifying a path overrides OMF behavior, but it’s not required for success if OMF parameters are set correctly.

B. Add the AUTOEXTEND ON clause with NEXT set to 100M.False. AUTOEXTEND is optional and affects file growth, not the initial creation success, which depends on available space in the OMF location.

C. Ensure that DB_RECOVERY_FILE_DEST and DB_CREATE_FILE_DEST each specify locations with at least 50 MB of available space.True. If both parameters are set,Oracle may use either for data files (depending on context), and sufficient space (e.g., 50 MB minimum for a smallfile) ensures success.

D. Ensure that DB_CREATE_FILE_DEST specifies a location with at least 100 MB of available space.True. This is the primary OMF parameter for data files; sufficient space (typically 100 MB minimum for a new file) guarantees the command succeeds.

E. Ensure that DB_RECOVERY_FILE_DEST and DB_CREATE_FILE_DEST each specify locations with at least 50 MB of available space.False. This is redundant with C; only one needs sufficient space, though C’s phrasing makes it a valid independent action.

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.False. RAC can run with one instance (degraded mode).

B.True. Possible, though not typical for RAC.

C.False. No minimum of three.

D.True. RAC instances run on separate nodes.

E.False. Instances are DB-specific in RAC.

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.

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.

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

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

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

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

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

A.False. Net Manager is local, not centralized.

B.False. NetCA can run standalone.

C.True. EMCC manages service names centrally.

D.True. EMCC configures listeners on managed targets.

E.False. lsnrctl starts a default listener if no listener.ora exists.

F.True. Net Manager configures local tnsnames.ora.

A. REDO is used for read consistency.False. Read consistency is achieved using UNDO, which provides a consistent view of data as of a specific point in time. REDO logs changes for recovery, not consistency.

B. UNDO is used for some flashback operations.True. Flashback features like Flashback Query and Flashback Table rely on UNDO to reconstruct past states of data.

C. UNDO is used for read consistency.True. UNDO stores pre-change data, enabling multi-version read consistency for queries.

D. Both REDO and UNDO can be multiplexed.False. REDO logs can be multiplexed (mirrored across multiple destinations), but UNDO is managed within a single UNDO tablespace per instance (though it can have multiple data files).

E. REDO is used for ROLLBACK.False. ROLLBACK uses UNDO to revert changes; REDO logs the changes but isn’t used to undo them.

F. REDO is used for instance recovery.True. REDO logs are replayed during instance recovery to reapply committed changes after a crash.