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

Which of the following built-in packages supports dynamic SQL?

1. DBMS_SQLB. DBMS_SCHEDULER
2. DBMS_MONITOR
3. DBMS_SCHEDULER
4. DBMS_CRYPTO

✅ Correct Answer: 1

From which of the following programming languages are the features of PL/SQL programming fetched?

1. C++ programming
2. Java programming
3. C programming
4. Ada programming

✅ Correct Answer: 4

Which of the following is ideal for querying a database table or view?

1. Range FOR Loop statement
2. Cursor FOR Loop statement
3. While Loop statement
4. Simple Loop statement

✅ Correct Answer: 2

Which of the following errors maps to the INVALID_CURSOR predefined exception in PL/SQL?

1. ORA-10100
2. ORA-10001
3. ORA-01000
4. ORA-01001

✅ Correct Answer: 4

Which of the following is a sequentially control statement?

1. GOTO
2. LOOP
3. EXIT
4. case

✅ Correct Answer: 1

Which of the following cannot be created and stored in the database?

1. NESTED TABLE
2. VARRAY
3. RECORD
4. Associative array

✅ Correct Answer: 3

In which of the following naming methods are the connect identifiers stored in a localized configuration file named tnsnames.ora?

1. Oracle Names
2. Directory naming
3. Local naming
4. External naming

✅ Correct Answer: 3

You perform differential incremental level 1 backups of your database on each working day and level 0 backup on Sundays, to tape. Which two statements are true about differential incremental backups?

1. The backup performed on Sundays contains all the blocks that have ever been used in the database.
2. The backup performed on Sundays contains all the blocks that have changed since the last level 1 backup.
3. The backup performed on each working day contains all the blocks that have changed since the last level 0 backup.
4. The backup performed on Monday contains all the blocks that have changed since the level 0 backup, and every other working day contains all the blocks that have changed since the level 1 backup.

✅ Correct Answer: 4

Identify two situations in which you can use Data Recovery Advisor for recovery.

1. The database files are corrupted when the database is open.
2. The user has dropped an important table that needs to be recovered.
3. The archived redo log files are missing for which the backup is not available.
4. The database is not opening because the required database files are missing.

✅ Correct Answer: 1

Your database is open and the LISTENER listener is running. The new DBA of the system stops the listener by using the command: LSNRCTL> STOP What happens to the sessions that are presently connected to the database instance?

1. The sessions are able to perform only queries.
2. The sessions are not affected and continue to function normally.
3. The sessions are terminated and the active transactions are rolled back.
4. The sessions are not allowed to perform any operations till the listener is started.

✅ Correct Answer: 2

You executed this command to create a temporary table: SQL> CREATE GLOBAL TEMPORARY TABLE report_work_area (startdate DATE, enddate DATE, class CHAR(20)) ON COMMIT PRESERVE ROWS; Which statement is true about the rows inserted into the REPORT_WORK_AREA table during a transaction?

1. The rows stay in the table only until session termination.
2. The rows stay in the table only until the next transaction starts on the table.
3. The rows are visible to all current sessions after the transaction is committed.
4. The rows stay available for subsequent sessions after the transaction is committed.

✅ Correct Answer: 1

In which of the scenarios will the DBA perform recovery?

1. The alert log file is corrupted.
2. A tablespace is accidentally dropped.
3. One of the redo log members is corrupted.
4. The hard disk on which the data file is stored is corrupted.

✅ Correct Answer: 2

A user, who is authenticated externally, logs in to a remote machine and connects to the database instance. What action would you take to ensure that a user cannot connect to the database instance by merely logging in to a remote machine?

1. Set REMOTE_OS_ROLES to FALSE.
2. Set the OS_ROLES parameter to FALSE.
3. Set the REMOTE_OS_AUTHENT parameter to FALSE.
4. Set the REMOTE_LOGIN_PASSWORD_FILE parameter to NONE.

✅ Correct Answer: 3

Your database instance is started using the server parameter file (SPFILE). You executed a command to change the value of the LOG_BUFFER initialization parameter: ALTER SYSTEM SET LOG_BUFFER=32M SCOPE=BOTH; What would be the outcome of this command?

1. The command succeeds only if Automatic Memory Management is not enabled.
2. The command succeeds, but you need to restart the database for changes to take effect.
3. The command returns an error because the size of the redo log buffer cannot be changed dynamically.
4. The parameter value is changed and it comes into effect as soon as space becomes available in the System Global Area (SGA).

✅ Correct Answer: 3

User A executes the following command to drop a large table in your database: SQL> DROP TABLE trans; while the drop table operation is in progress, user B executes the following command on the same table: SQL> DELETE FROM trans WHERE tr_type='SL'; Which statement is true regarding the DELETE command?

1. It fails to delete the records because the records are locked in SHARE mode.
2. It deletes the rows successfully because the table is locked in SHARE mode.
3. It fails to delete the records because the table is locked in EXCLUSIVE mode.
4. It deletes the rows successfully because the table is locked in SHARE ROW EXCLUSIVE mode.

✅ Correct Answer: 3

Which is the correct description of a pinned buffer in the database buffer cache?

1. The buffer is currently being accessed.
2. The buffer is empty and has not been used.
3. The contents of the buffer have changed and must be flushed to the disk by the DBWn process.
4. The buffer is a candidate for immediate aging out and its contents are synchronized with the block contents on the disk.

✅ Correct Answer: 1

You are working on a new Oracle Database 11g server, where only the software is installed and no database is created. You plan to create a database on this server using the Database Configuration Assistant (DBCA). Some of the requirements set for your database creation task are: 1. Configure the database to operate in shared server mode. 2. Disable automatic maintenance tasks such as optimizer statistics collection. 3. Configure a naming method to help the remote user connect to the database instance. 4. Use Automatic Storage Management (ASM) for storing the database files. 5. Configure daily database backup to flash recovery area. 6. Configure Enterprise Manager Database Control to manage the database. Which of these requirements can be met while creating the database?

1. 4 and 6
2. 2, 3, 4, and 6
3. 1, 2, 4, 5, and 6
4. 1, 2, 3, 4, 5, and 6

✅ Correct Answer: 3

Which two are the prerequisites to enable Flashback Data Archive? (

1. Undo retention guarantee must be enabled.
2. Database must be running in archivelog mode.
3. Automatic undo management must be enabled.
4. The tablespace on which the Flashback Data Archive is created must be managed with Automatic Segment Space Management (ASSM).

✅ Correct Answer: 4

Which dependent object will get invalidated even if it is not affected by the table redefinition?

1. views
2. triggers
3. packages
4. synonyms

✅ Correct Answer: 2

You are managing an Oracle 11g database with ASM storage, for which the COMPATIBLE initialization parameter is set to 11.1.0. In the ASM instance, the COMPATIBLE.RDBMS attribute for the disk group is set to 10.2 and the COMPATIBLE.ASM attribute is set to 11.1. Which two statements are true in this scenario for the features enabled for ASM?

1. .The ASM-preferred mirror read feature is enabled.
2. The ASM supports variable sizes for extents of 1, 8, and 64 allocation units.
3. The ASM disk is dropped immediately from a disk group when it becomes unavailable.
4. The RDBMS always reads the primary copy of a mirrored extent of the ASM disk group.

✅ Correct Answer: 2

Which statement describes the effect of the execution of the above PL/SQL block?

1. The plan baselines are verified with the SQL profiles.
2. All fixed plan baselines are converted into nonfixed plan baselines.
3. All the nonaccepted SQL profiles are accepted into the plan baseline.
4. The nonaccepted plans in the SQL Management Base are verified with the existing plan baselines.

✅ Correct Answer: 4

Which is the source used by Automatic SQL Tuning that runs as part of the AUTOTASK framework?

1. SQL statements that are part of the AWR baseline only
2. SQL statements based on the AWR top SQL identification
3. SQL statements that are part of the available SQL Tuning Set (STS) only
4. SQL statements that are available in the cursor cache and executed by a user other than SYS

✅ Correct Answer: 2

Which two statements are true with respect to the maintenance window?

1. A DBA can enable or disable an individual task in all maintenance windows.
2. A DBA cannot change the duration of the maintenance window after it is created.
3. C.In case of a long maintenance window, all Automated Maintenance Tasks are restarted every four hours.
4. A DBA can control the percentage of the resource allocated to the Automated Maintenance Tasks in each window.

✅ Correct Answer: 4

What are the recommendations for Oracle Database 11g installation to make it Optimal Flexible Architecture (OFA)-compliant?

1. ORACLE_BASE should be set explicitly.
2. An Oracle base should have only one Oracle home created in it.
3. Flash recovery area and data file location should be on separate disks.
4. Flash recovery area and data file location should be created under Oracle base in a non-Automatic Storage Management (ASM) setup

✅ Correct Answer: 1

During the installation of Oracle Database 11g, you do not set ORACLE_BASE explicitly. You selected the option to create a database as part of the installation. How would this environment variable setting affect the installation?

1. The installation terminates with an error.
2. The installation proceeds with the default value without warnings and errors.
3. The installation proceeds with the default value but it would not be an OFA-compliant database.
4. The installation proceeds with the default value but a message would be generated in the alert log file.

✅ Correct Answer: 4

Which two statements are true regarding the starting of the database instance using the following command? SQL>STARTUP UPGRADE

1. It enables all system triggers.
2. It allows only SYSDBA connections.
3. It ensures that all job queues remain active during the upgrade process.
4. It sets system initialization parameters to specific values that are required to enable database upgrade scripts to be run.

✅ Correct Answer: 2

Your organization decided to upgrade the existing Oracle 10g database to Oracle 11g database in a multiprocessor environment. At the end of the upgrade, you observe that the DBA executes the following script: SQL> @utlrp.sql What is the significance of executing this script?

1. It performs parallel recompilation of only the stored PL/SQL code.
2. It performs sequential recompilation of only the stored PL/SQL code.
3. It performs parallel recompilation of any stored PL/SQL as well as Java code.
4. It performs sequential recompilation of any stored PL/SQL as well as Java code.

✅ Correct Answer: 3

You installed Oracle Database 11g and are performing a manual upgrade of the Oracle9i database. As a part of the upgrade process, you execute the following script: SQL>@utlu111i.sql Which statement about the execution of this script is true?

1. It must be executed from the Oracle Database 11g environment.
2. It must be executed only after the SYSAUX tablespace has been created.
3. It must be executed from the environment of the database that is being upgraded.
4. It must be executed only after AUTOEXTEND is set to ON for all existing tablespaces

✅ Correct Answer: 3

Which two statements about Oracle Direct Network File System (NFS) are true?

1. t bypasses the OS file system cache.
2. A separate NFS interface is required for use across Linux, UNIX, and Windows platforms.
3. It uses the operating system kernel NFS layer for user tasks and network communication modules.
4. File systems need not be mounted by the kernel NFS system when being served through Direct NFS.

✅ Correct Answer: 1

You decided to use Direct NFS configuration in a non-RAC Oracle installation and created the oranfstab file in /etc. Which two statements are true regarding this oranfstab file?

1. Its entries are specific to a single database.
2. It contains file systems that have been mounted by Direct NFS.
3. It is globally available to all Oracle 11g databases on the machine.
4. It contains file systems that have been mounted by the kernel NFS system.

✅ Correct Answer: 4

Which two statements are true regarding hot patching?

1. It requires relinking of the Oracle binary.
2. It does not require database instance shutdown.
3. It can detect conflicts between two online patches.
4. It is available for installing all patches on all platforms.

✅ Correct Answer: 3

Which alternative command could you use to run the startmydb.sql script with SQL*Plus to start your Oracle 10g database?

1. sqlplus @startmydb.sql
2. sqlplus -Sstartmydb.sql
3. sqlplus -S @startmydb.sql
4. sqlplus /NOLOG @startmydb.sql

✅ Correct Answer: 4

Examine the following parameter settings from an initialization (init.ora) file: DB_CREATE_FILE_DEST = '/u01/oradata/' DB_CREATE_ONLINE_LOG_DEST_1 = '/u02/oradata/' DB_CREATE_ONLINE_LOG_DEST_2 = '/u03/oradata/' If you create an Oracle Managed Files (OMF) database using these settings, what is the result?

1. The data files, temp files, and control file will be on device /u01; one online redo log group will be on device /u02; another redo log group will be on device /u03.
2. The data files and temp files will be on device /u01; one copy of the control file and one online redo log group will be on device /u02; another copy of the control file and a second redo log group
3. The data files, temp files, online redo log files, and control file will be on device /u01; multiplexed copies of the archive log files will be created: one set on device /u02, and another set on de
4. The data files and temp files will be on device /u01; one copy of the control file and the first member in each online redo log group will be on device /u02; another copy of the control file and a s

✅ Correct Answer: 2

What is true regarding a shared, server-side parameter file for a Real Application Clusters database?

1. It can contain parameters with distinct values for each instance.
2. It can contain only parameters with identical values for each instance.
3. It must contain an IFILE parameter for each instance's individual parameter file.
4. It must be located in the default location for the primary instance's parameter file.

✅ Correct Answer: 1

After changing the PCTFREE value of the automatic space management table EMPLOYEES, which task can you perform in order for your change to take effect immediately?

1. Execute the command ANALYZE TABLE employees COMPUTE STATISTICS.
2. Execute the command DBMS_STATS.GATHER_TABLE_STATISTICS on the EMPLOYEES table.
3. Execute the DBMS_REPAIR.SEGMENT_FIX_STATUS procedure on the EMPLOYEES table.
4. none of the mentioned

✅ Correct Answer: 3

You can use Oracle Flashback technology to look at past activity in your database. What are two other characteristics of Oracle Flashback technology?

1. Oracle Flashback technology uses undo information to construct consistent data.
2. You can use a PL/SQL cursor opened while using an Oracle Flashback image to perform DML once you deactivate the Flashback option in your session.
3. You can only use an Oracle Flashback view of the data if the required records are still in the online redo log files.
4. Oracle Flashback lists the DML that was executed during the period you identify when initiating your session.

✅ Correct Answer: 2

Which of the following types of message output contains a chronological log of errors, initialization parameter settings, and administration operations, and also records values for overwritten control file records?

1. RMAN messages
2. alert_SID.log
3. sbtio.log
4. Oracle trace file

✅ Correct Answer: 2

Which of the following correctly represents floating point number literals?

1. F6.34
2. 6.34
3. 6.F34
4. 6.34F

✅ Correct Answer: 4

Which of the following clauses indicates that a function is cached only once in the SGA and is available across sessions?

1. PIPELINED
2. PARALLEL_ENABLE
3. RESULT_CACHE
4. DETERMINISTIC

✅ Correct Answer: 3

Which of the following is another name for bind variable?

1. User-defined variable
2. System variable
3. Session-level variable
4. Default variable

✅ Correct Answer: 3

Which of the following exceptions is raised when the value of amount plus the offset exceeds the maximum size of LOB allowed by the database?

1. Procedure
2. Function
3. Cursor
4. Access

✅ Correct Answer: 4

Which of the following terms is used for the declaration section of the named-block programs?

1. Wrapper
2. Header
3. Declarer
4. Cursor

✅ Correct Answer: 2

All of the following statements about a named system exception are true except for which one?

1. These exceptions are caught by referencing the standard name within an exception-handling routine.
2. These exceptions are declared explicitly.
3. These exceptions are raised when a predefined Oracle error occurs.
4. NO_DATA_FOUND is an example of a named system exception.

✅ Correct Answer: 2

All of the following statements are true about an explicit cursor except for which one?

1. An explicit cursor can be declared in any declaration section of a PL/SQL block.
2. A cursor name is not a PL/SQL variable.
3. The name of an explicit cursor can be up to 30 characters in length.
4. Values can be assigned to a cursor.

✅ Correct Answer: 4

Which of the following errors is raised when placeholders are put inside the overriding signature of an INSERT statement?

1. ORA-01006
2. ORA-06502
3. ORA-00928
4. PLS-00049

✅ Correct Answer: 3

Which of the following is the first step in using a cursor?

1. Open the cursor
2. Fetch from the cursor
3. Define the cursor
4. Close the cursor

✅ Correct Answer: 3

All of the following statements about the RAISE_APPLICATION_ERROR procedure are true except for which one?

1. RAISE_APPLICATION_ERROR is used to create a unique id for a user-defined exception.
2. RAISE_APPLICATION_ERROR raises an exception and handles it.
3. RAISE_APPLICATION_ERROR is a built-in procedure in oracle which is used to display the userdefined error messages along with the error number.
4. Whenever a message is displayed using RAISE_APPLICATION_ERROR, all previous transactions which are not committed within the PL/SQL block are rolled back automatically.

✅ Correct Answer: 2

Which of the following operators allows users to check whether a VARRAY or NESTED TABLE collection is a subset of a mirrored datatype?

1. SUBMULTISET
2. MEMBER OF
3. EMPTY
4. ISA SET

✅ Correct Answer: 1

Which of the following correctly represents the default format mask for dates in PL/SQL programming?

1. DD-MON-YY
2. MON-DD-YY
3. MON-DD-YYYY
4. DD-MON-YYYY

✅ Correct Answer: 4

What are the two types of CASE statements in PL/SQL?

1. Simple and compound
2. Simple and iterative
3. Direct and indirect
4. Simple and searched

✅ Correct Answer: 4

Which of the following is NOT a part of a PL/SQL exception?

1. A return value
2. A message
3. Type of exception
4. An Error Code

✅ Correct Answer: 1

All of the following statements are true about an exception except for which one?

1. The scope of an exception declared within a block is global for the block and local for all of its sub blocks.
2. The sub-block can refer to the global exception only when the exception name is qualified with the block label (block_label.exception_name).
3. When a global exception is declared again within a sub-block, the local declaration takes priority over the global declaration.
4. An exception cannot be declared more than once in a single block.

✅ Correct Answer: 1