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

When acquiring Tablespace lock more data is restricted than page level Lock (True or False)

  1. True

  2. False
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

A tablespace lock is the most restrictive type of lock in DB2. It prevents any other process from accessing any data in the tablespace. A page lock only prevents other processes from accessing the specific page that is locked.

Therefore, the statement "When acquiring Tablespace lock more data is restricted than page level Lock" is True.

A tablespace lock restricts access to all of the data in a tablespace, while a page lock only restricts access to a single page. This means that a tablespace lock is more restrictive than a page lock.

For example, if a tablespace contains 100 pages and a process acquires a tablespace lock on the tablespace, then no other process can access any of the data in the tablespace, even if the data is not on the page that is locked. However, if a process acquires a page lock on a single page in the tablespace, then other processes can still access the data on the other 99 pages in the tablespace.

In general, tablespace locks should only be used when it is absolutely necessary to prevent other processes from accessing any data in the tablespace. Page locks should be used whenever possible, as they provide a more granular level of locking and allow other processes to access more data.

Multiple choice technology databases

Your database is in NOARCHIEVELOG mode. After which two operations you should take backup of control file? ( choose two)

  1. Adding a new user to database.

  2. Adding a new tablespace to database.

  3. Dropping a table from the database.

  4. Dropping a user from the database.

  5. Dropping a datafile from a tablespace.
Reveal answer Fill a bubble to check yourself
B,E Correct answer
Explanation

In NOARCHIVELOG mode, you cannot recover from backups after a structural change. Adding a tablespace changes the database structure permanently. Dropping a datafile from a tablespace is another structural change that cannot be recovered without a backup. Adding/dropping users or tables are data dictionary changes, not structural changes that require control file backup.

Multiple choice technology databases

Which is the memory area that is created when a dedicated server process is started, and contains data and information for that server process?

  1. SGA

  2. Streams Pool

  3. PGA

  4. Shared Pool
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

PGA (Program Global Area) is a memory region created for each server process when it starts. It contains data and control information for that specific server process. SGA (System Global Area) is shared memory. Shared Pool and Streams Pool are components of the SGA, not per-process memory areas.

Multiple choice technology databases

What four data protection components have been enhanced in Oracle database 10g release 2

  1. Flashback

  2. Data guard

  3. ASM

  4. Tivoli data protection

  5. Flash recovery.
Reveal answer Fill a bubble to check yourself
A,B,C,E Correct answer
Explanation

Oracle 10g Release 2 enhanced Flashback technology, Data Guard for disaster recovery, ASM for storage management, and introduced Flash Recovery Area (now Fast Recovery Area). Tivoli Data Protection is an IBM product, not an Oracle component. These four native Oracle features were significantly improved in that release.

Multiple choice technology databases

Which is the correct description of the significance of the ORACLE_HOME environment variable?

  1. It specifies the directory containing the oracle managed files.

  2. It specifies the directory for database files, if not specified explicitly.

  3. It specifies the directory containing the oracle software

  4. It specifies the directory of Optimal Flexible Architecture.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

ORACLE_HOME is an environment variable that points to the directory where Oracle software is installed. It contains binaries, libraries, and configuration files. It is not related to managed files location, database file storage, or OFA directory structure - those are separate concepts.

Multiple choice technology databases

Which two statements about recovery manager (RMAN) backup are true?

  1. Online redo log files can be backed up.

  2. RMAN backup can be taken only if the database is configured in ARCHIEVELOG mode.

  3. Only used data blocks can be backed up as backup sets.

  4. Archived redo log files are backed up.

  5. Only consistent database backed up can be performed.
Reveal answer Fill a bubble to check yourself
C,D Correct answer
Explanation

RMAN backup sets only include used data blocks, skipping unused blocks to save space. Additionally, archived redo log files can be backed up using RMAN, whereas online redo logs cannot be backed up directly.

Multiple choice technology databases

You want the user APP_DBA to administer the oracle database from remote machine. APP_DBA is granted SYSDBA privilege to perform administrative task on the database. Which file is used by oracle database server to authenticate APP_DBA?

  1. Control file and password file

  2. Password file

  3. Control file

  4. Listener control file
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

To answer this question, the user needs to know about the authentication mechanism in Oracle databases and the files used to implement it.

In Oracle databases, SYSDBA privilege is required to perform administrative tasks on the database. The user APP_DBA has been granted this privilege to administer the database from a remote machine. To authenticate the user, Oracle uses a password file.

Therefore, the correct answer is:

The Answer is: B. Password file

Multiple choice technology databases

The junior DBA of your organization has accidentally deleted the alert log file. What will you do to create new alert log file?

  1. Create the new text file as ALERT.log

  2. You have to recover the alert log file from the valid backup.

  3. No action required. The file will be created automatically by the instance.

  4. Change the value for the BACKGROUND_DUMP_DEST parameter.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Oracle automatically creates a new alert log file when it doesn't exist or has been deleted. You don't need to manually create it or restore from backup. The instance will regenerate it automatically when it needs to write alerts. Changing BACKGROUND_DUMP_DEST only affects location, not creation.

Multiple choice technology databases

Which two statements are true about the role in Oracle Database?

  1. A role can contain both system and object privileges.

  2. A role cannot be assigned external authentication.

  3. A role can be granted to itself.

  4. Roles are owned by the SYS user.

  5. Roles can be granted to other roles
Reveal answer Fill a bubble to check yourself
A,E Correct answer
Explanation

Oracle roles can contain both system and object privileges, making them flexible for privilege management. Roles can be granted to other roles, allowing hierarchical privilege structures. Roles are NOT owned by SYS (they're owned by their creator). Roles cannot be self-referential (granted to themselves), and can use external authentication.

Multiple choice technology databases

Which of the following SQL statements results in a disconnection after a session is idle for 30 minutes?

  1. alter session set idle_timeout=30;

  2. alter session set idle_timeout=1800;

  3. alter profile limit idle_time 30;

  4. alter profile set idle_timout 30;
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

To disconnect idle sessions, use ALTER PROFILE with IDLE_TIME parameter (measured in minutes). Options A and B use non-existent IDLE_TIMEOUT parameter. Option D incorrectly uses SET instead of LIMIT and has typo (IDLE_TIMOUT). Option C correctly uses ALTER PROFILE LIMIT IDLE_TIME 30.

Multiple choice technology databases

Which three information are to be mandatorily provided while creating a new listener using Enterprise Manager Database Control?

  1. The database services to be registered with the listener

  2. The log file and trace file destination for the listener.

  3. The protocol used by the listener

  4. The server name where the listener runs.

  5. The port used by the listener.
Reveal answer Fill a bubble to check yourself
C,D,E Correct answer
Explanation

When creating a new listener, you must specify the protocol (typically TCP), the host/server name where the listener runs, and the port number it listens on.

Multiple choice technology databases

You specified segment space management as automatic for a tablespace what effect would this have on space management?

  1. Extents would be managed by freelists.

  2. Free space would be managed by data dictionary.

  3. The segment would be managed by bitmaps.

  4. The segment would be managed by freelists.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Automatic Segment Space Management (ASSM) uses bitmaps to track free space within segments, replacing traditional freelist management. When you specify segment space management as automatic, Oracle uses bitmap-based space tracking. Freelists and data dictionary management are manual space management techniques.

Multiple choice technology databases

A user wants to connect to the database instance from an application that is running on a remote machine. Which tool should the DBA use to establish the required configuration to ensure that the user is able to connect to the database instance? (Choose two).

  1. Oracle Net Manager.

  2. Data Pump.

  3. Oracle Universal Installer (OUI).

  4. Oracle Enterprise Manager.

  5. Database Configuration Assistant (DBCA).
Reveal answer Fill a bubble to check yourself
A,D Correct answer
Explanation

Oracle Net Manager is the GUI tool for configuring network parameters like listener.ora and tnsnames.ora files. Oracle Enterprise Manager provides a web-based interface for database administration including network configuration. Data Pump is for data transfer, OUI is for software installation, and DBCA is for database creation - none handle network connectivity setup.

Multiple choice technology databases

Which two are valid locking levels that are used by transactions in oracle database? (Choose two)

  1. Object level.

  2. Row level.

  3. Block level.

  4. Schema level.

  5. Database level.
Reveal answer Fill a bubble to check yourself
A,B Correct answer
Explanation

Row-level locking is the most granular and commonly used locking mechanism in Oracle, allowing concurrent transactions to access different rows. Object-level locking applies to entire tables, views, or other schema objects and is used for DDL operations or table locks. Block-level, schema-level, and database-level locking are not standard Oracle locking granularity levels.

Multiple choice technology databases

Your database is having two control files; three redo log file groups with two members in each group. Failure of which file would cause a instance to shut down?

  1. Loss of the initialization parameter file.

  2. One of the redo log members.

  3. Any data file belonging to the default permanent tablespace

  4. Any archive log file.

  5. Any control file.
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

The loss of any control file will cause an Oracle instance to immediately shut down because control files are continuously accessed and must be synchronized. Redo log members are multiplexed, so losing one member of a group does not crash the instance.