oracle 1z0-182 practice test

Oracle Database 23ai Administration Associate

Last exam update: Nov 18 ,2025
Page 1 out of 7. Viewing questions 1-15 out of 94

Question 1

Examine this command: ALTER DATABASE MOVE DATAFILE '/u01/sales01.dbf' TO '/u02/sales02.dbf';
Which two statements are true?

  • A. DML may be performed on tables with one or more extents in this data file during the execution of this command.
  • B. It overwrites any existing file with the name sales02.dbf in /u02 by default.
  • C. The "TO" clause containing the new file name must be specified even if Oracle Managed Files (OMF) is used.
  • D. Compressed objects in sales01.dbf will be uncompressed in sales02.dbf after the move.
  • E. Tables with one or more extents in this data file may be queried during the execution of this command.
Mark Question:
Answer:

A,E


Explanation:
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.
Reference:Oracle Database Administrator’s Guide 23ai, Chapter "Managing Data Files and Temp
Files," Section "Moving Data Files Online."

User Votes:
A
50%
B
50%
C
50%
D
50%
E
50%
Discussions
vote your answer:
A
B
C
D
E
0 / 1000

Question 2

You execute this command: CREATE SMALLFILE TABLESPACE sales DATAFILE
'/u01/app/oracle/sales01.dbf' SIZE 5G SEGMENT SPACE MANAGEMENT AUTO; Which two
statements are true about the SALES tablespace?

  • A. Free space is managed using freelists.
  • B. It uses the database default block size.
  • C. It must be smaller than the smallest BIGFILE tablespace.
  • D. It is a locally managed tablespace.
  • E. Any data files added to the tablespace must have a size of 5 gigabytes.
Mark Question:
Answer:

B,D


Explanation:
A . Free space is managed using freelists.False. The SEGMENT SPACE MANAGEMENT AUTO clause
specifies Automatic Segment Space Management (ASSM), which uses bitmaps to track free space,
not freelists (used in Manual Segment Space Management).
B . It uses the database default block size.True. The BLOCKSIZE clause is not specified in the
command, so the tablespace inherits the database’s default block size (typically 8K unless altered via
DB_BLOCK_SIZE).
C . It must be smaller than the smallest BIGFILE tablespace.False. There’s no such restriction;
SMALLFILE and BIGFILE tablespaces differ in structure (multiple vs. single data file), not mandated
size relationships.
D . It is a locally managed tablespace.True. In Oracle 23ai, all tablespaces created without an explicit
EXTENT MANAGEMENT DICTIONARY clause are locally managed by default, using extent allocation
bitmaps in the data file headers.
E . Any data files added to the tablespace must have a size of 5 gigabytes.False. The initial data file is
5G, but additional data files can have different sizes when added using ALTER TABLESPACE ... ADD
DATAFILE.
Reference:Oracle Database SQL Language Reference 23ai, "CREATE TABLESPACE" syntax; Oracle
Database Administrator’s Guide 23ai, "Managing Tablespaces."

User Votes:
A
50%
B
50%
C
50%
D
50%
E
50%
Discussions
vote your answer:
A
B
C
D
E
0 / 1000

Question 3

Your data center uses Oracle Managed Files (OMF) for all databases. All tablespaces are smallfile
tablespaces. SALES_Q1 is a permanent user-defined tablespace in the SALES database. The following
command is about to be issued by a DBA logged in to the SALES database: ALTER TABLESPACE
sales_q1 ADD DATAFILE; Which two actions independently ensure that the command executes
successfully?

  • A. Specify a path in the DATAFILE clause of the command specifying a location with at least 100 MB of available space.
  • B. Add the AUTOEXTEND ON clause with NEXT set to 100M.
  • D. Ensure that DB_CREATE_FILE_DEST specifies a location with at least 100 MB of available space.
  • E. Ensure that DB_RECOVERY_FILE_DEST and DB_CREATE_FILE_DEST each specify locations with at least 50 MB of available space.
Mark Question:
Answer:

C,D


Explanation:
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.
Reference:Oracle Database Administrator’s Guide 23ai, "Using Oracle Managed Files."

User Votes:
A
50%
B
50%
D
50%
E
50%
Discussions
vote your answer:
A
B
D
E
0 / 1000

Question 4

Which three are benefits of using temp UNDO when performing DML on global temporary tables?

  • A. It permits DML on global temporary tables even if the database is opened read-only.
  • B. It reduces the amount of UNDO stored in the UNDO tablespace.
  • C. It reduces I/Os to the SYSTEM tablespace.
  • D. It reduces the amount of redo generated.
  • E. It reduces I/Os to the SYSAUX tablespace.
Mark Question:
Answer:

B,C,D


Explanation:
Temp UNDO, introduced in Oracle 12c and refined in 23ai, stores undo for global temporary tables
(GTTs) in temporary tablespaces:
A . It permits DML on GTTs even if the database is opened read-only.False. In read-only mode, DML
on GTTs is allowed regardless of temp UNDO, as GTT data is session-private, but temp UNDO doesn’t
specifically enable this.
B . It reduces the amount of UNDO stored in the UNDO tablespace.True. Temp UNDO stores undo in
the temporary tablespace, reducing usage of the permanent UNDO tablespace.
C . It reduces I/Os to the SYSTEM tablespace.True. By avoiding permanent undo, it reduces metadata
updates in the SYSTEM tablespace related to undo management.
D . It reduces the amount of redo generated.True. Temp UNDO changes are not redo-logged to the
same extent as permanent undo, minimizing redo generation.
E . It reduces I/Os to the SYSAUX tablespace.False. SYSAUX is unrelated to undo management; temp
UNDO affects temporary and SYSTEM tablespaces.
Reference:Oracle Database Concepts 23ai, "Temporary Undo."

User Votes:
A
50%
B
50%
C
50%
D
50%
E
50%
Discussions
vote your answer:
A
B
C
D
E
0 / 1000

Question 5

You want to view the initialization parameter settings for only a specific PDB. Which of the following
statements is true?

  • A. From the PDB, execute SELECT db_uniq_name, pdb_uid, name, value$ FROM pdb_spfile$;
  • B. From the CDB root, execute SELECT NAME, VALUE, ISPDB_MODIFIABLE FROM v$parameter;
  • C. From the CDB root, execute SELECT db_uniq_name, pdb_uid, name, value$ FROM pdb_spfiles;
  • D. From the PDB, execute SELECT NAME, VALUE, ISPDB_MODIFIABLE FROM v$parameter;
Mark Question:
Answer:

D


Explanation:
A .Incorrect syntax and view (pdb_spfile$ is not a valid view; PDB_SPFILE$ exists but lacks value$).
B .From CDB root, V$PARAMETER shows all parameters, not PDB-specific ones.
C .pdb_spfiles is not a valid view; PDB_SPFILE$ exists but requires scoping to a PDB.
D .True. From the PDB, V$PARAMETER shows parameters specific to that PDB, including inherited
and PDB-modified values, with ISPDB_MODIFIABLE indicating alterability.
Reference:Oracle Multitenant Administrator’s Guide 23ai, "Managing PDB Initialization Parameters."

User Votes:
A
50%
B
50%
C
50%
D
50%
Discussions
vote your answer:
A
B
C
D
0 / 1000

Question 6

Which three statements are true about UNDO and REDO?

  • A. REDO is used for read consistency.
  • B. UNDO is used for some flashback operations.
  • C. UNDO is used for read consistency.
  • D. Both REDO and UNDO can be multiplexed.
  • E. REDO is used for ROLLBACK.
  • F. REDO is used for instance recovery.
Mark Question:
Answer:

B,C,F


Explanation:
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.
Reference:Oracle Database Concepts 23ai, "Undo and Redo Data"; Oracle Database Administrator’s
Guide 23ai, "Managing Undo."

User Votes:
A
50%
B
50%
C
50%
D
50%
E
50%
F
50%
Discussions
vote your answer:
A
B
C
D
E
F
0 / 1000

Question 7

Examine this command: SQL> ALTER TABLE ORDERS SHRINK SPACE COMPACT; Which two statements
are true?

  • A. Dependent indexes become UNUSABLE.
  • B. The SHRINK operation causes rows to be moved to empty space starting toward the end of the ORDERS segment.
  • C. Only queries are allowed on ORDERS while SHRINK is executing.
  • D. The high-water mark (HWM) of ORDERS is adjusted.
  • E. Queries and DML statements are allowed on ORDERS while the SHRINK is executing.
  • F. The SHRINK operation causes rows to be moved to empty space starting from the beginning of the ORDERS segment.
Mark Question:
Answer:

E,F


Explanation:
A . Dependent indexes become UNUSABLE.False. SHRINK SPACE COMPACT does not affect index
usability; only SHRINK SPACE without COMPACT may require index maintenance if CASCADE is
omitted.
B . ... starting toward the end of the ORDERS segment.False. Rows are compacted toward the
beginning of the segment, not the end.
C . Only queries are allowed ...False. Both queries and DML are allowed during SHRINK SPACE
COMPACT as it’s an online operation.
D . The high-water mark (HWM) of ORDERS is adjusted.False. COMPACT moves rows but doesn’t
adjust the HWM; the full SHRINK SPACE command is needed for HWM adjustment.
E . Queries and DML statements are allowed ...True. This is an online operation in 23ai, allowing
concurrent access.
F . ... starting from the beginning of the ORDERS segment.True. Rows are moved to fill free space
from the segment’s start.
Reference:Oracle Database SQL Language Reference 23ai, "ALTER TABLE ... SHRINK SPACE."

User Votes:
A
50%
B
50%
C
50%
D
50%
E
50%
F
50%
Discussions
vote your answer:
A
B
C
D
E
F
0 / 1000

Question 8

Which three statements are true about Oracle Managed Files (OMF)?

  • A. If DB_RECOVERY_FILE_DEST is specified but DB_CREATE_ONLINE_LOG_DEST_n is not, the redo logs and control files are placed in DB_RECOVERY_FILE_DEST by default.
  • B. If only DB_CREATE_FILE_DEST is specified, only data files and temp files are Oracle managed.
  • C. If DB_CREATE_ONLINE_LOG_DEST_1 is specified but DB_CREATE_FILE_DEST is not, new data files and temp files are stored in DB_CREATE_ONLINE_LOG_DEST_1 by default.
  • D. If DB_CREATE_FILE_DEST is specified but DB_CREATE_ONLINE_LOG_DEST_n is not, new redo logs and control files are stored in DB_CREATE_FILE_DEST by default.
  • E. If only DB_CREATE_ONLINE_LOG_DEST_1 is specified, only redo logs and control files are Oracle Managed.
  • F. If DB_RECOVERY_FILE_DEST is specified, at least two different locations must be specified for DB_CREATE_ONLINE_LOG_DEST_n.
Mark Question:
Answer:

A,C,D


Explanation:
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.
Reference:Oracle Database Administrator’s Guide 23ai, "Using Oracle Managed Files."

User Votes:
A
50%
B
50%
C
50%
D
50%
E
50%
F
50%
Discussions
vote your answer:
A
B
C
D
E
F
0 / 1000

Question 9

You have connected to a PDB to perform the administration operations of changing and verifying a
system parameter that is PDB_MODIFIABLE. What is the appropriate mode to open the PDB to
achieve this?

  • A. READ WRITE
  • B. RESTRICTED READ ONLY
  • C. READ ONLY
  • D. RESTRICTED WRITE ONLY
Mark Question:
Answer:

A


Explanation:
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.
Reference:Oracle Multitenant Administrator’s Guide 23ai, "Modifying PDB Parameters."

User Votes:
A
50%
B
50%
C
50%
D
50%
Discussions
vote your answer:
A
B
C
D
0 / 1000

Question 10

You must create a tablespace of nonstandard block size in a new file system and plan to use this
command: CREATE TABLESPACE ns_tbs DATAFILE '/u02/oracle/data/nstbs_f01.dbf' SIZE 100G
BLOCKSIZE 32K; The standard block size is 8K, but other nonstandard block sizes will also be used.
Which two are requirements for this command to succeed?

  • A. DB_32K_CACHE_SIZE must be less than DB_CACHE_SIZE.
  • B. DB_32K_CACHE_SIZE must be set to a value that can be accommodated in the SGA.
  • C. The operating system must use a 32K block size.
  • D. DB_32K_CACHE_SIZE should be set to a value greater than DB_CACHE_SIZE.
  • E. The /u02 file system must have at least 100G space for the datafile.
Mark Question:
Answer:

B,E


Explanation:
A .False. No such restriction exists; DB_32K_CACHE_SIZE is independent of DB_CACHE_SIZE.
B .True. A nonstandard block size (32K) requires a corresponding cache (DB_32K_CACHE_SIZE) set to
a non-zero value within SGA limits.
C .False. OS block size is irrelevant; Oracle manages its own block sizes.
D .False. No requirement for it to exceed DB_CACHE_SIZE.
E .True. The file system must have 100G available for the datafile.
Reference:Oracle Database Administrator’s Guide 23ai, "Managing Tablespaces with Nonstandard
Block Sizes."

User Votes:
A
50%
B
50%
C
50%
D
50%
E
50%
Discussions
vote your answer:
A
B
C
D
E
0 / 1000

Question 11

Which three statements are true about dynamic performance views?

  • A. Read consistency is not guaranteed.
  • B. V$FIXED_TABLE can be queried to display the names of all dynamic performance views.
  • C. Data displayed by querying dynamic performance views is derived from metadata in the data dictionary.
  • D. They are owned by the SYSTEM user.
  • E. They can be queried only when the database is open.
Mark Question:
Answer:

A,B,C


Explanation:
A .True. V$ views reflect real-time memory data, not consistent snapshots.
B .True. V$FIXED_TABLE lists all V$ views.
C .True. Data comes from memory structures and data dictionary metadata.
D .False. Owned by SYS, not SYSTEM.
E .False. Some V$ views are accessible in MOUNT state.
Reference:Oracle Database Reference 23ai, "Dynamic Performance Views."

User Votes:
A
50%
B
50%
C
50%
D
50%
E
50%
Discussions
vote your answer:
A
B
C
D
E
0 / 1000

Question 12

In which two ways would you disable timing information collected for wait events and much of the
performance monitoring capability of the database?

  • A. By setting the TIMED_STATISTICS system parameter to FALSE.
  • B. By executing the PL/SQL procedure DBMS_TIME_STATISTIC.DISABLE(TRUE).
  • C. By setting the TIMED_STATISTICS_LEVEL system parameter to FALSE.
  • D. By setting the STATISTICS_LEVEL parameter to BASIC.
  • E. By executing the PL/SQL procedure DBMS_TIME_STATISTIC.LEVEL(BASIC).
Mark Question:
Answer:

A,D


Explanation:
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.
Reference:Oracle Database Reference 23ai, "Initialization Parameters."

User Votes:
A
50%
B
50%
C
50%
D
50%
E
50%
Discussions
vote your answer:
A
B
C
D
E
0 / 1000

Question 13

Which statement regarding PDBs (Pluggable Databases) is correct?

  • A. You can drop a PDB as long as it is not the PDB seed.
  • B. You cannot drop a source PDB of a refreshable PDB.
  • C. You can drop an application root along with the associated PDBs.
  • D. When the relocation of a PDB is finished, the source PDB must be dropped.
Mark Question:
Answer:

A


Explanation:
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.
Reference:Oracle Multitenant Administrator’s Guide 23ai, "Dropping a PDB."

User Votes:
A
50%
B
50%
C
50%
D
50%
Discussions
vote your answer:
A
B
C
D
0 / 1000

Question 14

Which three statements are true about Deferred Segment Creation in Oracle databases?

  • A. It is supported for Index Organized Tables (IOTs) contained in locally managed tablespaces.
  • B. It is supported for SYS-owned tables contained in locally managed tablespaces.
  • C. It is the default behavior for tables and indexes.
  • D. Indexes inherit the DEFERRED or IMMEDIATE segment creation attribute from their parent table.
  • E. Sessions may dynamically switch back and forth from DEFERRED to IMMEDIATE segment creation.
Mark Question:
Answer:

C,D,E


Explanation:
A .False. IOTs require immediate segment creation.
B .False. SYS-owned tables don’t use deferred creation.
C .True. Default since 11gR2, continued in 23ai.
D .True. Indexes inherit this attribute unless overridden.
E .True. Sessions can alter this via DEFERRED_SEGMENT_CREATION.
Reference:Oracle Database Concepts 23ai, "Deferred Segment Creation."

User Votes:
A
50%
B
50%
C
50%
D
50%
E
50%
Discussions
vote your answer:
A
B
C
D
E
0 / 1000

Question 15

Which two statements are true regarding Oracle database space management within blocks
managed by Automatic Segment Space Management (ASSM)?

  • A. ASSM assigns blocks to one of the four fullness categories based on what percentage of the block is allocated for rows.
  • B. Update operations always relocate rows into blocks with free space appropriate to the length of the row being updated.
  • C. Insert operations always insert new rows into blocks with free space appropriate to the length of the row being inserted.
  • D. The first block with enough free space to accommodate a row being inserted will always be used for that row.
  • E. PCTFREE defaults to 10% for all blocks in all segments for all compression methods.
Mark Question:
Answer:

A,C


Explanation:
A .True. ASSM categorizes blocks (e.g., 0-25%, 25-50%) for efficient space use.
B .False. Updates may cause chaining/migration, not always relocation.
C .True. ASSM optimizes inserts into suitable blocks.
D .False. ASSM uses a bitmap, not necessarily the first block.
E .False. PCTFREE is segment-specific, not universally 10%.
Reference:Oracle Database Concepts 23ai, "Automatic Segment Space Management."

User Votes:
A
50%
B
50%
C
50%
D
50%
E
50%
Discussions
vote your answer:
A
B
C
D
E
0 / 1000
To page 2