Oracle 1z0-182 - Oracle Database 23ai Administration Associate

A.False. AWR doesn’t create PDBs.

B.True. AWR stats aid migration planning (e.g., performance baselines).

C.False. AWR doesn’t convert CDB to Non-CDB.

D.False. SID management isn’t AWR’s role.

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.

Dictionary-managed tablespaces (DMTs) use the data dictionary for extent management, whilelocally managed tablespaces (LMTs) use bitmaps. Let’s evaluate compatibility:

A. Capacity planning growth reports based on historical data in the Automatic Workload Repository (AWR).

Explanation:True. AWR tracks space usage (e.g., DBA_HIST_TBSPC_SPACE_USAGE) regardless of tablespace type, enabling growth reports for both DMTs and LMTs.

Mechanics:MMON collects metrics like segment growth, stored in SYSAUX, accessible via EM or scripts.

Practical Use:Helps predict when to add data files, universal across management types.

B. Online table segment shrink.

Explanation:False. ALTER TABLE ... SHRINK SPACE requires LMTs with Automatic Segment Space Management (ASSM), unavailable in DMTs, which lack bitmap-based free space tracking.

Why Incorrect:DMTs use freelists, incompatible with shrink operations.

C. Online index segment shrink.

Explanation:False. Like tables, ALTER INDEX ... SHRINK SPACE requires LMTs with ASSM, not supported in DMTs.

Why Incorrect:Same limitation as B; DMTs can’t compact online.

D. Oracle Managed Files (OMF).

Explanation:True. OMF automates file naming and placement (via DB_CREATE_FILE_DEST) for both DMTs and LMTs, agnostic to extent management.

Mechanics:Example: CREATE TABLESPACE ts1; creates an OMF file in either type.

Edge Case:DMTs are rare in 23ai, but OMF still applies.

E. Automatic data file extension (AUTOEXTEND).

Explanation:True. AUTOEXTEND ON allows data files to grow as needed, supported in both DMTs and LMTs since early versions.

Mechanics:ALTER DATABASE DATAFILE ... AUTOEXTEND ON NEXT 100M; works universally.

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.

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.

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

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