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 file is part of the Oracle database?

  1. Control file

  2. Password file

  3. Parameter file

  4. Archived log file
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

An Oracle database physically consists of control files, data files, and online redo log files. Control files contain metadata about the database structure. Parameter files, password files, and archived log files support database operations but are not part of the database itself.

Multiple choice

In which of the following server tasks is it possible to keep a cache of EJB state information in some readily accessible place to accelerate the initial creation of EJBs in order to speed up the process?

  1. Instance passivation

  2. Optimized method invocation

  3. TX_SUPPORTS

  4. Preached instances

  5. Application assembler
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

To speed up the process it is possible to keep a cache of EJB state information in some readily accessible place to accelrate the initial creation of EJBs.

Multiple choice

In which of the following server tasks, when a component wishes to access a database, it does not create a new database connection, instead it simply grabs a connection from a pool of available connections?

  1. Instance passivation

  2. Optimized method invocation

  3. Database connection pooling

  4. TX_MANDATORY

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

Here, when a component wishes to access a database, it does not create a new database connection instead it simply grabs a connection from a pool of available connections.

Multiple choice

What is the probable cause for getting the following error stack? ERROR: ORA-01034: ORACLE not available ORA-07318: smsget: open error when opening sgadef.dbf file. HP-UX Error: 2: No such file or directory

  1. The Oracle segment is shutdown that they are trying to access.

  2. The Oracle namespace is shutdown that they are trying to access.

  3. The Oracle instance is shutdown that they are trying to access.

  4. None of the above
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The error ORA-01034 'ORACLE not available' combined with ORA-07318 'sgadef.dbf file open error' and 'No such file or directory' definitively indicates the Oracle instance is shutdown. The sgadef.dbf file exists only when the instance is running (it's part of SGA - System Global Area). The error is not about segments or namespaces but the instance itself being unavailable.

Multiple choice

A database is currently configured with the database character set to WEBIS08859P1 and national character set to AL16UTF16.

Business requirements dictate the need to expand language requirements beyond the current character set, for Asian and additional Western European languages, in the form of customer names and addresses.

Which solution saves space storing Asian characters and maintains consistent character manipulation performance?

  1. Use SQL CHAR data types and change the database character set to UTF8.

  2. Use SQL NCHAR data types and change the national character set to UTF8.

  3. Use SQL CHAR data types and change the database character set to AL32UTF8.

  4. Use SQL NCHAR data types and keep the national character set to AL16UTF16.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

For storing Asian languages efficiently, AL16UTF16 (fixed 2-byte per character) is optimal because it uses consistent space regardless of the character, unlike UTF8 which uses 1-3 bytes variably. NCHAR data types with AL16UTF16 national character set saves space for Asian characters while maintaining consistent character manipulation performance. Changing to UTF8 would increase storage requirements for Asian characters (3 bytes each instead of 2).

Multiple choice

Which of the following data dictionary views displays the objects owned by the user?

  1. DICTIONARY

  2. TABLE_PRIVILEGES

  3. ALL_VIEWS

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

USER_OBJECTS is a data dictionary view that displays all objects owned by the current user, including tables, views, indexes, procedures, and other database objects. It only shows objects where the user is the owner, unlike ALL_OBJECTS which shows objects accessible to the user (including ones owned by others with privileges).

Multiple choice

When allowing RMAN to backup archived redo log files, how does RMAN know which archived redo logs to back up from the archive log destination?

  1. RMAN keeps a list of archived logs in a text file.

  2. The DBA must manually tell RMAN which archive logs to backup.

  3. RMAN has access to target control file and/or catalog information.

  4. RMAN looks at the ARCHIVE_LOG_DEST parameter of the target database and backs up all archive logs found.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

RMAN maintains metadata about which archived redo logs have been backed up in the target database's control file and/or the recovery catalog. When you issue a BACKUP ARCHIVELOG command, RMAN queries this metadata to identify which archived logs need backing up and automatically selects them without requiring manual specification.

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 contains all index information including index names, types (NORMAL, BITMAP, FUNCTION-BASED, etc.), status, and other properties. DBA_IND_COLUMNS shows which columns comprise each index but doesn't contain the index type information. DBA_USERS contains user account information, not index metadata.

Multiple choice

In a DSS application, the sales summary report queries data from seven different tables and groups the data by region and period. Which of the following storage structures is a good design choice to improve query performance?

  1. IOT

  2. Cluster

  3. Materialized View

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

Materialized views are ideal for DSS applications as they pre-compute and store aggregate results, dramatically improving query performance for complex, multi-table joins and grouping operations. The data is already summarized and stored physically, so queries run against the pre-aggregated data rather than recomputing each time. IOTs and clusters are better suited for OLTP workloads with high single-row access patterns.

Multiple choice

Can the performance of a database be tuned by adjusting init.ora parameters?

  1. Yes

  2. No

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

Oracle's initialization parameters control critical database behaviors including memory allocation (SGA/PGA), optimizer modes, parallel execution settings, I/O subsystem configuration, and many other performance-related aspects. Proper tuning of these parameters is a fundamental part of database performance optimization and can significantly impact throughput and response times.

Multiple choice

Examine the following SQL statement:

CREATE TABLESPACE user_data DATAFILE '/u01/oradata/userdata_0l.dbf' SIZE 100M LOCALLY MANAGED UNIFORM SIZE 1M AUTOMATIC SEGMENT SPACE MANAGEMENT;

Which part of the tablespace will be of a uniform size of 1 MB?

  1. Extent

  2. Segment

  3. Oracle block

  4. Operating system block

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

In a LOCALLY MANAGED tablespace with UNIFORM SIZE specification, each extent allocated within the tablespace will be exactly the uniform size specified (1 MB in this example). Segments are composed of multiple extents, Oracle blocks are smaller logical units within extents, and operating system blocks are the physical I/O units. The UNIFORM SIZE clause directly controls extent size allocation.

Multiple choice

Identify the complete list of the logical components of an Oracle database.

  1. Tablespaces, segments, extents, and data files

  2. Tablespaces, segments, extents, and Oracle blocks

  3. Tablespaces, database, segments, extents, and data files

  4. Tablespaces, database, segments, extents, and Oracle blocks
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The correct answer is B because Oracle blocks are the smallest unit of logical storage allocation, while data files are physical storage structures. The logical hierarchy flows from tablespaces (largest) down to Oracle blocks (smallest): tablespaces contain segments, segments contain extents, and extents contain Oracle blocks. Option A incorrectly includes data files which are physical, not logical. Options C and D incorrectly include 'database' as a component within the hierarchy.

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 correct answer is D because the UPDATE GLOBAL INDEXES clause maintains global indexes during partition DDL operations, allowing concurrent DML operations to continue using them without interruption. Options A and B are incorrect because the clause doesn't automatically rebuild indexes - it maintains them. Option C is incorrect because global indexes are updated, not left completely unchanged.

Multiple choice

___________ contain the physical structure of a database.

  1. Archive log files

  2. Initialization files

  3. Control files

  4. SGA definition files
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The correct answer is C because control files contain the physical structure of an Oracle database, including the database name, names and locations of data files and redo log files, and the current log sequence number. Archive log files (A) store redo entries, initialization files (B) contain parameter settings, and SGA definition files (D) are not a standard Oracle file type.