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

When allowing RMAN to backup archived redo log files, how does RMAN know which archived redo logs to back up from the archive log destination?

  1. RMAN keeps a list of archived logs in a text file

  2. The DBA must manually tell RMAN which archive logs to backup

  3. RMAN has access to target control file and/or catalog information

  4. RMAN looks at the ARCHIVE_LOG_DEST parameter of the target database and backs up all archive logs found
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

RMAN determines which archived redo logs to back up by querying the target database's control file, which contains a complete record of all archived logs. When a recovery catalog is used, RMAN can also access that repository for this information. RMAN doesn't use text files (option A), require manual specification (option B), or simply backup everything in ARCHIVE_LOG_DEST without checking (option D) - it intelligently tracks which logs are needed based on the control file's repository.

Multiple choice

A database has several simultaneous long-running insert transactions. The following statement was issued to ensure that multiple server processes are used to roll back uncommitted transactions:

ALTER SYSTEM SET FAST_START_PARALLEL_ROLLBACK = 5;

What is the result?

  1. Up to five rollback processes may be activated simultaneously

  2. The parameter will be ignored because the maximum number of rollback processes is four

  3. The parameter will be ignored because it is set to an invalid value

  4. This parameter will be ignored unless the RECOVERY_PARALLELISM parameter is used
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The FAST_START_PARALLEL_ROLLBACK parameter in Oracle can only be set to FALSE, LOW, or HIGH - not a numeric value. Setting it to 5 is invalid syntax, so Oracle ignores this setting and uses the default behavior. The numeric options A and B are incorrect because numeric values are not valid for this parameter. Option D is incorrect because RECOVERY_PARALLELISM is a separate parameter unrelated to rollback behavior.

Multiple choice

Which of the following packages should be used to grant resource groups to users?

  1. DBMS_REPCAT_AUTH

  2. DBMS_REPCAT_ADMIN

  3. DBMS_RESOURCE_MANAGER

  4. DBMS_RESOURCE_MANAGER_PRIVS
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

DBMS_RESOURCE_MANAGER_PRIVS is the Oracle package specifically designed for granting and revoking resource manager privileges to users and roles. It contains procedures like GRANT_SYSTEM_PRIVILEGE and REVOKE_SYSTEM_PRIVILEGE for managing resource group access. DBMS_REPCAT_AUTH and DBMS_REPCAT_ADMIN are for replication management. DBMS_RESOURCE_MANAGER is the main package for creating and managing resource plans and consumer groups, not for granting privileges.

Multiple choice

Which of the following actions cause a log switch?

  1. A transaction completes.

  2. The instance is started.

  3. The current online redo log group is filled.

  4. The ALTER SYSTEM SWITCH LOGFILE command is issued.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The current online redo log group is filled.

Multiple choice

An application that uses a database keeps cursors open. What should be confirmed before changing the CURSOR_SPACE_FOR_TIME parameter in the database initialization file to TRUE?

  1. The TIMED_STATISTICS parameter is set to TRUE.

  2. The hit percentage in the buffer cache is at least 95%.

  3. The OPEN_CURSOR parameter is set to at least twice the default value.
  4. The value in the RELOADS column of V$LIBRARYCACHE is consistently zero or close to zero.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

CURSOR_SPACE_FOR_TIME set to TRUE tells Oracle to keep cursors in the shared SQL area even after they're closed, trading memory for faster execution. This only makes sense if cursors are being frequently re-executed without needing reload. The V$LIBRARYCACHE view's RELOADS column shows how many times library cache pins had to be reloaded - if this is consistently zero or close to zero, it means the current cache size is sufficient and there's no benefit to changing this parameter. The other options are unrelated to cursor caching behavior.

Multiple choice

A cluster is a schema object that contains data from _____________ , all of which have one or more columns in common.

  1. one table

  2. one or more tables

  3. two or more tables

  4. atleast two tables
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

In Oracle, a cluster is a schema object that stores data from two or more tables that share one or more common columns. Tables in a cluster are physically stored together based on the shared cluster key, which improves performance for joins between these tables. A single table cannot form a cluster, and 'at least two' is less precise than 'two or more'.

Multiple choice

Which of the following components of an instance holds session variables and arrays when you are NOT running in MTS mode?

  1. SGA

  2. SQL Area

  3. Library Cache

  4. PGA

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

The PGA (Program Global Area) is a memory region that stores session-specific information and variables for each user session when NOT running in MTS (Multi-Threaded Server) mode. It contains private SQL areas, session variables, and other session-specific data structures. The SGA is shared across all sessions, while the PGA is exclusive to each session.

Multiple choice

Which of the following memory areas is used to cache the data dictionary information?

  1. Database Buffer Cache

  2. PGA

  3. Redo Log Buffer

  4. Shared Pool
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The Shared Pool in the SGA (System Global Area) caches data dictionary information, including table definitions, user privileges, and other metadata. This cache is frequently accessed by SQL parsing and execution. The Database Buffer Cache stores actual data blocks, PGA is for session-specific memory, and the Redo Log Buffer holds transaction log entries before they're written to redo log files.

Multiple choice

Which of the following options is not contained in the Program Global Area (PGA)?

  1. Sort Area

  2. System Change Number (SCN)

  3. Session Information

  4. Cursor state

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

The System Change Number (SCN) is stored in the SGA's control files and data headers, not in the PGA. The PGA contains session-specific memory areas like the Sort Area (for sorting operations), Session Information (user session data), and Cursor State (information about open cursors). The SCN is a database-wide mechanism for tracking changes and maintaining consistency, not session-specific.

Multiple choice

A media failure occurred causing NOARCHIVELOG mode database to crash. It was discovered that two of the data files are corrupt. The last whole closed database backup was taken one week ago with a online redo log sequence number of 83. At the time the data files were corrupted, the online redo log sequence number was 85. Although there were few committed transactions after the last backup, as much data as possible has to be recovered. Which of the following recovery processes should be used?

  1. Shut down the instance, restore all of the Oracle files from the most recent backup, and restart the instance

  2. Manually restore and recover the two corrupt data files from the most recent backup

  3. Shut down the instance, restore the two corrupt data files from the most recent backup, and restart the instance

  4. Shut down the instance, restore all of the Oracle files from the most recent backup, restart the instance, and open the database using the 'ALTER DATABASE OPEN RESETLOGS' statement
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

In NOARCHIVELOG mode, no archived redo logs are maintained, making point-in-time recovery impossible. When media failure occurs, you can only restore from the last whole closed backup - you cannot apply incremental changes. Restoring only corrupt files won't work because the database would be in an inconsistent state. RESETLOGS is for archived log recovery scenarios. The only option is to restore all files from backup, accepting data loss since the last backup.

Multiple choice

The alert log file for a database instance indicated that the checkpoints are frequently failing to complete. Which of the following actions would be a remedy in this situation?

  1. Increase the number of archiver (ARCn) processes

  2. Increasing the number of members for all log groups

  3. Increasing the number of log writer (LGWR) processes

  4. Increasing the number of database writer (DBWn) processes
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Increasing the number of database writer (DBWn) processes

Multiple choice

An Oracle System Change Number (SCN) _______.

  1. is a value that is incremented whenever a dirty read occurs

  2. is incremented whenever a deadlock occurs

  3. is a value that keeps track of explicit locks

  4. is a value that is incremented whenever database changes are made
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Oracle SCN (System Change Number) is a logical timestamp that increments whenever database changes are made. It's used for data consistency, recovery, and tracking the order of transactions. It is NOT related to dirty reads, deadlocks, or explicit locks.

Multiple choice

An application that uses a database keeps cursors open. What should be confirmed before changing the CURSOR_SPACE_FOR_TIME parameter in the database initialization file to TRUE?

  1. The TIMED_STATISTICS parameter is set to TRUE.

  2. The hit percentage in the buffer cache is at least 95%.

  3. The OPEN_CURSOR parameter is set to at least twice the default value.
  4. The value in the RELOADS column of V$LIBRARYCACHE is consistently zero or close to zero.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

CURSOR_SPACE_FOR_TIME=TRUE keeps cursors in memory to reduce reparsing. Before setting it, confirm V$LIBRARYCACHE shows RELOADS at or near zero, indicating cursors aren't being reloaded. This prevents wasting memory if the cache is already ineffective.

Multiple choice

Of the three ways to create an Oracle database, which one is the easiest and most recommended?

  1. Using the Oracle Database Configuration Assistant.

  2. Using the Oracle-supplied database creation procedures.

  3. Using the SQL CREATE DATABASE command.

  4. None of the above is correct.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The Oracle Database Configuration Assistant (DBCA) is a GUI tool that automates database creation with wizards and templates. It handles all complex setup steps including parameter files, password files, and initialization parameters. Manual methods require deep expertise and are error-prone. DBCA is Oracle's recommended approach for most production and development environments.

Multiple choice

Which of the following statements is true with regard to the shared pool?

  1. The shared pool CANNOT be dynamically resized.

  2. The shared pool contains only fixed structures.

  3. The shared pool consists of the library cache and buffer cache.

  4. The shared pool stores the most recently executed SQL statements and the most recently accessed data definitions.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The shared pool is a memory structure in the SGA that caches parsed SQL statements, PL/SQL code, and data dictionary definitions. It consists of the library cache and dictionary cache, NOT the buffer cache (which stores actual data blocks). This caching eliminates the need to re-parse frequently executed statements, significantly improving performance.