Oracle 1Z0-182 Practice Exam Questions & Answers

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.

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.

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.

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.

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.

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

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.

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.

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

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

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

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. 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. Not all trace files indicate errors; some log normal activity.

B .True. Server processes write traces (e.g., ora_s000_123.trc) to the file system.

C .True. Background processes (e.g., ora_lgwr_123.trc) also write traces.

D .False. Names include process type and PID, not just DB name and sequence.

E .False. Traces go to DIAGNOSTIC_DEST, not necessarily FRA unless configured there.

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.