Free and Premium Oracle 1z0-182 Dumps Questions Answers

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.

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. External tables are read-only; no DELETE.

B.False. Require OPEN state, unlike V$ views.

C.False. No UPDATE support; read-only.

D.True. Queryable like regular tables without loading.

E.True. Data Pump can unload query results to files.

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

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. 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.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. 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.False. EXCLUDE=index skips all indexes, not re-creates them.

B.False. TABLE_EXISTS_ACTION=replace drops and re-creates tables.

C.True. EXCLUDE=index omits all indexes from import.

D.True. replace drops and re-creates existing tables.

E.False. Skips all indexes, not just overlapping ones.

Roles in Oracle manage privileges efficiently. Let’s dive into each option:

A. Roles must be password protected.

Explanation:False. Roles can be password-protected (e.g., CREATE ROLE mgr IDENTIFIED BY secret), but it’s optional. Non-protected roles (default) are enabled automatically if granted, requiring no password.

Mechanics:Password-protected roles need SET ROLE mgr IDENTIFIED BY secret, enhancing security for sensitive privileges.

B. Roles may be granted to other roles.

Explanation:True. Roles can form hierarchies (e.g., GRANT clerk TO mgr), allowing nested privilege management.

Mechanics:A user with mgr inherits clerk privileges indirectly. Revoking clerk from mgr cascades appropriately.

Practical Use:Simplifies complex privilege structures in large organizations.

C. The SET ROLE statement can enable one or more roles for a session.

Explanation:True. SET ROLE role1, role2; activates specified roles for the session, assuming they’re granted and not password-protected (or password is provided).

Mechanics:Enabled roles grant their privileges immediately within the session scope.

D. Object privileges may not be granted to roles.

Explanation:False. Object privileges (e.g., GRANT SELECT ON emp TO clerk) are a primary use of roles, making this statement incorrect.

Why Incorrect:Roles are designed for this purpose, contradicting the option.

E. All roles granted to a user are set on default when the user logs in.

Explanation:False. Only roles marked as DEFAULT ROLE (via ALTER USER ... DEFAULT ROLE role1) are enabled at login. Non-default roles require SET ROLE.

Mechanics:Check via SELECT * FROM DBA_ROLE_PRIVS WHERE DEFAULT_ROLE='YES'.

F. The SET ROLE statement can disable one or more roles for a session.

Explanation:True. SET ROLE NONE disables all roles, or SET ROLE role1 implicitly disables others not listed, providing granular control.

Practical Use:Useful for testing or restricting privileges temporarily.

A.True. PDBs inherit default schemas from the seed.

B.True. Metadata tablespaces (e.g., SYSTEM, SYSAUX) are created.

C.False. ADMIN1 (not ADMIN) isn’t granted SYSDBA.

D.False. No read-only requirement post-creation.

E.False. PDBs start in MOUNTED state, not open.

F.True. ADMIN1 is a local user with CONNECT role only.

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.

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.True. Active (in-use) UNDO is never overwritten.

B.False. Unexpired UNDO can be reused if space is needed.

C.False. Applies to unexpired, not expired UNDO.

D.False. Only unexpired UNDO is targeted.

E.True. UNDO_RETENTION sets the retention goal for unexpired UNDO.

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.

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

A. Creating dedicated server connections.

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

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

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

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

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

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

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.

A.False. Data files open in OPEN.

B.False. Redo logs open in OPEN.

C.False. PFILE/SPFILE is read at NOMOUNT.

D.False. SGA is allocated at NOMOUNT.

E.True. Alert log records mount event.

F.True. Control files are read in MOUNT.

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.True. After ALTER PLUGGABLE DATABASE ... UNPLUG, CDB_PDBS.STATUS shows UNPLUGGED.

B.False. UNAVAILABLE isn’t a valid status here.

C.False. REMOVED isn’t used; UNPLUGGED persists until dropped.

D.False. DISABLED isn’t a PDB state in this context.

A.False. Schema owners need EXECUTE on DBMS_FGA.

B.False. Not limited to SYSDBA.

C.True. AUDIT_ADMIN includes FGA creation rights.

D.False. No such role exists.

E.True. DBMS_FGA.ADD_POLICY requires this privilege.

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.

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