Oracle Database Multiple Choice Questions and Answers on Installing Oracle Database 11g and Creating a Database for Freshers

1. Oracle server configuration is dedicated for

a) One server process – Many user processes
b) Many server processes – One user process
c) One server process – One user process
d) Many server processes – Many user processes

Answer: c

Explanation: Single-process Oracle is a database system in which all Oracle code is executed by one process. Different processes are not used to separate execution of the parts of Oracle and the client application program. Instead, all code of Oracle and the single user’s database application is executed by a single process.

2. What SYSTEM VARIABLE is used to refer DATABASE TIME ?

a) $$dbtime$$
b) $$time$$
c) $$datetime$$
d) None of the Mentioned

Answer: a

Explanation: The DB time Oracle metric is the amount of elapsed time (in microseconds) spent performing Database user-level calls.

3. You notice that the database instance takes a long time to start up after the instance crash. How will you resolve the problem?

a) Increase the size of the redo log files
b) Decrease the number of redo log members
c) Set LOG_CHECKPOINT_TO_ALERT to TRUE
d) Decrease the value for the FAST_START_MTTR_TARGET initialization parameter

Answer: d

Explanation: FAST_START_MTTR_TARGET enables you to specify the number of seconds the database takes to perform crash recovery of a single instance.
FAST_START_MTTR_TARGET contains below information.

Property                  Description
Parameter type            :Integer
Default value             :0
Modifiable                :ALTER SYSTEM
Range of values           :0 to 3600 seconds
Basic                     :No
Real Application Clusters :Multiple instances can have different values, 
                           and you can change the values at run-time.

4. Which statement is correct regarding undo management?

a) The database can have more than one undo tablespaces
b) The undo data must be purged manually when the transaction is over
c) The UNDO_TABLESPACE parameter is valid only if the UNDO_MANAGEMENT parameter is set AUTO OFF
d) Undo management is automatic by default even if the UNDO_MANAGEMENT initialization parameter is set to NULL

Answer: a

Explanation: Oracle provides an automatic undo management, for managing undo information and space. With automatic undo management, the database manages undo segments in an undo table-space.

5. Which operations can be performed using the Data Recovery Ad-visor?

a) Diagnosing data failure
b) It is a percentage of rows in which the statistics are collected incrementally for partitions
c) Presenting appropriate repair options
d) Generating reports for possible causes of failure

Answer: a

Explanation: The Data Recovery Ad-visor automatically diagnoses corruption or loss of persistent data on disk, determines the appropriate repair options, and executes repairs at the user’s request. This reduces the complexity of recovery process, thereby reducing the Mean Time To Recover (MTTR).

6. A user receives the following error while executing a query:
ORA-01555: snapshot too old
Predict one way to avoid such errors in near future.

a) Increase the size of redo log files
b) Increase the size of the undo tablespace
c) Increase the size of the Database Buffer Cache
d) Increase the size of the default temporary tablespace

Answer: b

Explanation: The error occur because of size in the undo retention, can be overcome by increasing the size of your rollback segment (undo) size.Also can be resolve by increase the setting of UNDO_RETENTION. Otherwise, use larger rollback segments.

7. You want to be warned automatically when more than 100 sessions are opened with your database instance.Identify the action that would help you achieve this.

a) Set the TRACE_ENABLED parameter to TRUE
b) Set the threshold for the Current Logons Count metric
c) Set the LOG_CHECKPOINT_TO_ALERT parameter to TRUE
d) Set the SESSIONS_PER_USER limit in the profiles used by users

Answer: b

Explanation: A metric is defined as the rate of change in some cumulative statistic. This rate can be measured against a variety of units, including time, transactions, or database calls. For example, the number database calls per second is a metric.

8. While the database instance is up and running, you receive an out-of-memory error due to the under-sizing of the shared pool. You checked the trace file and observed that the following error was always recorded during peak hours:

 ORA-04031: unable to allocate 4000 bytes of shared memory 

On investigation, you found that SGA_MAX_SIZE was larger than the sum of the sizes of all System Global Area (SGA) components and you do not have the scope to increase it further. Identify a solution to reduce the probability of getting this error in future.

a) Set the PRE_PAGE_SGA parameter to TRUE
b) Set the LOCK_SGA initialization parameter to TRUE
c) Implement Automatic Shared Memory Management
d) Set the SGA_TARGET initialization parameter to zero

Answer: c

Explanation: Cause: More shared memory is needed than was allocated in the shared pool.Action : If the shared pool is out of memory, either use the DBMS_shared_pool package to pin large packages, reduce your use of shared memory, or increase the amount of available shared memory by increasing the value of the INIT.ORA parameters

9. You created a new user on the database by executing the following command:

SQL> CREATE USER user01 IDENTIFIED BY user01 
DEFAULT TABLESPACE users 
TEMPORARY TABLESPACE temp; 

Then you granted the following privileges to user01 by executing the following command: 

SQL> GRANT CREATE SESSION, CREATE TABLE TO user01; 

Which of the following is true in this scenario?

a) The user can not create tables
b) The user can create tables
c) The user cannot query any tables
d) None of the Mentioned

Answer: b

Explanation: GRANT command give access to user.GRANT CREATE SESSION, CREATE TABLE TO user01 get the access for that session to create tables.

10. 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?
a) MEMORY_TARGET defines the maximum limit for SGA_TARGET
b) SGA_TARGET and PGA_AGGREGATE_TARGET combined will never grow beyond 500M
c) The value for MEMORY_MAX_TARGET is the sum of SGA_TARGET and PGA_AGGREGATE_TARGET
d) SGA_TARGET and PGA_AGGREGATE_TARGET will keep growing till a maximum of 300M and 70M, respectively

Answer: b

Explanation: In a text initialization parameter file, if you omit the line for MEMORY_MAX_TARGET and include a value for MEMORY_TARGET, the database automatically sets MEMORY_MAX_TARGET to the value of MEMORY_TARGET. If you omit the line for MEMORY_TARGET and include a value for MEMORY_MAX_TARGET, the MEMORY_TARGET parameter defaults to zero. After start-up, you can then dynamically change MEMORY_TARGET to a nonzero value, provided that it does not exceed the value of MEMORY_MAX_TARGET.