oracle pl sql interview questions for 3+ years experience
41.What is
Referential Integrity and Referential integrity constraint ?
Ans:
Referential Integrity : Referential integrity defines the relationships among
different columns and tables in a relational database. It’s called referential
integrity because the values in one column or set of columns refer to or must
match the values in a related column or set of columns.
A
referential integrity constraint requires that for each row of a table, the
value in the foreign key matches a value in a parent key.
42.What is
groups by and having clause? Explain with example
Ans: Group
by clause tells oracle to group rows based on distinct values that exists for
specified columns. The group by clause creates a data set , containing several
sets of records grouped together based on condition.
Having
Clause: Having clause can be used with GROUP BY clause. Having imposes a
condition on the group by clause which further filters the group created by the
GROUP BY clause. Select ename,empno From Empl Group by empno having empno >
10;
43.What
are LOCKS? What are types of different types of Lock?
Ans: Locks
are mechanisms intended to prevent destructive interaction between users
accessing ORACLE data. ORACLE uses locks to control concurrent access to data.
Locks are used to achieve two important database goals : Consistency : Ensures
that the data a user is viewing or changing is not changed (by other users)
until the user is finished with the data. Integrity : Ensures that the
database's data and structures reflect all changes made to them in the correct
sequence.
Types of
Locks :
1. Data
Locks (DML)
2.
Dictionary Locks (DDL)
3.
Internal Locks and Latches
4.
Distributed Locks
5.
Parallel Cache Management Locks
Data Locks
: Row Level and Table Level Row Level : Exclusive Locks Table Level
1. Row
Share Table Locks (RS)
2. Row
Exclusive Table Locks (RX)
3. Share
Table Locks (S)
4. Share
Row Exclusive Table Locks (SRX)
5.
Exclusive Table Locks (X)
Dictionary
Locks :
1.
Exclusive DDL Locks
2. Share
DDL Locks
3.
Breakable Parse Locks Restrictiveness of Locks : In general, two levels of
locking can be used in a multi-user database: • Exclusive Locks : An exclusive
lock prohibits the sharing of the associated resource. The first transaction to
exclusively lock a resource is the only transaction that can alter the resource
until the exclusive lock is released. • Share Locks : A share lock allows the
associated resource to be shared, depending on the operations involved (e.g.,
several users can read the same data at the same time). Several transactions
can acquire share locks on the same resource. Share locks allow a higher degree
of data concurrency than exclusive locks.
44.Difference
between unique key,primary key and foreign key ?
Ans:
Foreign key: A foreign key is one or more columns whose values are based on the
primary or candidate key values from another table. Unique key can be null;
Primary key cannot be null.
45.What are
Advantages of TRUNCATE Command over DELETE/DROP TABLE Command ?
Ans: The
TRUNCATE command provides a fast, efficient method for deleting all rows from a
table or cluster.
1. A
TRUNCATE statement does not generate any rollback information and it commits
immediately; it is a DDL statement and cannot be rolled back.
2. A
TRUNCATE statement does not affect any structures associated with the table
being truncated (constraints and triggers) or authorizations (grants).
3. A
TRUNCATE statement also specifies whether space currently allocated for the
table is returned to the containing tablespace after truncation.
4. As a
TRUNCATE statement deletes rows from a table (or clustered table), triggers
associated with the table are not fired.
5. Also, a
TRUNCATE statement does not generate any audit information corresponding to
DELETE statements if auditing is enabled. Instead, a single audit record is
generated for the TRUNCATE statement being issued.
46.What are
steps involved in Execution of SQL statements?
Ans: STEPS
IN EXECUTION OF SQL STATEMENTS :
1. Create
a cursor
2. Parse
the statement
3.
Describe Results
4.
Defining outputs
5. Bind
any variables
6. Execute
the statement
7. Fetch
rows of a query result
47.What do
you mean by Parsing?
Ans:
Parsing : Parsing is the process of: 1. Translating a SQL statement, verifying
it to be a valid statement 2. Performing data dictionary lookups to check table
and column definitions 3. Acquiring parse locks on required objects so that
their definitions do not change during the statement's parsing 4. Checking
privileges to access referenced schema objects 5. Determining the execution
plan to be used when executing the statement 6. Loading it into a shared SQL
area 7. For distributed statements, routing all or part of the statement to
remote nodes that contain referenced data
48.What is a
HINT and what are types HINT?
Ans: Hints
are suggestions that you give the optimizer for optimizing a SQL statement.
Hints allow you to make decisions usually made by the optimizer.
TYPES
OF HINTS :
ALL_ROWS
: The ALL_ROWS hint explicitly chooses the cost-based approach to optimize a
statement block with a goal of best throughput.
FIRST_ROWS
: The FIRST_ROWS hint explicitly chooses the cost-based approach to optimize a
statement block with a goal of best response time.
FULL
: The FULL hint explicitly chooses a full table scan for the specified table.
ROWID :
The ROWID hint explicitly chooses a table scan by ROWID for the specified
table.
CLUSTER :
The CLUSTER hint explicitly chooses a cluster scan to access the specified
table.
HASH
: The HASH hint explicitly chooses a hash scan to access the specified table.
INDEX :
The INDEX hint explicitly chooses an index scan for the specified table.
AND_EQUAL:
The AND_EQUAL hint explicitly chooses an execution plan that uses an access
path that merges the scans on several single-column indexes. (You can specify
multiple indexes through this hint) INDEX_ASC: The INDEX_ASC hint explicitly
chooses an index scan for the specified table. If the statement uses an index
range scan, ORACLE scans the index entries in ascending order of their indexed
values.
INDEX_DESC:
The INDEX_DESC hint explicitly chooses an index scan for the specified table.
If the statement uses an index range scan, ORACLE scans the index entries in
descending order of their indexed values.
ORDERED
: The ORDERED hint causes ORACLE to join tables in the order in which they
appear in the FROM clause.
USE_NL :
The USE_NL hint causes ORACLE to join each specified table to another row
source with a nested loops join using the specified table as the inner table.
USE_MERGE
: The USE_MERGE hint causes ORACLE to join each specified table with another
row source with a sort-merge join.
49.What do u
mean by EXCEPTION_INIT Pragma ?
Ans:
EXCEPTION_INIT Pragma : To handle unnamed internal exceptions, you must use the
OTHERS handler or the pragma EXCEPTION_INIT. A "pragma" is a compiler
directive, which can be thought of as a parenthetical remark to the compiler.
Pragmas (also called "pseudoinstructions") are processed at compile
time, not at run time. They do not affect the meaning of a program; they simply
convey information to the compiler. The predefined pragma EXCEPTION_INIT tells
the PL/SQL compiler to associate an exception name with an Oracle error number.
That allows you to refer to any internal exception by name and to write a
specific handler for it. You code the pragma EXCEPTION_INIT in the declarative
part of a PL/SQL block, subprogram, or package
using the
syntax PRAGMA EXCEPTION_INIT(exception_name, Oracle_error_number); where
"exception_name" is the name of a previously declared exception. For
internal exceptions, SQLCODE returns the number of the associated Oracle error.
The number that SQLCODE returns is negative unless the Oracle error is "no
data found," in which case SQLCODE returns +100. SQLERRM returns the
message associated with the Oracle error that occurred. The message begins with
the Oracle error code. For user-defined exceptions, SQLCODE returns +1 and
SQLERRM returns the message “User-Defined Exception” unless you used the pragma
EXCEPTION_INIT to associate the exception name with an Oracle error number, in
which case SQLCODE returns that error number and SQLERRM returns the
corresponding error message. The maximum length of an Oracle error message is
512 characters including the error code, nested messages, and message inserts
such as table and column names.
What do u
mean by JSP query?
Ans: JSP
Query : The JSP Query is a standard query for number to words conversion, used
especially for converting amount in number into equivalent amount in words. The
query is as follows : Select to_char ( to_date ( ‘&no’, ‘J’ ), ‘JSP’ )
words from dual; For eg : Select to_char ( to_date ( '23949','j' ), 'JSP' )
"words" from dual; The value that can pass to &no cannot exceed 7
digits.
50.Describe
Oracle database’s physical and logical structure ?
No comments:
Post a Comment