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

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 specifies the mean time to recover (MTTR) in seconds, which directly influences checkpoint frequency. Oracle automatically adjusts checkpoint intervals to meet the specified recovery time goal, affecting the roll forward phase during crash recovery.

Multiple choice

When is the best time to tune a database application?

  1. At design time

  2. At development time

  3. Before going live

  4. When problems are experienced

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

Tuning at design time is most cost-effective and efficient. Design-time tuning prevents performance issues before code is written, avoids expensive rework later, and establishes proper architecture, indexing strategies, and access patterns from the start.

Multiple choice

When should one back up a read-only tablespace?

  1. During regular backup window

  2. Prior to placing the tablespace into read-only mode

  3. Immediately after placing the tablespace into read-only mode

  4. Prior to creating a new tablespace

  5. Prior to altering the database structure

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

A read-only tablespace should be backed up immediately after being placed in read-only mode. Once read-only, the tablespace cannot change, so this single backup suffices until the tablespace returns to read-write mode. Backing up after the mode change ensures consistency.

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 specifies the mean time to recover (MTTR) in seconds, which directly influences checkpoint frequency. Oracle automatically adjusts checkpoint intervals to meet the specified recovery time goal, affecting the roll forward phase during crash recovery.

Multiple choice

An RMAN recover catalog is to be created which stores information about the STUD database. The database should be named RCSTUD for the storage of the recovery catalog. The steps that should be followed to create a recover catalog are not displayed in the correct order. What is the correct order?

  1. Grant the appropriate privileges (CONNECT and RESOURCE) and roles (RECOVERY CATALOG OWNER) to the user responsible for performing backup and recover operations.

    2. Register the target database in the catalog.

    3. Create the catalog using the RMAN command line interpreter.

    4. Create a user and schema which will own the recovery catalog.

    5. Create tablespace to hold the catalog in the database that has been dedicated for the RMAN recovery catalog.

    6. Connect to the target database using SYSDBA privileges.

  1. 5, 4, 1, 3, 6, 2

  2. 4, 1, 5, 6, 3, 2

  3. 4, 3, 1, 5, 6, 2

  4. 4, 5, 1, 3, 2, 6

  5. 5, 4, 1, 3, 2, 6
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The correct order for creating an RMAN recovery catalog is: 5) Create tablespace first, 4) Create user/schema owner, 1) Grant necessary privileges (CONNECT, RESOURCE, RECOVERY_CATALOG_OWNER), 3) Create the catalog using RMAN, 6) Connect to target database with SYSDBA, 2) Register target database in catalog. This sequence ensures prerequisites are met before dependent steps - the tablespace must exist before creating the user, the user must exist before granting privileges, and privileges must be granted before creating the catalog.

Multiple choice

The following command was executed in MOUNT mode:

ALTER DATABASE ARCHIVELOG;

Where is the information that the database is now in ARCHIVELOG mode stored?

  1. SPFILE

  2. Archive Log

  3. Control File

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

When a database is switched to ARCHIVELOG mode using ALTER DATABASE ARCHIVELOG, this mode change is recorded in the control file. The control file stores critical database information including the database name, checkpoint information, and archiving mode. SPFILE (option A) stores initialization parameters. Archive logs (option B) are the output of archiving, not where the mode is stored. The System tablespace (option D) stores data dictionary objects, not database state information.

Multiple choice

To avoid a large number of extents for big file tablespaces in very large databases, what does Oracle recommend?

  1. Use the AUTOALLOCATE clause for allocating new extents and set a very high extent size.

  2. Use the UNIFORM clause and simply let Oracle take care of the extent sizing.

  3. Use the UNIFORM clause for allocating new extents and set a very high extent size.

  4. Use the AUTOALLOCATE clause and simply let Oracle take care of the extent sizing.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Use the UNIFORM clause for allocating new extents and set a very high extent size.

Multiple choice

When a replication site contains subsets of another site's data, what type of replication is used?

  1. Master site

  2. Snapshot site

  3. Either a snapshot or master site

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

When a replication site contains subsets of another site's data, this is called snapshot replication (also known as materialized view replication). A snapshot site stores and maintains copies of data from a master site, allowing for read-only access and periodic refresh. This is different from master-site replication (option A) which maintains full copies. Option C is incorrect because the question specifically describes a subset scenario, which characterizes snapshot sites, not a combination of both types.

Multiple choice

Which of the following statements concerning the Export/Import Utilities is true?

  1. The direct-path feature allows a user to extract database objects faster than the conventional-path.

  2. The Export Utility can be used to make a logical backup of a table, a tablespace, or a table's associated data files.

  3. The FULL database export mode exports all database objects regardless of the schema.

  4. Performing an export/import of a tablespace is quicker than using the transportable tablespace method to transfer a table and its data.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The Export/Import direct-path feature extracts data faster than conventional-path export because it bypasses the SQL evaluation layer and reads data directly from disk blocks, formatting it as Oracle export format. This reduces CPU overhead and processing time. Option B is incorrect because Export cannot directly export tablespaces or data files (those are physical storage structures, not logical database objects). Option C is incorrect because FULL export mode only exports objects in schemas that the user has privileges to access, not all objects regardless of schema. Option D is incorrect because transportable tablespaces (which copy data files) are much faster than export/import for moving large volumes of table data.

Multiple choice

Which of the following instance startup stages to a usable state where normal users might access them?

  1. STARTUP NOMOUNT, STARTUP MOUNT, STARTUP OPEN, STARTUP CLOSE

  2. STARTUP OPEN, STARTUP CLOSE, START MOUNTALL

  3. STARTUP EXIT, STARTUP START, STARTUP OPEN, STARTUP CLOSE

  4. STARTUP MOUNT, STARTUP NOMOUNT, STARTUP OPEN
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Oracle instance startup progresses through three main states: NOMOUNT (reads parameter file and starts instance), MOUNT (associates instance with control file and database), and OPEN (makes database available for users). Option D lists all three valid stages. Options A, B, and C include invalid or non-existent commands like STARTUP CLOSE, START MOUNTALL, STARTUP EXIT, and STARTUP START.

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

Online control file backups require the database to be in ARCHIVELOG mode, which enables continuous archiving of redo logs. In NOARCHIVELOG mode, the database must be shut down for consistent backups. FAILOVER and FASTBACK are not valid database modes. ALTER DATABASE with backup options requires ARCHIVELOG mode for online operation.

Multiple choice

Dynamic data replication is the way in which updating or inserting records in remote database through _________.

  1. stored procedures

  2. cursors

  3. database procedures

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

Dynamic data replication to remote databases is achieved through database triggers, which can automatically propagate DML changes to remote tables. Stored procedures and database procedures must be explicitly invoked. Cursors are query result iterators, not replication mechanisms. Triggers provide the automatic, event-driven replication needed.

Multiple choice

The database is set up to run Multithreaded Server. Which view would show contention for server processes?
  1. V$CIRCUIT
  2. V$SESSION

  3. DBA_USERS
  4. V$QUEUE
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

In Multithreaded Server (MTS) architecture, V$QUEUE displays contention statistics for dispatcher queues, showing if requests are waiting for server processes. V$CIRCUIT shows virtual circuit information. V$SESSION shows session information but not server process contention. DBA_USERS lists database users, not server contention. V$QUEUE is specifically designed to monitor MTS dispatcher queue performance.

Multiple choice

The LOG_ARCHIVE_START initialization parameter for NOARCHIVELOG mode database is set to 'TRUE'. The ARCn processes are not automatically copying the online redo log files. While troubleshooting, it was verified that valid archived redo log file destinations are provided using the LOG_ARCHIVE_DEST_n initialization parameters, yet no files are written to these destinations. What should be done next?

  1. Refresh the current online redo log files.

  2. Switch the database from NOARCHIVELOG to ARCHIVELOG mode.

  3. Issue the 'ALTER SYSTEM ARCHIVE LOG START' statement to activate the ARCn processes.

  4. Shut down and restart the database to activate the ARCn processes.
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The LOG_ARCHIVE_START parameter only controls automatic archiving in ARCHIVELOG mode, not NOARCHIVELOG mode. In NOARCHIVELOG mode, automatic redo log archiving is not possible regardless of parameter settings because the database doesn't maintain archive log destinations. Even though valid LOG_ARCHIVE_DEST_n parameters are set, they are ignored in NOARCHIVELOG mode. To enable automatic archiving by ARCn processes, the database must be switched to ARCHIVELOG mode using ALTER DATABASE ARCHIVELOG.

Multiple choice

Which data dictionary view should be used to get a list of object privileges for all database users?

  1. DBA_TAB_PRIVS

  2. ALL_TAB_PRIVS

  3. USER_TAB_PRIVS

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

DBA_TAB_PRIVS is a data dictionary view that displays all table privileges granted to all users in the database, making it the comprehensive choice for reviewing object privileges system-wide. ALL_TAB_PRIVS shows only table privileges accessible to the current user (granted to or by the current user). USER_TAB_PRIVS shows only table privileges granted to the current user. ALL_TAB_PRIVS_MADE shows privileges granted by the current user to others. For a complete view across all users, DBA_TAB_PRIVS is required.