1z0-182 Sample Questions Answers

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.

Database links enable cross-database queries in Oracle. Let’s analyze each option with extensive detail:

A. A public database link can be created only by SYS.

Explanation:False. Any user with the CREATE PUBLIC DATABASE LINK privilege (not just SYS) can create a public link (e.g., CREATE PUBLIC DATABASE LINK remote_db CONNECT TO scott IDENTIFIED BY tiger USING 'orcl'). While SYS typically has this privilege, it’s not exclusive to SYS.

Mechanics:Privilege is granted via GRANT CREATE PUBLIC DATABASE LINK TO user;. Public links are accessible to all users in the local DB.

Why Incorrect:Overly restrictive; Oracle’s security model allows delegation.

B. A database link can be created only between two Oracle databases.

Explanation:False. Database links can connect to non-Oracle databases using Oracle Heterogeneous Services or gateways (e.g., ODBC or JDBC drivers), such as linking to SQL Server. Example: CREATE DATABASE LINK mssql_link USING 'hsodbc';.

Mechanics:Requires configuration of hs_ parameters in init.ora and a gateway listener.

Historical Note:Heterogeneous links were introduced in 8i, expanded in 23ai for cloud integration.

C. A database link created in a database allows a connection from that database's instance to the target database's instance, but not vice versa.

Explanation:True. A database link is unidirectional; it enables queries from the local instance to the remote instance (e.g., SELECT * FROM emp@remote_db), but the remote instance can’t use it to query back unless a separate link is created there.

Mechanics:Stored in DBA_DB_LINKS, the link defines a one-way connection via a TNS alias or connect string.

Practical Use:Ensures controlled access; bidirectional access requires explicit configuration.

Edge Case:Loops are prevented unless explicitly designed with mutual links.

D. A public database link can be used by a user connected to the local database instance to connect to any schema in the remote database instance.

Explanation:False. Public links allow all local users to use them, but access to remote schemas depends on the link’s credentials (e.g., CONNECT TO scott) and the user’s remote privileges. “Any schema” overstates it; access is limited to what the link’s user can see.

Why Incorrect:Misrepresents privilege scope; remote schema access isn’t universal.

E. Private database link creation requires the same user to exist in both the local and the remote databases.

Explanation:False. A private link (e.g., CREATE DATABASE LINK my_linkCONNECT TO scott IDENTIFIED BY tiger USING 'orcl') requires the remote user (scott) to exist, but the local creator (e.g., HR) need not match. The link is owned locally and authenticated remotely.

Mechanics:Only the CREATE DATABASE LINK privilege is needed locally.

A.True. Dispatchers return responses to clients.

B.False. Dispatchers manage queues, not write directly.

C.False. Responses are connection-specific, not broadcast.

D.True. Dispatchers receive client requests.

E.False. Queues are common, not per-connection.

F.True. Dispatchers monitor the response queue.

Unified Auditing in Oracle 23ai consolidates audit records into a single trail, managed via the DBMS_AUDIT_MGMT package. Let’s evaluate each option with extensive detail:

A. Only viewed audit records can be purged from Unified Audits.

Explanation:False. There’s no concept of “viewed” audit records restricting purging. Unified Audit records (stored in UNIFIED_AUDIT_TRAIL) can be purged based on time, policy, or manual intervention, regardless of whether they’ve been viewed. This option misrepresents audit management capabilities.

Mechanics:Purging is controlled by retention policies or explicit commands, not view status. For example, records older than a set retention period (e.g., 90 days via DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY) are eligible for purging.

Why Incorrect:No Oracle documentation ties purging to viewing, making this a fabricated limitation.

B. Use DBMS_AUDIT_MGMT.CREATE_PURGE_JOB as a privileged user toschedule an automatic purge job.

Explanation:True. This procedure creates a scheduled job to automatically purge audit records based on a retention policy or custom criteria. It’s a standard method for ongoing audit trail maintenance, requiring privileges like AUDIT_ADMIN.

Mechanics:Example: BEGIN DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, JOB_FREQUENCY => 'DAILY', JOB_STATUS => DBMS_AUDIT_MGMT.JOB_ENABLED); END;. This schedules daily purges of old records, using the retention period set by SET_AUDIT_TRAIL_PROPERTY.

Practical Use:Ideal for production environments to prevent the audit trail from growing indefinitely (e.g., avoiding tablespace exhaustion in SYSAUX).

Edge Case:If no retention period is set, the job purges nothing until configured, highlighting the need for prior setup.

C. Only the owner of a Unified Audit Policy can purge audit records by resetting the policy.

Explanation:False. Audit policies don’t have “owners” in the traditional sense; they’re created by users with AUDIT_ADMIN and managed globally. Resetting or disabling a policy (e.g., NOAUDIT POLICY my_policy) stops auditing but doesn’t purge existing records. Purging is a separate operation via DBMS_AUDIT_MGMT.

Why Incorrect:This conflates policy management with audit trail cleanup, which are distinct in Oracle.

D. Use DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL as a privileged user to manually purge audit records.

Explanation:True. This procedure manually purges all Unified Audit records up to the current timestamp (or a specified time), requiring AUDIT_ADMIN privileges. It’s a one-time cleanup tool.

Mechanics:Example: BEGIN DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, USE_LAST_ARCH_TIMESTAMP => FALSE); END;. This clears the entire trail unless restricted by a timestamp.

Practical Use:Useful for immediate space reclamation or post-incident cleanup, unlike scheduled jobs.

Edge Case:If the audit trail is large, this may require significant undo space and time, potentially impacting performance.

E. Use DBMS_AUDIT_MGMT.DELETE_AUDIT_RECORDS('POLICY_NAME') as a privileged user to manually purge audit records of a specified Unified Policy.

Explanation:False. No such procedure exists in DBMS_AUDIT_MGMT. The package offers CLEAN_AUDIT_TRAIL and CREATE_PURGE_JOB, but nothing targets a specific policy’s records by name in this format. You can filter records in UNIFIED_AUDIT_TRAIL by policy (e.g., SELECT * WHERE UNIFIED_AUDIT_POLICIES = 'POLICY_NAME'), but purging is all-or-nothing or time-based, not policy-specific via a single command.

Why Incorrect:This appears to be a fabricated or misinterpreted option, possibly confusing audit policy management with trail purging.

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. Not real-time; collected periodically.

B.False. Not about datafile distribution.

C.True. Includes table, column, index stats for plan generation.

D.False. Stored in data dictionary, not AWR.

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. 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. 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. 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. 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.False. lsnrctl can start a default listener without listener.ora.

B.True. EMCC provides centralized Net Services configuration.

C.False. NetCA can run standalone, not just during installation.

D.True. Net Manager configures local naming (e.g., TNSNAMES.ORA).

E.False. Net Manager is local, not centralized like EMCC.

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.False. Recovery happens on startup, not shutdown.

B.False. TRANSACTIONAL waits for commits or rollbacks, then disconnects.

C.True. New connections are blocked during shutdown.

D.True. Inactive sessions persist until shutdown completes.

E.True. Active transactions can commit before disconnection.

A.True. TIMED_STATISTICS = FALSE disables timing data collection.

B.False. No such procedure exists.

C.False. No TIMED_STATISTICS_LEVEL parameter exists.

D.True. STATISTICS_LEVEL = BASIC disables most performance monitoring.

E.False. No such procedure exists.

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.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. CREATE SESSION doesn’t grant DML rights.

B.True. CREATE SESSION allows login.

C.True. WITH ADMIN OPTION allows revoking from others HR granted it to.

D.True. WITH ADMIN OPTION permits granting with the same option.

E.False. Users can’t revoke their own privileges.

F.False. DDL requires additional privileges (e.g., CREATE TABLE).

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

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. IOTs require immediate segment creation.

B.False. SYS-owned tables don’t use deferred creation.

C.True. Default since 11gR2, continued in 23ai.

D.True. Indexes inherit this attribute unless overridden.

E.True. Sessions can alter this via DEFERRED_SEGMENT_CREATION.

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. PDB_DBA grants admin rights (e.g., CREATE SESSION, ALTER SYSTEM) within the PDB.

B.False. DDL on objects requires ownership or explicit grants.

C.False. User management needs CREATE USER privilege, not inherent.

D.False. It includes multiple privileges, not none.

A.False. Data files open in OPEN.

B.False. Redo logs open in OPEN.

C.False. PFILE/SPFILE is read at NOMOUNT.

D.False. SGA is allocated at NOMOUNT.

E.True. Alert log records mount event.

F.True. Control files are read in MOUNT.