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

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.

A.False. Backups are configured via RMAN, not DBCA.

B.True. DBCA allows nonstandard block sizes during DB creation.

C.True. DBCA can register new DBs with EM.

D.False. Block size is fixed post-creation.

E.False. Flashback is enabled via SQL, not DBCA for existing DBs.

A.False. LREG handles registration in 23ai, not PMON.

B.False. CKPT (Checkpoint) process updates the control file.

C.True. PMON cleans up after failed processes (e.g., releases locks).

D.True. PMON rolls back uncommitted transactions of failed processes.

E.False. SMON (System Monitor) manages temporary segment cleanup.

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.

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.True. Local checkpoints are allowed in PDBs.

B.False. Buffer cache is CDB-level.

C.False. Log switching is CDB-level.

D.True. Restricted session can be toggled per PDB.

A.False. Limited to captured privileges, not all schema objects.

B.False. SYS is excluded from capture.

C.True. Captures indirect role privileges used.

D.True. Captures direct role privileges used.

E.False. Focus is on roles, not self-owned objects broadly.

A.True. Without DB_CREATE_ONLINE_LOG_DEST_n, redo logs and control files default to DB_RECOVERY_FILE_DEST.

B.False. If only DB_CREATE_FILE_DEST is set, redo logs and control files also use it unless overridden.

C.True. DB_CREATE_ONLINE_LOG_DEST_1 becomes the default for data files and temp files if DB_CREATE_FILE_DEST is unset.

D.True. DB_CREATE_FILE_DEST serves as the default for all file types if no log-specific parameter is set.

E.False. Data files and temp files would also use DB_CREATE_ONLINE_LOG_DEST_1 if no other parameter is specified.

F.False. No such requirement exists; DB_RECOVERY_FILE_DEST operates independently.

A.True. DUAL is public; any user with SELECT can query it.

B.True. One row, one VARCHAR2 column (DUMMY).

C.False. Fixed at one row.

D.False. Not restricted to SYS.

E.False. One row, one column only.

F.False. Can return expressions, not just constants.

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.

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. 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.False. Undo is stored in a dedicated undo tablespace, not SYSTEM.

B.True. Large transactions can span multiple undo segments.

C.False. Undo isn’t stored in SYSAUX.

D.True. Undo segments extend as needed, space permitting.

E.False. Undo doesn’t “wrap”; new extents are allocated.

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.

A.True. Oracle 23ai uses DEFAULT_MAINTENANCE_PLAN with ORA$AUTOTASK subplan for maintenance tasks (e.g., stats gathering). Others are incorrect or speculative.

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.

D.True. The .pdb file (archive) contains the manifest and data files; all must be copied to the target CDB for plugging.

A-C.False. The archive alone isn’t enough; all components are needed.

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.

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. REDO is used for read consistency.False. Read consistency is achieved using UNDO, which provides a consistent view of data as of a specific point in time. REDO logs changes for recovery, not consistency.

B. UNDO is used for some flashback operations.True. Flashback features like Flashback Query and Flashback Table rely on UNDO to reconstruct past states of data.

C. UNDO is used for read consistency.True. UNDO stores pre-change data, enabling multi-version read consistency for queries.

D. Both REDO and UNDO can be multiplexed.False. REDO logs can be multiplexed (mirrored across multiple destinations), but UNDO is managed within a single UNDO tablespace per instance (though it can have multiple data files).

E. REDO is used for ROLLBACK.False. ROLLBACK uses UNDO to revert changes; REDO logs the changes but isn’t used to undo them.

F. REDO is used for instance recovery.True. REDO logs are replayed during instance recovery to reapply committed changes after a crash.

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

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.

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.

A.False. External tables are read-only.

B.True. Defines how external data (e.g., CSV) is mapped to SQL.

C.False. Data is external, not in the DB.

D.False. Storage is external; Oracle doesn’t define it.

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.