1z0-182 Oracle Database 23ai Administration Associate Questions and Answers

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.

A.True. CREATE SPFILE FROM PFILE works when idle.

B.False. Includes dynamic parameters too.

C.True. CREATE PFILE FROM SPFILE generates a PFILE.

D.False. A PFILE can start the instance if no SPFILE exists.

E.True. Dynamic parameters (e.g., DB_CACHE_SIZE) are modifiable.

A.False. DBCA can’t change character sets post-creation.

B.False. Listeners are managed via NetCA or lsnrctl.

C.True. DBCA can switch server modes for existing DBs.

D.False. Tablespaces are created via SQL, not DBCA for existing DBs.

E.True. DBCA can generate clone templates from existing DBs.

A.True. Grants unlimited space across all tablespaces.

B.True. Explicit quotas override the privilege.

C.False. Not limited to default tablespace.

D.False. Roles can’t have this privilege directly.

E.False. Default is zero quota unless specified.

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. One instance can’t mount multiple DBs without multitenant.

B.True. RAC allows multiple instances to share one DB.

C.False. Same as A; not possible without CDB.

D.True. An instance can start in NOMOUNT with no DB.

E.True. Standard single-instance configuration.

A.False. Requires MOUNT to access data files.

B.False. Requires MOUNT for redo logs.

C.True. CREATE DATABASE runs in NOMOUNT.

D.True. CREATE CONTROLFILE is possible.

E.False. Recovery needs MOUNT or OPEN.

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

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.

A.False. Backups are via RMAN, not AMTs.

B.True. Stats collection is an AMT.

C.False. Error logging isn’t an AMT.

D.True. SPM tasks are included.

E.False. Notifications aren’t AMTs.

F.True. Segment Advisor is an AMT.

C.True. Hot cloning requires the source PDB (PDB1) to be read-only to ensure consistency during the clone. Others are not prerequisites.

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.

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.

Dynamic service registration allows a database to automatically register its services with a listener without manual configuration in LISTENER.ORA. Let’s analyze each option:

A. The definition for LISTENER_1 requires a CONNECT_DATA section to enable dynamic service registration.

Explanation:False. The CONNECT_DATA section is part of a client-side TNSNAMES.ORA entry for connecting to a service, not for listener registration. Dynamic registration is handled by the database’s LREG (Listener Registration) process, which uses the LOCAL_LISTENER parameter to locate the listener’s address (e.g., host1.abc.com:1521). No CONNECT_DATA is needed in the listener address definition itself. This option confuses client connection syntax with listener configuration.

Mechanics:The listener address in TNSNAMES.ORA (LISTENER_1) is sufficient for LREG to find and register with it, as long as the listener is running at that address.

B. LISTENER_1 must also be defined in the LISTENER.ORA file to enable dynamic service registration.

Explanation:False. Dynamic registration doesn’t require the listener to be explicitly defined in LISTENER.ORA. The LOCAL_LISTENER parameter pointing to LISTENER_1 (resolved via TNSNAMES.ORA) tells LREG where to register services. If the listener is running on host1.abc.com:1521, LREG will find it without a LISTENER.ORA entry. However, LISTENER.ORA is needed tostartthe listener process, but that’s separate from dynamic registration.

Practical Note:If LISTENER.ORA isn’t configured, a default listener might run on port 1521, but the question implies LISTENER_1 is operational.

C. The LREG process registers services dynamically with the LISTENER_1 listener.

Explanation:True. In Oracle 23ai, the LREG background process (replacing PMON’s registration role in earlier versions) dynamically registers database services with listeners specified by LOCAL_LISTENER. Here, LOCAL_LISTENER=LISTENER_1 resolves to host1.abc.com:1521 via TNSNAMES.ORA. LREG periodically sends service information (e.g., service names, instance details) to the listener, enabling clients to connect without static configuration.

Mechanics:LREG uses the TNS alias (LISTENER_1) to locate the listener’s IP and port, registers services like orcl or orclpdb, and updates the listener’s service table. This happens automatically every 60 seconds or on significant events (e.g., instance startup).

D. Dynamic service registration cannot be used for this database instance.

Explanation:False. The setup (LOCAL_LISTENER set and a valid TNSNAMES.ORA entry) explicitly supports dynamic registration. No blockers (e.g., REGISTRATION_EXCLUDED_LISTENERS) are mentioned, so LREG can function normally.

E. There are two listeners named LISTENER and LISTENER_1 running simultaneously using port 1521 on the same host as the database in LISTENERS.

Explanation:False. The question mentions only LISTENER_1 in the SPFILE and TNSNAMES.ORA. There’s no evidence of a second listener (LISTENER) or a LISTENERS configuration (possibly a typo). Two listeners can’t share the same port (1521) on the same host due to port conflicts unless explicitly configured with different IPs, which isn’t indicated here.

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.

A.False. STOP_JOB=IMMEDIATE halts the job, not backgrounds it.

B.False. You can monitor before stopping or after restarting.

C.True. Before stopping, you can reattach with expdp attach=exp_db_full.

D.True. STOP_JOB=IMMEDIATE terminates the job instantly.

A.True. Any PDB except PDB$SEED can be dropped.

B.False. Source PDBs of refreshable clones can be dropped after cloning.

C.False. Dropping an application root requires explicit handling of PDBs.

D.False. Relocation doesn’t mandate dropping the source PDB.

A.False. Works with DMTs too.

B.True. LMTs use bitmaps, unlike DMT freelists.

C.False. OMF is independent of management type.

D.False. Alerts work with both.

E.True. Shrink requires LMTs with ASSM.

A.True. Background processes (e.g., PMON) start.

B.False. Consistency checks require MOUNT.

C.False. Control files open in MOUNT.

D.True. SGA is allocated at startup.

E.False. SYS access requires OPEN.

A.False. Connect Time Failover doesn’t need TAF; TAF is for runtime failover.

B.False. Source Routing doesn’t mandate encryption.

C.True. Needs multiple addresses (e.g., (ADDRESS_LIST=...)) for failover.

D.False. Load balancing works with tnsnames.ora, no name server required.

E.True. Balances connections across shared server dispatchers.

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.

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