Question 1

Which data dictionary view describes the data sources of external tables?

Accepted Answer

A

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

Question 2

You want to view the initialization parameter settings for only a specific PDB. Which of the following statements is true?

Accepted Answer

D

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

Question 3

Which three tasks are performed by background processes in an Oracle database instance?

Accepted Answer

B, C, D

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

Question 4

Which statement is true about database links?

Accepted Answer

D

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

Question 5

Which three statements are true about the Oracle Database server during and immediately after SHUTDOWN TRANSACTIONAL?

Accepted Answer

C, D, E

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

Question 6

Which two methods can be used to purge audit records of the Unified Audits?

Accepted Answer

B, D

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

Question 7

What are Oracle Database Metrics?

Accepted Answer

A, C

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

Question 8

Which statement is true about database links?

Accepted Answer

C

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

Question 9

Which two statements describe how Optimizer Statistics are collected?

Accepted Answer

A, C

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

Question 10

How do you validate that the database was migrated to Unified Auditing?

Accepted Answer

A

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

Question 11

Which three statements are true about Enterprise Manager Cloud Control?

Accepted Answer

A, B, C

Explanation: A .True. Integrates with MOS for patches and support. B .True. Manages Exadata, etc. C .True. Covers WebLogic and other middleware. D .False. Web-based, but not built into the DB. E .False. Can manage stopped instances via agents. Reference:Oracle Enterprise Manager Cloud Control 23ai Documentation, "Overview."

Question 12

Which three are benefits of using temp UNDO when performing DML on global temporary tables?

Accepted Answer

B, C, D

Explanation: 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. Reference:Oracle Database Concepts 23ai, "Temporary Undo."

Question 13

Which two statements are true regarding Oracle database space management within blocks managed by Automatic Segment Space Management (ASSM)?

Accepted Answer

A, C

Explanation: A .True. ASSM categorizes blocks (e.g., 0-25%, 25-50%) for efficient space use. B .False. Updates may cause chaining/migration, not always relocation. C .True. ASSM optimizes inserts into suitable blocks. D .False. ASSM uses a bitmap, not necessarily the first block. E .False. PCTFREE is segment-specific, not universally 10%. Reference:Oracle Database Concepts 23ai, "Automatic Segment Space Management."

Question 14

In one of your databases, the user HR has the password HRMGR. You want to connect to a database
instance whose listener listens on port 1531 by using this statement: CONNECT HR/HRMGR@orcl. No
name server is used. Which statement is true about ORCL?

Accepted Answer

B

Explanation: A .False. ORCL is a TNS alias, not necessarily the DB name. B .True. Must map to a connect descriptor (e.g., HOST=... PORT=1531) in tnsnames.ora. C .False. It’s not the server name but a network alias. D .False. Client-side tnsnames.ora is used, not server-side. E .False. SERVICE_NAMES is server-side, not client-side. Reference:Oracle Database Net Services Administrator’s Guide 23ai, "TNSNAMES.ORA."

Question 15

Which two statements are true about the Automatic Diagnostic Repository (ADR)?

Accepted Answer

C, D

Explanation: A .False. ADR is file-based, not in a schema. B .False. No such default exists. C .True. ADR logs ASM diagnostics. D .True. Supports Clusterware diagnostics. E .False. Defaults to $ORACLE_BASE or /u01/app/oracle if unset. Reference:Oracle Database Administrator’s Guide 23ai, "ADR Overview."

Free Oracle 1Z0-182 Database 23ai Actual Exam Questions