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

A primary database named PROD and a standby database named PRODS is maintained using standby log transmission to send the log files to the standby database.

To prevent any propagation of errors, the standby database should wait for 20 minutes before processing any log files shipped from the primary database.

What steps should be taken?

  1. The LOG_ARCHIVE_DEST_n = 'SERVICE=PRODS DELAY 20' parameter should be included in the parameter file on the primary database

  2. The LOG_ARCHIVE_DEST_n = 'SERVICE=PROD DELAY 20' parameter should be included in the parameter file on the standby database

  3. The REOPEN option should be specified on the LOG_ARCHIVE_DEST_n parameter

  4. At least one of the log archive destinations should be set to MANDATORY
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The DELAY attribute in LOG_ARCHIVE_DEST_n parameter on the PRIMARY database causes archived logs to be held for the specified minutes before being applied on the standby. Setting 'SERVICE=PRODS DELAY 20' on PROD ensures PRODS waits 20 minutes before applying shipped logs. This prevents corrupted or erroneous data from being immediately propagated to the standby, providing a recovery window.

Multiple choice

A tablespace was created for the recovery catalog. A user and a schema were created for the recovery catalog. Next, the appropriate privileges and roles were granted to the user that was created to enable them to maintain the recovery catalog. At the RMAN prompt, the catalog was created. What should be done next?

  1. Set the target database to a consistent incarnation.

  2. Synchronize the control file of the target database with the recovery catalog

  3. Register the target database in the newly created catalog

  4. Reset the target database to an incarnation that is synchronized with the recovery catalog
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

After creating the recovery catalog tablespace, user, schema, and granting privileges, then creating the catalog with RMAN, the mandatory next step is registering the target database in the catalog. Option A (setting incarnation) happens later if needed. Option B (synchronizing) occurs during registration and subsequent updates. Option D (resetting incarnation) is only required for advanced recovery scenarios like flashback or point-in-time recovery to a different incarnation, not during initial setup.

Multiple choice

Which of the following storage structures provides a way to physically store rows from more than one table in the same data block?

  1. Cluster table

  2. Partitioned table

  3. Unclustered table

  4. Index-organized table
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Cluster tables allow rows from multiple tables to be stored in the same data block based on a cluster key. This improves join performance for frequently joined tables. Partitioned tables split a single table, unclustered is the default, and index-organized tables store data within the index structure.

Multiple choice

A hot backup is _______.

  1. taking up the backup of the database while it is still up and running

  2. taking up the backup of the database after the user logs off

  3. taking up the backup followed by taking cold backup

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

A hot backup is taken while the database is online and running, allowing users to continue working during backup. This contrasts with cold backup, which requires shutting down the database. The key characteristic is that the database remains operational.

Multiple choice

What is the difference between I/O slaves and DBWn processes for the DB Writer?

  1. In Oracle8i, I/O slaves are not available; only DBWn processes are available.

  2. I/O slaves perform the write function only, while DBWn processes also perform date-gathering activity.

  3. I/O slaves will work only with synchronous I/O, whereas DBWn processes are available only within asynchronous I/0.

  4. I/O slaves will work only with asynchronous I/O, whereas DBWn processes are available only within synchronous I/0.
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

I/O slaves are specialized processes that only perform write operations to data files. DBWn (Database Writer) processes perform both data gathering (identifying dirty buffers) and writing them to disk. The synchronous/asynchronous I/O distinction is incorrect - both can work with either type.

Multiple choice

Which of the following groups allows its members to connect to a 9i database using the SYSDBA privilege?

  1. OSDBA

  2. 9iDBA

  3. OSADM

  4. OSOPER

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

OSDBA is the operating system group that grants SYSDBA privileges when connecting to an Oracle database. Members of this group can perform database administration tasks. The other options (9iDBA, OSADM, OSOPER) are not valid Oracle operating system groups for SYSDBA access.

Multiple choice

JOHN is using the Oracle Net Configuration Assistant to configure his machine to use local naming. He chose TNSNAMES (local naming) as his connection method, specified a database service name, entered the network protocol, and then specified a host name and a listener port. What is the next step he should take?

  1. Test the connection

  2. Configure a listener

  3. Specify a net service name

  4. Specify the Oracle database version
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

After configuring connection parameters (service name, protocol, host, port), the Oracle Net Configuration Assistant's logical next step is testing whether the connection works. This validates the configuration before completing. The listener and net service name would have been configured earlier in the process.

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

Direct-path export bypasses the SQL statement processing layer, writing data directly to the export file, which is significantly faster than conventional path. Export cannot export tablespaces or data files directly (only schema objects). FULL export respects schema boundaries. Transportable tablespaces are faster than export/import for table transfer.

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 of the following actions 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 LOG_ARCHIVE_START parameter in the initialization parameter file controls whether automatic archiving starts when the instance starts up. Setting it to TRUE enables this. Automatic archiving is not the default. LOG_ARCHIVE_MAX_PROCESSES controls the number of archiver processes, not whether archiving is enabled.

Multiple choice

A partitioned table named ITEM is maintained. The following statement was issued:

ALTER TABLE ITEM DROP PARTITION p1 UPDATE GLOBAL INDEXES PARALLEL (DEGREE 3);

Which of the following statements represents the result of including the UPDATE GLOBAL INDEXES clause?

  1. After the P1 partition is dropped, all global B-tree and function-based indexes are automatically rebuilt.

  2. After the P1 partition is dropped, all global indexes that were marked UNUSABLE will be automatically rebuilt.

  3. All global indexes on the ITEM table remain intact throughout the DDL operation.

  4. Other DML statements can continue to use the any global B-tree indexes without interruption.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The UPDATE GLOBAL INDEXES clause ensures that dropping a partition does not invalidate global indexes. Instead of marking indexes UNUSABLE, Oracle asynchronously maintains them during the DDL operation. This allows concurrent DML operations to continue accessing global indexes without interruption, improving availability during partition maintenance.

Multiple choice

___________ is an area on disk which comprises of one or more disk files.

  1. A package

  2. A table space

  3. A partition

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

A tablespace is a logical storage container in Oracle databases that consists of one or more physical data files on disk. It provides a layer of abstraction between database objects (tables, indexes) and physical storage, allowing administrators to manage disk space efficiently. Tables, indexes, and other schema objects are stored within tablespaces.

Multiple choice

Examine the following statement:

ALTER DATABASE BACKUP CONTROL FILE TO 'control01.bkp';

What does the statement generate?

  1. A file containing a SQL statement which will re-create the database.

  2. A text copy to the control file.

  3. A binary copy of the control file.

  4. A file containing a SQL statement which will re-create the control file.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The BACKUP CONTROL FILE TO trace creates a binary copy of the control file at the specified location. This is different from the TO TRACE option which creates a text file with SQL commands. The binary backup preserves the exact state of the control file including data file info, checkpoint details, and recovery metadata - essential for disaster recovery scenarios.

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 specific package used to grant resource group privileges and administer the Resource Manager. It provides procedures like GRANT_SYSTEM_PRIVILEGE and GRANT_SWITCH_CONSUMER_GROUP to authorize users for resource management tasks. The other packages listed serve different purposes - DBMS_REPCAT packages handle replication, while DBMS_RESOURCE_MANAGER creates and configures plans but doesn't handle privilege grants.