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

On enabling which of the following features does the database automatically create, delete and manage flashback logs inside the flash recovery area?

  1. Recovery through resetlogs

  2. Proxy archived log backups

  3. Oracle flashback database

  4. Duplicate database

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

When you enable the flashback database feature, the database automatically creates, deletes and manages flashback logs inside the flash recovery area.

Multiple choice

Which one of the following is a Data Base Management System?

  1. MS Access

  2. MS Excel

  3. MS Outlook

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

MS Access is a relational database management system designed specifically for creating and managing databases. MS Excel is a spreadsheet application, MS Outlook is an email client, and neither are database management systems.

Multiple choice

Which of the following is true about the SQL Tuning Advisor?

  1. It automatically collects statistics if they are missing

  2. It only makes recommendations to collect statistics

  3. It ignores optimizer statistics completely

  4. It uses only the available statistics
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The SQL Tuning Advisor is an Oracle tool that analyzes SQL statements and provides recommendations for performance improvement, including automatically collecting missing optimizer statistics when needed. While it can recommend statistics collection (option B), it can also proactively collect them. Option C is incorrect because the advisor heavily relies on optimizer statistics for analysis. Option D is incomplete because the advisor will collect missing statistics rather than work with incomplete data.

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

Option B is correct because it specifies COPY LEVEL 0 to create a level 0 image copy suitable for incremental backups, allocates a DISK channel (c1), and omits CHECK LOGICAL as requested. Option A includes CHECK LOGICAL, which performs logical block corruption detection - exactly what Mary wants to avoid. Option C is missing the LEVEL 0 specification required for incremental backup compatibility. Option D allocates an sbt_tape channel instead of the required DISK channel.

Multiple choice

Which system privilege must the RMAN user have to be able to connect and execute RMAN commands on a target database?

  1. SYSDBA

  2. SYSOPER

  3. No system privileges are required for the RMAN user

  4. Only SYS user can connect and execute RMAN commands on a target database
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

RMAN (Recovery Manager) requires the SYSDBA privilege to connect to a target database and perform backup and recovery operations. This is because RMAN needs to read and write critical database files, including data files, control files, and redo logs - operations that demand database administrator level privileges. SYSOPER (option B) provides more limited privileges. Option C is incorrect - privileges are mandatory. Option D is false because users other than SYS can connect if granted SYSDBA.

Multiple choice

What Oracle backup and recover file contains user and system data?

  1. Control file

  2. Datafile

  3. OnLine ReDo file

  4. Offline ReDo file
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Datafiles are the Oracle files that contain actual user data and system data stored in tables, indexes, and other database segments. Control files (option A) contain metadata about the database structure but not the actual data. Online and offline redo log files (options C and D) contain transaction records for recovery purposes, not user or system data themselves. When users insert, update, or query data, they are interacting with data stored in datafiles.

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.