1Z0-182 Practice Questions

A .True. DBA_EXTERNAL_LOCATIONS (or prefixed variants) shows external table data sources (e.g.,

file paths).

B-D .False. These views cover columns or tables but not external sources specifically.

Reference:Oracle Database Reference 23ai, "DBA_EXTERNAL_LOCATIONS."

A .Incorrect syntax and view (pdb_spfile$ is not a valid view; PDB_SPFILE$ exists but lacks value$).

B .From CDB root, V$PARAMETER shows all parameters, not PDB-specific ones.

C .pdb_spfiles is not a valid view; PDB_SPFILE$ exists but requires scoping to a PDB.

D .True. From the PDB, V$PARAMETER shows parameters specific to that PDB, including inherited

and PDB-modified values, with ISPDB_MODIFIABLE indicating alterability.

Reference:Oracle Multitenant Administrator’s Guide 23ai, "Managing PDB Initialization Parameters."

Background processes manage core database operations. Let’s break it down:

A . Creating dedicated server connections.

False. Dedicated server connections are created by the listener, not background processes. The

listener hands off the connection to a server process (e.g., ora_s000), which isn’t a background

process.

B . Registering services with Oracle Net listeners.

True. The LREG process (Listener Registration, formerly PMON’s role) registers services with listeners,

using parameters like LOCAL_LISTENER.

Mechanics:LREG sends service details (e.g., orcl) to the listener every 60 seconds or on startup.

C . Writing redo to log files.

True. The LGWR (Log Writer) process writes redo entries from the redo log buffer to online redo log

files, ensuring transaction durability.

Mechanics:Triggered by commits, log buffer fills, or checkpoints.

D . Writing dirty database block images from the buffer cache.

True. The DBWn (Database Writer) processes write modified (dirty) blocks from the buffer cache to

data files, maintaining consistency.

Mechanics:Occurs at checkpoints or when free buffers are needed.

E. Reading database blocks into the buffer cache.

False. Server processes (foreground) read blocks into the buffer cache during user queries, not

background processes.

Reference:Oracle Database Concepts 23ai, "Background Processes."

A .False. Links can connect to non-Oracle DBs via gateways.

B .False. No such user requirement; authentication is separate.

C .False. Any user with CREATE PUBLIC DATABASE LINK can create one.

D .True. Links enable remote schema access (e.g., SELECT * FROM emp@remote).

E .False. Public links allow access, but privileges on remote objects are needed.

Reference:Oracle Database Administrator’s Guide 23ai, "Managing Database Links."

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.

Reference:Oracle Database Administrator’s Guide 23ai, "Shutting Down a Database."

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.

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.

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.

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.

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.

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.

Reference:Oracle Database Security Guide 23ai, "Managing the Unified Audit Trail"; Oracle Database

PL/SQL Packages and Types Reference 23ai, "DBMS_AUDIT_MGMT Package."

A .True. Metrics (e.g., V$SYSMETRIC) use thresholds for alerts.

B .False. Metrics aren’t tied to email notifications directly.

C .True. Metrics measure rates (e.g., IOPS, transactions/sec).

D .False. EMCC uses metrics, but they’re DB-level, not EM-specific.

E .False. Metrics exist in the DB, not just EMCC.

Reference:Oracle Database Performance Tuning Guide 23ai, "Database Metrics."

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.

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.

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.

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.

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.

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.

Reference:Oracle Database Administrator’s Guide 23ai, "Managing Database Links"; Oracle Database

SQL Language Reference 23ai, "CREATE DATABASE LINK."

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.

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.

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.

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.

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.

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.

Reference:Oracle Database SQL Tuning Guide 23ai, "Managing Optimizer Statistics"; Oracle Database

PL/SQL Packages and Types Reference 23ai, "DBMS_STATS Package."

Unified Auditing is enabled at database creation or migration in 23ai. Let’s analyze:

A . By querying V$OPTION for parameter Unified Auditing.

True. SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing'; returns TRUE if

enabled. It’s the definitive way to confirm Unified Auditing is active at the database level.

Mechanics:V$OPTION reflects compiled-in features; TRUE indicates the binary was linked with

Unified Auditing (uniauflt=on during relink).

Practical Use:Quick, reliable check post-migration or upgrade.

B . By using the LSINVENTORY Command to query the Oracle Database Software Library.

False. LSINVENTORY (from OPatch) lists installed software components, not runtime features like

auditing mode.

C . By querying the DBA_UNIFIED_AUDIT_OPTION view.

False. This view doesn’t exist; DBA_AUDIT_POLICIES or UNIFIED_AUDIT_TRAIL show policies and

records but not migration status.

D . By executing DBMS_AUDIT_MGMT PL/SQL package in Verify mode.

False. No “Verify mode” exists in DBMS_AUDIT_MGMT; it manages audit trails, not migration

validation.

Reference:Oracle Database Security Guide 23ai, "Verifying Unified Auditing."