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 technology databases

One of the tablespaces is read-only in your database. The loss of all control files forced you to re-create the control file. Which operation do you need to perform after re-creating the control file and opening the database?

  1. drop and re-create the read-only tablespaces

  2. rename the read-only data files to their correct file names

  3. change the tablespace status from read/write to read-only

  4. re-create the read-only tablespace because it is automatically removed

  5. none of the above
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

When you re-create a control file, read-only tablespaces are not automatically added back with their correct file names. You must rename the read-only data files to point to their correct locations to make them accessible.

Multiple choice technology databases

The data file belonging to theSYSTEMtablespaceis corrupted and no backup of the file is available. How do you recover the data file?

  1. The data file cannot be recovered.

  2. The date file can be restored from the SYSTEM auto backup.

  3. Take thetablespaceoffline,drop,kand re-create it.

  4. Recovery Manager (RMAN) is used to recover the data file.

  5. The ALTER DATABSE CREATE DATAFILE
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

If a SYSTEM tablespace data file is corrupted and no backup exists, the database cannot be recovered because the SYSTEM tablespace contains the data dictionary essential for database operation.

Multiple choice technology databases

You work as a database administrator for TCS .com. You decided to manage client and server connections using Local Naming method in TCS .com. When you try to connect to the database you get the following error: ERROR: ORA-12154:TNS:couldnot resolve service name Which network configuration files would you look into to resolve this error?

  1. snmp.ora

  2. names.ora

  3. spfile.ora

  4. sqlnet.ora

  5. listener.ora

  6. tnsnames.ora
Reveal answer Fill a bubble to check yourself
D,F Correct answer
Explanation

The ORA-12154 error indicates TNS cannot resolve the service name, which is a Local Naming configuration issue. The two key files to check are sqlnet.ora (which configures Oracle Net services) and tnsnames.ora (which contains service name aliases). snmp.ora is for SNMP monitoring, names.ora is for Oracle Names, spfile.ora is the server parameter file, and listener.ora configures the listener - none of these directly cause TNS resolution errors.

Multiple choice technology databases

You work as a database administrator for TCS .com. In your production database, the size of Database Buffer Cache needs to be increased immediately for the current as well as future instances of the database. The Oracle instance has been configured to accommodate any changes in the size of the memory structures. At this is production database, you want to accomplish this task with no impact on the user's connections. Which activity must you have completed before accomplishing this task?

  1. You must have started the database instance in restricted mode.

  2. You must have started the database instance in NORMALmode.

  3. You must have started the database instance with server parameter file.

  4. You must have started the database instance but must not have mounted.

  5. You must have started the database instance but must not have opened.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

To dynamically change Database Buffer Cache size without impacting users, the instance must use a Server Parameter File (SPFILE). With SPFILE, changes to memory parameters like DB_CACHE_SIZE can be made online using ALTER SYSTEM. PFILE (text parameter file) requires a restart to apply changes. Options A, D, and E involve restricted or incomplete startup states not needed. Option B doesn't specify the critical requirement of SPFILE.

Multiple choice technology databases

Your TCS .com database is in ARCHIVELOG mode. The data file that belongs to the SYSTEMtablespacehas become corrupted. Up to what point can you recover the database?

  1. Until last commit

  2. Until the time you perform recovery

  3. Until the time the date file got corrupted

  4. Until the point where the last transaction begun

  5. You cannot recover the SYSTEM tablespaceand must re-create the database.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

If the database is in ARCHIVELOG mode and a SYSTEM tablespace data file is corrupted, you can restore and recover the database up to the point of the last committed transaction (until last commit) using archived and online redo logs.

Multiple choice technology databases

You work as a database administrator for TCS .com. The UNDO_RETENTION parameter in your database is set to 1000 and undoretention is not guaranteed. Which statement regarding retention of undo data is correct?

  1. Undo data becomes obsolete after 1,000 seconds.

  2. Undo data gets refreshed after 1,000 seconds.

  3. Undo data will be stored permanently after 1,000 seconds.

  4. Committed undo data would be retained for 1,000 seconds if free undo space is available.

  5. Undo data will be retained in the UNDOtablespacefor 1,000 seconds, then it gets movedmovedto the TEMPORARY tablespace to provide read consistency.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

When UNDO_RETENTION is set to 1000 and retention is not guaranteed, Oracle attempts to retain committed undo data for 1000 seconds IF sufficient free undo space exists. Option D correctly states this. Option A is incorrect because undo doesn't become obsolete exactly at 1000 seconds - it stays longer if space permits. Option B is wrong because undo isn't refreshed. Option C is incorrect because undo isn't stored permanently. Option E is wrong - undo never moves to TEMPORARY tablespace.

Multiple choice technology databases

You work as a database administrator for TCS .com. On a Monday morning, you find the database instance aborted. After inspecting the alert log file, you execute the STARTUP command in SQL*Plus to bring the instance up. What statement is true?

  1. PMON coordinates media recovery.

  2. SMON coordinates instance recovery.

  3. PMON coordinates instance recovery.

  4. Undo Advisor would roll back all uncommitted transactions.

  5. SQL*PLUS reports an error with the message asking you to perform instance recovery.
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

When an instance aborts and is restarted with STARTUP, SMON (System Monitor Process) automatically performs instance recovery. SMON rolls forward changes from online redo logs then rolls back uncommitted transactions. Option B is correct. PMON (Process Monitor) handles individual process recovery, not instance recovery, so Options A and C are wrong. Undo Advisor is a tuning tool, not involved in recovery. SQL*Plus doesn't report recovery errors - SMON handles it automatically.

Multiple choice technology databases

You work as a database administrator for TCS .com. The database is open. A media failure has occurred, resulting in loss of all the control files in your database. Which statement regarding the database instance is true in this scenario?

  1. The instance would hang.

  2. The instance needs to be shut down.

  3. The instance would be in the open state.

  4. The instance would abort in such cases.

  5. The instance would be in the open and invalid state.

  6. The instance would in the open state, but all the background processes will be restarted.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

When ALL control files are lost while the database is open, Oracle cannot continue operating without access to control file metadata. The instance immediately aborts and crashes. This is different from a graceful shutdown - it's an abrupt failure. The database cannot remain open or hang; it terminates immediately.

Multiple choice technology enterprise content management

The fact in which flags/dates are maintained for each state of the fact event is an example of

  1. Transaction

  2. Periodic Snapshot

  3. Accumulating Snapshot

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

Accumulating snapshot facts track the entire lifecycle of a business process by maintaining date/timestamp flags for each state or milestone. Unlike transaction facts (single event) or periodic snapshots (state at fixed intervals), accumulating snapshots have multiple date columns that get updated as the process progresses through each stage - e.g., order_date, ship_date, delivery_date in an order pipeline.

Multiple choice technology enterprise content management

A degenerate dimension is:

  1. an attribute of a fact which quailifies as a dimension

  2. stored in the fact table

  3. has no surrogate keys

  4. has fixed values that cannot be generated outside the fact
Reveal answer Fill a bubble to check yourself
A,B Correct answer
Explanation

A degenerate dimension is a dimension attribute that has no dimensions of its own (like order number or invoice number) and is therefore stored directly in the fact table rather than in a separate dimension table. Options A and B correctly define this - it's an attribute that qualifies as dimension but is stored in the fact table for efficiency. Option C is incorrect because degenerate dimensions typically have no surrogate keys (they use natural keys). Option D is incorrect - values are generated outside the fact (from source transactions).

Multiple choice technology databases

In your DB -multiplexed online redo log files,one of the members in a group is lost due to media failure? You wud..

  1. import the database from the last export

  2. restore all the members in the group from the last backup

  3. drop the lost member from the database and then add a new member to the group

  4. restore all the database files from the backup and then perform a complete recovery

  5. restore all the database files from the backup and then perform an incomplete recovery
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

In multiplexed redo log groups, each member is a copy. Losing one member doesn't cause data loss since other members have identical content. The correct action is to drop the damaged member from the group and add a new one - Oracle continues operating using the remaining good members. Options A, B, D, E are overreactions - full restore/import/incomplete recovery are unnecessary when you have redundant copies.

Multiple choice technology databases

Which statement regarding an incomplete recovery is true?(Oracle 10g)

  1. You do not need to restore all the data files.

  2. You do not need to open the database with the RESETLOGS operation

  3. You do not need to perform a full backup after the RESETLOGS operation.

  4. You do not need to recover all the data files to the same system change number (SCN).
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

In Oracle 10g, after incomplete recovery with RESETLOGS, the database is in a consistent state and doesn't require an immediate full backup (though it's recommended practice). Options A and D are incorrect - incomplete recovery still requires restoring all datafiles and recovering them to a consistent point. Option B is false - RESETLOGS is mandatory after incomplete recovery to reset the redo log sequence.

Multiple choice technology databases

In which scenarios would you rebuild an index?

  1. when you need to disable the index usage

  2. when you need to move the index to another tablespace

  3. when you need to enable index monitoring

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

Rebuilding an index recreates it from scratch, which is necessary when moving the index to a different tablespace. Options A and C are incorrect - disabling/enabling index usage or monitoring don't require rebuild. Rebuilding is also used for defragmentation or to change storage parameters, but not for the administrative operations in A and C.

Multiple choice technology databases

A redo log file is corrupted while the database is open;You would ..

  1. clear the redo log group

  2. perform redo log file import

  3. perform an incomplete recovery

  4. perform a redo log recovery using Recovery Manager (RMAN)

  5. shut down the database and open the database in the NOARCHIVELOGmode
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

When a redo log file is corrupted while the database is open and the corruption is in the CURRENT group, you can use the 'ALTER DATABASE CLEAR LOGFILE' command to clear the corrupted log member if archiving is enabled and it's not the only log member. This reinitializes the log file. Options B and D describe procedures that don't exist in this form. Option C (incomplete recovery) is not needed for a single corrupt log when you can clear it. Option E would make things worse.