I85PREP
⌘ K
Oracle Database Administration I study materials
Oracle Database Administration I study resources

Oracle Database Administration I practice questions

One question at a time. Build your Oracle Database Administration I understanding.

First 50 of 200 questions. Each one lists the choices, the correct answer, and an explanation. The checker at the top of this page saves progress in this browser when it is available.

Continue in the practice tool above to load more questions, 50 at a time.

Question 1Oracle Database Architecture and Instance Management

Which memory structure is a shared work area within the System Global Area that caches parsed SQL statements and their execution plans for reuse across sessions?

  1. The redo log buffer
  2. The Java pool
  3. The shared pool Correct answer.
  4. The large pool

Explanation

The shared pool is a component of the SGA that holds the library cache, which stores parsed SQL, PL/SQL, and execution plans so that identical statements can be reused. The large pool serves session memory and I/O buffers, the Java pool serves Java session state, and the redo log buffer stages change vectors before LGWR writes them.

Question 2Oracle Database Architecture and Instance Management

A database instance starts but users report that recently committed changes are being rolled back after a crash. Which structure must be examined to confirm that committed redo was persisted before the crash?

  1. The temp tablespace
  2. The control file
  3. The undo tablespace
  4. The online redo log files Correct answer.

Explanation

Committed transactions are made durable when LGWR writes the redo to the online redo log files, which instance recovery reads to redo committed changes. Undo is used to roll back uncommitted transactions, not to redo committed ones. The control file tracks database structure and checkpoint information, and the temp tablespace holds sort and hash work areas.

Question 3Oracle Database Architecture and Instance Management

Which background process is responsible for writing dirty buffers from the database buffer cache to data files?

  1. DBWn Correct answer.
  2. SMON
  3. PMON
  4. CKPT

Explanation

Database Writer (DBWn) writes modified buffers from the buffer cache to the data files, and it is the process that makes checkpoint progress possible. CKPT only signals and records checkpoint information, SMON performs instance recovery and segment cleanup, and PMON cleans up failed processes.

Question 4Oracle Database Architecture and Instance Management

Which process must always be running for a database to be open and is responsible for writing redo entries to the online redo log?

  1. ARCn
  2. LGWR Correct answer.
  3. MMON
  4. RECO

Explanation

Log Writer (LGWR) is a mandatory background process that writes redo from the redo log buffer to the current online redo log group, and it commits transactions. ARCn is optional and copies filled log files to archive destinations, MMON gathers performance statistics, and RECO resolves distributed transaction failures.

Question 5Oracle Database Architecture and Instance Management

A DBA wants the database to be able to recover from the loss of one control file without operator intervention. What is the appropriate configuration?

  1. Enable ARCHIVELOG mode on the database.
  2. Set the undo retention to the maximum value.
  3. Multiplex the control file across separate disks. Correct answer.
  4. Increase the size of the redo log buffer.

Explanation

Multiplexing the control file creates multiple copies that Oracle maintains in parallel, so the instance can continue if one copy is damaged. ARCHIVELOG mode protects redo for recovery but does not duplicate the control file, and the other two options affect memory and undo retention rather than control file redundancy.

Question 6Oracle Database Architecture and Instance Management

Which statement accurately describes the relationship between a database and an instance in Oracle Database?

  1. A database can be opened by multiple instances simultaneously in all configurations.
  2. A database consists of memory structures, while an instance consists of the data files and redo logs.
  3. An instance consists of memory structures and background processes, while a database consists of the physical files on disk. Correct answer.
  4. An instance and a database are the same object and always share a single name.

Explanation

An instance is the combination of the SGA and background processes, and it mounts and opens a set of physical files called the database. The two are separate concepts: the instance lives in memory and can be started without opening the database. Only Real Application Clusters allows multiple instances to open one database, not all configurations.

Question 7Oracle Database Architecture and Instance Management

Which files are required for an Oracle database to start and remain open, and are tracked by the control file?

  1. The alert log and trace files
  2. The archived redo log files and RMAN catalog
  3. The data files, online redo log files, and control files Correct answer.
  4. Only the password file and the parameter file

Explanation

The data files, online redo log files, and control files together form the physical database that the instance opens. A missing required data file or redo log prevents the database from opening. Password files, parameter files, alert logs, and archived logs are important but are not part of the set of files the control file tracks as the database.

Question 8Oracle Database Architecture and Instance Management

Which memory area is a private area allocated for each server process and used for bind variables, cursor state, and session-specific information?

  1. The large pool
  2. The redo log buffer
  3. The Program Global Area Correct answer.
  4. The System Global Area

Explanation

The Program Global Area (PGA) is a non-shared memory region allocated per server process, containing session memory and a private SQL area. The SGA, including the large pool and redo log buffer, is shared across all sessions. Private SQL areas in the PGA hold bind data and runtime cursor state.

Question 9Oracle Database Architecture and Instance Management

A DBA runs STARTUP MOUNT and then attempts a query against a user table, which fails. What is the reason?

  1. The undo tablespace has not been created.
  2. The redo log buffer is too small.
  3. The database is in MOUNT state, so data files are not yet opened for user access. Correct answer.
  4. The listener has not been configured for the database.

Explanation

STARTUP MOUNT starts the instance and reads the control file but does not open the data files, so user queries cannot access tables. The OPEN stage is required before normal data access. Listener configuration, undo tablespace existence, and redo buffer sizing do not cause this specific failure at the MOUNT stage.

Question 10Oracle Database Architecture and Instance Management

Which startup stage reads the server parameter file and allocates the SGA but does not read the control file?

  1. NOMOUNT Correct answer.
  2. OPEN READ ONLY
  3. OPEN
  4. MOUNT

Explanation

In the NOMOUNT stage the instance starts: the parameter file is read, the SGA is allocated, and background processes are started, but the control file has not yet been read. MOUNT reads the control file, and OPEN makes data files available. OPEN READ ONLY is a restricted open mode, not a startup stage before control file access.

Question 11Oracle Database Architecture and Instance Management

Which parameter determines the total size of the System Global Area when automatic shared memory management is used?

  1. PROCESSES
  2. PGA_AGGREGATE_TARGET
  3. DB_CACHE_SIZE
  4. SGA_TARGET Correct answer.

Explanation

SGA_TARGET sets the total SGA size when automatic shared memory management is enabled, allowing Oracle to redistribute memory among the buffer cache, shared pool, and other components. PGA_AGGREGATE_TARGET controls the aggregate PGA, DB_CACHE_SIZE sets only the default buffer cache, and PROCESSES limits concurrent processes.

Question 12Oracle Database Architecture and Instance Management

A DBA wants Oracle to manage PGA memory automatically by specifying a target for all server processes combined. Which initialization parameter should be set?

  1. MEMORY_TARGET
  2. PGA_AGGREGATE_TARGET Correct answer.
  3. WORKAREA_SIZE_POLICY
  4. SGA_TARGET

Explanation

PGA_AGGREGATE_TARGET defines the total PGA memory Oracle aims to use across all server processes, and Oracle then distributes work area memory automatically. SGA_TARGET and MEMORY_TARGET size shared and total memory rather than aggregate PGA. WORKAREA_SIZE_POLICY enables automatic or manual work area sizing but does not itself set a memory target.

Question 13Oracle Database Architecture and Instance Management

Which structure stores the checkpoint position and the names and locations of data files and redo log files?

  1. The alert log
  2. The control file Correct answer.
  3. The SPFILE
  4. The data dictionary

Explanation

The control file is a binary file that records the database structure, including data file and redo log locations, the current log sequence, and checkpoint information. The SPFILE stores initialization parameters, the data dictionary stores metadata inside the database, and the alert log is a chronological text log of messages.

Question 14Oracle Database Architecture and Instance Management

What is the primary purpose of the redo log buffer in the SGA?

  1. To store parsed SQL statements for reuse
  2. To hold sort and hash join work areas
  3. To cache frequently accessed data blocks for query performance
  4. To hold redo change vectors temporarily before LGWR writes them to disk Correct answer.

Explanation

The redo log buffer is a circular buffer in the SGA that temporarily holds redo change vectors generated by data changes until LGWR writes them to the online redo log. Caching data blocks is the job of the buffer cache, parsed SQL is held in the shared pool, and sort work areas are allocated in the PGA.

Question 15Oracle Database Architecture and Instance Management

Which background process performs instance recovery automatically when the instance is restarted after an abrupt failure?

  1. CKPT
  2. SMON Correct answer.
  3. DBWn
  4. ARCn

Explanation

System Monitor (SMON) performs instance recovery at startup by applying redo to roll forward and using undo to roll back uncommitted transactions. DBWn writes dirty buffers during normal operation, CKPT records checkpoints, and ARCn archives redo logs.

Question 16Oracle Database Architecture and Instance Management

A DBA wants the database to automatically archive redo log files so that point-in-time recovery is possible. Which action is required?

  1. Set the database to ARCHIVELOG mode and enable archiving. Correct answer.
  2. Increase the number of redo log groups.
  3. Configure a flashback data archive.
  4. Enable force logging on the database.

Explanation

Archived redo logs are produced only when the database is in ARCHIVELOG mode, which must be enabled with ALTER DATABASE ARCHIVELOG and requires a consistent state such as MOUNT. Adding log groups changes redo capacity, force logging affects NOLOGGING operations, and a flashback data archive tracks row history rather than archiving redo.

Question 17Oracle Database Architecture and Instance Management

What is the role of the checkpoint (CKPT) process in an Oracle instance?

  1. It signals DBWn and updates data file headers and the control file with checkpoint information. Correct answer.
  2. It writes all modified buffers to disk at every commit.
  3. It copies redo log files to the archive destination.
  4. It manages the allocation of extents within tablespaces.

Explanation

CKPT coordinates checkpoints by signaling DBWn to write dirty buffers and by recording the checkpoint position in data file headers and the control file. It does not itself write data blocks, archive redo, or manage extent allocation, which are handled by DBWn, ARCn, and space management respectively.

Question 18Oracle Database Architecture and Instance Management

Which component of the SGA holds the data dictionary cache and the library cache?

  1. The database buffer cache
  2. The fixed SGA
  3. The large pool
  4. The shared pool Correct answer.

Explanation

The shared pool contains the library cache, which holds parsed SQL and PL/SQL, and the data dictionary cache, which caches metadata about schema objects. The buffer cache holds data blocks, the large pool serves optional large allocations, and the fixed SGA holds internal instance data structures.

Question 19Oracle Database Architecture and Instance Management

A user session receives an ORA-00257 archiver error. Which condition best explains the cause?

  1. The archive destination is full and ARCn cannot archive redo log files. Correct answer.
  2. The database buffer cache is too small.
  3. The undo tablespace has been set to NOUNDO.
  4. The PGA_AGGREGATE_TARGET is set too low.

Explanation

ORA-00257 indicates that the archiver process cannot write redo log files because the archive destination has run out of space or reached a quota. LGWR then stalls when all redo log groups are full and unarchived. Buffer cache size, undo mode, and PGA targets do not produce this error.

Question 20Oracle Database Architecture and Instance Management

Which statement about the database buffer cache is correct?

  1. It holds only blocks from the SYSTEM tablespace.
  2. It stores redo change vectors until a commit occurs.
  3. It is a private memory area allocated to each server process.
  4. It stores copies of data blocks read from data files and tracks whether each buffer is dirty or clean. Correct answer.

Explanation

The buffer cache holds copies of data blocks in memory and maintains state information such as dirty, pinned, and clean status for each buffer. Redo vectors are staged in the redo log buffer, private memory belongs to the PGA, and the cache holds blocks from all tablespaces, not just SYSTEM.

Question 21Oracle Database Architecture and Instance Management

A DBA needs to change an initialization parameter so that the change takes effect after the next instance restart without editing a text file manually. Which approach accomplishes this?

  1. Use ALTER SYSTEM SET with SCOPE=SPFILE. Correct answer.
  2. Use ALTER DATABASE SET.
  3. Use ALTER SYSTEM SET with SCOPE=MEMORY.
  4. Use ALTER SESSION SET.

Explanation

ALTER SYSTEM SET with SCOPE=SPFILE writes the change to the server parameter file so it is applied at the next startup without manual editing. SCOPE=MEMORY changes only the running instance, ALTER SESSION SET affects only the current session, and ALTER DATABASE SET is not valid for this purpose.

Question 22Oracle Database Architecture and Instance Management

Which process is responsible for monitoring the instance and collecting performance statistics into the Automatic Workload Repository?

  1. MMON Correct answer.
  2. RECO
  3. MMNL
  4. CJQ0

Explanation

The Manageability Monitor (MMON) process gathers performance statistics and writes them to the Automatic Workload Repository at regular intervals. MMNL flushes lightweight metric data, CJQ0 runs job scheduler jobs, and RECO handles distributed transaction recovery.

Question 23Oracle Database Architecture and Instance Management

What happens during a log switch in an Oracle database?

  1. LGWR stops writing redo until a checkpoint completes.
  2. DBWn writes all dirty buffers and the database shuts down.
  3. LGWR begins writing to the next online redo log group, and ARCn may archive the filled group. Correct answer.
  4. SMON merges the current redo log group with the control file.

Explanation

A log switch causes LGWR to move to the next redo log group, and the previously current group becomes eligible for archiving by ARCn when the database is in ARCHIVELOG mode. The database does not shut down, and LGWR continues writing to the new group.

Question 24Oracle Database Architecture and Instance Management

A DBA observes that the instance is using automatic memory management. Which parameter must be set to enable it?

  1. SGA_TARGET only
  2. DB_CACHE_SIZE and SHARED_POOL_SIZE together
  3. MEMORY_TARGET Correct answer.
  4. PGA_AGGREGATE_TARGET only

Explanation

Automatic memory management is enabled by setting MEMORY_TARGET, which defines total instance memory that Oracle distributes dynamically between the SGA and PGA. SGA_TARGET and PGA_AGGREGATE_TARGET are used for automatic shared memory management and automatic PGA management separately, and manual component sizes are not required.

Question 25Oracle Database Architecture and Instance Management

Which background process is optional and, when running, copies filled online redo log files to one or more archive destinations?

  1. DBWn
  2. CKPT
  3. LGWR
  4. ARCn Correct answer.

Explanation

Archiver (ARCn) processes copy completed redo log groups to the archive log destinations when the database is in ARCHIVELOG mode, and they are optional because a NOARCHIVELOG database does not need them. DBWn writes data blocks, LGWR writes redo, and CKPT records checkpoint information.

Question 26Oracle Database Architecture and Instance Management

An Oracle instance is started with a server parameter file. Which statement describes how parameter changes made with ALTER SYSTEM SCOPE=BOTH behave when the instance is restarted?

  1. The change applies only to the current instance and is lost when the instance is shut down.
  2. The change is applied to the running instance and written to the server parameter file, so it persists across restarts. Correct answer.
  3. The change is written to the server parameter file but does not affect the running instance until restart.
  4. The change is applied only to sessions that connect after the ALTER SYSTEM statement completes.

Explanation

SCOPE=BOTH is the default for ALTER SYSTEM when a server parameter file (SPFILE) is in use: the modification takes effect immediately in memory and is also recorded in the SPFILE, so it survives a restart. SCOPE=MEMORY would keep the change only for the current instance lifetime, and SCOPE=SPFILE would defer it to the next startup.

Question 27Oracle Database Architecture and Instance Management

A database is running in ARCHIVELOG mode. Which statement about the instance's redo log configuration is required for the database to remain open while archiving keeps pace with redo generation?

  1. All online redo log groups must be multiplexed to two members on different disks.
  2. The database must have more than one online redo log group so that LGWR can switch to another group while an archived copy is being written. Correct answer.
  3. The redo log buffer must be sized larger than the largest online redo log file.
  4. At least two online redo log groups must exist, each with a single member.

Explanation

LGWR writes to one group at a time; when that group fills, a log switch moves writing to the next group while the archiver process copies the filled group. Multiple groups allow this overlap, and Oracle requires at least two groups for normal operation. Multiplexing protects against media failure of a member but is not what permits archiving to overlap with redo generation.

Question 28Oracle Database Architecture and Instance Management

A DBA creates a tablespace with automatic segment space management and no explicit default storage. Which statement about how Oracle tracks free space in the data files is correct?

  1. Free space is tracked using bitmaps stored in segment headers and data file headers. Correct answer.
  2. Free space is tracked by the DBWn process in the buffer cache and flushed on checkpoint.
  3. Free space is tracked in the data dictionary using free lists for each segment.
  4. Free space is tracked only in the undo tablespace and derived at query time.

Explanation

With automatic segment space management, Oracle maintains bitmaps that describe the free and used state of blocks; these bitmaps are kept in segment headers and data file headers rather than in free lists. This removes free-list contention and makes space usage self-tuning. Free lists belong to the older manual segment space management model.

Question 29Oracle Database Architecture and Instance Management

A DBA queries V$INSTANCE and sees STATUS = OPEN and DATABASE_STATUS = ACTIVE. What does this indicate about the instance and database?

  1. The instance is open, so online data files are accessible and normal user operations can proceed. Correct answer.
  2. The database is in a restricted session and only users with RESTRICTED SESSION privilege may connect.
  3. The instance has mounted the control file but the data files are still offline.
  4. The instance has allocated the SGA but has not yet read the control file.

Explanation

An OPEN status means the instance has completed the NOMOUNT, MOUNT, and OPEN stages, so the control file has been read and the online data files and redo logs are available for normal operations. DATABASE_STATUS = ACTIVE reflects that the database is open and functioning, not that access is restricted.

Question 30Oracle Database Architecture and Instance Management

A DBA wants to shut down a database so that no new connections are allowed, current transactions are allowed to finish, and sessions are disconnected only after they complete their work. Which shutdown mode meets these requirements?

  1. SHUTDOWN IMMEDIATE
  2. SHUTDOWN TRANSACTIONAL Correct answer.
  3. SHUTDOWN NORMAL
  4. SHUTDOWN ABORT

Explanation

SHUTDOWN TRANSACTIONAL prevents new connections and new transactions, waits for active transactions to commit or roll back, then disconnects each session once its transaction ends. SHUTDOWN NORMAL also blocks new connections but waits for all sessions to disconnect voluntarily, which can take indefinitely.

Question 31Oracle Database Architecture and Instance Management

Which statement about the large pool in the SGA is correct?

  1. It provides memory for operations such as shared server sessions, parallel execution message buffers, and RMAN backup buffers. Correct answer.
  2. It caches parsed SQL statements and execution plans for reuse.
  3. It stores the most recently accessed data blocks for all segments.
  4. It holds undo records generated by transactions in the current instance.

Explanation

The large pool is an optional SGA area used for allocations that are large or long-lived, including shared server session memory, parallel execution message buffers, and I/O buffers for RMAN. Separating these from the shared pool keeps them from fragmenting the library cache.

Question 32Oracle Database Architecture and Instance Management

A DBA needs to know which initialization parameter was changed most recently and when. Which source provides this information?

  1. V$SPPARAMETER, which shows the contents of the server parameter file Correct answer.
  2. V$SYSTEM_PARAMETER, which shows instance-wide parameter settings
  3. V$PARAMETER, which shows the current in-memory value of each parameter
  4. V$PARAMETER_VALID_VALUES, which lists acceptable values for each parameter

Explanation

V$SPPARAMETER reads the server parameter file and includes the update comment and timestamp for parameters changed with ALTER SYSTEM, so it reveals which values were last modified and when. The V$PARAMETER family shows effective in-memory values but does not record modification history.

Question 33Oracle Database Architecture and Instance Management

A DBA wants to reserve a portion of the shared pool so that large PL/SQL packages and anonymous blocks can be allocated without fragmenting the pool. Which action accomplishes this?

  1. Set DB_CACHE_SIZE to a nonzero value.
  2. Set LARGE_POOL_SIZE to a nonzero value.
  3. Set JAVA_POOL_SIZE to a nonzero value.
  4. Set SHARED_POOL_RESERVED_SIZE to a nonzero value. Correct answer.

Explanation

SHARED_POOL_RESERVED_SIZE carves out a reserved area of the shared pool for large allocations, protecting them from fragmentation caused by many small allocations. Oracle sizes this area automatically by default, but setting it explicitly controls the reservation.

Question 34Oracle Database Architecture and Instance Management

Which statement best describes the role of the DBWn background process?

  1. It rolls back uncommitted transactions during instance recovery.
  2. It writes modified buffers from the database buffer cache to the data files. Correct answer.
  3. It writes redo entries from the redo log buffer to the online redo log files.
  4. It copies filled online redo log files to the archive destination.

Explanation

Database writer processes move dirty buffers from the buffer cache to the data files so that the cache can be reused. DBWn writes asynchronously and may write buffers whose changes are not yet committed, relying on redo for recovery.

Question 35Oracle Database Architecture and Instance Management

A DBA sets SGA_TARGET to 2G and leaves the individual pool sizes at their defaults. Which statement describes the resulting memory behavior?

  1. The 2G target applies only to the shared pool; other components are sized by PGA_AGGREGATE_TARGET.
  2. Each SGA component is fixed at the value computed at instance startup and never changes.
  3. The entire 2G is allocated to the database buffer cache because it is the largest component.
  4. Oracle automatically adjusts the sizes of the shared pool, buffer cache, large pool, and Java pool within the 2G target. Correct answer.

Explanation

With automatic shared memory management, SGA_TARGET defines the total SGA size and Oracle redistributes memory among the auto-tuned components based on workload, using internal advisors. The individual pool parameters act as minimums when set explicitly rather than as fixed allocations.

Question 36Oracle Database Architecture and Instance Management

A DBA issues ALTER SYSTEM KILL SESSION for a user session. What happens on the database server?

  1. The entire instance is shut down and restarted to clear the session.
  2. The session is suspended until the user disconnects, then removed.
  3. The session is marked for termination; PMON cleans up the session and rolls back its transaction. Correct answer.
  4. The session's server process is terminated immediately and its resources are released at once.

Explanation

ALTER SYSTEM KILL SESSION marks the session for termination; the process monitor then releases the session's resources, including rolling back any uncommitted transaction. The session may remain visible in dynamic performance views until cleanup completes.

Question 37Oracle Database Architecture and Instance Management

Which statement about the fast recovery area is correct?

  1. It stores only archived redo log files and never data file backups.
  2. It must reside on the same disk as the online redo log files to function.
  3. It is a disk location managed by Oracle for recovery-related files such as backups, archived logs, and control file copies. Correct answer.
  4. It replaces the need for a control file because it tracks all backups itself.

Explanation

The fast recovery area is a managed disk area holding recovery-related files, including RMAN backups, archived redo logs, and control file and SPFILE copies. Oracle tracks space usage there and deletes obsolete files according to retention policy.

Question 38Oracle Database Architecture and Instance Management

A DBA observes that the instance has been running for days and V$SGASTAT shows a steadily growing row for 'free memory' in the shared pool shrinking to near zero. Which conclusion is most defensible?

  1. The redo log buffer is too small for the current redo generation rate.
  2. The database buffer cache is undersized and should be increased.
  3. The shared pool is fully allocated and new large allocations may fail with ORA-04031. Correct answer.
  4. The PGA is over-allocated and should be reduced.

Explanation

Shrinking free memory in the shared pool indicates that the pool is nearly exhausted; when no contiguous chunk is available for a new allocation, Oracle raises ORA-04031. Remedies include increasing the shared pool or reserving space with SHARED_POOL_RESERVED_SIZE.

Question 39Oracle Database Architecture and Instance Management

Which statement describes the relationship between a checkpoint and instance recovery time?

  1. A checkpoint deletes obsolete redo log files so recovery never needs them.
  2. A checkpoint copies the control file to the fast recovery area after every transaction.
  3. A checkpoint forces all committed data to be written before the commit completes, eliminating the need for redo.
  4. A checkpoint advances the position from which recovery must begin, so more frequent checkpoints generally shorten recovery. Correct answer.

Explanation

A checkpoint records a position in the redo stream at which all changes to blocks up to that point are known to be on disk. Recovery needs to apply redo only from the last checkpoint, so more frequent checkpoints reduce the amount of redo to apply, at the cost of extra I/O.

Question 40Oracle Database Architecture and Instance Management

A DBA wants to see how much time sessions are spending waiting on events such as db file sequential read. Which dynamic performance view should be queried?

  1. V$SYSSTAT
  2. V$SESSION_EVENT Correct answer.
  3. V$ROLLSTAT
  4. V$LIBRARYCACHE

Explanation

V$SESSION_EVENT reports cumulative wait statistics per session per wait event, including total waits and time waited, which makes it suitable for diagnosing events like db file sequential read. V$SYSSTAT reports counters rather than per-event wait time.

Question 41Database Storage Structures and Space Management

A tablespace is created with the DATAFILE clause and no AUTOEXTEND specification. What happens when the datafile reaches its configured size?

  1. The database raises ORA-01653 when an object tries to extend. Correct answer.
  2. The datafile grows in fixed increments until the disk is full.
  3. Oracle automatically adds a new datafile to the tablespace.
  4. The tablespace is automatically set to read-only mode.

Explanation

A datafile is non-autoextensible by default, so once it reaches its specified size no further space can be allocated. Any segment that needs a new extent in that file fails with ORA-01653 (unable to extend table). Adding files or enabling AUTOEXTEND is an explicit administrative action.

Question 42Database Storage Structures and Space Management

Which statement about the relationship between tablespaces and datafiles in a permanent tablespace is correct?

  1. Each datafile must reside on a different disk from every other file in the same tablespace.
  2. A tablespace can contain multiple datafiles, and each datafile belongs to exactly one tablespace. Correct answer.
  3. A datafile can be shared by several tablespaces to balance I/O.
  4. A tablespace maps to exactly one datafile, which can also hold the undo segments.

Explanation

A tablespace is a logical storage container backed by one or more physical datafiles, and each datafile is associated with a single tablespace. This one-to-many relationship lets a tablespace span disks for capacity and I/O distribution. Datafiles are never shared across tablespaces.

Question 43Database Storage Structures and Space Management

You want Oracle to manage free space in a locally managed tablespace using bitmap tracking. Which segment space management setting should the tablespace use?

  1. FREELIST
  2. MANUAL
  3. AUTOALLOCATE
  4. AUTO Correct answer.

Explanation

In a locally managed tablespace, SEGMENT SPACE MANAGEMENT AUTO uses bitmaps in each segment header to track block free space, replacing freelists. AUTOALLOCATE is an extent management option, not a segment space management setting. MANUAL and FREELIST describe the older freelist-based approach.

Question 44Database Storage Structures and Space Management

Which tablespace is used by Oracle to store temporary segments for sort operations and global temporary table data?

  1. TEMP Correct answer.
  2. UNDO
  3. SYSTEM
  4. SYSAUX

Explanation

A temporary tablespace holds transient segments used for sorts, hash joins, and global temporary table contents, and its files are typically TEMPFILEs. SYSTEM and SYSAUX are permanent tablespaces for the data dictionary and auxiliary components. UNDO stores undo records, not sort spill data.

Question 45Database Storage Structures and Space Management

Which logical structure is the smallest unit of storage that Oracle allocates to a segment?

  1. Extent Correct answer.
  2. Block
  3. Segment
  4. Tablespace

Explanation

An extent is a set of contiguous database blocks and is the unit of space allocation; when a segment grows, it acquires additional extents. A block is the smallest I/O unit but not the allocation unit. A segment is a collection of extents, and a tablespace is a collection of segments.

Question 46Database Storage Structures and Space Management

A DBA creates a tablespace with EXTENT MANAGEMENT LOCAL and UNIFORM SIZE 1M. What is the effect on extents allocated in that tablespace?

  1. The first extent is 1 MB and later extents follow Oracle's autoallocate algorithm.
  2. Extent size is chosen by the DBA per CREATE TABLE statement and ignores the 1 MB setting.
  3. Every extent is exactly 1 MB regardless of segment size. Correct answer.
  4. Extents start at 1 MB and double each time a segment grows.

Explanation

UNIFORM SIZE forces every extent in the tablespace to the specified size, which makes allocation predictable and reduces fragmentation. The doubling behavior belongs to AUTOALLOCATE, and per-object extent clauses cannot override a uniform tablespace. Uniform sizing is common for temporary tablespaces.

Question 47Database Storage Structures and Space Management

Which data dictionary view would you query to see the current size and autoextend settings of every datafile in the database?

  1. DBA_SEGMENTS
  2. DBA_EXTENTS
  3. DBA_TABLESPACES
  4. DBA_DATA_FILES Correct answer.

Explanation

DBA_DATA_FILES lists each datafile with its tablespace, bytes allocated, AUTOEXTENSIBLE flag, increment, and maximum size. DBA_TABLESPACES shows tablespace-level attributes without per-file size. DBA_SEGMENTS and DBA_EXTENTS describe segment and extent allocation, not file configuration.

Question 48Database Storage Structures and Space Management

A tablespace is running low on free space and you want to grow an existing datafile by 500 MB without adding a new file. Which statement accomplishes this?

  1. ALTER TABLESPACE users ADD DATAFILE '/u01/app/orcl/users01.dbf' SIZE 500M;
  2. ALTER TABLESPACE users SHRINK SPACE 500M;
  3. ALTER DATABASE DATAFILE '/u01/app/orcl/users01.dbf' RESIZE 500M; Correct answer.
  4. ALTER DATABASE DATAFILE '/u01/app/orcl/users01.dbf' AUTOEXTEND ON NEXT 500M;

Explanation

ALTER DATABASE DATAFILE ... RESIZE sets the file to a new absolute size, so RESIZE 500M makes the file 500 MB total (not 500 MB larger). To add capacity beyond the current size, the target value must exceed the current file size. AUTOEXTEND changes future growth behavior rather than immediately adding space.

Question 49Database Storage Structures and Space Management

What is the primary purpose of the SYSAUX tablespace in a current Oracle database?

  1. It holds auxiliary components such as the AWR repository and Enterprise Manager metadata. Correct answer.
  2. It stores undo records for transaction rollback.
  3. It stores user application tables by default.
  4. It stores the core data dictionary base tables.

Explanation

SYSAUX is the auxiliary tablespace that consolidates many optional database components, including Automatic Workload Repository snapshots and Enterprise Manager data. The core data dictionary remains in SYSTEM, and undo records belong in the undo tablespace. User objects should be placed in dedicated tablespaces.

Question 50Database Storage Structures and Space Management

You need to create a tablespace that allows segments to be created but prevents any new data from being written to existing objects. Which tablespace state should you use?

  1. OFFLINE
  2. TEMPORARY
  3. READ ONLY Correct answer.
  4. READ WRITE

Explanation

A READ ONLY tablespace permits queries and allows some segment creation, but blocks DML on existing objects, which suits historical or reference data. OFFLINE makes the tablespace unavailable to all users. READ WRITE permits changes, and TEMPORARY describes a tablespace's contents, not a read-only restriction.