50 Oracle Database Administration I Practice Questions: Question Bank 2025

Which Oracle Database background process is responsible for writing modified data blocks from the database buffer cache to the data files?

A database administrator needs to shut down an Oracle database instance immediately due to a critical hardware failure, without waiting for users to disconnect or transactions to complete. Which shutdown command should be used?

Which file contains the connection descriptor information that allows client applications to connect to an Oracle database service?

A DBA needs to create a user account that can connect to the database and create tables in their own schema. Which minimum privileges must be granted?

An application requires a tablespace where data files automatically extend when space is needed, but growth should be limited to prevent filling up the disk. Which CREATE TABLESPACE clause combination should be used?

A database is experiencing performance issues, and the DBA notices that undo tablespace is constantly running out of space. Which parameter should be checked and potentially increased to retain undo data longer for read consistency?

A DBA wants to view all current database sessions and identify which users are currently connected. Which data dictionary view provides this information?

A company requires that users' passwords expire every 90 days and must be different from the last 5 passwords used. How should the DBA implement this requirement?

During a complete database recovery after media failure, the DBA has restored all data files from backup. The control file and all redo log files are intact. Which RMAN command sequence will complete the recovery and open the database?

A database administrator needs to configure the listener to support multiple database instances on the same server, with different service names routing to different instances. After adding service entries to listener.ora and reloading the listener, connections still fail. What is the most likely cause?

A database administrator needs to create a new user account that can only connect to the database but should not have any privileges on database objects. Which SQL statement accomplishes this?

An Oracle database instance has crashed unexpectedly. Upon restart, which background process is primarily responsible for performing instance recovery by applying redo log entries?

You need to configure the listener to accept connections on multiple ports. The database server has IP address 192.168.1.100 and you want the listener to accept connections on ports 1521 and 1526. Which listener.ora configuration is correct?

Which statement about undo tablespace management is correct in Oracle Database?

A DBA needs to prevent a specific user account from consuming excessive system resources. Which Oracle feature should be implemented to limit the CPU time per session and the number of concurrent sessions for this user?

During database startup, you receive the error 'ORA-01034: ORACLE not available' when trying to connect. What is the most likely cause and solution?

A tablespace is running out of space and you want to add a datafile that will automatically extend as needed. Which SQL statement correctly creates an 100MB datafile that can autoextend in 50MB increments up to a maximum of 2GB?

Your database is running in NOARCHIVELOG mode. Which backup and recovery capabilities are available in this configuration?

An application uses a global temporary table (GTT) to store intermediate results during transaction processing. The DBA notices that the temporary tablespace is growing rapidly. Which characteristic of global temporary tables should the DBA verify to address this issue?

A database administrator needs to configure the database to listen for connection requests through a specific service name 'PRODDB_APP' that should only be used by application servers, while 'PRODDB' remains the default service. Which approach correctly implements this requirement?

You need to configure the Oracle listener to accept connections on multiple ports. Which approach should you use in the listener.ora file?

A user reports they cannot connect to the database using SQL*Plus. You check and find that the LISTENER service is running. What is the MOST likely cause if local connections work but remote connections fail?

Your database has a tablespace with three datafiles. You need to move one datafile to a different disk location while the database is open. Which sequence of steps is correct?

Which statement accurately describes the relationship between undo retention and undo tablespace sizing?

You are creating a new user and want to ensure they can only connect to the database during business hours (8 AM to 6 PM on weekdays). What is the appropriate approach?

Which background process is responsible for writing modified database blocks from the buffer cache to datafiles?

A database administrator needs to perform maintenance that requires shutting down the database. Users are currently connected and executing transactions. Which shutdown mode will wait for all current transactions to complete but prevent new connections?

What is the primary purpose of the Fast Recovery Area (FRA) in an Oracle database?

Your database is experiencing performance issues. You discover that queries using temporary space are failing because the default temporary tablespace is full and cannot extend. What is the BEST immediate solution?

A user has been granted the CREATE SESSION privilege but cannot query tables in another schema even though they have been granted SELECT on those specific tables. The user receives 'table or view does not exist' errors. What is the MOST likely cause?

You need to create a user account that can connect to the database but should not own any objects. The user should authenticate using a password. Which SQL statement accomplishes this?

A DBA needs to configure the listener to accept connections on multiple ports for security segmentation. Which approach should be used in the listener.ora configuration file?

After a system crash, you notice that the TEMP tablespace is consuming no space even though the database is operational. What explains this behavior?

You are configuring a profile to enforce password policies. You want passwords to expire after 90 days and allow a 7-day grace period for users to change their passwords. Which profile parameters should you set?

A tablespace is running out of space and you need to add a new datafile. The tablespace is using Automatic Segment Space Management (ASSM). What is the recommended approach?

An application is experiencing 'ORA-01555: snapshot too old' errors. The undo tablespace has sufficient space and UNDO_RETENTION is set to 900 seconds. What is the most likely cause?

You need to grant a user the ability to create tables in their own schema and also create tables in any schema. Which combination of privileges is required?

During database startup, the instance fails at the MOUNT stage with an error indicating it cannot find the control file. You verify that all control file locations specified in the parameter file exist and are accessible. What is the most likely cause?

You need to perform an RMAN backup that includes all datafiles, the control file, and the current server parameter file. Which RMAN command accomplishes this in a single operation?

Your database uses Automatic Memory Management (AMM). You need to increase the maximum size of the SGA and PGA combined from 4GB to 8GB while the instance is running. Which parameter must you modify, and what is the restriction?

A DBA needs to configure the database to automatically gather optimizer statistics for all tables that have stale or missing statistics. Which Oracle feature should be used to accomplish this task?

An application user reports that their session was terminated with an ORA-02393 error. Upon investigation, you discover that the UNDO tablespace ran out of space during a long-running UPDATE operation. What is the most appropriate long-term solution to prevent this issue?

You need to create a user profile that prevents users from reusing their last 5 passwords and requires passwords to be changed every 90 days. Which profile parameters should be configured?

During a database instance startup, the alert log shows that the instance stopped at the MOUNT stage and did not proceed to OPEN. What is the most likely cause of this issue?

A DBA wants to enable Flashback Database to support point-in-time recovery without using backups. Which prerequisite must be met before Flashback Database can be enabled?

You are troubleshooting connectivity issues and notice that some client connections are using the DEDICATED server process while others use SHARED server processes. Which listener configuration parameter determines the default server mode for incoming connections?

A tablespace is configured with the segment space management set to AUTO. What does this configuration control?

An organization has a requirement to implement a security policy where users can only access rows in the EMPLOYEES table that belong to their own department. The department ID is stored in an application context. Which Oracle feature is most appropriate for implementing this requirement?

A database is experiencing performance issues during peak hours. Upon investigation, you find that many sessions are waiting on 'db file sequential read' wait events. The database has sufficient memory allocated to the buffer cache. What is the most likely cause and solution?

You need to move a datafile from one location to another while the database is open and operational. The tablespace containing the datafile is a standard permanent tablespace (not SYSTEM or SYSAUX). What is the correct procedure?