Free Oracle 1z0-182 Practice Exam with Questions & Answers | Set: 2

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.

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.

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.

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.

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.

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.

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

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

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

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

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

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.True. Auditing tracks privilege use, enforcing least privilege.

B.True. Revoking unnecessary PL/SQL access limits capabilities.

C.True. ACLs restrict network access precisely.

D.False. TRUE relaxes dictionary access, violating least privilege.

E.False. TRUE allows risky OS auth, weakening security.

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.

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.