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

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

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.

Multiple choice

____________ involves putting different rows into different tables.

  1. Horizontal partitioning`

  2. Vertical partitioning

  3. Range partitioning

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

Horizontal partitioning divides table rows based on a key or range - different subsets of rows go into different tables. This differs from vertical partitioning (splitting columns), range partitioning (a specific horizontal method), and hash partitioning (using a hash function). The question asks about the general concept of putting different rows into different tables, which is horizontal partitioning.

Multiple choice

How do you connect to RMAN without a recovery catalog?

  1. rman target system/manager nocatalog

  2. rman system/manager catalog=no

  3. rman system/manager without catalog

  4. rman target system/manager rcvcat no

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

To connect to RMAN without a recovery catalog, use 'rman target nocatalog'. This connects to the target database using the control file for metadata instead of a catalog database. Options B, C, and D use incorrect syntax - 'catalog=no', 'without catalog', and 'rcvcat no' are not valid RMAN connection parameters.

Multiple choice

A Profile is assigned to _____.

  1. a database

  2. tables

  3. views

  4. both (2) and (3)

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

A profile in Oracle is a database-level resource limit that can only be assigned to users, not to schema objects like tables or views. Profiles control password settings (expiration, complexity, failed login attempts) and resource limits (CPU time, sessions, idle time). They are assigned with the CREATE USER or ALTER USER commands using the PROFILE clause.

Multiple choice

A database is running on a server that has 3 CPUs, and has these settings for initialization parameters: db_cache_size= 4M shared_pool_size= 52428800 db_block_size= 2048 large_pool_size= 614400 java_pool_size = 20971520 db_file_multiblock_read_count= 8 parallel_max_servers= 4 What is a valid statement about the above scenario?

  1. Tune the number of latches by setting it to db_cache_size(50*db_block_size)

  2. Tune the number of latches by setting to large_pool_size/1000

  3. Tune the number of latches by setting it to Number of CPUs *1 *2

  4. Oracle9i would automatically calculate the number of latches required, based on the environment defined by the initialization parameters for the database and the operating system level parameters
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Oracle9i introduced automatic latch calculation based on initialization parameters and OS configuration. Manual latch tuning formulas are obsolete in Oracle9i. The database now determines optimal latch counts dynamically using db_cache_size, db_block_size, CPU count, and other parameters.

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

With LOG_ARCHIVE_MIN_SUCCEED_DEST=2, Oracle requires successful archival to at least 2 destinations before overwriting redo logs. Since there are 2 local destinations (1 mandatory + 1 optional), once both local destinations succeed, the requirement is met even if the remote destination hasn't finished.

Multiple choice

If a database is queried with the following command:

SELECT object_name FROM all_objects WHERE object_type = 'TABLE';

Which of the following values are displayed?

  1. Only the names of all the tables that can be accessed.

  2. Only the names of all the objects that can be accessed.

  3. Only the names of the objects that are owned.

  4. Only the names of the tables that are owned.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The ALL_OBJECTS data dictionary view shows all objects accessible to the current user, including objects owned by the user plus those on which privileges have been granted. By filtering WHERE object_type='TABLE', only table names are returned. USER_OBJECTS would show only owned objects.

Multiple choice

Which memory area is not part of the SGA?

  1. Database Buffer Cache

  2. Redo log Buffer

  3. PGA

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

The SGA (System Global Area) is a shared memory region in Oracle that includes the Database Buffer Cache, Redo Log Buffer, and Shared Pool - all of which are accessible by multiple processes. PGA (Program Global Area) is private memory specific to each server process and is NOT part of the SGA. Each Oracle process has its own PGA containing session-specific variables and runtime areas.

Multiple choice

The location of all the tables and indexes owned by one user has to be determined. Which DBA view should be looked into?

  1. DBA_TABLES

  2. DBA_INDEXES

  3. DBA_SEGMENTS

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

DBA_SEGMENTS is the correct view because it contains information about ALL segments allocated to a user, including both tables and indexes. Tables and indexes are both stored as segments in the database. DBA_TABLES and DBA_INDEXES would require querying two separate views and joining them. DBA_TABLESPACES shows tablespace information, not user-specific object locations. DBA_SEGMENTS provides a comprehensive view of all storage segments owned by a user.

Multiple choice

What is the difference between Oracle Forms and Apps Forms?

  1. Oracle forms runs server-side PL/SQL while app forms run client side.

  2. Apps forms use uses exact positioning while Oracle forms dont

  3. Oracle forms runs client-side PL/SQL while app forms run server side.

  4. Oracle forms Flash Charts as its integrated charting engine while app forms dont
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Oracle Forms (standard) runs PL/SQL code on the client side in the Forms runtime, while Oracle Apps Forms (E-Business Suite) runs PL/SQL server-side for better performance, security, and centralized business logic management.

Multiple choice

Which data dictionary view should be used, when finding out whether two databases are cross-transportable compliant or not?

  1. V$TRANSPORTABLE_PLATFORM
  2. V$ENDIAN FORMAT
  3. V$PLATFORM
  4. V$COMPATIBILITY_LEVEL
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

V$TRANSPORTABLE_PLATFORM shows which platforms can cross-transport tablespaces. It lists platforms and their endian formats, allowing you to verify compatibility between source and target databases before attempting transportable tablespace operations.