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

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 no undo tablespace is specified in CREATE DATABASE, Oracle automatically creates an undo tablespace named SYS_UNDOTBS. This default undo tablespace is essential for automatic undo management functionality and transaction rollback support.

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 view provides statistics on undo tablespace usage, including undo blocks written per second. This dynamic performance view is specifically designed for monitoring undo space consumption and helps track undo block write rates to disk.

Multiple choice technology databases

You discover that a disk failure occured on DISK3 where the data file belonging to the USER_DATA table is stored. The database is currently open and you need to recover the data file. You will restore the data file to DISK2. what is the correct sequence RMAN commands to restore and recover the data file ?

  1. RUN { RESTORE TABLESPACE user_data; RECOVER TABLESPACE user_data; ALTER DATABASE OPEN; }

  2. RUN { ET NEWNAME FOR '/DISK3/user_data01.dbf' TO '/DISK2/user_data01.dbf'; RESTORE TABLESPACE user_data; SWITCH DATAFILE '/DISK3/user_data01.dbf';

  3. RUN { RESTORE TABLESPACE user_data; SET NEWNAME FOR '/DISK3/user_data01.dbf' TO '/DISK2/user_data01.dbf'; SWITCH DATAFILE '/DISK3/user_data01.dbf';

  4. RUN { SET NEWNAME FOR '/DISK3/user_data01.dbf' TO '/DISK2/user_data01.dbf'; RESTORE TABLESPACE user_data; RECOVER TABLESPACE user_data; ALTER DATABASE OPEN;}
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The correct sequence begins with SET NEWNAME to specify the new location, followed by RESTORE to recover the datafile to that location. The SWITCH DATAFILE command then updates the control file to reflect the new location. This order ensures proper restoration and control file updates when moving a datafile during recovery.

Multiple choice technology databases

When performing an incomplete recovery of the whole database, what must be true about the data files that are restored ?

  1. All of the data files must be from a backup taken prior to the point in time to which you want to recover.

  2. Only the data files belonging from the SYSTEM tablespace must be from a backup taken prior to the point in time to which you want to recover.

  3. Only the data files that need recovery must be from a backup taken prior to the point in time which you want to recover.

  4. Only the data files belonging to the SYSTEM tablespace and the data files that need recovery must be from a backup taken prior to the point in time to which you want to recover.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Incomplete database recovery requires ALL data files to be from backups taken before the target recovery point. This ensures consistent database state across all tablespaces - you cannot recover partially or use files from different time periods, as this would create database inconsistencies.

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 backup procedures, the DBA uses operating system commands (like cp, tar, or utilities in Windows/Unix) to copy Oracle data files. This contrasts with RMAN-managed backups where Oracle's Recovery Manager handles file operations. Options A and B are SQL-level tools, not file-level operations. Option D describes server-managed backups, not user-managed.

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

The UNIFORM SIZE 1M clause in a LOCALLY MANAGED tablespace specifies that each extent will be exactly 1 MB in size. This is the fundamental allocation unit in the tablespace. Segments are composed of multiple extents. Oracle block size is set at database level (typically 8K). OS block size is OS-dependent. The uniform allocation ensures all extents are the same size for performance and 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

A tablespace-mode export of the SAMPLE tablespace exports the EMPLOYEES table, its dependent index IPK_EMP (even though it is in the INDX tablespace), and partition P1 of the SALES table.

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 with the INCLUDING CONTENTS AND DATAFILES clause will automatically delete the data files. However, if Oracle Managed Files (OMF) is configured, the data files are automatically deleted without needing the DATAFILES clause. Option A's command doesn't exist. Option C would work but is more specific than needed. Option D is related to OMF configuration but not directly the solution.

Multiple choice technology databases

You have performed the first step in placing your database into ARCHIVELOG mode. The second step is deciding whether the online redo log files are to be archived automatically or manually. which action should you take to enable archiving an instance startup?

  1. Set the LOG_ARCHIVE_START parameter to TRUE in the initialization parameter file.

  2. Automatic archive is the default when ARCHIVELOG mode is set. No further actions are required.

  3. Set the LOG_ARCHIVE_MAX_PROCESSES parameter to n (where n is the number of Archiver process) in the initialization parameter file.

  4. Immediately after setting the database in ARCHIVELOG mode, in SQL*Plus select automatic archiving using the syntax : ALTER DATABASE SET LOG_ARCHIVE_START=TRUE
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

To enable automatic archiving of redo logs, set LOG_ARCHIVE_START=TRUE in the initialization parameter file. This causes the ARCH process to automatically archive filled redo logs. Option B is incorrect - automatic archiving is not the default. Option C controls the number of archiver processes but doesn't enable automatic archiving. Option D has incorrect syntax - this is a parameter, not an ALTER DATABASE option.

Multiple choice technology databases

You issue this statement ALTER DATABASE BACKUP CONTROLFILE TO TRACE; What does the statment generate ?

  1. A text copy of the control file.

  2. A binary copy of the control file.

  3. A file containing a SQL statement which will re-create the database.

  4. A file containing a SQL statement which will re-create the control file.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The ALTER DATABASE BACKUP CONTROLFILE TO TRACE command generates a trace file in the user_dump_dest directory containing SQL statements (CREATE CONTROLFILE) that can be used to recreate the control file. Option A is incorrect - it's not a direct copy. Option B describes binary control file backups. Option C is wrong - it recreates the control file, not the entire database. This is useful for recovery or control file reconstruction.

Multiple choice technology databases

Which statement is true regarding RMAN components?

  1. Each target database requires its own recover catalog.

  2. RMAN scripts can be stored in the recovery catalog or the targer control file.

  3. A channel is a stream of data to a device type and corresponds to one server session.

  4. The RMAN executable interprets server session commands and establishes connections to the target database.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

An RMAN channel represents a single stream of data to a backup device and corresponds to one active server session on the target database instance.

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

An Oracle checkpoint marks the highest system change number (SCN) guaranteed to be written to data files. During recovery, only redo records containing SCNs higher than the checkpoint need to be applied because all changes at or below the checkpoint are already safely on disk.

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 'INCLUDING CONTENTS AND DATAFILES' clause drops the tablespace, its schema objects, and deletes the associated physical operating system data files, regardless of whether they are Oracle-Managed Files.

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

PMON (Process Monitor) is the background process responsible for cleaning up failed user processes and rolling back their uncommitted transactions. When the client machine loses power mid-transaction, PMON automatically detects and rolls back the incomplete transaction when the instance remains available.

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

In NOARCHIVELOG mode, when a data file is lost, RMAN can restore it to a new location using SET NEWNAME to update the control file metadata, then RESTORE to copy from backup and RECOVER to apply available redo. This is necessary because DISK 3 failed and the file must be restored elsewhere.