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 company hired ALLEN, a DBA who will be working from home. Allen needs to have the ability to start the database remotely.A password file was created for the database and REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE was set in the parameter file. Which of the following commands adds Allen to the password file, allowing him remote DBA access?

  1. GRANT DBA TO ALLEN;

  2. GRANT SYSDBA TO ALLEN;

  3. GRANT RESOURCE TO ALLEN;

  4. orapwd file=orapwdSTUD user=ALLEN password=DBA
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

To grant remote DBA access in Oracle, you must grant SYSDBA privilege to the user. The GRANT SYSDBA TO ALLEN command adds Allen to the password file with SYSDBA privileges, allowing remote database startup and administration. Option A is incorrect because DBA is a role, not a privileged connection. Option D shows orapwd syntax for creating password files, not granting privileges.

Multiple choice

While checking the alert log for the database many lines are discovered that say "Checkpoint Not Complete". How can the problem be solved?

  1. Delete archived log files

  2. Add more online redo log groups

  3. Increase the size of archived log files

  4. increase the size of online redo log files
Reveal answer Fill a bubble to check yourself
B,D Correct answer
Explanation

The 'Checkpoint Not Complete' message occurs when LGWR cannot write to redo log files because a checkpoint hasn't completed. This happens when redo logs are too small or too few. Solutions include: adding more redo log groups (B) so LGWR has more available logs, and increasing size of online redo log files (D) to provide more space between checkpoints. Deleting archived logs (A) doesn't help and increasing archived log size (C) is irrelevant.

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. Up to 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

The FAST_START_PARALLEL_ROLLBACK parameter in Oracle can only be set to LOW or HIGH (values are case-insensitive). Setting it to a numeric value like 5 is invalid syntax, causing the parameter to be ignored. Option A is incorrect because rollback processes use the LOW/HIGH logic, not specific process counts. Option B incorrectly states a maximum of four processes. Option D is wrong because RECOVERY_PARALLELISM is a different, unrelated parameter.

Multiple choice

Which data dictionary view(s) are needed to query to find the following information about a user? Whether the user's account has expired The user's default tablespace name
The user's profile name

  1. DBA_USERS only

  2. DBA_USERS and DBA_PROFILES

  3. DBA_USERS and DBA_TABLESPACES

  4. DBA_USERS, DBA_TS_QUOTAS, and DBA_PROFILES
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

DBA_USERS contains the ACCOUNT_STATUS column (shows expired/open), DEFAULT_TABLESPACE column, and a PROFILE column. All three required pieces of information are in this single view. DBA_PROFILES is not needed because the profile name (not profile details) is already in DBA_USERS. DBA_TABLESPaces is unnecessary for this query. DBA_TS_QUOTAs shows tablespace quotas, not required information.

Multiple choice

Which of the following files is a part of the Oracle database?

  1. Control file

  2. Password file

  3. Parameter files

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

The control file is a core binary file that is part of the Oracle database, containing critical metadata like database name, data file locations, checkpoint information, and redo log thread info. The database CANNOT open without at least one control file. Password files, parameter files, and archived log files are important but are not technically 'part of the database' in the same architectural sense.

Multiple choice

Extents are a logical collection of contiguous ___________.

  1. Segments

  2. Database blocks

  3. Table spaces

  4. operating system blocks
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

In Oracle's storage hierarchy: a data block is the smallest unit, an EXTENT is a contiguous collection of data blocks, a SEGMENT is a collection of extents (like a table or index), and a TABLESPACE contains segments. Therefore, extents are composed of contiguous database blocks (option B), not operating system blocks, table spaces, or segments.

Multiple choice

Name the tool for tuning SQL statements.

  1. TUNEUP SYS

  2. EXPLAIN SYS

  3. DB_TUNEUP

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

EXPLAIN PLAN is the Oracle tool used to analyze and display the execution plan SQL statements will use, helping identify performance bottlenecks like full table scans or inefficient joins. The other options (TUNEUP SYS, DB_TUNEUP) are not standard Oracle tools, and EXPLAIN SYS is not a valid tuning command.

Multiple choice

While attempting to connect to a remote server, the following error appeared: ORA-12533: TNS:illegal ADDRESS parameters To attempt to resolve the error, the tnsnames.ora file was examined. What should be verified in this file?

  1. That the correct service name exists in the tnsnames.ora file

  2. That the service name of the database was entered correctly in the connect string

  3. That the connect descriptor's' protocol-specific parameters located in the ADDRESS section are correct

  4. That the service name of the database was entered correctly in the connect string
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

ORA-12533 indicates illegal ADDRESS parameters in the TNS connect string, meaning the protocol-specific settings (HOST, PORT, PROTOCOL) in the ADDRESS section are incorrect. The service name options (B, D) would produce different errors like ORA-12154 (TNS:could not resolve the connect identifier), while option A is too general.

Multiple choice

Which of the following tables is created to enable the building of scalable applications and is useful for large tables that can be queried or manipulated using several processes concurrently?

  1. Regular table

  2. Clustered table

  3. Partitioned table

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

Partitioned tables divide large datasets into smaller, more manageable pieces that can be queried or modified in parallel across multiple processes, improving performance and scalability. Regular tables (A) don't offer this parallelism, clustered tables (B) group related tables physically, and index-organized tables (D) store data within the index structure but don't inherently enable concurrent operations.

Multiple choice

Where is the trace file generated, when a deadlock shutdown is detected by Oracle?

  1. SQL_TRACE

  2. TRACE_DEST

  3. USER_DUMP_DEST

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

When Oracle detects a deadlock and terminates a session, it writes a trace file to the directory specified by USER_DUMP_DEST, which contains diagnostic information about the deadlock (sessions involved, resources waited on, wait-for graph). SQL_TRACE is a facility, not a destination; TRACE_DEST is not a valid parameter; CORE_DUMP_DEST is for core dumps from crashes.

Multiple choice

Consider the following command:

DROP TABLE ITEM;

An explicit COMMIT has NOT been issued. What can be done to reverse this action and restore the data in the INVENTORY table?

  1. Shut down the database

  2. Terminate your current session

  3. The action cannot be reversed

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

DDL statements like DROP TABLE include an implicit COMMIT before execution and another COMMIT after execution, meaning the transaction is permanently committed before ROLLBACK can be issued. Once the DROP TABLE executes, the data is gone (unless using Flashback Drop in 10g+). Shutting down the database or terminating the session won't undo a committed DDL operation.

Multiple choice

A media failure occurred causing NOARCHIVELOG mode database to crash. It was discovered that two of the data files are corrupt. The last whole closed database backup was taken one week ago with a online redo log sequence number of 83. At the time the data files were corrupted, the online redo log sequence number was 85. Although there were few committed transactions after the last backup, as much data as possible has to be recovered. Which of the following recovery processes should be used?

  1. Shut down the instance, restore all of the Oracle files from the most recent backup, and restart the instance.

  2. Manually restore and recover the two corrupt data files from the most recent backup.

  3. Shut down the instance, restore the two corrupt data files from the most recent backup, and restart the instance.

  4. Shut down the instance, restore all of the Oracle files from the most recent backup, restart the instance, and open the database using the 'ALTER DATABASE OPEN RESETLOGS' statement.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

In NOARCHIVELOG mode, you cannot apply redo logs from a restored backup, so any changes after the last backup are lost. The only option is to restore the entire database from the last whole closed backup. You cannot selectively restore individual data files without opening with RESETLOGS, and even then, NOARCHIVELOG mode prevents point-in-time recovery.

Multiple choice

Which of the following 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 transaction access to a data block, while PCTUSED determines when a block returns to the free list for new inserts. Together, they control block contention and space reuse frequency. INITRANS sets the initial transaction slot reservation, and PCTFREE controls reserved space for updates.

Multiple choice

How can automatic archiving at instance startup be enabled for a ARCHIVELOG database?

  1. With the database open, issue the 'ARCHIVE LOG START ALL' SQL*Plus command.

  2. With the database shut down, set the LOG_ARCHIVE_START initialization parameter to 'TRUE'.

  3. With the database mounted, issue the 'ALTER DATABASE ARCHIVELOG' SQL statement.

  4. With the database open, issue the 'ALTER SYSTEM ARCHIVE LOG START TO <file_name>' SQL statement.
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The LOG_ARCHIVE_START parameter controls automatic archiving at instance startup. Setting it to TRUE in the initialization parameter file enables automatic archiving when the database starts up. Option A is incorrect because ARCHIVE LOG START is a runtime command, not a startup configuration. Option C places the database in ARCHIVELOG mode but does not enable automatic archiving at startup. Option D describes a manual archive command, not automatic startup archiving.