I85PREP
⌘ K
Oracle 1Z0-076 study materials
Oracle 1Z0-076 study resources

Oracle 1Z0-076 practice questions

One question at a time. Build your Oracle 1Z0-076 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 19c Administration

You want to create a tablespace whose data files automatically extend when they fill. Which clause explicitly enables this and specifies no user-defined size cap, subject to database and platform limits?

  1. AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED Correct answer.
  2. SEGMENT SPACE MANAGEMENT AUTO
  3. AUTOEXTEND ON NEXT 100M
  4. EXTENT MANAGEMENT LOCAL

Explanation

AUTOEXTEND ON enables automatic data-file growth, NEXT 100M sets the growth increment, and MAXSIZE UNLIMITED explicitly specifies no user-defined maximum. UNLIMITED does not remove Oracle's data-file size limits or platform and storage constraints, so growth is not literally unlimited.

Question 2Oracle Database 19c Administration

A user reports that a query against a table fails with ORA-01555. Which action most directly reduces the chance of this error recurring?

  1. Increase the value of DB_BLOCK_SIZE
  2. Decrease the size of the redo log buffer
  3. Move the table to a locally managed tablespace
  4. Increase the size of the UNDO tablespace and set UNDO_RETENTION appropriately Correct answer.

Explanation

ORA-01555 occurs when undo information needed to build a read-consistent image has been overwritten. A larger undo tablespace combined with an adequate UNDO_RETENTION keeps the required before-images available longer.

Question 3Oracle Database 19c Administration

Which file is required for the instance to start and contains parameters such as MEMORY_TARGET and CONTROL_FILES?

  1. The server parameter file or a text initialization parameter file Correct answer.
  2. The alert log
  3. The password file
  4. The redo log member

Explanation

The instance reads initialization parameters from the server parameter file (SPFILE) or a text PFILE at startup. Without valid parameters the instance cannot allocate memory or locate the control files.

Question 4Oracle Database 19c Administration

You need to guarantee that committed transactions survive an instance failure. Which configuration supports this?

  1. A bigfile undo tablespace
  2. A large TEMP tablespace
  3. ARCHIVELOG mode with multiplexed redo log groups Correct answer.
  4. NOARCHIVELOG mode with a single redo log group

Explanation

Committed changes are protected by redo, and multiplexing redo log groups guards against loss of a single member. ARCHIVELOG mode additionally preserves redo for media recovery, supporting durability across failures.

Question 5Oracle Database 19c Administration

Which statement about locally managed tablespaces is true?

  1. They cannot use automatic segment space management.
  2. They require the EXTENT MANAGEMENT DICTIONARY clause.
  3. They store extent allocation information in the data dictionary.
  4. They use a bitmap in each data file header to track extent allocation. Correct answer.

Explanation

In a locally managed tablespace each data file header maintains a bitmap of allocated and free extents, eliminating recursive dictionary updates. This design also permits automatic segment space management.

Question 6Oracle Database 19c Administration

A developer needs to see the execution plan of a SQL statement without executing it. Which approach accomplishes this?

  1. Running the statement with a /*+ GATHER_PLAN_STATISTICS */ hint
  2. SET AUTOTRACE ON STATISTICS
  3. EXPLAIN PLAN FOR followed by a query of PLAN_TABLE Correct answer.
  4. Querying V$SQL_PLAN after execution

Explanation

EXPLAIN PLAN FOR populates PLAN_TABLE with the optimizer's chosen plan without running the statement, and DBMS_XPLAN.DISPLAY formats it. The other methods either execute the statement or require it to have run already.

Question 7Oracle Database 19c Administration

Which view would you query to determine whether a tablespace is close to running out of space in its data files?

  1. V$SESSION
  2. DBA_USERS
  3. V$INSTANCE
  4. DBA_TABLESPACE_USAGE_METRICS Correct answer.

Explanation

DBA_TABLESPACE_USAGE_METRICS reports space used and allocated per tablespace, which helps identify space pressure. V$INSTANCE, V$SESSION, and DBA_USERS describe instance, session, and user metadata rather than space consumption.

Question 8Oracle Database 19c Administration

Which command creates a user who can connect but cannot create any objects in their own schema?

  1. CREATE USER app IDENTIFIED BY secret GRANT CONNECT;
  2. CREATE USER app IDENTIFIED BY secret; Correct answer.
  3. CREATE USER app IDENTIFIED BY secret QUOTA UNLIMITED ON USERS;
  4. CREATE USER app IDENTIFIED BY secret DEFAULT TABLESPACE USERS;

Explanation

A newly created user has no privileges or quotas, so it can authenticate only if granted CREATE SESSION, but it cannot create objects without a quota and object privileges. The other options either grant quota or use invalid syntax.

Question 9Oracle Database 19c Administration

A tablespace was created with SEGMENT SPACE MANAGEMENT AUTO. What is the effect on the freelists of its segments?

  1. Freelists are disabled only for indexes.
  2. Each segment maintains multiple freelists.
  3. Freelists are replaced by bitmaps in the segment header. Correct answer.
  4. Freelists are stored in the data dictionary.

Explanation

Automatic segment space management uses bitmaps in the segment header and data blocks to track free space, so freelists and freelist groups are not used. This removes the need to manually tune FREELISTS and PCTFREE-related parameters.

Question 10Oracle Database 19c Administration

You want a tablespace to contain exactly one very large data file. Which tablespace type and clause combination is appropriate?

  1. Locally managed tablespace with multiple data files
  2. Bigfile tablespace with a single data file Correct answer.
  3. Temporary tablespace with a tempfile
  4. Smallfile tablespace with AUTOEXTEND ON

Explanation

A bigfile tablespace contains exactly one data file, which can be extremely large. Smallfile tablespaces allow multiple files, and temporary tablespaces use tempfiles for sort data rather than permanent storage.

Question 11Oracle Database 19c Administration

Which action immediately makes a newly granted role usable by a session that is already connected?

  1. The user runs SET ROLE NONE.
  2. The user runs SET ROLE role_name for the newly granted role. Correct answer.
  3. The user reconnects to the database.
  4. The DBA issues ALTER SYSTEM FLUSH SHARED_POOL.

Explanation

SET ROLE role_name enables the newly granted role in the current session. Changing a user's default roles affects which roles are enabled at subsequent logons and does not immediately enable the role in an already-connected session.

Question 12Oracle Database 19c Administration

Which parameter must be set to enable automatic shared memory management on a database that uses the SGA_TARGET parameter?

  1. SGA_TARGET set to a nonzero value with SGA_MAX_SIZE specified Correct answer.
  2. DB_CACHE_SIZE set larger than SGA_TARGET
  3. PGA_AGGREGATE_TARGET set to zero
  4. MEMORY_TARGET set to zero with SGA_TARGET unset

Explanation

Automatic shared memory management is enabled when SGA_TARGET is set to a nonzero value and SGA_MAX_SIZE defines the ceiling. The instance then redistributes memory among the automatically sized SGA components.

Question 13Oracle Database 19c Administration

A data file was accidentally deleted, and the database is in ARCHIVELOG mode. Which recovery step restores the file?

  1. RESTORE DATAFILE followed by RECOVER DATAFILE Correct answer.
  2. ALTER DATABASE BACKUP CONTROLFILE TO TRACE
  3. STARTUP FORCE only
  4. DROP TABLESPACE with INCLUDING DATAFILES

Explanation

In ARCHIVELOG mode you restore the damaged data file from a backup and then apply archived redo with RECOVER DATAFILE to bring it current. The database can remain open while other files are recovered.

Question 14Oracle Database 19c Administration

Which statement about the FAST_START_MTTR_TARGET parameter is correct?

  1. It controls the number of redo log groups.
  2. It sets the size of the redo log buffer.
  3. It sets the maximum time in seconds for instance recovery. Correct answer.
  4. It disables automatic checkpointing.

Explanation

FAST_START_MTTR_TARGET specifies a target bound, in seconds, for instance or crash recovery. The database uses it to tune checkpoint frequency so recovery completes within the target.

Question 15Oracle Database 19c Administration

You need to move a nonpartitioned table to a different tablespace while the table remains available for queries. Which statement accomplishes this?

  1. ALTER TABLE ... TRUNCATE PARTITION
  2. CREATE INDEX ... REBUILD
  3. ALTER TABLE ... SHRINK SPACE
  4. ALTER TABLE ... MOVE TABLESPACE new_ts Correct answer.

Explanation

ALTER TABLE ... MOVE relocates the table's segments to the specified tablespace and rebuilds them, and it does not require the table to be dropped. Note that indexes become unusable and must be rebuilt afterward.

Question 16Oracle Database 19c Administration

Which statement creates a partitioned table where each partition is stored in its own tablespace?

  1. CREATE TABLE t (id NUMBER) PARTITION BY RANGE (id) (PARTITION p1 VALUES LESS THAN (100) TABLESPACE ts1, PARTITION p2 VALUES LESS THAN (MAXVALUE) TABLESPACE ts2); Correct answer.
  2. CREATE TABLE t (id NUMBER) PARTITION BY RANGE (id) TABLESPACE ts1;
  3. CREATE TABLE t (id NUMBER) TABLESPACE ts1, ts2;
  4. CREATE TABLE t (id NUMBER) STORAGE (TABLESPACE ts1, ts2);

Explanation

Each partition clause may include its own TABLESPACE clause, allowing partitions to be stored separately. This is a common technique to spread I/O and simplify partition-level maintenance.

Question 17Oracle Database 19c Administration

Which privilege allows a user to create a table in another user's schema?

  1. SELECT ANY TABLE
  2. CREATE TABLE
  3. CREATE SESSION
  4. CREATE ANY TABLE Correct answer.

Explanation

CREATE ANY TABLE is a system privilege that permits creating tables in any schema. CREATE TABLE allows creation only in the user's own schema, and SELECT ANY TABLE grants query access rather than object creation.

Question 18Oracle Database 19c Administration

Which view shows the actual execution statistics, such as buffer gets and rows processed, for a SQL statement already executed?

  1. V$DATABASE
  2. DBA_TAB_COLUMNS
  3. DBA_OBJECTS
  4. V$SQL_PLAN_STATISTICS_ALL Correct answer.

Explanation

V$SQL_PLAN_STATISTICS_ALL reports per-row-source runtime statistics for statements whose plans were collected with the GATHER_PLAN_STATISTICS hint or STATISTICS_LEVEL set to ALL. It helps compare estimated and actual cardinalities.

Question 19Oracle Database 19c Administration

A tablespace contains many small free extents that cannot satisfy a large allocation. Which action can consolidate free space in a dictionary-managed tablespace?

  1. ALTER TABLESPACE ... COALESCE Correct answer.
  2. ALTER TABLESPACE ... SHRINK SPACE
  3. ALTER TABLESPACE ... DEFAULT STORAGE
  4. ALTER DATABASE DATAFILE ... RESIZE

Explanation

COALESCE merges adjacent free extents in a dictionary-managed tablespace to form larger contiguous extents. Locally managed tablespaces generally do not need it because the bitmap tracks extents directly.

Question 20Oracle Database 19c Administration

Which statement about the RESULT_CACHE is correct?

  1. It stores the results of queries and returns them for identical subsequent queries. Correct answer.
  2. It requires the database to be in NOARCHIVELOG mode.
  3. It replaces the shared pool entirely.
  4. It caches only the execution plan of a query.

Explanation

The server result cache retains query result sets in memory and can return them for repeated identical queries, avoiding re-execution. It is sized by RESULT_CACHE_MAX_SIZE and is distinct from the plan cache in the shared pool.

Question 21Oracle Database 19c Administration

Which command would you use to create a guaranteed undo retention policy that keeps undo for at least 30 minutes?

  1. ALTER SYSTEM SET UNDO_RETENTION = 1800;
  2. ALTER SYSTEM SET UNDO_MANAGEMENT = AUTO;
  3. ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS1;
  4. ALTER SYSTEM SET UNDO_RETENTION = 30; Correct answer.

Explanation

UNDO_RETENTION is expressed in seconds, so 30 minutes equals 1800 seconds. Setting it to 1800 tells the database to retain undo for that period when space allows, and guaranteed retention additionally requires the undo tablespace to have sufficient space.

Question 22Oracle Database 19c Administration

You observe that a query performs a full table scan despite a selective predicate. The table was recently loaded with a bulk insert, and statistics were never gathered. Which action is most appropriate?

  1. Rebuild all indexes on the table.
  2. Add a hint to force an index range scan.
  3. Increase the size of the buffer cache.
  4. Gather optimizer statistics for the table using DBMS_STATS. Correct answer.

Explanation

Without statistics the optimizer lacks cardinality information and may default to a full scan. Gathering statistics with DBMS_STATS gives the optimizer accurate selectivity estimates for choosing an index access path.

Question 23Oracle Database 19c Administration

A database uses automatic undo management, and a long-running query repeatedly fails with ORA-01555 while the undo tablespace is large but not full. Which condition best explains the failure?

  1. The PGA_AGGREGATE_TARGET is set too low.
  2. The database is running in NOARCHIVELOG mode.
  3. The redo log buffer is too small.
  4. UNDO_RETENTION is set shorter than the query's run time and the undo tablespace is not sized for guaranteed retention. Correct answer.

Explanation

Even with a large undo tablespace, undo may be reused before the query finishes if retention is shorter than the query duration or if guaranteed retention is not enabled and space pressure forces reuse. Sizing undo for the longest expected query and enabling guaranteed retention resolves the issue.

Question 24Oracle Database 19c Administration

You must clone a running database to a new host using an RMAN duplicate with no target downtime. Which prerequisite is essential?

  1. The auxiliary instance must use the same DB_NAME as the target.
  2. The target must be shut down cleanly before duplication.
  3. The target must be in NOARCHIVELOG mode.
  4. The target must be in ARCHIVELOG mode and the auxiliary instance must have access to backups or an active network connection. Correct answer.

Explanation

An active database duplicate requires the target to be in ARCHIVELOG mode so redo can be shipped to the auxiliary instance, and the auxiliary instance must be able to reach the backups or the target over the network. The target stays open throughout the operation.

Question 25Oracle Database 19c Administration

A tablespace is created with SEGMENT SPACE MANAGEMENT AUTO and PCTFREE 10. A block fills with rows beyond the PCTFREE threshold. What happens to subsequent inserts into that block?

  1. They are redirected to the undo tablespace temporarily.
  2. They are placed in the block only if space remains above the PCTFREE threshold; otherwise a new block is used. Correct answer.
  3. They are blocked until the block is reorganized by a shrink operation.
  4. They are allowed until PCTUSED is reached.

Explanation

PCTFREE reserves space in each block for future row growth, so inserts stop using a block once free space falls to the PCTFREE threshold. In automatic segment space management, PCTUSED is ignored and free space is tracked with bitmaps.

Question 26Oracle Database 19c Administration

Which Oracle 19c background process is responsible for writing redo log buffer entries to the online redo log files?

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

Explanation

LGWR (Log Writer) flushes the redo log buffer to the online redo log files, writing on commit, when the buffer is one-third full, or every three seconds. DBWn writes dirty data blocks, CKPT updates checkpoint information, and SMON performs instance recovery and space cleanup.

Question 27Oracle Database 19c Administration

A DBA wants a tablespace whose data files are automatically extended when they fill, up to a defined limit. Which storage clause achieves this?

  1. RESIZE 2G NEXT 100M
  2. EXTENT MANAGEMENT LOCAL UNIFORM SIZE 100M
  3. SEGMENT SPACE MANAGEMENT AUTO MAXSIZE 2G
  4. AUTOEXTEND ON NEXT 100M MAXSIZE 2G Correct answer.

Explanation

The AUTOEXTEND ON clause with NEXT and MAXSIZE lets Oracle grow a data file automatically by the specified increment up to the maximum size. The other clauses control extent allocation or segment space management and do not enable automatic file growth.

Question 28Oracle Database 19c Administration

Which initialization parameter determines the maximum number of user processes that can connect concurrently to an instance?

  1. OPEN_CURSORS
  2. PROCESSES Correct answer.
  3. SESSIONS
  4. TRANSACTIONS

Explanation

PROCESSES sets the maximum number of operating system processes that can connect to the instance, including background processes. SESSIONS and TRANSACTIONS are derived from it by default, and OPEN_CURSORS limits cursors per session.

Question 29Oracle Database 19c Administration

Which default profile resource parameter limits how long a user session may remain idle before being terminated?

  1. CONNECT_TIME
  2. SESSIONS_PER_USER
  3. CPU_PER_SESSION
  4. IDLE_TIME Correct answer.

Explanation

IDLE_TIME in a profile specifies the maximum consecutive idle period for a session; when exceeded, the session is terminated. CONNECT_TIME limits total elapsed session time, SESSIONS_PER_USER limits concurrent sessions, and CPU_PER_SESSION caps CPU usage.

Question 30Oracle Database 19c Administration

Which type of tablespace must be online for the database to be opened, and stores the data dictionary and SYSTEM undo segments?

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

Explanation

The SYSTEM tablespace holds the data dictionary and SYSTEM undo segments and must be online for the database to open. SYSAUX is auxiliary, UNDO holds undo for user transactions, and TEMP holds temporary sort and hash data.

Question 31Oracle Database 19c Administration

Which command correctly creates a locally managed tablespace with automatic segment space management?

  1. CREATE TABLESPACE app_data DATAFILE '/u01/app_data01.dbf' SIZE 500M UNIFORM SIZE 1M SEGMENT SPACE MANAGEMENT AUTO;
  2. CREATE TABLESPACE app_data DATAFILE '/u01/app_data01.dbf' SIZE 500M SEGMENT SPACE MANAGEMENT MANUAL EXTENT MANAGEMENT LOCAL;
  3. CREATE TABLESPACE app_data DATAFILE '/u01/app_data01.dbf' SIZE 500M EXTENT MANAGEMENT DICTIONARY SEGMENT SPACE MANAGEMENT AUTO;
  4. CREATE TABLESPACE app_data DATAFILE '/u01/app_data01.dbf' SIZE 500M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; Correct answer.

Explanation

A locally managed tablespace with automatic segment space management specifies EXTENT MANAGEMENT LOCAL and SEGMENT SPACE MANAGEMENT AUTO. Dictionary extent management is deprecated and incompatible with automatic segment space management, and ASSM requires LOCAL extent management.

Question 32Oracle Database 19c Administration

Which view reports cumulative statistics such as physical reads and physical writes for the instance since startup?

  1. V$SESSION
  2. V$INSTANCE
  3. V$SYSSTAT Correct answer.
  4. V$SQLAREA

Explanation

V$SYSSTAT contains cumulative instance-level statistics since instance startup, including physical reads and writes. V$SESSION shows per-session state, V$SQLAREA aggregates SQL text and statistics, and V$INSTANCE reports instance status.

Question 33Oracle Database 19c Administration

Which statement about an Oracle 19c instance's SPFILE is correct?

  1. It is a text file that can be edited with any text editor while the instance is running.
  2. It can only be created by manually copying the PFILE and renaming it.
  3. It stores data file and redo log names but not initialization parameters.
  4. It is a binary server-side file that can be modified with ALTER SYSTEM SET ... SCOPE=SPFILE. Correct answer.

Explanation

The SPFILE is a binary server parameter file maintained by the server and modified through ALTER SYSTEM SET with SCOPE=SPFILE (or SCOPE=BOTH/MEMORY). It is created by CREATE SPFILE FROM PFILE, not by manual editing.

Question 34Oracle Database 19c Administration

A DBA wants to determine the current free space available inside each data file of the USERS tablespace. Which view provides per-file free space information?

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

Explanation

DBA_FREE_SPACE lists free extents with their file ID and byte sizes, allowing calculation of free space per data file. DBA_DATA_FILES reports file sizes, DBA_TABLESPACES reports tablespace attributes, and DBA_SEGMENTS reports segment allocation.

Question 35Oracle Database 19c Administration

A user reports that a session is waiting on the 'db file sequential read' event. Which activity most likely causes this wait?

  1. A full table scan reading multiple blocks with multiblock I/O
  2. Waiting for a redo log buffer to be flushed on commit
  3. An indexed single-block read from a data file into the buffer cache Correct answer.
  4. Waiting for a free buffer in the shared pool

Explanation

The 'db file sequential read' wait occurs during single-block reads, typically index or table access by ROWID. Multiblock full scans show 'db file scattered read', commit waits show 'log file sync', and shared pool waits appear as latch or mutex events.

Question 36Oracle Database 19c Administration

A DBA wants a tablespace to retain at least 30 minutes of undo even if it means transactions may fail. Which clause achieves guaranteed undo retention?

  1. ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS1; and UNDO_RETENTION = 1800
  2. ALTER SYSTEM SET UNDO_MANAGEMENT = MANUAL; and UNDO_RETENTION = 1800
  3. ALTER SYSTEM SET UNDO_RETENTION = 1800; with an autoextending undo tablespace
  4. ALTER SYSTEM SET UNDO_RETENTION = 1800; with a fixed-size undo tablespace and RETENTION GUARANTEE enabled Correct answer.

Explanation

Guaranteed undo retention requires the undo tablespace to be created or altered with RETENTION GUARANTEE and sufficient space; otherwise Oracle may overwrite unexpired undo. Setting UNDO_RETENTION alone with autoextend only influences best-effort retention.

Question 37Oracle Database 19c Administration

A DBA needs to move a table to a new tablespace while the table remains fully available for DML. Which statement accomplishes this?

  1. ALTER TABLE sales REBUILD TABLESPACE sales_new;
  2. ALTER TABLE sales SHRINK SPACE TABLESPACE sales_new;
  3. ALTER TABLE sales MOVE TABLESPACE sales_new;
  4. ALTER TABLE sales MOVE ONLINE TABLESPACE sales_new; Correct answer.

Explanation

The ONLINE keyword in ALTER TABLE ... MOVE ONLINE TABLESPACE allows DML to continue during the move, with brief locks at start and end. Without ONLINE the move locks the table, SHRINK SPACE does not relocate segments, and REBUILD is not valid for tables.

Question 38Oracle Database 19c Administration

A query on a large table is performing a full table scan despite a highly selective predicate. The table was bulk-loaded and no statistics have been gathered. Which action is the most direct fix?

  1. Rebuild all indexes on the table.
  2. Increase the size of the buffer cache.
  3. Gather optimizer statistics for the table using DBMS_STATS.GATHER_TABLE_STATS. Correct answer.
  4. Force the query to use the index with a NO_INDEX hint.

Explanation

Missing or stale optimizer statistics cause the optimizer to misestimate cardinality and choose a full scan. Gathering statistics with DBMS_STATS gives the optimizer accurate information to select the index. Buffer size, hints, and index rebuilds do not correct missing statistics.

Question 39Oracle Database 19c Administration

Which statement about restoring a control file from an autobackup is correct?

  1. A control file can only be restored from a full database backup, never from an autobackup.
  2. The control file can be restored from an RMAN autobackup using RESTORE CONTROLFILE FROM AUTOBACKUP. Correct answer.
  3. The control file must be recreated manually with CREATE CONTROLFILE before any restore.
  4. The control file is automatically restored when the data files are restored.

Explanation

RMAN automatically backs up the control file and SPFILE when CONFIGURE CONTROLFILE AUTOBACKUP is ON. RESTORE CONTROLFILE FROM AUTOBACKUP recovers it without needing a catalog entry, and the database must then be mounted and recovered.

Question 40Oracle Database 19c Administration

A DBA wants to enable block change tracking to speed up incremental backups. Which statement is correct?

  1. Enable it with ALTER DATABASE ENABLE BLOCK CHANGE TRACKING; and it stores changes in the control file only.
  2. Enable it with ALTER SYSTEM SET DB_CREATE_BLOCK_CHANGE_TRACKING = TRUE; and it writes changes to the redo logs.
  3. Enable it with ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE 'path'; and it records changed blocks in a tracking file. Correct answer.
  4. Enable it with DBMS_BACKUP_RESTORE.ENABLE_BCT; and it records changes in the SYSAUX tablespace.

Explanation

Block change tracking is enabled with ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE, which creates a tracking file recording modified blocks. RMAN then reads this file to limit incremental backups to changed blocks.

Question 41Oracle Database 19c Administration

A DBA wants to limit the amount of PGA memory used by a dedicated server session for sorting. Which initialization parameter is the primary control?

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

Explanation

PGA_AGGREGATE_TARGET sets the target aggregate PGA memory for the instance, and Oracle manages work areas within it automatically. SGA_TARGET, DB_CACHE_SIZE, and SHARED_POOL_SIZE control SGA components, not PGA.

Question 42Oracle Database 19c Administration

A DBA observes that a tablespace's data file has reached MAXSIZE and the tablespace is full. Which action allows the tablespace to grow further without adding a new data file?

  1. Set the tablespace to READ WRITE with ALTER TABLESPACE ... READ WRITE.
  2. Increase MAXSIZE with ALTER DATABASE DATAFILE ... AUTOEXTEND ON MAXSIZE n. Correct answer.
  3. Run ALTER TABLESPACE ... COALESCE.
  4. Enable RESUMABLE space management for the tablespace.

Explanation

When a data file reaches its MAXSIZE, raising the MAXSIZE (or adding a data file) is required to allow further growth. READ WRITE changes access mode, RESUMABLE suspends rather than grows, and COALESCE applies to dictionary-managed tablespaces.

Question 43Oracle Database 19c Administration

Which privilege must be granted to a user so they can create a table in another user's schema?

  1. CREATE ANY TABLE Correct answer.
  2. CREATE TABLE
  3. RESOURCE
  4. CREATE SESSION

Explanation

CREATE ANY TABLE is a system privilege allowing a user to create tables in any schema, including another user's. CREATE TABLE permits creation only in the user's own schema, and CREATE SESSION and RESOURCE do not grant cross-schema object creation.

Question 44Oracle Database 19c Administration

A DBA needs to capture a snapshot of optimizer statistics before making a schema change, so the previous statistics can be restored if performance regresses. Which feature supports this?

  1. Automatic statistics history retention, restored with DBMS_STATS.RESTORE_TABLE_STATS Correct answer.
  2. The RESULT_CACHE, restored with DBMS_RESULT_CACHE
  3. SQL plan baselines, restored with DBMS_SPM
  4. AWR snapshot retention, restored with DBMS_WORKLOAD_REPOSITORY

Explanation

Oracle retains historical optimizer statistics for a configurable window, and DBMS_STATS.RESTORE_TABLE_STATS (with a timestamp or SCN) restores a prior statistics version. AWR, SQL plan baselines, and the result cache do not store optimizer statistics versions.

Question 45Oracle Database 19c Administration

Which statement about a temporary tablespace group is correct?

  1. It allows a user to use temporary space from any tablespace in the group, reducing space exhaustion in a single temporary tablespace. Correct answer.
  2. It stores permanent user segments when the default tablespace is full.
  3. It is created with CREATE TEMPORARY TABLESPACE ... GROUP, which also creates the member tablespaces.
  4. It can contain only one temporary tablespace at a time.

Explanation

A temporary tablespace group lets a user's temporary segments be allocated across multiple temporary tablespaces, spreading space usage. It is created with ALTER USER ... TEMPORARY TABLESPACE group_name, and member tablespaces are created separately.

Question 46Oracle Database 19c Administration

A DBA wants to ensure a session's uncommitted transaction changes are not visible to other sessions. Which Oracle mechanism enforces this?

  1. The redo log buffer
  2. Undo data combined with SCN-based read consistency Correct answer.
  3. The buffer cache LRU lists
  4. The control file checkpoint records

Explanation

Oracle uses undo segments and system change numbers to provide read consistency, so other sessions see the pre-change data until the transaction commits. Redo logs, LRU lists, and checkpoint records do not enforce isolation.

Question 47Oracle Database 19c Administration

A DBA needs to identify which SQL statement is currently consuming the most CPU in the instance. Which approach is most appropriate?

  1. Query V$DATAFILE to find the most active data file.
  2. Query DBA_TABLES to find the largest table.
  3. Query V$LOG to find the most recently switched redo log.
  4. Query V$SQL ordered by CPU_TIME descending for the current SQL statements. Correct answer.

Explanation

V$SQL reports cumulative CPU_TIME and ELAPSED_TIME per SQL statement, so ordering by CPU_TIME identifies the top CPU consumers. Data file, redo log, and table size views do not report SQL CPU usage.

Question 48Oracle Database 19c Administration

A DBA wants to create a role that is enabled by default when a user connects, but can be disabled during a session. Which clause accomplishes this?

  1. CREATE ROLE app_role; and grant it with ALTER USER ... DEFAULT ROLE app_role; Correct answer.
  2. CREATE ROLE app_role IDENTIFIED BY pwd; and grant it with GRANT app_role TO user;
  3. CREATE ROLE app_role NOT IDENTIFIED; and grant it with GRANT app_role TO user WITH ADMIN OPTION;
  4. CREATE ROLE app_role; and grant it with GRANT app_role TO user WITH GRANT OPTION;

Explanation

A role becomes enabled by default for a user when it is listed in the user's DEFAULT ROLE clause (or granted as a default). WITH ADMIN OPTION and WITH GRANT OPTION control role administration, not default enablement.

Question 49Oracle Database 19c Administration

A DBA must recover a database to a point in time before a user accidentally dropped a table, and the database is in ARCHIVELOG mode. Which statement about incomplete recovery is correct?

  1. The database must be restored from a backup and recovered with RECOVER DATABASE UNTIL TIME, then opened with RESETLOGS. Correct answer.
  2. Incomplete recovery requires the database to be in NOARCHIVELOG mode.
  3. Only the affected data file needs recovery; the rest of the database remains online.
  4. The database can be opened normally and recovered online without a restore.

Explanation

Point-in-time recovery restores data files from backup and applies archived redo up to the target time, then opens the database with RESETLOGS to create a new incarnation. It cannot be done online, and NOARCHIVELOG mode does not support it.

Question 50Oracle Database 19c Administration

A DBA wants to ensure that a query's execution plan remains stable after statistics change. Which feature is designed for this purpose?

  1. SQL plan baselines managed by DBMS_SPM Correct answer.
  2. Automatic undo management
  3. Automatic shared memory management
  4. The database buffer cache advisory

Explanation

SQL plan baselines store accepted execution plans and prevent the optimizer from switching to a new plan unless it is verified to perform better. Memory and undo management and buffer cache advisories do not stabilize execution plans.