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 programming languages

Which line of code represents the correct syntax to establish a reference to a database handle?
  1. $dbh = DBI::connect("dbi:mysql:myPhoneBook");
  2. $dbh = DBD:->connect("dbi::mysql::myPhoneBook");
  3. $dbh = DBD::connect("mysql:dbi:myPhoneBook");
  4. $dbh = DBI->connect("dbi:mysql:myPhoneBook");
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The correct Perl DBI syntax is DBI->connect() with the DSN string 'dbi:mysql:myPhoneBook'. Options A and B incorrectly use static (::) instead of arrow (->) syntax. Option C has the wrong DSN format (mysql:dbi instead of dbi:mysql). The DBI module uses object-oriented syntax with the arrow operator.

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 an Oracle database is restarted after an unexpected crash. PMON cleans up failed user processes, RECO resolves pending distributed transactions, ARCn archives redo logs, and CKPT signals checkpoints, making them incorrect for instance recovery.

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 1M, every EXTENT allocated within that tablespace is exactly 1 MB. This provides predictable space allocation and avoids fragmentation.

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 index can be rebuilt, but users will not have access to the index during this time.

  5. The fragmented can be ignored because Oracle resolve index fragmentation by means of a freelist.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Oracle allows online index rebuild using the ONLINE keyword (ALTER INDEX ... REBUILD ONLINE), enabling users to continue DML operations during the rebuild. This is essential for 24/7 systems.

Multiple choice technology

What is the effect of checking out a project?

  1. Locks the project in the server repository but not in the local repository

  2. Locks the projects in the local database repository

  3. Locks the projects in the server database repository

  4. Copies all object definitions in the project on the server to the local database
Reveal answer Fill a bubble to check yourself
B,C,D Correct answer
Explanation

Checking out a Siebel project locks it in both local and server repositories (preventing concurrent modifications), and copies all object definitions from server to local database, enabling offline development.

Multiple choice technology

Which server task creates the Docking\User directory on the Siebel Server for a new Mobile Client?

  1. Transaction Router

  2. Generate New Database

  3. Synchronization Manager

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

In Siebel Mobile Web Client architecture, the Database Extract task is responsible for initializing a new mobile client's local database. This task creates the necessary directory structure including the Docking\User directory on the Siebel Server, which stores the client-specific data and configuration files.

Multiple choice technology programming languages

Which line of code represents the correct syntax to establish a reference to a database handle?
  1. $dbh = DBI::connect("dbi:mysql:myPhoneBook");
  2. $dbh = DBD:->connect("dbi::mysql::myPhoneBook");
  3. $dbh = DBD::connect("mysql:dbi:myPhoneBook");
  4. $dbh = DBI->connect("dbi:mysql:myPhoneBook");
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The correct Perl DBI syntax is $dbh = DBI->connect("dbi:mysql:myPhoneBook"); DBI is the database interface module, and connect is called as a class method using arrow notation. The dbi:mysql: prefix specifies the DBI driver and database name. Options B and C incorrectly use DBD or wrong syntax, and option A omits the arrow operator.

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.