- 200 Oracle DBA Interview Questions, Answers and Proven Strategies for getting hired as an IT professional
- Dozens of examples to respond to interview questions
- 51 HR Questions with Answers and Proven strategies to give specific, impressive, answers that help nail the interviews
- 2 Aptitude Tests included on the CD-ROM
Answer:
We have to use sequence name with NEXTVAL variable and get that value in a variable using select into PL/SQL statement.
54: Is there any engine in SQL*Plus such as PL/SQL?
Answer:
PL/SQL are send directly to the database engine for execution so there is no engine in SQL*Plus.
55: What is the maximum limit on the size of a PL/SQL block?
Answer:
The maximum size of PL/SQL block is 64K and maximum code size is 100K.
59: What is the advantage of using Stored Procedure over SQL Query?
Answer:
Writing the SQL statements inside our code is usually not a good idea. In this way you expose your database schema (design) in the code which may be changed. Hence most of the time programmers use stored procedures instead of plain SQL statements. A stored procedure is a precompiled executable object that contains one or more SQL statements. Hence you can replace your complex SQL statements with a single stored procedure. Since, stored procedures are precompiled objects they execute faster at the database server. Most of the time, stored procedures contain more than one command; in this case, the time to pass the individual commands to the database server from the program is saved. The database is issued just one command (to execute the stored procedure) and the DB server executes all the commands and returns the result in the end. Hence, the overall interaction time with the DB server reduces in a great deal. This can result in a huge optimization in case where the DB server is accessed via a slow network.
60: Differentiate between LEFT JOIN and an INNER JOIN?
Answer:
The main difference between LEFT JOIN and INNER JOIN is that LEFT JOIN takes all the values from the first table and second table and INNER JOIN takes only matching values of two tables.
76: Explain Oracle Instance?
Answer:
An Oracle instance (also known as a database instance) contains the set of Oracle Database background processes that operate on the stored data and the shared allocated memory that those processes use to do their work.
An instance must be started to read and write information to the database. It is the instance that actually creates the database upon receipt of instructions from the Oracle Database Configuration Assistant (DBCA) utility or the CREATE DATABASE SQL statement. When the database instance is not available, your data is safe in the database, but it cannot be accessed by any user or application.
The properties of an Oracle instance are specified using instance initialization parameters. When the instance is started, an initialization parameter file is read, and the instance is configured accordingly.
81: What do you mean by Cluster Key?
Answer:
A Cluster Key is the column or columns that cluster tables have in common, and which is chosen as the storage / access key. For example two tables, WORKER and WORKERSKILL, might be clustered on the column name. A cluster key is the same thing as a cluster column.
82: What are the elements of base configuration of an Oracle database?
Answer:
The Base configuration of an Oracle database consists of:
a) one or more data files,
b) control files,
c) redo log files,
d) schemas,
e) tablespaces,
f) dictionary tables,
g) SGA
124: Tell me something about SGA?
Answer:
A System Global Area (SGA) is a group of shared memory structures that contains data and control information for one Oracle database instance. If multiple users are concurrently connected to the same instance, then the data in the instance's SGA is shared among the users. Consequently, the SGA is sometimes called the Shared Global Area.
An SGA and Oracle processes constitute an Oracle instance. Oracle automatically allocates memory for an SGA when you start an instance, and the operating system reclaims the memory when you shut down the instance. Each instance has its own SGA.
The SGA is read/write. All users connected to a multiple-process database instance can read information contained within the instance's SGA, and several processes write to the SGA during execution of Oracle.
The SGA contains the following data structures:
a) Database buffer cache
b) Redo log buffer
c) Shared pool
d) Java pool
e) Large pool (optional)
f) Streams pool
g) Data dictionary cache
h) Other miscellaneous information
125: What do you mean by shared pool?
Answer:
At the most fundamental level, the shared pool is a metadata cache. The shared pool is used for caching complex objects describing where the data is stored, how it relates to other data and how it can be retrieved. In simple words, the area in SGA in which data dictionary cache is stored is called shared pool. The shared pool portion of the SGA contains the library cache, the dictionary cache, buffers for parallel execution messages, and control structures.The total size of the shared pool is determined by the initialization parameter SHARED_POOL_SIZE. The default value of this parameter is 8MB on 32-bit platforms and 64MB on 64-bit platforms. Increasing the value of this parameter increases the amount of memory reserved for the shared pool.
