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 is the SGA created in an Oracle database environment?

  1. When the database is created

  2. When the instance is started

  3. When the database is mounted

  4. When a user process is started
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The System Global Area (SGA) is allocated when the Oracle instance starts up, not when the database is created or mounted. The instance consists of memory structures (SGA) and background processes. Database creation creates physical files, mounting reads the control file, but neither allocates the SGA. User processes connect to an already-running instance with an existing SGA.

Multiple choice

Which of the following are different types of record group?

  1. Query record group

  2. Non-query record group

  3. Static record group

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

Oracle Forms supports three types of record groups: query record groups (populated by SQL queries), non-query record groups (created programmatically), and static record groups (created at design time). Option D correctly lists all three types. Each type serves different purposes - query for database data, non-query for dynamic data, static for fixed values.

Multiple choice

One of the tasks of a DBA is to periodically monitor the alert log file and the background trace files. In doing so, the DBA notices repeated messages indicating that Log Writer (LGWR) frequently has to wait for a redo log group because a checkpoint has not completed or a redo log group has not been archived. What should he do to eliminate the wait LGWR frequently encounters?

  1. Increase the number of redo log groups to guarantee that the groups are always available to LGWR

  2. Increase the size of the log buffer to guarantee that LGWR always has information to write

  3. Decrease the size of the redo buffer cache to guarantee that LGWR always has information to write

  4. Decrease the number of redo log groups to guarantee that checkpoints are completed prior to LGWR writing
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

LGWR waits when redo log groups are not available due to incomplete checkpoints or archiving. Adding more redo log groups ensures that at least one group is always available for LGWR to write to, preventing these waits. Option B (increasing log buffer) doesn't address log group availability. Option C (decreasing buffer) would worsen performance. Option D (decreasing groups) would make the problem worse by reducing availability.

Multiple choice

Which of the following parameters enables users to perform a data-only unloading of data?

  1. EXCLUDE

  2. INCLUDE

  3. CONTENT

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

The CONTENT parameter controls what to unload: DATA_ONLY unloads only table data without metadata, METADATA_ONLY unloads only DDL, and ALL (default) unloads both. Options A (EXCLUDE) and B (INCLUDE) filter specific objects within whatever CONTENT is specified. Option D (DATA_ONLY) is not a valid parameter name - the correct parameter is CONTENT with value DATA_ONLY.

Multiple choice

The following statement was issued: ALTER TABLESPACE users_data READ ONLY; Which of the following statements is not true?

  1. A checkpoint occurs for each data file associated with the USERS_DATA tablespace.

  2. The data files for the USERS_DATA tablespace will need to be recovered if they become corrupt or a media failure occurs.

  3. The control file is updated to identify the data files for the USERS_DATA tablespace as read-only during recovery.

  4. The checkpoint numbers in the data files for the USERS_DATA tablespace are frozen and do not need to be synchronized with the checkpoint number in the control file.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

When a tablespace is made read-only with ALTER TABLESPACE, Oracle performs a checkpoint and updates the control file to mark the data files as read-only for recovery purposes. This prevents the database from attempting to apply redo logs to these files during recovery. The checkpoint numbers in the data file headers are frozen at this point, so they don't need to stay synchronized with the control file's checkpoint number.

Multiple choice

Which of the following options lists the correct hierarchy of storage structures, from largest to the smallest?

  1. Segment, extent, tablespace, data block

  2. Data block, extent, segment, tablespace

  3. Tablespace, extent, data block, segment

  4. Tablespace, segment, extent, data block
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The storage hierarchy from largest to smallest is: tablespace (logical container for segments), segment (collection of extents storing an object like a table or index), extent (contiguous data blocks), and data block (smallest unit of I/O, typically 2-32KB). This hierarchy organizes physical storage into manageable logical units.

Multiple choice

What command is used to encrypt a PL/SQL application?

  1. DB_ENCRYPT

  2. DBMS_ENCRYPT

  3. DBMS_WRAP

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

The WRAP command encrypts PL/SQL source code by converting it into a format that's difficult to reverse-engineer but remains executable. This protects intellectual property while allowing deployment. DBMS_DDL.WRAP is the underlying package, but WRAP is the command-line interface. There is no DB_ENCRYPT or DBMS_ENCRYPT package for PL/SQL wrapping.

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 affects ARCHIVELOG mode databases. In NOARCHIVELOG mode, automatic archiving is disabled regardless of this parameter's value. To enable ARCn processes to copy redo logs, you must switch the database to ARCHIVELOG mode using ALTER DATABASE ARCHIVELOG. Only then does LOG_ARCHIVE_START become meaningful.

Multiple choice

Which of the following statements is true when connecting to the Oracle instance using the multithreaded server configuration?

  1. The User Global Area (UGA) may only contain sort areas

  2. The UGA may be accessible to dedicated servers.

  3. The UGA components may reside in the large pool.

  4. The UGA components may reside in the buffer cache.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

In multithreaded server (MTS) configuration, the User Global Area (UGA) is moved from the PGA to the large pool to enable memory sharing among multiple user processes. Dedicated servers store UGA in PGA, but MTS uses the large pool for efficient memory utilization.

Multiple choice

What are the two types of tables involved in producing a star schema?

  1. Fact tables and user tables

  2. Dimension tables and error tables

  3. Fact tables and dimension tables

  4. User tables and error tables
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

A star schema in data warehousing consists of fact tables (containing quantitative measurements and foreign keys) and dimension tables (containing descriptive attributes). Fact tables connect to multiple dimension tables in a star-like pattern.

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 can only be performed when the database is in ARCHIVELOG mode. In NOARCHIVELOG mode, the database must be shut down for consistent backups. The ALTER DATABASE BACKUP CONTROLFILE TO TRACE command works online only in ARCHIVELOG mode.

Multiple choice

Which parameters should be set, to limit the number of transactions that can simultaneously make changes to data in a block, and increase the frequency with which Oracle returns a block back on the free list?

  1. INITRANS and PCTUSED

  2. MAXTRANS and PCTFREE

  3. INITRANS and PCTFREE

  4. MAXTRANS and PCTUSED
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

MAXTRANS limits concurrent transactions per block, while PCTUSED determines when a block returns to the free list (when free space falls below this percentage after deletions). Together they control block-level concurrency and space reuse.

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. Upto 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

FAST_START_PARALLEL_ROLLBACK is a boolean parameter accepting only values FALSE, LOW, or HIGH. Setting it to 5 (a numeric value) is invalid and causes Oracle to ignore the parameter. It does not accept numeric values for the number of processes.