Computer Knowledge

Database Management Systems

5,543 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

Which statements are true regarding the logical structure of database? (Choose three)

  1. It is possible to have tablespaces of different block sizes in a database.

  2. A data block is the smallest unit of I/O for data files.

  3. Multiple tablespaces can share single data file.

  4. Each data block in the database always corresponds to one OS block.

  5. Each segment contains one or more extents.
Reveal answer Fill a bubble to check yourself
A,B,E Correct answer
Explanation

Oracle supports multiple block sizes in a single database using different tablespaces with specific BLOCKSIZE clauses. A data block is indeed the smallest unit of I/O operations - Oracle reads entire blocks. Each segment (like a table or index) is composed of one or more extents. However, multiple tablespaces cannot share a single data file - each data file belongs to exactly one tablespace. Data blocks do not necessarily correspond one-to-one with OS blocks.

Multiple choice technology databases

Which three statements are true about the stages of database startup?

  1. Data files and redo log files can be renamed at the MOUNT stage.

  2. Control files are required to bring the database to the NOMOUNT stage.

  3. Data files and online redo log files are checked for consistency while opening the database.

  4. Data files and redo log files are made available to users at the OPEN stage.

  5. Control files are read at the OPEN stage for the location of data files.
Reveal answer Fill a bubble to check yourself
A,B,D Correct answer
Explanation

At NOMOUNT stage, Oracle reads the parameter file to locate control files but control files themselves are required. At MOUNT stage, control files are read and data files/redo logs can be renamed using ALTER DATABASE commands. At OPEN stage, Oracle checks file consistency and makes data accessible to users. Control files are read at MOUNT stage, not OPEN stage. The consistency check happens during MOUNT, not OPEN.

Multiple choice technology databases

Identify two tablespaces whose data files are so critical that only a closed recovery is possible?

  1. USERS

  2. TOOLS

  3. UNDO

  4. INDEX

  5. SYSTEM
Reveal answer Fill a bubble to check yourself
C,E Correct answer
Explanation

To solve this question, the user needs to know about the concept of tablespaces and recovery methods in Oracle database.

Closed recovery is a type of database recovery method in which all the data files of the database are restored from a backup and then the redo logs are applied to bring the database to a consistent state. Closed recovery is required when the database is in a state of complete failure and no data is available in the memory structures.

Only those tablespaces whose data files are so critical that they cannot be recovered using an open recovery method require a closed recovery.

Now, let's go through each option and identify if it requires a closed recovery or not:

A. USERS: This tablespace contains the user's data. It does not require a closed recovery if it gets damaged because it can be restored using an open recovery method.

B. TOOLS: This tablespace contains the database tools data. It does not require a closed recovery if it gets damaged because it can be restored using an open recovery method.

C. UNDO: This tablespace contains the undo data. It does not require a closed recovery if it gets damaged because it can be restored using an open recovery method.

D. INDEX: This tablespace contains the database index data. It does not require a closed recovery if it gets damaged because it can be restored using an open recovery method.

E. SYSTEM: This tablespace contains the system data. It is critical to the functioning of the database. If its data files are damaged, a closed recovery is required to restore it from a backup.

Therefore, the correct answer is:

The Answer is: E (SYSTEM)

Multiple choice technology databases

Data files of which three tablespaces can be recovered by performing an open recovery?

  1. TEMP

  2. UNDO

  3. INDEX

  4. SYSAUX

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

TEMP tablespace files can be recovered while the database is open because temporary data is reconstructable. INDEX tablespace is purely structural - indexes can be rebuilt. SYSAUX contains auxiliary data that can be recovered online. SYSTEM tablespace requires the database to be closed for recovery as it's critical for database operation. UNDO tablespace requires special handling and typically needs the database closed.

Multiple choice technology databases

One user session was terminated abnormally in the middle of a transaction What does oracle database do to recover it?

  1. uses undo data for roll forward

  2. uses flashback log for recovery

  3. uses online redo logs to roll back

  4. uses undo data to roll back the transaction

  5. uses recovery managers (RMAN) to roll back.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

When a user session terminates abnormally, Oracle automatically uses undo data from the undo tablespace to roll back the uncommitted transaction. This maintains data consistency by reversing all changes made by the incomplete transaction. Redo logs are for roll forward during recovery, not roll back. Flashback logs and RMAN are not involved in automatic transaction rollback.

Multiple choice technology databases

You are using the following command to clear the log file group ALTER DATABASE CLEAR LOGFILE GROUP 2; In which case would the command be successful?

  1. Group 2 is the active group

  2. Group 2 is the current group

  3. Database is in the MOUNT stage

  4. Group 2 has been already archived
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The ALTER DATABASE CLEAR LOGFILE GROUP command succeeds only when the log group has been archived. This prevents accidental loss of redo data needed for recovery. The command fails if the group is current (actively being written), active (needed for crash recovery), or if the database is not in the appropriate state. The database must be at MOUNT stage to execute this command.

Multiple choice technology databases

Users in PROD database reported about the slow responses of the transaction. While investigating the reason you find that the transactions are waiting for the undo segments to be available, and undo retention has been set to zero. What would you do to overcome this problem?

  1. increase the undo retention

  2. create more undo segments

  3. create another undo tablespace

  4. increase the size of the undo tablespace.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

When undo retention is zero and transactions wait for undo segments, it means the undo tablespace is full and cannot create new extents. Increasing the size of the undo tablespace provides more space for undo segments, allowing concurrent transactions to proceed. Creating another undo tablespace doesn't help the current one. Increasing retention without space is ineffective. Creating more segments manually is not the solution.

Multiple choice technology databases

Your application demands frequent connection and disconnection from the database. You have three listener processes those are listening for the database PROD. While settings up connect string using Oracle Enterprise Manager 10g Database Control, which two options would you select to balance the connection load across all the listener process? (Choose two)

  1. Use only the first address.

  2. Try one address selected at random.

  3. Try each address, in order, until one succeeds.

  4. Try each address, randomly, until one succeeds.
Reveal answer Fill a bubble to check yourself
B,D Correct answer
Explanation

To balance load across multiple listeners, you can either randomly select one address (TRY ONE ADDRESS SELECTED AT RANDOM) or try addresses randomly until one succeeds (TRY EACH ADDRESS, RANDOMLY, UNTIL ONE SUCCEEDS). Using only the first address sends all traffic to one listener. Trying addresses in order is sequential failover, not load balancing.

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

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.