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

Which of the following SQL statements results in a disconnection after a session is idle for 30 minutes?

  1. alter session set idle_timeout=30;

  2. alter session set idle_timeout=1800;

  3. alter profile limit idle_time 30;

  4. alter profile set idle_timout 30;
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

To disconnect idle sessions, use ALTER PROFILE with IDLE_TIME parameter (measured in minutes). Options A and B use non-existent IDLE_TIMEOUT parameter. Option D incorrectly uses SET instead of LIMIT and has typo (IDLE_TIMOUT). Option C correctly uses ALTER PROFILE LIMIT IDLE_TIME 30.

Multiple choice technology databases

Which three information are to be mandatorily provided while creating a new listener using Enterprise Manager Database Control?

  1. The database services to be registered with the listener

  2. The log file and trace file destination for the listener.

  3. The protocol used by the listener

  4. The server name where the listener runs.

  5. The port used by the listener.
Reveal answer Fill a bubble to check yourself
C,D,E Correct answer
Explanation

When creating a new listener, you must specify the protocol (typically TCP), the host/server name where the listener runs, and the port number it listens on.

Multiple choice technology databases

You specified segment space management as automatic for a tablespace what effect would this have on space management?

  1. Extents would be managed by freelists.

  2. Free space would be managed by data dictionary.

  3. The segment would be managed by bitmaps.

  4. The segment would be managed by freelists.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Automatic Segment Space Management (ASSM) uses bitmaps to track free space within segments, replacing traditional freelist management. When you specify segment space management as automatic, Oracle uses bitmap-based space tracking. Freelists and data dictionary management are manual space management techniques.

Multiple choice technology databases

A user wants to connect to the database instance from an application that is running on a remote machine. Which tool should the DBA use to establish the required configuration to ensure that the user is able to connect to the database instance? (Choose two).

  1. Oracle Net Manager.

  2. Data Pump.

  3. Oracle Universal Installer (OUI).

  4. Oracle Enterprise Manager.

  5. Database Configuration Assistant (DBCA).
Reveal answer Fill a bubble to check yourself
A,D Correct answer
Explanation

Oracle Net Manager is the GUI tool for configuring network parameters like listener.ora and tnsnames.ora files. Oracle Enterprise Manager provides a web-based interface for database administration including network configuration. Data Pump is for data transfer, OUI is for software installation, and DBCA is for database creation - none handle network connectivity setup.

Multiple choice technology databases

Which two are valid locking levels that are used by transactions in oracle database? (Choose two)

  1. Object level.

  2. Row level.

  3. Block level.

  4. Schema level.

  5. Database level.
Reveal answer Fill a bubble to check yourself
A,B Correct answer
Explanation

Row-level locking is the most granular and commonly used locking mechanism in Oracle, allowing concurrent transactions to access different rows. Object-level locking applies to entire tables, views, or other schema objects and is used for DDL operations or table locks. Block-level, schema-level, and database-level locking are not standard Oracle locking granularity levels.

Multiple choice technology databases

Your database is having two control files; three redo log file groups with two members in each group. Failure of which file would cause a instance to shut down?

  1. Loss of the initialization parameter file.

  2. One of the redo log members.

  3. Any data file belonging to the default permanent tablespace

  4. Any archive log file.

  5. Any control file.
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

The loss of any control file will cause an Oracle instance to immediately shut down because control files are continuously accessed and must be synchronized. Redo log members are multiplexed, so losing one member of a group does not crash the instance.

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.