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

What is the least amount of work needed if the database should be recoverable in the future?

  1. Make a complete backup right after opening the database.

  2. Copy the data file DF1 back to the backup directory.

  3. Back up the data file.

  4. Bring tablespace TS1 online.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Making a complete backup immediately after opening the database ensures that all changes made during recovery (including any uncommitted transactions that were rolled back) are preserved. This provides a recoverable baseline. Options B and C only address individual files, and option D doesn't create any backup.

Multiple choice

Which tool will help a newly appointed DBA to examine the performance of a database?

  1. Recovery Manager

  2. Oracle Enterprise Manager

  3. Oracle Universal Installer

  4. Oracle Database Configuration Assistant
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Oracle Enterprise Manager is the comprehensive monitoring and management tool for Oracle databases, providing performance dashboards, alerts, and diagnostic capabilities. Recovery Manager (RMAN) handles backups, Oracle Universal Installer manages software installation, and Database Configuration Assistant creates databases - none focus on performance examination.

Multiple choice

In which situations does the Log Writer (LGWR) process write the redo entries from the redo log buffer to the current online redo log group?

  1. When a transaction commits

  2. When a rollback is executed

  3. When the redo log buffer is about to become completely full (90%)

  4. Before the DBWn writes modified blocks in the database buffer cache to the data files
Reveal answer Fill a bubble to check yourself
A,D Correct answer
Explanation

LGWR writes redo entries to online redo log files at two key times: when a transaction commits (to ensure durability) and before DBWn writes dirty buffers to data files (to maintain recovery capability). Rollbacks do not trigger LGWR writes, and the buffer writes at 1/3 full, not 90%.

Multiple choice

MARY wants to create an image copy backup that can be used in an incremental backup set. She wants to allocate the C1 channel and create image copies of two data files. She does not want the Oracle Server session to perform logical block corruption detection. These image copies will be used in an incremental backup set. Which RUN block set of commands should she use?

  1. rman> RUN 2> CHECK LOGICAL 3> ALLOCATE CHANNEL c1 TYPE DISK; 4> DATAFILE 1 TO '/backup/f1.dbf', 5> DATAFILE 2 TO '/backup/f2.dbf';

  2. rman> RUN 2> ALLOCATE CHANNEL c1 TYPE DISK; 3> COPY LEVEL 0 4> DATAFILE 1 TO '/backup/f1.dbf', 5> DATAFILE 2 TO '/backup/f2.dbf';

  3. rman> RUN 2> ALLOCATE CHANNEL c1 TYPE DISK; 3> DATAFILE 1 TO '/backup/f1.dbf', 4> DATAFILE 2 TO '/backup/f2.dbf';

  4. rman> RUN 2> ALLOCATE CHANNEL c1 TYPE sbt_tape; 3> DATAFILE 1 TO '/backup/f1.dbf', 4> DATAFILE 2 TO '/backup/f2.dbf';
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The correct RUN block allocates a DISK channel and creates image copies with LEVEL 0 specification, making them usable for incremental backups. Option A includes CHECK LOGICAL (explicitly NOT wanted), and options C, D lack the LEVEL 0 clause needed for incremental backup compatibility. The TYPE should be DISK, not sbt_tape.

Multiple choice

A 10 GB index is to be created on the ITEM table. The index should be spread across many tablespaces, decreasing contention for index lookup, and increasing scalability and manageability. Which index would be best for this table?

  1. Bitmap

  2. Unique

  3. Partitioned

  4. Reverse key

  5. Single column
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

A partitioned index spreads a large index across multiple tablespaces, reducing contention during index lookups and improving manageability by allowing operations on individual partitions. Bitmap indexes are for low-cardinality data, unique indexes enforce uniqueness, reverse key indexes reduce contention in sequence-based PKs, and single-column describes structure not distribution.

Multiple choice

After adding a new object to a replication group, what do you need to do before it can be replicated to other master sites?

  1. Create a snapshot log for the object.

  2. Enter the object into the deferred transaction queue.

  3. Generate replication support for the object.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

After adding an object to a replication group, you must generate replication support to create the necessary triggers, procedures, and packages that track changes and propagate them to other master sites. Snapshot logs are for materialized views, and the deferred transaction queue is automatically populated - manual entry is not required.

Multiple choice

The default directory location for the sqlnet.ora file is ______.

  1. %ORACLE_HOME%BIN

  2. %ORACLE_HOME%

  3. %ORACLE_HOME%NETWORKADMIN

  4. %ORACLE_HOME%RDBMSADMIN

Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The sqlnet.ora file resides in the NETWORK/ADMIN directory under ORACLE_HOME (ORACLE_HOME/network/admin on Unix, %ORACLE_HOME%\NETWORK\ADMIN on Windows). This directory contains all SQL*Net configuration files including tnsnames.ora and listener.ora. BIN is for executables, RDBMS/ADMIN is for database scripts.

Multiple choice

Where is free space managed for a tablespace created with automatic segment-space management?

  1. In the extent

  2. In the control file

  3. In the data dictionary

  4. In the undo tablespace
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

With automatic segment-space management (ASSM), free space is tracked using bitmaps stored in the segment header itself (within the extent), not in the data dictionary or control file. This eliminates contention on dictionary tables and improves space management efficiency. Undo tablespaces store rollback data, not free space maps.

Multiple choice

Which of the following statements about the use of the LOG_ARCHIVE_DEST_n initialization parameters is true?

  1. When defining multiple archive destinations, at least one LOCATION parameter must be included.

  2. The maximum number of remote archive destinations is ten.

  3. Archive destinations may only be modified at the system level.

  4. Possible archive destinations include the local disk, any remote database, or a remote standby database.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

When defining multiple LOG_ARCHIVE_DEST_n destinations, at least one must be a local disk destination (mandatory for the primary database). The maximum is 10 or 31 depending on Oracle version, not specifically 10. Destinations can be modified at both system and session level. Options include local disk and remote standby databases, but not arbitrary remote databases without Data Guard.

Multiple choice

Which of the following are considered as types of segments?

  1. Only LOBS

  2. Only nested tables

  3. Nested tables and index-organized tables

  4. Nested tables, LOBS, index-organized tables, and boot straps

  5. All of the above
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Oracle recognizes LOBs, nested tables, and index-organized tables as types of segments. Bootstrap segments exist internally but are not user-accessible segment types. Options A, B, C are incomplete (each lists only some types). Option E would be correct if D stated 'All of the above', but D lists specific types accurately.

Multiple choice

Which of the following is not a responsibility of DBWR process?

  1. Defers writes for I/O optimization

  2. Uses a LRU algorithm to keep most recently used blocks in memory

  3. Writes to database redo log files as people COMMIT data

  4. Writes all changed (i.e. dirty) buffers to datafiles
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The DBWR (Database Writer) process writes dirty buffers to datafiles, uses LRU algorithm for buffer management, and can defer writes for I/O optimization. Writing to redo log files is the responsibility of LGWR (Log Writer), not DBWR. LGWR writes redo entries as users COMMIT data. This is a fundamental separation of duties in Oracle's process architecture.

Multiple choice

The ____________ are the views that get continuously updated even while the database is open or in use.

  1. materialized views

  2. dynamic performance views

  3. oracle view

  4. Both (1) and (2)
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Materialized views can be set to continuously refresh even while the database is open and active through the ON COMMIT refresh option. Dynamic performance views (V$ views) are updated continuously as well, but the question specifies 'views' in the context of stored data objects. 'Oracle view' is not a standard category name. Option A is correct as materialized views are designed to maintain pre-computed results with ongoing refresh capabilities.

Multiple choice

A loss of data files was experienced in a database. The control files however, were not lost. RMAN was being used to restore and recover the database. Currently, the instance is shut down. Which sequence of commands is correct to restore and recover the database?

  1. RMAN> STARTUP NOMOUNT; RMAN> RESTORE DATABASE; RMAN> RECOVER DATABASE; RMAN> ALTER DATABASE OPEN;

  2. RMAN> STARTUP MOUNT; RMAN> RESTORE DATABASE; RMAN> RECOVER DATABASE; RMAN> ALTER DATABASE OPEN

  3. RMAN> STARTUP NOMOUNT; RMAN> RESTORE DATABASE; RMAN> RECOVER DATABASE; RMAN> ALTER DATABASE OPEN RESETLOGS;

  4. RMAN> STARTUP MOUNT; RMAN> RESTORE DATABASE; RMAN> RECOVER DATABASE; RMAN> ALTER DATABASE OPEN RESETLOGS;
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

When data files are lost but control files are intact, the database starts in NOMOUNT state to read control files, then RESTORE DATABASE recovers the data files from backup, RECOVER DATABASE applies archived and online redo logs, and ALTER DATABASE OPEN opens normally without RESETLOGS since no control file creation occurred. Option B is incorrect because NOMOUNT is required when instance is down. Option C and D incorrectly use RESETLOGS unnecessarily.

Multiple choice

In a DSS application, the sales summary report queries data from seven different tables and groups the data by region and period. Which one of the following storage structures is a good design choice to improve query performance?

  1. IOT

  2. Cluster

  3. Materialized View

  4. Partion Table
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Materialized Views pre-compute and store complex query results, especially aggregations from multiple tables. For a DSS (Decision Support System) reporting query joining 7 tables with GROUP BY operations, a Materialized View eliminates the need to re-execute the expensive join and aggregation on each report run.