R4RIN – MCQs, Mock Tests | Oracle DB Oracle DB Mcq Question Set 4

DCL Provides Commands To Perform Actions Like ______

1. Change The Structure Of Tables
2. Insert, Update Or Delete Records And Data Values
3. Authorizing Access And Other Control Over Database
4. None of the mentioned

✅ Correct Answer: 3

To obtain the structure of an Oracle table, the command to use is:

1. STRUCTURE [TableName].
2. DESCRIBE [TableName].
3. DESCRIBE STRUCTURE [TableName].
4. DESC TABLE [TableName].

✅ Correct Answer: 2

Database management systems are intended to _______

1. Eliminate data redundancy
2. Establish relationship among records in different files
3. Manage file access
4. All of the Mentioned

✅ Correct Answer: 4

Database management systems are intended to _________

1. Eliminate data redundancy
2. Establish relationship among records in different files
3. Manage file access
4. All of the Mentioned

✅ Correct Answer: 4

The Oracle environment of database is called as _______

1. Database Schema
2. Database Instances
3. Data Structure
4. All of the Mentioned

✅ Correct Answer: 2

Collection of information stored in database at particular instance of time is called as _______

1. Instance of Database
2. Objects in Databases
3. Data structure
4. Database Schema

✅ Correct Answer: 1

Oracle database’s design is also called as______

1. Database Abstraction
2. Database Instance
3. Database Schema
4. None of the mentioned

✅ Correct Answer: 3

Map entities, attributes and relations in Oracle is represented by______

1. Conceptual Schema
2. Logical Schema
3. Physical Schema
4. All of the Mentioned

✅ Correct Answer: 2

A ______ is used to logically group data together

1. Database
2. Tablespace
3. Datafiles
4. Object

✅ Correct Answer: 2

A _____ is a set of tables physically stored together as one table that shares a common column

1. Index
2. Object
3. Datafiles
4. Cluster

✅ Correct Answer: 4

Examine the following settings for the initialization parameters: MEMORY_MAX_TARGET=0 MEMORY_TARGET=500M SGA_TARGET=300M PGA_AGGREGATE_TARGET=70M Which statement is true about the memory management for the newly started database instance?

1. MEMORY_TARGET defines the maximum limit for SGA_TARGET
2. SGA_TARGET and PGA_AGGREGATE_TARGET combined will never grow beyond 500M
3. The value for MEMORY_MAX_TARGET is the sum of SGA_TARGET and PGA_AGGREGATE_TARGET
4. SGA_TARGET and PGA_AGGREGATE_TARGET will keep growing till a maximum of 300M and 70M, respectively

✅ Correct Answer: 2

What type of failure occurs when Oracle fails due to an operating system or computer hardware failure?

1. Application failure
2. Instance Failure
3. Media Failure
4. Rollback failure

✅ Correct Answer: 2

Which statement about sequences is not true?

1. A sequence is an object that generates a sequential series of unique numbers.
2. Sequences are most often used to provide values for surrogate keys.
3. NextVal and CurrVal are both sequence methods.
4. Sequences guarantee valid surrogate key values.

✅ Correct Answer: 4

Which prefixes are available to Oracle triggers?

1. :new only
2. :old only
3. Both :new and :old
4. Neither :new nor :old

✅ Correct Answer: 3

In creating a procedure, you may get a message if you have compile errors. Which of the following is true?

1. The line numbers reported match the line numbers you see in your text editor.
2. SQL*Plus will automatically show the errors to you.
3. To see the errors, enter SHOW ERRORS in SQL*Plus.
4. If there are no syntax errors, you will receive the message "NO ERRORS."

✅ Correct Answer: 3

Which of the following is not true about indexes?

1. Indexes are created to enforce uniqueness on columns.
2. Indexes are created to enable fast retrieval by column values.
3. Columns that are frequently used with equal conditions in WHERE clauses are good candidates for indexes.
4. Indexes are created with the ALTER TABLE command.

✅ Correct Answer: 4

Which of the following is not true of SQL views?

1. Oracle views cannot use the ORDER BY clause in view definitions.
2. Oracle views are created using the standard SQL-92 CREATE VIEW command.
3. Oracle views can by queried.
4. The SQL-92 standard does not allow the use of the ORDER BY clause in view definitions.

✅ Correct Answer: 1

SQL*Plus will finish the statement and execute it when the user types in this:

1. A left slash ( ) followed by [Enter].
2. A colon ( : ) followed by [Enter].
3. A semicolon ( ; ) followed by [Enter].
4. A period ( . ) followed by [Enter].

✅ Correct Answer: 3

Which of the following is NOT an Oracle-supported trigger?

1. BEFORE
2. DURING
3. AFTER
4. INSTEAD OF

✅ Correct Answer: 2

After a table has been created, its structure can be modified using the SQL command:

1. UPDATE TABLE [TableName].
2. MODIFY TABLE [TableName].
3. ALTER TABLE [TableName].
4. CHANGE TABLE [TableName].

✅ Correct Answer: 3

Which of the following is not true about modifying table columns?

1. You can drop a column at any time.
2. You can add a column at any time as long as it is a NULL column.
3. You can increase the number of characters in character columns or the number of digits in numeric columns
4. You cannot increase or decrease the number of decimal places.

✅ Correct Answer: 3

Of the three ways to create an Oracle database, which one is the easiest and most recommended?

1. Using the Oracle Database Configuration Assistant.
2. Using the Oracle-supplied database creation procedures.
3. Using the SQL CREATE DATABASE command.
4. none of the above is correct.

✅ Correct Answer: 1

What Oracle backup and recover file contains user and system data?

1. Control file
2. Datafile
3. OnLine ReDo file
4. Offline ReDo file

✅ Correct Answer: 2

When using SQL*Plus, Oracle commands, column names, table names and all other database elements:

1. are case insensitive.
2. are case sensitive.
3. must always be in lower case.
4. must always be in upper case.

✅ Correct Answer: 1

Which SQL phrase is not supported by Oracle?

1. ON DELETE CASCADE
2. ON UPDATE CASCADE
3. CREATE SEQUENCE [SequenceName]
4. DROP SEQUENCE [SequenceName]

✅ Correct Answer: 2

What is the type of Oracle backup in which all uncommitted changes have been removed from the datafiles?

1. Full backup
2. Consistent backup
3. Inconsistent backup
4. Differential backup

✅ Correct Answer: 2

You have linked SQL*Plus to an external text editor. To invoke the text editor for use within SQL*Plus, which command do you use?

1. Open [FileName]
2. Show [FileName]
3. Alter [FileName]
4. Edit [FileName]

✅ Correct Answer: 4

The default extension for an Oracle SQL*Plus file is:

1. .txt.
2. .pls.
3. .ora.
4. .sql.

✅ Correct Answer: 4

An Oracle System Change Number (SCN):

1. is a value that is incremented whenever a dirty read occurs.
2. is incremented whenever a deadlock occurs.
3. is a value that keeps track of explicit locks.
4. is a value that is incremented whenever database changes are made.

✅ Correct Answer: 4

To see the contents of the SQL*Plus buffer, type:

1. CONTENTS.
2. BUFFER.
3. CURRENT.
4. LIST.

✅ Correct Answer: 4

Oracle database is a ?

1. Centralised database
2. End-user database
3. Commercial database
4. Relational database

✅ Correct Answer: 4

How many editions of the Oracle database there?

1. 2
2. 3
3. 4
4. 5

✅ Correct Answer: 3

A __________ is a single field or combination of fields that contains a unique record.

1. Compound Key
2. Primary key
3. Foreign Key
4. Index

✅ Correct Answer: 2

Which statement is used to create a table from an existing table by copying the columns of existing table?

1. CREATE TABLE new_table AS (SELECT * FROM old_table);
2. CREATE TABLE new_table (SELECT * FROM old_table);
3. CREATE TABLE new_table WHERE (SELECT * FROM old_table);
4. CREATE TABLE new_table LIKE (SELECT * FROM old_table);

✅ Correct Answer: 1

A ________ is created by a query joining one or more tables.

1. Trigger
2. Procedure
3. Both A and B
4. View

✅ Correct Answer: 4

Which query is used to add, modify, delete or drop colums of a table?

1. Update
2. Alter
3. Truncate
4. Drop

✅ Correct Answer: 2

Which clause is used to remove the duplicate records from the result set?

1. Where
2. Group by
3. Like
4. Distinct

✅ Correct Answer: 4

How many CREATE TRIGGER statements are there?

1. 4
2. 5
3. 6
4. 7

✅ Correct Answer: 3

view is a virtual table that does not physically exist.

1. TRUE
2. FALSE
3. Can be true or false
4. Can not say

✅ Correct Answer: 1

In your Oracle 10g database , you have scheduled a job to update the optimizer statistics at 05:00 pm every Friday. The job has successfully completed. Which three pieces of information would you check to confirm that the statistics have been collected? (Choose three.)

1. average row size
2. last analyzed date
3. size of table in bytes
4. size of table in database blocks

✅ Correct Answer: 1

You are in the middle of a transaction and very crucial data has been modified. Because of a hardware failure, the instance has shut down before synchronizing all the database files. Which two statements are true? (Choose two.)

1. On startup, SMON coordinates instance recovery.
2. On startup, CKPT coordinates instance recovery.
3. On startup, use RMAN to perform instance recovery.
4. Uncommitted changes will be rolled back after the database is opened.

✅ Correct Answer: 1

You are using the backup scheduler in Enterprise Manager (EM) to schedule a backup of your database. Which type of script does the backup scheduler generate?

1. SQL script
2. PL/SQL script
3. Operating System (OS) script
4. Recovery Manager (RMAN) script

✅ Correct Answer: 4

Which step do you need to perform to enable a user with the SYSDBA privilege to log in as SYSDBA in iSQL*Plus?

1. The user must be granted the database administrator (DBA) privilege.
2. The user must be listed in the password file for the authentication.
3. No special setup is needed for the user to connect as SYSDBA in iSQL*Plus.
4. Set up a user in the Oracle Application Server Containers for J2EE (OC4J) user manager, and grant the webDba role to the user.

✅ Correct Answer: 4

Which two statements regarding the LOGGING clause of the CREATE TABLESPACE. .. statement are correct? (Choose two.)

1. This clause is not valid for a temporary or undo tablespace.
2. If the tablespace is in the NOLOGGING mode, no operation on the tablespace will generate redo.
3. The tablespace will be in the NOLOGGING mode by default, if not specified while creating a tablespace.
4. The tablespace-level logging attribute can be overridden by logging specifications at the table, index, materialized view, materialized view log, and partition levels.

✅ Correct Answer: 4

Which two statements are true about the primary key constraint in a table? (Choose two.)

1. It is not possible to disable the primary key constraint.
2. It is possible to have more than one primary key constraint in a single table.
3. The primary key constraint can be referred by only one foreign key constraint.
4. The primary key constraint can be imposed by combining more than one column.

✅ Correct Answer: 4

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

1. control file
2. password file
3. listener controller file
4. control file and password file

✅ Correct Answer: 2

Which three statements are true regarding the logical structure of the Oracle database? (Choose three.)

1. Each segment contains one or more extents.
2. Multiple tablespaces can share single data file.
3. A data block is the smallest unit of I/O for data files.
4. It is possible to have tablespaces of different block sizes in a database.

✅ Correct Answer: 3

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

1. SGA
2. PGA
3. Shared Pool
4. Streams Pool

✅ Correct Answer: 2

You find that the database performance degrades while you backup the PROD database using Recovery Manager (RMAN). The PROD database is running in shared server mode. The database instance is currently using 60% of total operating system memory. You suspect the shared pool fragmentation to be the reason. Which action would you consider to overcome the performance degradation?

1. Configure Java Pool to cache the java objects.
2. Configure Streams Pool to enable parallel processing.
3. Increase Shared Pool size to cache more PL/SQL objects.
4. Configure Large Pool to be used by RMAN and shared server.

✅ Correct Answer: 4

You execute the following command to audit the database activities: SQL> AUDIT DROP ANY TABLE BY scott BY SESSION WHENEVER SUCCESSFUL; What is the effect of this command?

1. One audit record is created for the whole session if user SCOTT successfully drops one or more tables in his session.
2. One audit record is created for every session when any user successfully drops a table owned by SCOTT.
3. One audit record is created for each successful DROP TABLE command executed by any user to drop tables owned by SCOTT.
4. One audit record is generated for the session when SCOTT grants the DROP ANY TABLE privilege to other users in his session.

✅ Correct Answer: 1

The SAVE_AMT column in the ACCOUNTS table contains the balance details of customers in a bank. As part of the year-end tax and interest calculation process, all the rows in the table need to be accessed. The bank authorities want to track access to the rows containing balance amounts exceeding $200,000, and then send an alert message to the administrator. Which method would you suggest to the bank for achieving this task?

1. implementing value-based auditing by using triggers
2. implementing fine-grained auditing with audit condition and event handler
3. performing standard database auditing to audit object privileges by setting the AUDIT_TRAIL parameter to EXTENDED
4. performing standard database auditing to audit SQL statements with granularity level set to ACCESS

✅ Correct Answer: 2

Which three statements are true regarding the fine-grained auditing (FGA)? (Choose three.)

1. FGA is possible on SELECT statements only.
2. The audit trail for FGA is stored in the FGA_LOG$ table.
3. The audit trail for FGA is stored in the AUD_LOG$ table.
4. FGA enables a SQL predicate to define when to audit an event.

✅ Correct Answer: 4