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

MARY used an ALTER DATABASE statement on her database to perform an online control file backup. In which mode is her database operating?

  1. FAILOVER

  2. FASTBACK

  3. ARCHIVELOG

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

The correct answer is C because online control file backups can only be performed when the database is in ARCHIVELOG mode. This mode is necessary to ensure that all redo logs are archived before the control file backup occurs. Options A, B, and D are not valid database operating modes for this purpose.

Multiple choice

You have noticed that you are left with some old backups which are no longer required. You have been using RMAN and now you want to have a list of these files. Which Recovery Manager task will accomplish this?

  1. LIST NEED BACKUP;

  2. REPORT EXPIRED BACKUPS;

  3. REPORT OBSOLETE;

  4. LIST OBSOLETE BACKUPS;

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

The correct answer is C because REPORT OBSOLETE is the correct RMAN command to list backups that are no longer needed based on the retention policy. Option A (LIST NEED BACKUP) is not a valid RMAN command. Option B (REPORT EXPIRED BACKUPS) refers to backups that have exceeded their retention period, not those that are simply no longer needed. Option D (LIST OBSOLETE BACKUPS) is not the correct syntax.

Multiple choice

The first step in placing a database into ARCHIVELOG mode has been performed. The second step is deciding whether the online redo log files are to be archived automatically or manually. Which action should be taken to enable archiving at instance startup?

  1. Immediately after setting the database in ARCHIVELOG mode, in SQL*Plus select automatic archiving using this syntax: ALTER DATABASE SET LOG_ARCHIVE_START=TRUE.

  2. Automatic archiving is the default when ARCHIVELOG mode is set. No further actions are required.

  3. Set the LOG_ARCHIVE_MAX_PROCESSES parameter to n (where n is the number of Archiver processes) in the initialization parameter file.

  4. Set the LOG_ARCHIVE_START parameter to TRUE in the initialization parameter file.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The correct answer is D because LOG_ARCHIVE_START is the initialization parameter that enables automatic archiving when set to TRUE. Option A is incorrect because ALTER DATABASE SET LOG_ARCHIVE_START is not valid syntax. Option B is incorrect because automatic archiving is not the default. Option C is incorrect because LOG_ARCHIVE_MAX_PROCESSES controls the number of archiver processes, not whether archiving is enabled.

Multiple choice

A data file was moved to another physical location because of a media failure, and the ALTER DATABASE RENAME FILE statement was issued. Which file is automatically updated to reflect this change?

  1. Control

  2. Alert

  3. Registry

  4. Parameter
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The correct answer is A because the control file is automatically updated to reflect the new data file location when ALTER DATABASE RENAME FILE is executed. The control file maintains the database's physical structure. Options B (Alert log), C (Registry), and D (Parameter file) are not automatically updated by this operation.

Multiple choice

User Smith created indexes on some tables owned by user John. The following needs to be displayed:

index names index types

Which data dictionary view(s) should be queried?

  1. DBA_INDEXES only

  2. DBA_IND_COLUMNS only

  3. DBA_INDEXES and DBA_USERS

  4. DBA_IND COLUMNS and DBA_USERS
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

DBA_INDEXES is the data dictionary view that contains both index names (INDEX_NAME column) and index types (INDEX_TYPE or TYPE column). DBA_IND_COLUMNS only contains column-level information about indexes. DBA_USERS contains user information, not index details.

Multiple choice

In database theory, what is a virtual table called?

  1. Abstraction layer

  2. View

  3. Database engine

  4. Database trigger

  5. Relation
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

A view is the result set of a stored query — or map-and-reduce functions — on the data, which the database users can query just as they would a persistent database collection object. This pre-established query command is kept in the database dictionary. Unlike ordinary base tables in a relational database, a view does not form part of the physical schema: as a result set, it is a virtual table computed or collated from data in the database, dynamically when access to that view is requested.

Multiple choice

A database administrator's responsibilities does not include which of the following tasks?

  1. Maintaining archived data

  2. Installing the database server and application tools

  3. Modifying the database structure as per the requirement

  4. Marketting the software

  5. Enrolling users and maintaining system security
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Marketting the software is not a responsibility of the DBA rather it is the responsibility of the marketting team.

Multiple choice

In the context of relational databases, which of the following is used to establish and enforce a link between the data in two tables?

  1. Compound key

  2. Primary key

  3. Foreign key

  4. Alternate key

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

A foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table. In other words, a foreign key is a column or a combination of columns that is used to establish and enforce a link between the data in two tables.

Multiple choice

Which initialization parameter affects the roll forward phase of recovery by manipulating the frequency at which checkpoints are written?

  1. FAST_START_IO_TARGET

  2. FAST_START_MTTR_TARGET

  3. LOG_CHECKPOINT_TIMEOUT

  4. LOG_CHECKPOINT_INTERVAL
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

FAST_START_MTTR_TARGET controls the Mean Time To Recover (MTTR) and indirectly affects checkpoint frequency. A lower MTTR target requires more frequent checkpoints, which speeds up the roll forward phase of recovery by reducing redo to apply. Other options either don't control checkpoint frequency (LOG_CHECKPOINT_* parameters control timing directly) or aren't primarily about MTTR.

Multiple choice

The ARCn processers are writing online redo log files to one local mandatory destination, one local optional destination, and one remote mandatory destination. The LOG_ARCHIVE_MIN_SUCCEED_DEST parameter is set to 2. What effect does this parameter have on the automatic archival process?

  1. After the archival of online redo log files to the two mandatory destinations occurs, the online redo logs may be overwritten.

  2. After the archival of online redo log files to the two local destinations occurs, the online redo logs may be overwritten.

  3. No effect because the LOG_ARCHIVE_MIN_SUCCEED_DEST parameter value is not equal to the number of local mandatory archive destinations.

  4. After the archival of online redo log files to the one local mandatory destination occurs, the online redo logs may be overwritten.
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

LOG_ARCHIVE_MIN_SUCCEED_DEST=2 means archival must succeed to at least 2 destinations before redo logs can be overwritten. The destinations include 1 local mandatory + 1 local optional = 2 local destinations. The remote mandatory is the third destination but isn't counted for the minimum since we already have 2 local ones. Option B correctly identifies that archival to both local destinations is sufficient.

Multiple choice

JOHN has been taking snapshots once a week over the last two years. He will use these snapshots to outline the cost associated with the increase in database resources over the past two years. Which tool or utility would allow him to compare the snapshots?

  1. LOCK MANAGER

  2. STATSPACK

  3. ORACLE EXPERT

  4. UTLBSTAT /UTLESTAT
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

STATSPACK is the correct tool for comparing snapshots over time. It captures database performance statistics at intervals and provides reports to compare different snapshots. LOCK MANAGER handles locking, ORACLE EXPERT is for tuning recommendations, and UTLBSTAT/UTLESTAT are older tools replaced by STATSPACK.

Multiple choice

What will the command EXECUTE DBMS_SCHEDULER.PURGE_LOG(); do?

  1. Purge only the window logs.

  2. Purge only the job logs.

  3. Purge all window and job logs.

  4. Purge only yesterday's job logs.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

DBMS_SCHEDULER.PURGE_LOG() purges both window logs and job logs from the scheduler. It doesn't selectively purge only one type or only yesterday's logs. The command cleans up all accumulated logs to manage disk space.

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

With control files intact, STARTUP NOMOUNT is sufficient (control files exist so MOUNT isn't needed). The sequence is: NOMOUNT → RESTORE → RECOVER → OPEN. RESETLOGS is only required after incomplete recovery or missing logs, which isn't stated here. Option A shows the correct sequence without RESETLOGS.

Multiple choice

Which data dictionary view shows the available free space in a certain tablespace?

  1. DBA_EXTENTS
  2. V$FREESPACE

  3. DBA_FREE_SPACE

  4. DBA_TABLESPACE

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

DBA_FREE_SPACE is the data dictionary view that displays information about free extents available in tablespaces. Each row represents one free extent in a tablespace, showing the tablespace name, file ID, block ID, and bytes/blocks free.