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

An Oracle instance is executing in a nondistributed configuration. The instance fails because of an operating system failure. Which background process would perform the instance recovery when the atabase is reopened ?

  1. PMON

  2. SMON

  3. RECO

  4. ARCn

  5. CKPT
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The System Monitor (SMON) background process is responsible for performing instance recovery when a crashed database is reopened. It applies redo log entries (roll forward) and discards uncommitted changes (roll back). PMON cleans up failed user processes, RECO resolves distributed transactions, and CKPT updates file headers during checkpoints.

Multiple choice technology databases

CREATE TABLESPACE user_data DATAFILE '/u01/oradata/user_data_01.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. Segement

  3. Oracle block

  4. Operating system block
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

In a locally managed tablespace with UNIFORM SIZE specification, all extents are allocated at the uniform size specified (1 MB in this case). The extent is the basic unit of allocation - not segments, Oracle blocks, or OS blocks. Each extent will be exactly 1 MB regardless of the segment size or Oracle block size. This simplifies space management and reduces fragmentation.

Multiple choice technology databases

Which statement is ture regarding checkpoints and recovery ?

  1. A checkpoint defines the highest system change number (SCN)

  2. All redo entries higher or at the SCN are known to be written to the data files.

  3. Only the redo records containing SCNs higher then the checkpoint need to be applied during recovery.

  4. The LOG_CHECKPOINT_INTERVAL initialization paramter specifies the amount of time beteen incremental checkpoints.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

A checkpoint records the highest SCN written to data files. During recovery, only redo records with SCNs higher than the checkpoint SCN need to be applied - changes below that point are already persisted. This dramatically reduces recovery time. Option A is wrong because checkpoint doesn't define SCN, B is wrong because redo above checkpoint is NOT written to data files yet, and D refers to a different parameter.

Multiple choice technology databases

What should you look at to compute the number of undo blocks written per second on disk ?
  1. V$UNDOSTAT
  2. V$TRANSACTION
  3. V$ROLLSTAT

  4. DBA_UNDO_EXTENTS

  5. DBA_ROLLBACK_SEGS
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The V$UNDOSTAT dynamic performance view contains statistics for monitoring the effects of undo space management, including the number of undo blocks consumed (UNDOBLKS) in 10-minute intervals. This data allows administrators to calculate the rate of undo blocks written per second. Other views do not provide this specific time-series undo block data.

Multiple choice technology databases

In user-managed backup and recover procedure, how are data files backed up ?

  1. Using SQL commands.

  2. Using SQL*Plus Commands.

  3. Using operating system commands.

  4. Using Recover Manager commands.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

In user-managed backups, the database administrator manually copies the database files using operating system copy utilities (such as cp or copy) after putting the tablespaces into backup mode. SQL or SQL*Plus commands are used to manage the database state, but they do not perform the actual file copying.

Multiple choice technology databases

Consider this RMAN command: RMAN> CONFIGURE RETENTION POLICY CLEAR; What is the effect of this command?

  1. Backups will never expire.

  2. It removes any retention policy.

  3. The DELETE OBSOLETE command will fail with an error.

  4. It sets the retention policy to the default of REDUNDANCY 1.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The CONFIGURE ... CLEAR command in RMAN resets the specified configuration parameter back to its default value. For the retention policy, the default setting is REDUNDANCY 1. It does not remove the retention policy entirely, nor does it cause the DELETE OBSOLETE command to fail.

Multiple choice technology databases

You have just run this syntax to start the listener from the Listener Control utility: LSNRCTL>START Starting /mstgelai/oracle/bin/tnslsnr: please wait... System parameter file is /u01/oracle/network/admin/listener.ora Log messages written to /u01/oracle/network/log/listener.log Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=stc-sun01-server)(PORT=1521))) Connecting to... The command completed successfully What is the name of the Listener you started?

  1. TNSLSNR

  2. LISTENER

  3. LOCAL_LISTENER

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

Running the START command in the LSNRCTL utility without specifying a listener name starts the default listener, which is named LISTENER. TNSLSNR is the executable program name, not the listener name, while LOCAL_LISTENER is an initialization parameter, and DEFAULT_LSRN is not a standard default name.

Multiple choice technology databases

When performing recovery of a database in NOARCHIVELOG mode with RMAN, the target database must be in the ______ state to restore the control file.

  1. OPEN

  2. IDLE

  3. MOUNT

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

When restoring a control file in NOARCHIVELOG mode using RMAN, the database must be in NOMOUNT state. NOMOUNT allows instance initialization and control file access without opening the data files. OPEN state would require a valid control file already, MOUNT needs the control file to be accessible, and IDLE is not a valid Oracle state. The NOMOUNT state enables control file restoration before mounting.

Multiple choice technology databases

The ORDERS table has a constant transaction load 24 hours a day, so down time is not allowed. The index become fragmented. Whic statment is true ?

  1. The index need to be dropped, and then re-created.

  2. The resolution of index fragmentation depends on the type of index.

  3. The index can be rebuilt while users continue working on the table.

  4. The fragmented can be ignored because Oracle resolve index fragmentation by means of a freelist.

  5. The index can be rebuilt, but users will not have access to the index during this time.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Rebuilding an index online (REBUILD ONLINE) allows users to perform DML operations on the underlying table during the rebuild process, ensuring zero downtime. Dropping and recreating the index or rebuilding it offline restricts user access, which violates the requirement of continuous 24/7 availability. Freelists do not automatically resolve index fragmentation.

Multiple choice technology databases

You omit the UNDO tablespace clause in your CREATE DATABASE statment. The UNDO_MANAGEMENT parameter is set to AUTO. What is the result of your CREATE DATABASE statement ?

  1. The oracle server creates no undo tablespace.

  2. The oracle server creates an undo segment in the system tablespace.

  3. The oracle server cerates one undo tablespace with the name SYS_UNDOTBS.

  4. Database creation fails because you did not specify an undo tablespace on the create database statment.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

When UNDO_MANAGEMENT is set to AUTO and you omit the UNDO tablespace clause in CREATE DATABASE, Oracle automatically creates a default undo tablespace named SYS_UNDOTBS. This is the expected behavior in automatic undo management mode. Options A and D are incorrect because the database creation succeeds and Oracle does create an undo tablespace automatically. Option B is wrong because undo segments are not placed in the SYSTEM tablespace when using automatic undo management.

Multiple choice technology databases

The employees table is stored in the SAMPLE tablespace. The corresponding IPK_EMP index for the EMPLOYEES table's primary key is stored in INDX tablespace. Out of 12 partitions, only partition P1 of the SALES table is stored in the SAMPLE tablespace. Which object will be exported by this command ? exp system/manager tablespace=SAMPLE

  1. Only the EMPLOYEES table.

  2. Only the EMPLOYEES table and its corresponding primary key index.

  3. The EMPLOYEES table and partition P1.

  4. The EMPLOYEES table, IPK_EMP index, and the SALES table.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Specifying tablespace-mode export (tablespace=SAMPLE) exports all tables located in that tablespace (EMPLOYEES), their indexes regardless of tablespace (IPK_EMP), and any table partitions located in that tablespace (SALES partition P1).

Multiple choice technology databases

You want to drop the TBSI tablespace from your database. You also want to delete corresponding data files automatically, and not have to do it manually. What should you do ?

  1. User the DROP DATAFILE command.

  2. Use the DROP TABLESPACE command.

  3. Ensure that all database files are Oracle Managed Files(OMF) before using the DROP TABLESPACE command.

  4. Ensure that the DB_FILE_CREATE_DEST initialization parameter is set before using the DROP TABLESPACE command.
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The DROP TABLESPACE command drops the tablespace. In modern Oracle versions, adding the 'INCLUDING CONTENTS AND DATAFILES' clause deletes the datafiles, but the basic command to initiate this is DROP TABLESPACE.

Multiple choice technology databases

Examine the statement : DROP TABLESPACE IND2 INCLUDING CONTENTS AND DATAFILES; What is the result of the statement?

  1. The contents and data files belonging to the IND2 tablespace are dropped.

  2. The statement will only succeed if the IND2 tablespace was built using Oracle-Managed Files (OMF) data files.

  3. The statement will only succeed if the data files belonging to the IND2 tablespace are Oracle Managed Files (OMF) or of the database was created using OMF.

  4. The contents of the tablespace are dropped along with all its data files except those identical with Oracle-Managed Files (OMF) naming convention.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The DROP TABLESPACE statement with INCLUDING CONTENTS AND DATAFILES removes the tablespace, all its contents (segments/data), and the associated data files from the operating system. This is the complete cleanup option. Option B and C incorrectly suggest Oracle-Managed Files (OMF) is required, but this clause works for both OMF and non-OMF data files. Option D is wrong because the AND DATAFILES clause removes all data files regardless of naming convention.

Multiple choice technology databases

A user is running a long update batch file. Many tables are updated and all the statements are in one transaction. Before the process is finished, the user machine lost power. When the user calls you, what should you do as DBA?

  1. Do nothing, the PMON process handles everything.

  2. Shut down the database and restore all the tables the user was updating.

  3. Log in as DBA, clean up the partial updates in those tables, and do another manual backup.

  4. Use LogMiner to find out which records are changed based on the user’s ID, then roll back all those changes.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

When a client machine loses power during a transaction, Oracle's PMON (Process Monitor) background process automatically detects the interrupted session and rolls back the uncommitted changes. This ensures database consistency and atomicity. Option B is incorrect because you don't need to restore - the transaction was never committed. Option C is unnecessary as PMON handles cleanup. Option D is overly complex when automatic rollback occurs.

Multiple choice technology databases

Your database is running in NOARCHIVELOG mode. Every night there is a backup taken using RMAN. During your startup this morning you received an error regarding DISK 3 on your server.This is the location of your data file for the SYSTEM tablespace. Which option is open to you?

  1. The data file needs to be restored by using operating system commands, because a data file movement must take place, and then the database can be recovered from RMAN.

  2. The data file needs to be restored by using operating system commands, since a data file movement must take place, and then the database must be recovered manually.

  3. From RMAN you can issue the commands RESTORE and RECOVER. RMAN automatically restores the data file on DISK 3 to another disk, based upon utilization statistics.

  4. From RMAN you can issue a SET NEWNAME command to update the data file location information in the control file, and then issue the RESTORE and RECOVER commands to recover the database.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

When a data file is lost and you need to restore it to a different location, use RMAN's SET NEWNAME command to tell the control file the new location, then RESTORE and RECOVER. This is necessary because the original disk (DISK 3) is unavailable. Options A and B are incorrect because you don't use operating system commands to move data files in RMAN recovery. Option C is wrong because RMAN doesn't automatically select a new disk based on utilization - you must explicitly specify it with SET NEWNAME.