Oracle Database Administration I Intermediate Practice Exam: Medium Difficulty 2025

Your Oracle database instance is experiencing performance issues during peak hours. Upon investigation, you discover that the shared pool is constantly being flushed and reloaded. Which combination of actions would BEST address this issue?

A DBA needs to restart a database instance that crashed unexpectedly. Upon attempting to start the instance, it progresses through NOMOUNT and MOUNT stages successfully but fails during the OPEN stage with an error about redo log corruption. What is the appropriate recovery approach?

Your organization requires that user SALES_MGR can grant SELECT privileges on the SALES_DATA table to other users, but those users should NOT be able to further grant this privilege. Which statement accomplishes this requirement?

A tablespace named SALES_TBS is configured with AUTOEXTEND ON for its datafiles. The DBA receives an alert that the tablespace is at 95% capacity. Upon investigation, they discover that a large amount of space is occupied by deleted rows from frequent DELETE operations. What is the MOST efficient approach to reclaim this space?

An application frequently runs long-running queries that sometimes fail with 'snapshot too old' errors. The DBA wants to configure the undo tablespace to retain undo data for at least 2 hours. Currently, UNDO_RETENTION is set to 900 seconds and the undo tablespace has AUTOEXTEND OFF with limited free space. What changes should be made?

A database administrator needs to allow a remote application server to connect to the Oracle database using the service name PRODDB. The listener is running on the default port, but connection attempts fail with 'TNS: could not resolve the connect identifier' error. The listener.ora is properly configured. What is the MOST likely cause and solution?

During database instance startup, you execute STARTUP MOUNT but need to perform maintenance that requires all datafiles to be offline. However, you also need to query the control file and data dictionary metadata. Which instance state allows you to accomplish this?

A user profile named APP_PROFILE has been created with PASSWORD_LIFE_TIME set to 90 days and FAILED_LOGIN_ATTEMPTS set to 3. A user assigned to this profile has forgotten their password and exceeded the failed login attempts. What steps must the DBA take to restore access?

Your database has multiple temporary tablespaces. Users in the DEVELOPERS group are experiencing performance issues with sort operations. You want to create a temporary tablespace group to distribute temporary space usage. Which sequence of commands correctly implements this solution?

A database is running in ARCHIVELOG mode. The DBA needs to take a backup while the database remains open for user transactions. Which combination of backup strategy and prerequisites is correct for this scenario?