Computer Knowledge

Database Management Systems

5,344 Questions

Database Management Systems (DBMS) form the core framework for data storage, retrieval, and security in modern software applications. Concepts such as the E-R model, backup planning, SQL integration, and big data architecture are essential for computer knowledge sections. This hub offers a comprehensive set of practice questions to master DBMS fundamentals and advanced database operations.

E-R Model ConceptsBackup and RecoverySQL Server UpgradesJDBC and ODBCBig Data CharacteristicsData VirtualizationOracle Database

Database Management Systems Questions

Multiple choice technology databases

In your database you discovered that a tempfile in locally managed temporary tablespace has been deleted at the operating system level. How would you recover the tablespace?

  1. Flash back the database

  2. Perform a point-in-time recovery.

  3. Perform a full database recovery.

  4. Drop and re-create the tablespace.

  5. Use recovery manager (RMAN) to recover the database.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

If a tempfile in a locally managed temporary tablespace is deleted, the simplest and most common recovery method is to drop and recreate the temporary tablespace, or drop and recreate the tempfile itself.

Multiple choice technology databases

You started a long transaction before committing; you executed a query on one of the tables currently being modified. You received the following error: ORA- 01555: snapshot too old. How would you prevent such an error in future? (Choose three)

  1. Guarantee undo retention

  2. Add one more redo log group.

  3. Configure an appropriate undo retention interval.

  4. Size the UNDO tablespace appropriately.

  5. Size the SYSTEM tablespace appropriately.
Reveal answer Fill a bubble to check yourself
A,C,D Correct answer
Explanation

To prevent 'ORA-01555: snapshot too old' errors, you should size the UNDO tablespace appropriately, configure an adequate undo retention interval, and guarantee undo retention to prevent active undo data from being overwritten.

Multiple choice technology databases

When you trying to open your database, you receive the following error: ORA- 00205: error in identifying control file. Where would you find the details to resolve this error?

  1. audit trail

  2. alert log file

  3. user trace file

  4. online trace file

  5. background trace file.
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The alert log file is the primary location where Oracle records all database errors and critical events. ORA-00205 indicates a control file problem - the alert log will contain detailed error messages, timestamps, and often the root cause. Audit trails track user actions, trace files are for process-specific debugging, and there is no 'online trace file' - these are not used for control file error diagnosis.

Multiple choice technology databases

What advantage would you get if you increase the length of the time during which the undo information would be restored in the database

  1. roll back of large transaction

  2. roll forward to redo a transaction

  3. data concurrency for large updates

  4. read consistency for long running transactions.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Increasing undo retention allows Oracle to keep older undo data longer, which is essential for maintaining read consistency for long-running queries and transactions. This prevents 'snapshot too old' errors. It does not directly help with rolling back large transactions (that depends on undo space), roll forward (redo logs handle that), or concurrency (that's about locking, not retention).

Multiple choice technology databases

The database users are connecting the PROD database from different applications thereby degrading the database performance. The senior database administrator suspects the large number of concurrent connection to be the reason for low performance and ask you to restrict the number of concurrent connections per database user to one. Which action would you take to achieve this objective?

  1. Set the SESSION to 1 in the parameter file.

  2. Grant SINGLE SESSION privileges to all of the users.

  3. Set SESSION_PER_USER to 1 in user’s profile.

  4. Grant RESTRICTED SESSION to all of the database users.

  5. Create a role with SINGLE SESSION privilege and assign the role to users.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

To restrict the number of concurrent sessions per user to one, you should set the SESSIONS_PER_USER resource limit to 1 within the user's assigned profile and ensure resource limits are enabled.

Multiple choice technology databases

Where do you find the information about missing redo log file?

  1. trace file

  2. audit trail

  3. control file

  4. alert log file

  5. event viewer
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Oracle's alert log file records all critical database events and errors, including information about missing or corrupted redo log files. When the database encounters issues with redo logs, it writes detailed messages to the alert log. This makes the alert log the primary source for diagnosing redo log problems. The trace file contains process-specific dumps, audit trails track user activities, the control file stores database metadata, and event viewer is a Windows tool - none of these are the correct location for redo log issue information.

Multiple choice technology databases

Your database is in NOARCHIEVELOG mode. Since last night’s offline backup the logs in group 1 have been written to twice. This morning the system data file has become corrupted. Up to what point can it be recovered?

  1. Until the last commit.

  2. Cannot be recovered.

  3. Until the last offline backup.

  4. Until the beginning of the last transaction.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

In NOARCHIVELOG mode, Oracle does not preserve online redo logs for recovery purposes. Once redo logs are overwritten (they've been written twice here), any changes after the last consistent backup are lost. The database can only be recovered to the point of the last offline backup, which is when the database was in a consistent state.

Multiple choice technology databases

In oracle 10g, which statement is true about the analysis of the Automatic Database Diagnostic Monitor (ADDM)?

  1. The analysis tool doesn’t provide output for the performance of SQL statement

  2. The results of analysis are stored in tables, belonging to SYS user in the SYSAUX tablespace.

  3. The results of analysis are stored in tables, belonging to SYS user in the SYSTEM tablespace.

  4. The analysis runs automatically after each Automatic Workload Repository (AWR) snapshot.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

ADDM (Automatic Database Diagnostic Monitor) runs automatically after each AWR snapshot is taken, analyzing the performance data between snapshots. The results are stored in the SYSAUX tablespace (not SYSTEM), in tables owned by the SYS user. ADDM provides comprehensive analysis including SQL statement performance recommendations.

Multiple choice technology databases

In oracle 10g, which statement regarding the usage of redo log file during instance recovery is true?

  1. Transactions are rolled back using redo log files.

  2. Transactions are rolled forward using redo log files.

  3. Transactions are rolled back using redo log files and archived log files.

  4. Transactions are rolled forward using redo log files and rolled back using redo log files
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

During instance recovery, Oracle uses redo log files to roll forward (reapply) all committed and uncommitted changes. Uncommitted transactions are subsequently rolled back using undo data, not redo logs.

Multiple choice technology databases

You have determined that the archiving was not successful in on of the destinators. You want to check the alert log file for more information on this. Which initialization parameter gives you the location of the alert log file?

  1. USER_DUMP_DEST

  2. CORE_DUMP_DEST

  3. ALERT_LOG_DEST

  4. BACKGROUND_DUMP_DEST

  5. ALERT_DUMP_DEST
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The BACKGROUND_DUMP_DEST initialization parameter specifies the directory location for background process trace files, including the alert log (alert_.log). The alert log contains diagnostic information about database operations, errors, and archiving issues. USER_DUMP_DEST is for server process traces, CORE_DUMP_DEST for core dumps.

Multiple choice technology databases

You want to administer your database servers, which are running on different host machines using the web enabled grid control interface. You have configured the agent in the host machines. You have started Oracle database10g grid control utility on your machine. Which two additional components would you configure to achieve this objective?

  1. Application server control.

  2. Oracle Management service.

  3. Oracle enterprise manager repository.

  4. Oracle enterprise manager database control.
Reveal answer Fill a bubble to check yourself
C,D Correct answer
Multiple choice technology databases

Your database is in NOARCHIEVELOG mode and you want to perform a backup. Which type of backup would be appropriate in this case?

  1. Hot backup

  2. Online backup

  3. Consistent backup

  4. Incremental backup

  5. Inconsistent backup
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

In NOARCHIVELOG mode, you cannot perform hot (online) backups because the database must be shut down cleanly to ensure a consistent backup. A consistent backup requires the database to be closed with all changes written to disk (consistent state). Hot/incremental backups require ARCHIVELOG mode to apply archived logs during recovery.

Multiple choice technology databases

You have enabled tracking while creating a listener to your database. What additional information is available to you when you enable tracking?

  1. Information about each oracle Net connection.

  2. Information about each entry in the Listener.ora file

  3. Information about the changes done in the database.

  4. None
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

When listener tracking is enabled, the listener logs detailed information about each Oracle Net connection attempt, including client network address, connection timestamp, and any errors encountered. This helps in troubleshooting connectivity issues. It does not track Listener.ora changes (that's static configuration) or database DDL changes.

Multiple choice technology databases

Your database is opened in NOARCHIVELOG mode. You decide to place the database in ARCHIEVELOG mode. How would you do this? ( Choose two)

  1. Execute the ALTER DATABASE ARCHIVELOG statement

  2. Select ARCHIVELOG option in database control and restart the database to apply the changes.

  3. Shutdown and restart the instance, and in NOMOUNT state execute the ALTER DATABASE ARCHIVELOG statement.

  4. Shutdown and restart the instance mount the database and execute the ALTER DATABASE ARCHIVELOG statement.
Reveal answer Fill a bubble to check yourself
B,D Correct answer
Explanation

To enable ARCHIVELOG mode, the database must be in MOUNT state (not open or NOMOUNT). You can: 1) Use Database Control to select ARCHIVELOG mode and restart (option B), or 2) Manually shutdown, mount with STARTUP MOUNT, execute ALTER DATABASE ARCHIVELOG, then open. Option C is wrong because NOMOUNT doesn't access control files, and option A is incomplete without the mount state requirement.