Oracle 1z0-182 Oracle Database 23ai Administration Associate Exam Practice Test

A.True. Auditing tracks suspicious actions.

B.False. That’s monitoring, not auditing.

C.False. Auditing detects, doesn’t prevent.

D.False. Corruption is a reliability issue.

E.True. Ensures privileged user compliance.

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

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

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. Diagnostic collection is ADR, not AMT.

B.True. Resource Manager allocates resources to AMTs.

C.True. Scheduler defines maintenance windows.

D.False. Packaging is IPS/ADR, not AMT.

E.False. Alert log is separate from AMTs.

F.True. Tasks like stats collection are AMTs.

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

B.True. The SQL Statistics Advisor (new in 23ai) identifies stale or missing statistics. Others focus on tuning, performance analysis, or access paths.

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.

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.

Logical structures (e.g., segments, extents) map to physical structures (e.g., data files, blocks). Let’s dissect each option:

A. A segment can span multiple data files in some tablespaces.

Explanation:True. A segment (e.g., a table or index) is a logical entity stored in a tablespace. In a smallfile tablespace (default in Oracle), a segment’s extents can span multiple data files if the tablespace has multiple files and space allocation requires it. This is common in large tables or when autoextend adds new files.

Mechanics:Oracle allocates extents across available data files in a round-robin fashion (with ASSM) or as needed, ensuring the segment’s data is distributed. This doesn’t apply to bigfile tablespaces, which use a single data file.

Example:A 10GB table in a tablespace with two 5GB data files will span both.

B. Segments can span multiple tablespaces.

Explanation:False. A segment is confined to a single tablespace. Oracle enforces this to maintain logical separation (e.g., a table’s data stays in its assigned tablespace). Partitioned tables can have partitions in different tablespaces, but each partition is a separate segment.

Why Not:The segment header and extent map reside in one tablespace, preventing cross-tablespace spanning for a single segment.

C. A segment might have only one extent.

Explanation:True. A segment starts with one extent upon creation (e.g., a small table or index). If no further growth occurs, it remains a single-extent segment. This is common with small objects or when INITIAL extent size suffices.

Mechanics:In locally managed tablespaces (default), the initial extent is allocated based on INITIAL or tablespace defaults (e.g., 64KB), and additional extents are added only as needed.

D. All tablespaces may have one or more data files.

Explanation:False. Bigfile tablespaces are restricted to one data file (up to 128TB). Smallfile tablespaces (traditional) can have multiple data files (up to 1022), but the “all” phrasing makes this false due to bigfile exceptions.

Clarification:The question’s intent may assume smallfile tablespaces, but Oracle 23ai supports both types.

E. A segment’s blocks can be of different sizes.

Explanation:False. All blocks in a segment use the tablespace’s block size (e.g., 8KB). While a database can have tablespaces with different block sizes (e.g., 8KB, 32KB), a single segment’s blocks are uniform, as it resides in one tablespace.

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

B.True. Without SCOPE, ALTER SYSTEM defaults to BOTH (memory and SPFILE); PFILE isn’t updated unless manually recreated.

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.

D.True. The Shared Pool caches data dictionary metadata (e.g., table definitions) in the Library Cache and Dictionary Cache, accessible to all processes. Others serve different purposes (e.g., Large Pool for backups).

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.True. ADR is a file system directory structure (e.g., /u01/app/oracle/diag), external to the database.

B.False. ADR also stores diagnostics for non-database components (e.g., ASM, listener).

C.False. It’s file-based, not schema-based.

D.True. ADR logs (e.g., alert logs, trace files) are accessible even if the instance is down.

E.True. DIAGNOSTIC_DEST sets the ADR base directory.

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. Multiple sessions with different users are possible.

B.False. Buffer cache and redo log buffer are in SGA, not large pool.

C.True. Session tracks login state.

D.True. Each process has its own PGA.

E.False. Connection is the network link; session is the state.

F.True. Same user can have multiple sessions (e.g., via different terminals).

To change a PDB_MODIFIABLE parameter, the PDB must be in READ WRITE mode, allowing ALTER SYSTEM commands to modify parameters stored in the PDB’s SPFILE or memory. READONLY modes prevent modifications, and no RESTRICTED WRITE ONLY mode exists.

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.

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.

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.