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

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 SCN up to which all changes have been written to data files. During recovery, only redo records with SCNs higher than the checkpoint SCN need to be applied. This is because all changes at or below the checkpoint are already safely stored in the data files. Checkpoints do not define the highest SCN, they don't guarantee redo entries at the SCN are written, and LOG_CHECKPOINT_INTERVAL specifies redo blocks, not time.

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 ... INCLUDING CONTENTS AND DATAFILES statement drops the tablespace from the data dictionary, deletes all contents, and deletes the associated operating system datafiles, regardless of whether OMF is used. Hence, option 554180 is correct and others are false restrictions.

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 with a damaged SYSTEM data file, use SET NEWNAME in RMAN to redirect the restore to a new location, then RESTORE and RECOVER. SET NEWNAME updates the control file with the new data file location. OS-level restore commands alone won't work because RMAN needs to know the new location. RMAN doesn't automatically choose disk locations based on utilization - you must specify them.

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

CONFIGURE RETENTION POLICY CLEAR resets the retention policy to the default, which is REDUNDANCY 1 (keeps one copy of each backup). It doesn't remove retention policies or make backups never expire. The DELETE OBSOLETE command will continue to work normally after clearing. CLEAR restores the Oracle default, not a no-policy state.

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

If no specific listener name is provided to LSNRCTL, it starts the listener named LISTENER by default. TNSLSNR is the executable name, and LOCAL_LISTENER is an initialization parameter, not the default listener name started by default configuration.

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
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
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 a consistent point in time - a backup taken prior to the target recovery time. If some files are from newer backups, the database won't be consistent at the recovery point. Oracle applies redo logs to bring all files forward to the same point-in-time, but they must all start from before that point.

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 at instance startup in older Oracle releases (e.g. Oracle 9i and earlier), LOG_ARCHIVE_START must be set to TRUE in the initialization parameter file. Option 554209 is incorrect because ARCHIVELOG mode does not automatically start archiving unless configured. Other options do not control startup archiving behavior.

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 statement writes a text file to the database's trace directory containing the SQL statement (CREATE CONTROLFILE) needed to recreate the control file. It does not produce a copy of the database schema, nor does it create a binary or exact text copy of the control file itself.

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

In Oracle RMAN, a channel represents a stream of data to a specified device (disk or tape) and corresponds to a single server session on the target database. Target databases share a recovery catalog, RMAN scripts are not stored in control files, and the RMAN executable compiles commands before execution.

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 user session terminates abnormally (power loss), Oracle's PMON (Process Monitor) background process automatically detects the dead session and rolls back the uncommitted transaction. The database remains consistent - no manual intervention is needed. Options B, C, and D suggest unnecessary manual actions that PMON already handles automatically.

Multiple choice technology databases

When an oracle Instance is started, background process are started. Background processes perform which two functions ? (Choose two)

  1. Perform I/O

  2. Lock rows that are not data dictionary rows

  3. Monitor other Oracle processes

  4. Connect users to the Oracle instance

  5. Execute SQL statments issued through an application
Reveal answer Fill a bubble to check yourself
A,C Correct answer
Explanation

Oracle background processes primarily handle I/O operations (reading/writing data files, redo logs) on behalf of server processes, and they monitor other Oracle processes for proper functioning. They do NOT execute SQL statements (that's server processes), connect users, or lock non-dictionary rows. LGWR, DBWn, ARCH, and other background processes perform these I/O and monitoring functions.

Multiple choice technology databases

Which three are the physical structures that constiture the Oracle database ? (Choose Three)

  1. Control file

  2. Extent

  3. Segment

  4. Data file

  5. Log file

  6. Tablespace
Reveal answer Fill a bubble to check yourself
A,D,E Correct answer
Explanation

Oracle's physical database structures at the OS level are: Control files (database metadata/structure), Data files (actual database data storage), and Log files (redo logs for recovery). Extents, Segments, and Tablespaces are LOGICAL storage structures, not physical files. Extent and Segment are logical allocations within tablespace, and Tablespace is a logical container.

Multiple choice technology databases

What file will give you Oracle instance status information?

  1. alert.ora

  2. init.ora

  3. tnsname.ora

  4. redolog
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The alert log (alert.log or alert.ora) is the primary file containing Oracle instance status information, errors, and significant events. It records startup/shutdown, errors, checkpoints, and other critical instance information. init.ora contains initialization parameters, tnsnames.ora has network connection strings, and redolog files contain redo data - none provide instance status.